CODEWITHSUNDEEP
mysqlconnection
magl1te
magl1te

Reputation: 119

Should I open a a MySQL Connection every request or always?

Assuming I'll execute every 2 seconds a query, should I open the connection on every request or should I keep the connection alive until the application(server) stops?

Upvotes: 6

Views: 2224

Answers (1)

erik258
erik258

Reputation: 16302

In my experience, establishing connections is unlikely to be a bottleneck for a mysql server (connection overhead is fairly low in mysql). That having been said, reusing existing connections is often an appropriate approach, but it requires some careful considerations: if the database server is temporarily unavailable, the code must reconnect; if the server is replaced, it must reconnect (mysql implementations tend towards failover solutions rather than true high availability); if the application uses multiple connections to mysql, you must be sure not to cross your connections between users or sessions (active database, timezone, charset, and so on are sessions variables, essentially tied to a connection). If you're not up to the task of making your reusable connection reliable in these and other edge cases, creating a new connection every 2 seconds may provide this durability for free.

In short, there can be less-than-obvious benefits to short lived connections. I would not bother to add intelligence around maintaining a persistent connection unless you have reason to believe it will actually make a meaningful difference in your case (eg benchmarks).

Upvotes: 7

Related Questions