multiple connections on MySQL database with no users on httpd

Question

My database seems to have many open connections despite having no users on httpd.

mysql> SHOW PROCESSLIST;
+-------+----------+--------------------+---------------------+---------+------+-------+------------------+
| Id    | User     | Host               | db                  | Command | Time | State | Info             |
+-------+----------+--------------------+---------------------+---------+------+-------+------------------+
| 22865 | rdsadmin | localhost:51066    | mysql               | Sleep   |    8 |       | NULL             |
| 25098 | ubuntu   | 123.45.67.89:48060 | database_name_here! | Sleep   |  350 |       | NULL             |
| 25099 | ubuntu   | 123.45.67.89:48069 | database_name_here! | Sleep   | 3731 |       | NULL             |
| 25126 | ubuntu   | 123.45.67.89:49558 | database_name_here! | Sleep   |  349 |       | NULL             |
| 25161 | ubuntu   | 123.45.67.89:50770 | database_name_here! | Sleep   |  289 |       | NULL             |
| 26583 | ubuntu   | 123.45.67.89:37593 | database_name_here! | Sleep   | 4550 |       | NULL             |
| 29729 | ubuntu   | 123.45.67.89:49280 | database_name_here! | Sleep   |  953 |       | NULL             |
| 29737 | ubuntu   | 123.45.67.89:49489 | database_name_here! | Sleep   |  953 |       | NULL             |
| 29738 | ubuntu   | 123.45.67.89:49490 | database_name_here! | Sleep   |  349 |       | NULL             |
| 29755 | ubuntu   | 123.45.67.89:50674 | database_name_here! | Sleep   | 2751 |       | NULL             |
| 29759 | ubuntu   | 123.45.67.89:50892 | database_name_here! | Sleep   |  600 |       | NULL             |
| 35600 | ubuntu   | 123.45.67.89:49677 | NULL                | Query   |    0 | NULL  | SHOW PROCESSLIST |
+-------+----------+--------------------+---------------------+---------+------+-------+------------------+

I don't understand how there are so many connections. I'm using amazon EC2 with RDS, apache/mysql/PHP with PDO. I have persistent connections "on", but I thought this simply cached the connection. It seems somehow 10 connections remain open constantly even though there are 1-2 users on my site at any given time.

What is the cause of this, and how can I fix it? Is this normal behavior, are there a minimum amount of connections that MySQL keeps open to handle different users? I cannot seem to find any information on how to analyze what is going on.

Answer 1

You will need to look into how your connection pool is configured in your PHP web app. Even a fully functional connection pool will often keep many connections to a database open even when they are not in use. Opening connections to the database can be very expensive for a web app and it makes sense to have some unused ones sitting around.

Most connection pools have settings for things like:

• Minimum number of connections to keep open
• Maximum number of connections to keep open
• Number of connections to open when new connections are needed

---

There is also the possibility that your web app is not releasing connections back to the connection pool properly. When you are done with your connections, you need to close them to let the pool know that they are available. Not doing so is a a kind of resource leak and you will eventually run out of connections.

Answer 2

You can see that command = 'Sleep' these indicates that your server's connection thread are idle and is waiting for the client to send a new statement to it.

You do not have to worry about it. Make sure that max_connections value is more than default and your application closes connection after it's internal operation.