MySQL connection count much larger than query count

Question

I'm maintaining a relatively simple LAMP server. It does how ever have a some what high load.

What I'm noticing is that in a one hour time span there are roughly 50k connections made to the mysql server, yet there are only 17k-18k actual queries made. There are only 12 aborted clients.

I've enabled the general log, but I couldn't see any empty connections.

I don't maintain the PHP scripts that connect to the database myself, but I do have access to them. They always execute at least one query.

The bulk of the connections are made by a php script that does:

Connect to mysql
Execute one very fast query
Close connection

So the connection hardly ever show up in the processlist.

There are no connections left open. netstat only shows the 3 connections made from my own computer. The processlist shows the same thing.

Does anyone have suggestions on how I could trace the 34k connections made, that apparently don't do anything.

Here is a screenshot from the phpMyAdmin Status tab:

Answer 1

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:

• http://bugs.mysql.com/bug.php?id=36872
• http://bugs.mysql.com/bug.php?id=56979
• http://www.webhostingtalk.com/showthread.php?t=251177

Answer 2

SELECT * 
FROM processlist 
WHERE Command <> 'Query' 
AND info <> 'SHOW FULL PROCESSLIST' 
ORDER BY time DESC

This will tell you connections (connection thread) list which are not executing queries, you can run this command periodically or schedule it.