Connection pooling with xorm and go-mysql

Question

Just cross-posting this from github.

I am using xorm 0.4.3 with go-mysql. We are on Golang 1.4.

We have specified maxIdleConnetions and maxOpenConnections in xorm as below:-

var orm *xorm.Engine
...
orm.SetMaxOpenConns(50)
orm.SetMaxIdleConns(5)

And we are using the same single xorm instance to query Mysql.

But still we are seeing lot of connections in TCP Connection Establised state which is way over the numbers I have configured in maxIdleConnetions and maxOpenConnections state when we lsof:-

app 8747 10568 sandeshsharma 16u IPv4 691032 0t0 TCP 127.0.0.1:57337->127.0.0.1:mysql (ESTABLISHED)

We have also observed that even if we stop the MySQL, the connection numbers still remain fixed but in the CLOSED_WAIT state. If we shutdown the app then all connections go away.

app 8747 10844 sandeshsharma 38u IPv4 505058 0t0 TCP 127.0.0.1:54160->127.0.0.1:mysql (CLOSE_WAIT)

However in mysql process list it is showing the correct number of connections as I have specified in maxIdleConnetions and maxOpenConnections.

Can some one please explain me this behaviour? Why are we observing so much TCP connections even though we have specified maxIdleConnetions and maxOpenConnections to 5 & 50 respectively?

methane · Accepted Answer · 2016-07-24 08:48:10Z

1

First of all, Go 1.4 is too old. Use latest Go 1.6. This answer is written with knowledge of Go 1.6. So some details may be different in your case.

There are four state in connection: connecting, idle, inuse, and closing. MaxOpenConnections limits number of connection in connecting, idle, inuse state. So, if your application closes and reopen connection quickly, it can happen.

Since TCP is CLOSED_WAIT state in MySQL server side, your app is waiting EOF from connection. I suppose your app is under very high load and slow at reading EOF and closing connection. Until read EOF and close connection, TCP state is ESTABLISHED on client side, regardless TCP state in server side.

I recommend you to update Go and "go-sql-driver/mysql", and set MaxIdleConns equal to MaxOpenConns to avoid high reconnect rate. Instead, you can use SetConnMaxLifetime (new API in Go 1.6) to close connections when your app is idle.

edited Jul 24, 2016 at 8:48

answered Jul 21, 2016 at 9:06

methane

4893 gold badges5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tuk Over a year ago

Yes you are correct seeing this only when app is under heavy load. But the doubt I am having is even though I have specified maxOpenConnections to 50. But under load I am seeing open connections way above 50 that too in ESTABLISHED state as I have posted in my question. However in mysql process list it is showing 50. Why is this happening?

methane Over a year ago

Your app sends COM_CLOSE packet to MySQL. MySQL closes connection. But your app doesn't read EOF yet. TCP connection is alive in ESTABLISHED state until application reads EOF. Since your app is under heavy loads, your app can't read EOF quickly.

tuk Over a year ago

Can you please update your answer with the comments? One last query how will it help if I set maxIdleConnections equal to maxOpenConnection?

methane Over a year ago

> Can you please update your answer with the comments? I did it. > One last query how will it help if I set maxIdleConnections equal to maxOpenConnection? It already explained my answer: " to avoid high reconnect rate". I don't want to explain why small maxIdleConns cause high reconnection rate. Everyone should notice it if think carefully.

Collectives™ on Stack Overflow

Connection pooling with xorm and go-mysql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related