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.
How about:
SELECT * from performance_schema.accounts;
See:
http://dev.mysql.com/doc/refman/5.6/en/accounts-table.html
-- Marc Alff,
Oracle.
Link | June 21st, 2012 at 10:14 am
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
Link | June 21st, 2012 at 10:29 am
@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.
Link | June 21st, 2012 at 10:32 am
@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.
Link | June 21st, 2012 at 5:33 pm
pt-mysql-summary can also display this information.
Link | June 21st, 2012 at 6:00 pm
@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.
Link | June 21st, 2012 at 6:06 pm
Log Buffer #275, A Carnival of the Vanities for DBAs | The Pythian Blog wrote:
[...] Shlomi Noach is blogging about a real world experience about MySQL connections. [...]
Link | June 22nd, 2012 at 10:01 am
@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.
Link | June 25th, 2012 at 9:21 pm
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)
Link | June 26th, 2012 at 2:28 am
@Ike,
Cool. Yes, I completely forgot about it!
Link | June 26th, 2012 at 6:52 am
@Rick,
As do I; but the aforementioned connections were not idle. Due to some bug they continuously pinged the server.
Link | June 26th, 2012 at 6:53 am