英文:
Golang sql.DB WaitCount greater than 0 even when there are enough idle connections in the pool
问题
我正在查看一个使用Golang编写的Web应用程序的DBStats。这些指标每10秒钟通过sqlstats导出到Prometheus。
在应用程序中，MaxOpenConns设置为100，MaxIdleConns设置为50。当我查看指标时，我注意到打开连接的数量稳定在50左右。这是预期的，意味着我们保持了50个空闲连接。然而，正在使用的连接数在0和5之间徘徊，并且大部分时间都为0。这对我来说很奇怪，因为有持续的流量涌入，我不希望正在使用的连接数为0。
此外，我注意到WaitCount和MaxIdleClosed非常大。WaitCount表示没有空闲连接了，而sql.DB由于MaxOpenConns限制无法打开更多连接。但从上面的统计数据来看，sql.DB似乎有足够的余地来创建更多的连接（OpenConnections远低于MaxOpenConnections）。而MaxIdleClosed的大数字也表明即使有足够的空闲连接，sql.DB仍在创建额外的连接。
与此同时，我在应用程序中观察到一些driver: bad connection错误，我们正在使用MySQL。
为什么在有足够的空闲连接时应用程序会尝试打开更多的连接，以及我应该如何调整数据库参数以减少这个问题？
英文:
I am looking at the DBStats of a web application in Golang. The metrics is exported to prometheus every 10s by sqlstats.
In the application, MaxOpenConns is set to 100, and MaxIdleConns is set to 50. And when I look into the metrics, I notice the number of open connections is stable around 50. This is expected, which means we are keeping 50 idle connections. However, the number of InUse connection is hovering between 0 and 5, and is 0 for most of the time. This is strange to me, because there is a constant inflow of traffic, and I don't expect the number of InUse connections to be 0.
Also, I notice WaitCount and MaxIdleClosed are pretty large. WaitCount means there is no idle connections left and sql.DB cannot open more connections due to MaxOpenConns limit. But from the stats above, there seems to be more than enough of headroom for sql.DB to create more connections (OpenConnections is way below MaxOpenConnections ). The big number of MaxIdleClosed also suggests sql.DB is making additional connections even when there are enough idle connections.
At the same time I am observing some driver: bad connection errors in the app and we are using MySQL.
Why does the app try to open more connections when there are enough idle connections around, and how should I tune the db param to reduce the issue?
答案1
得分: 3
然而，InUse连接的数量在0和5之间徘徊，并且大部分时间都为0，这对我来说很奇怪，因为有持续的流量输入，我不希望InUse连接的数量为0。
这并不奇怪。InUse连接的数量会像尖峰一样变动。由于您每10秒才获取一次统计数据，所以您看不到这个尖峰。
当有足够的空闲连接时，为什么应用程序会尝试打开更多的连接？
请参考https://github.com/go-sql-driver/mysql#important-settings
建议将db.SetMaxIdleConns()设置为与db.SetMaxOpenConns()相同或更大。当它小于SetMaxOpenConns()时，连接的打开和关闭频率可能会超出您的预期。
如何调整数据库参数以减少这个问题？
遵循go-sql-driver/mysql README中的建议。
使用db.SetConnMaxLifetime()，并将db.SetMaxIdleConns()设置为与db.SetMaxOpenConns()相同。
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(100)
db.SetConnMaxLifetime(time.Minute * 3)
英文:
> However, the number of InUse connection is hovering between 0 and 5, and is 0 for most of the time. This is strange to me, because there is a constant inflow of traffic, and I don't expect the number of InUse connections to be 0.
It is not strange. The number of InUse connections moves like spikes. Since you get stats only at every 10s, you just don't see the spike.
> Why does the app try to open more connections when there are enough idle connections around,
See https://github.com/go-sql-driver/mysql#important-settings
"db.SetMaxIdleConns() is recommended to be set same to (or greater than) db.SetMaxOpenConns(). When it is smaller than SetMaxOpenConns(), connections can be opened and closed very frequently than you expect."
> and how should I tune the db param to reduce the issue?
Follow the recommendation of the go-sql-driver/mysql README.
Use db.SetConnMaxLifetime(), and set db.SetMaxIdleConns() same to db.SetMaxOpenConns().
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(100)
db.SetConnMaxLifetime(time.Minute * 3)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论