Skip to content

# Who is hogging my MySQL connections?

MySQL common_schema June 21, 2012June 21, 2012

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
11
Leave a Reply

11 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
6 Comment authors
Recent comment authors

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

Subscribe
Notify of
Guest
Marc Alff

How about:

SELECT * from performance_schema.accounts;

See:
http://dev.mysql.com/doc/refman/5.6/en/accounts-table.html

— Marc Alff,
Oracle.

Author
shlomi

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

Author
shlomi

@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.

Guest
Sinisa Milivojevic

@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.

Guest
Justin Swanhart

pt-mysql-summary can also display this information.

Author
shlomi

@Sinisa,
You’re absolutely right about this. With InnoDB Plugin + I_S plugins enables this is solvable. May integrate this onto common_schema.

@Justin,
Very cool. pt-mysql-summary is a very useful tool.

Log Buffer #275, A Carnival of the Vanities for DBAs | The Pythian Blog

[…] Shlomi Noach is blogging about a real world experience about MySQL connections. […]

Guest
Ike Walker

@shlomi, regarding your desire for “killing of processes using user name / host name / combination / regex”, you can probably do that with pt-kill using the –match-user and –match-host options.

Guest
Rick James

I like to keep @@global.wait_timeout low enough so that those connections that fail to disconnected get booted.

Also, if Apache is involved, I like to keep MaxClients at a reasonable level. Note: You need to have ((#apaches) * MaxClients) < ((#slaves) * max_connections)

Author
shlomi

@Ike,
Cool. Yes, I completely forgot about it!

Author
shlomi

@Rick,
As do I; but the aforementioned connections were not idle. Due to some bug they continuously pinged the server.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.