Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

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.

share|improve this answer
    
I've asked similar questions to peers before, and they have reassured me that leaving connections open is fine as PHP will automatically close them for you. Is that true? –  user29447 Oct 16 '13 at 22:02

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.