MySQL “Too Many Connections”
By Dan S. on Apr. 18, 2008.
There isn't a day that goes by without someone, somewhere, reaching the maximum number of concurrent connections to their MySQL database. Generally, the solution is to increase this value; However this is not the best solution, nor does it alway help.
What MySQL users need to understand is that queries generally execute very fast. A busy server should really never need more than 200 concurrent connections for any given user. The trick in solving this problem is to understand why it is happening. What is stopping your queries from executing fast?
Many times, the problem is as simple as your daily backups running. If your database is large and the backup takes 10 or 15 minutes to process, then all of the queries coming into the database will queue up and wait for the backup to finish. Processing 20 - 50 queries per second, it's not hard to do the math - your server could easily hit 200 connections queued waiting for the backup to finish - within seconds.
Solutions to this case can range from the simple, for instance, scheduling your backups to run at a time where you do not expect any traffic - to the more complex such as using replication as a backup aid. (Replication is something I will get into in later postings.)
Lastly, a backup of connections can always be caused from table locking issues, or slow queries (they take a long time). Running 1 complex query which takes 300 - 500 seconds to complete may indeed lock your table, preventing other queries from accessing the same data. You can always get a hint at what kind of queries are taking a long time to execute by logging the slowest of them. This "slow-query-log" can be enabled in your MySQL configuration file, commonly located at /etc/my.cnf by adding the following under the "[mysqld]" section:
log-slow-queries=/the/log/file/path
Where /the/log/file/path is wherever you want the file kept. You must restart MySQL after adding this setting.
When analyzing this file, it's always helpful to use "mysqldumpslow". I'm not going to give you all the answers, but if you want to learn how to use it to help analyze your slow query log, try:
mysqldumpslow --help
Category: MySQL