Mysql – multiple connections on MySQL database with no users on httpd

max-connectionsMySQLPHP

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.

Best Answer

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.

Related Solutions

MySQL connection count much larger than query count

Here is something interesting to remember : the status variables Questions takes a count of all queries executed by mysqld, regardless of who issued each query.

Why do I say regardless of who issued each query ?

There are queries that are launched internally by mysqld. Here is a simple example:

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 56    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Com_select'; show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 15    |
+---------------+-------+
1 row in set (0.00 sec)

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 58    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 59    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global status like 'Questions';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 60    |
+---------------+-------+
1 row in set (0.00 sec)

mysql>

Please note that issuing a simple query like show global status like 'Questions'; is a query even if your are just requesting internal data from mysqld.

Multiply this by any number of DB Connections coming from MONyog, Munin, Nagios, MySQL Enterprise Monitor, or any other MySQL Monitoring tools and will always have a steady rise in the number of lightweight queries being executed.

I wrote about this before in ServerFault in a question "1 billion mysql queries in 24 hours? Can something be wrong?"

Queries can quitely come and go. The question left to answer is, why so many DB Connections? You need to make sure Apache is closing DB Connections at the same rate as mysqld. You may need to see run netstat and look for DB Connections to mysql that have the TIME_WAIT status.

There are bug reports on this going back to MySQL 4.1 although the root of the problem may actually be with PHP:

MySQL server connections capped at 1013

We had the same issue. Put this at the top of the mysql start script.

ulimit -u 102400 -n 102400

Has to be at mysql start time.

Best Answer

Related Solutions

MySQL connection count much larger than query count

MySQL server connections capped at 1013

Related Question