Who is hogging my MySQL connections?

Got “too many connections” this morning. New attempts continuously abort. Every once in a while some slipped through, but overall behavior was unacceptable.

max_connections is set to 500, well above normal requirements.

Immediate move: raise max_connections to 600, some urgent connections must take place. But, this is no solution: if 500 got hogged, so will the extra 100 I’ve just made available.

So, who’s to blame? SHOW PROCESSLIST is so unfriendly at that. Wait. Didn’t I create that view in common_schema, called processlist_per_userhost? I wonder what it says…

SELECT * FROM common_schema.processlist_per_userhost;
+-------------+------------------+-----------------+------------------+---------------------+
| user        | host             | count_processes | active_processes | average_active_time |
+-------------+------------------+-----------------+------------------+---------------------+
| maatkit     | sqlhost02.myweb  |               1 |                0 |                NULL |
| rango       | webhost04.myweb  |               2 |                0 |                NULL |
| rango       | webhost07.myweb  |               8 |                0 |                NULL |
| rango       | sqlhost02.myweb  |              38 |                0 |                NULL |
| rango       | management.myweb |              35 |                0 |                NULL |
| rango       | webhost03.myweb  |              10 |                0 |                NULL |
| rango       | local01.myweb    |               8 |                0 |                NULL |
| rango       | analytic02.myweb |              11 |                0 |                NULL |
| mytop       | localhost        |               2 |                0 |                NULL |
| buttercup   | sqlhost02.myweb  |             451 |                5 |              0.0000 |
| replc_user  | sqlhost00.myweb  |               1 |                1 |         392713.0000 |
| replc_user  | sqlhost02.myweb  |               1 |                1 |          38028.0000 |
| root        | localhost        |               2 |                0 |                NULL |
| system user |                  |               2 |                2 |         196311.5000 |
+-------------+------------------+-----------------+------------------+---------------------+

Ah! It’s buttercup connecting from sqlhost02.myweb who is making a fuss. I knew that view was created for a reason.

The is easy enough to solve, some iterative process got hanged, so I just killed it.

But – additional mental note for common_schema: allow killing of processes using user name / host name / combination / regex, instead of gathering the process IDs then killing them one by one.

11 thoughts on “Who is hogging my MySQL connections?

  1. Marc, cool! Wasn’t aware of this table in 5.6

    Of course, a few years will pass before everyone is updated with a MySQL 5.6 server.

    Keep up the good work

  2. @Marc,

    Taking this chance to throw in a suggestion: add a “active_connections” columns to indicate the numebr of connections not sleeping, for the “accounts”, “hosts”, “users” tables.

  3. @schlomi

    I personally have a small problem with sleeping connections.

    A connection can be sleeping while still running a non-committed transaction. Yet another column can be invented, like “Total of active transactions”, but I do not think it would help much.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.