CODEWITHSUNDEEP
mysqlgo
Zhixin Wen
Zhixin Wen

Reputation: 105

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

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?

Upvotes: 1

Views: 1463

Answers (1)

methane
methane

Reputation: 479

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)

Upvotes: 3

Related Questions