MySQL Too Many Connections – Debugging Guide

databaseMySQL

I get a "too many connections" error from time to time with my mysql database, if I do a

show status like 'Conn%';

I get a result like this

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 291   | 
+---------------+-------+
1 row in set (0.00 sec)

but if I look at the processlist with

SHOW FULL PROCESSLIST;

I only see a few real connections

+-----+---------+------------------------------+---------+---------+------+-------+-----------------------+
| Id  | User    | Host                         | db      | Command | Time | State | Info                  |
+-----+---------+------------------------------+---------+---------+------+-------+-----------------------+
|  28 | www     | webhost01:38876              | webapp  | Sleep   |    2 |       | NULL                      | 
|  29 | www     | webhost01:53322              | webapp  | Sleep   |    2 |       | NULL                      | 
|  30 | www     | webhost01:45583              | webapp  | Sleep   |    2 |       | NULL                      | 
|  31 | www     | webhost01:35779              | webapp  | Sleep   |    2 |       | NULL                      | 
|  32 | www     | webhost01:49122              | webapp  | Sleep   |    2 |       | NULL                      | 
|  36 | root    | localhost                    | NULL    | Query   |    0 | NULL  | SHOW FULL PROCESSLIST     | 
| 126 | stats   | localhost                    | stats   | Sleep   | 1056 |       | NULL                      | 
+-----+---------+------------------------------+---------+---------+------+-------+-----------------------+

How can I see all connections? I suspect that some applications don't close their connections, how can I debug/prevent this?

Best Answer

The SHOW STATUS value that you want to look at is Threads_connected. Because Connections shows only the accumulated number of connections since the daemon started.

It's often important to compare the value of Max_used_connections against the SHOW VARIABLES values max_connections and max_user_connections.

Your process ID count from SHOW PROCESSLIST isn't very high, which suggests that one of those values may be set unreasonably low, unless MySQL has been restarted since the error last arose.

Related Topic