英文:
what is the best solutions to mysql connection timeout?
问题
我正在使用Go编写一个小型Web应用程序，它使用mysql来存储数据。
如果Web服务器在一段时间后（> 8小时）没有收到任何请求，我会遇到间歇性的mysql错误：
[mysql] 2017/02/08 16:31:56 packets.go:33: unexpected EOF
[mysql] 2017/02/08 16:31:56 packets.go:130: write tcp 127.0.0.1:49188->127.0.0.1:3306: write: broken pipe
我在github上找到了一些相关的讨论（问题529，问题257和问题446）。据我所了解，如果达到超时时间，mysql数据库会关闭连接。
我尝试将SetMaxOpenConns设置为9，并将SetMaxIdleConns设置为0，因为有些人推荐这样做。然而，这立即引发了异常。（但是，如果我将SetMaxIdleConns设置为大于0，就不会立即引发异常）
我还尝试将SetConnMaxLifetime设置为5分钟。这也在5分钟后引发了异常。
现在我正在尝试以下代码：
db.SetConnMaxLifetime(0)
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
它已经运行了20分钟。现在还为时过早。（更新：这也不起作用）
以下是配置信息：
- 驱动程序：go-sql-driver V1.3。
- Go版本：go1.7.1 darwin/amd64
- mysql：来自docker hub的最新版本
- rkt版本：1.18
- CoreOS：1284.0.0
英文:
I am writing a small web app in Go, which uses mysql to store data.
I got intermittent mysql error if the web sever didn't get any request after some amount of time(> 8 hours):
[mysql] 2017/02/08 16:31:56 packets.go:33: unexpected EOF
[mysql] 2017/02/08 16:31:56 packets.go:130: write tcp 127.0.0.1:49188->127.0.0.1:3306: write: broken pipe
I found some related discussion on github(issue 529, issue 257 and issue 446). From what I understand, mysql db would close the connection if timeout is reached.
I tried to set SetMaxOpenConns to 9 and SetMaxIdleConns to 0 as some people recommended. However, this threw exception immediately. (But if I set SetMaxIdleConns larger than 0, there was no immediate exception thrown)
I also tried to set SetConnMaxLifetime to 5 mins. This threw exception too after 5 mins.
Now I am trying the code below:
db.SetConnMaxLifetime(0)
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
It has been running for 20 mins. It's still too early to tell.
(UPDATE: this doesn't work either)
Here is configuration:
- driver: go-sql-driver V1.3.
- go version: go1.7.1 darwin/amd64
- mysql: latest from docker hub
- rkt version: 1.18
- CoreOS: 1284.0.0
答案1
得分: 3
你可以检查你的MySQL的time_wait变量：
mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 300 |
+---------------+-------+
1 row in set (0.00 sec)
然后使用db.SetConnMaxLifetime(120*time.Second)，这意味着当数据库连接空闲超过120秒时，sql.db将重新打开或从数据库连接池中获取一个新的连接。如果你没有设置连接的最大生命周期，你可能会使用一个已关闭的连接并得到错误。
通过观察MySQL的进程列表，mysql> show processlist;，如果连接的休眠时间超过300秒，它将被MySQL回收：
mysql> show processlist;
+-------+-----------------+------------------+-------------+---------+---------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+------------------+-------------+---------+---------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 1363480 | Waiting on empty queue | NULL |
| 26539 | root | 172.17.0.1:48732 | NULL | Query | 0 | starting | show processlist |
| 26575 | auditcenter | 172.17.0.1:51714 | obs_gb_test | Sleep | 51 | | NULL |
+-------+-----------------+------------------+-------------+---------+---------+------------------------+------------------+
3 rows in set (0.00 sec)
SetMaxOpenConns和SetMaxIdleConns用于设置连接资源，参考这里。
英文:
-
you can check your mysql time_wait variable:
<pre>
mysql> show global variables like 'wait_timeout':
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 300 |
+---------------+-------+
1 row in set (0.00 sec)
</pre> -
then use
db.SetConnMaxLifetime(120*time.Second), which mean when db connection is idle over than 120s, sql.db will reopen or get a new connection from db pool bydb.Open. If you not set connection max life time, you maybe use a closed connection and got the error. -
watching the mysql process list，
mysql> show processlist;，if connection sleep over than 300s，it's recycled by mysql:<pre>
mysql> show processlist;
+-------+-----------------+------------------+-------------+---------+---------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+------------------+-------------+---------+---------+------------------------+------------------+
| 4 | event_scheduler | localhost | NULL | Daemon | 1363480 | Waiting on empty queue | NULL |
| 26539 | root | 172.17.0.1:48732 | NULL | Query | 0 | starting | show processlist |
| 26575 | auditcenter | 172.17.0.1:51714 | obs_gb_test | Sleep | 51 | | NULL |
+-------+-----------------+------------------+-------------+---------+---------+------------------------+------------------+
3 rows in set (0.00 sec)
</pre> -
SetMaxOpenConnsandSetMaxIdleConnsis used for setting connection resource, see enter link description here
答案2
得分: 2
也许你可以启动一个心跳 Goroutine 来避免超时。
英文:
Perhaps you can start a heartbeat Goroutine to avoid timeout.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论