Mysql 连接池问题

发表于 2017-04-26

| 0 Comments

最近应用日志里发现了mysql偶尔会出现问题

[mysql] 2017/04/26 10:01:05 packets.go:130: write tcp 127.0.0.1:56346->127.0.0.1:3306: write: broken pipe
[mysql] 2017/04/26 10:01:05 packets.go:130: write tcp 127.0.0.1:56346->127.0.0.1:3306: write: broken pipe
[mysql] 2017/04/26 10:01:05 packets.go:130: write tcp 127.0.0.1:56350->127.0.0.1:3306: write: broken pipe
[mysql] 2017/04/26 10:01:05 packets.go:130: write tcp 127.0.0.1:56350->127.0.0.1:3306: write: broken pipe

找GitHub issues, 提到了和mysql的wait_timeout变量有关系, https://github.com/go-sql-driver/mysql/issues/446, 于是找MySQL文档https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#idm140549060476496.

相关说明如下:
The number of seconds the server waits for activity on a noninteractive connection before closing it.
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout.

默认是28800s, 8小时.

mysql> show variables like '%wait_timeout%';                                                                                                                                                                                                                      
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set (0.00 sec)

解决办法:

db.SetConnMaxLifetime(time.Hour*7)