Golang sql.DB WaitCount greater than 0 even when there are enough idle connections in the pool

Question

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?

In the question above you have mentioned active connections. But in DBStats there is no parameter with name as active connections. It would be easily understandable if you can describe the issue using the terms used in the package you are using. For example by active connections do you mean InUse connections?? — Sai Ravi Teja K
– Sai Ravi Teja K, Commented May 23, 2021 at 3:55

methane · Accepted Answer · 2021-05-23 21:32:12Z

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)

Collectives™ on Stack Overflow

Golang sql.DB WaitCount greater than 0 even when there are enough idle connections in the pool

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related