December 13, 2002
Persistent database connections are a great idea, but in my experience often used where they don't belong. The result: reduced performance. I can understand the appeal of a perceived free performance enhancement, but there are trade-offs to keep in mind.
MySQL AB has published a little article about using persistent connections with MySQL, discussing some of the things to consider before implementing persistent connections. MySQL's connection handling is often fast enough that they're not necessary.
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.
The lesson: benchmark your application and make optimizations only when necessary and appropriate.
