3

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?

mzehrer
  • 133
  • 1
  • 5
  • How do I run these commands if I already have too many connections? If I try to launch mysql I once again get "too many connections". – riv May 21 '19 at 10:59

2 Answers2

2

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.

Dan Carley
  • 25,189
  • 5
  • 52
  • 70
0

The "Connections" variable is a connection counter, it is zero when the mysql server starts. see the manual

lg.
  • 4,579
  • 3
  • 20
  • 20