MySQL Newsletter Using persistent connections with MySQL

Persistent connections is a concept, which is designed to improve performance for some application. Instead of connection being established for each operation, it is opened once and kept in pool for the applications lifetime.

Persistent connections concept allows elimination of several types of overheads, e.g. authentication is performed only once, and so extra network traffic is saved. Some internal structures have to be allocated to handle extra connection. Finally there could be some connection level caches, which need to be filled up for proper performance.

MySQL is tuned to keep all of these overheads low. Authentication in MySQL is rather fast, which takes only one roundtrip and small amount of traffic. The slowest operation involved is connection creation. Creation of the OS level thread is eliminated with thread cache. Freed threads are not freed to OS but kept in the pool to be reused by new connections. Most of the caches in MySQL are kept global. Even tables opened by connection are kept in the table cache to be recycled. As MySQL has fast connections handles, in most cases performance benefit of using persistent connection is low.

Are there any drawbacks of persistent connections?
Unfortunately there are. At first each connections takes up resources. This includes MySQL internal resources (memory, table cache elements, connections) as well as OS level resources - threads, open files and sockets. Some operating systems are quite OK with this, while others may show performance degradation. MySQL resources are often more important. By having the same amount of memory that would be used for persistent connections, you usually can configure MySQL to use larger buffers, which often gives extra performance. Will this outweigh the benefit from persistent connections? It completely depends on the application.

Persistent connections can also lead you to the trouble if you're not using them carefully enough. Some issues described here are handled automatically by good persistent connection support, while others are not. If you've started transaction but did not complete it, the result could be unpredictable - based on what next connection user will do first commit or rollback transaction. Exactly the same issue happens with locked tables. They could be locked stale leading to various issues. Moreover other thread specific settings are left - connection specific variables (sort_buffer,record_buffer...), server level variables (@my_variable), state dependend functions found_rows(), last_insert_id() etc.

If you're planning to use persistent connections and are using features similar to the ones listed above, you should check if your persistent connections support library handles these issues and if it does not, you need to fix them manually.

Due to the nature of persistent connections, biggest benefit by using them can be achieved over slow network connections and with a few quick queries per connection. If you have complex queries, which take long time to execute or more than 3-5 queries per connection, persistent connections will give you almost no benefit.

Conclusion: Instead of blindly enabling persistent connections in MySQL you may wish to benchmark your application to see if persistent connections really give you the performance benefit, and if it is large enough to justify for the limitations introduced by them.