0

I want to know if there is any parameter to show the current number of mysql connections (or peak value in the past 10 minutes or something else). Right now, I only see max and max_used connections.

mysql> show global status like '%connection%';
+-----------------------------------+---------------------+
| Variable_name                     | Value               |
+-----------------------------------+---------------------+
| Connection_errors_accept          | 0                   |
| Connection_errors_internal        | 0                   |
| Connection_errors_max_connections | 434                 |
| Connection_errors_peer_address    | 0                   |
| Connection_errors_select          | 0                   |
| Connection_errors_tcpwrap         | 0                   |
| Connections                       | 2380515             |
| Max_used_connections              | 152                 |
| Max_used_connections_time         | 2020-07-09 19:28:43 |
+-----------------------------------+---------------------+
9 rows in set (0.00 sec)

mysql> show global variables like '%connection%';
+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_connection | latin1            |
| collation_connection     | latin1_swedish_ci |
| max_connections          | 300               |
| max_user_connections     | 0                 |
+--------------------------+-------------------+
3
  • 1
    How about SHOW PROCESSLIST;?
    – Kondybas
    Jul 12 '20 at 7:53
  • See also dba.stackexchange.com/questions/270718/…
    – Rick James
    Aug 29 '20 at 22:30
  • SHOW GLOBAL STATUS LIKE '%connect%'; will also reveal details like threads_connected that could be helpful for your research. And SHOW GLOBAL VARIABLES LIKE '%thread%'; may be useful and SHOW GLOBAL VARIABLES LIKE '%connect%' could be useful for your research. Sep 3 '20 at 14:04
0

max_connections limits the number of connections overall. But usually many are in "Sleep" mode, therefore encurring virtually no load.

There is one "extra" connection allowed -- this lets 'root' get in to see what is going wrong, even when the max is hit.

Max_used_connections is a high-water-mark toward max_connections.

Threads_running may be closest to what you are looking for. It is the current number of connections not in "Sleep". It is often "1", namely the connection you are using to read the value. A value of "10" is rather high. "100" probably means that MySQL is stumbling over itself.

SHOW PROCESSLIST gives you hints of some of the above info, plus clues of what queries are running.

Connections is a counter that started when the server started. Divide it by Updtime to get an average of new connections. 1/second is pretty typical. 100/second is high, but not necessarily 'bad'.

There is no metric to recent "recent", etc. The would require a monitoring program or script. I would not do SHOW GLOBAL STATUS more than once a minute, else it might have an adverse impact on the system.

Connection_errors_max_connections a counter that is optimally 0. Since you have 434 and Max_used_connections = 152, I will guess that you started max_connections with the default of 151, hit the error, then raised max_connections to 300. Am I right?

Raising to 300 is fine, though not necessary on 'most' machines. You should also see if having lots of connections is justified by your app. Maybe something else is going wrong.

A common issue is to have the webserver that talks to MySQL allow lots of connections. It then simply assumes it can hand things off to MySQL; instead, it should allow fewer connections to the webserver itself.

If Threads_running gets high, response time will probably suffer. The likely remedy is to find "slow" queries via the slowlog. The fix may be a better index or a reformulation of a query. Alas, Threads_running is hard to monitor.

For the slowlog: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Another tunable: wait_timeout controls how quickly an idle connection will be disconnected. Check that the clients are disconnecting when finished.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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