Bug #41901 Unable to kill queries
Submitted: 6 Jan 2009 18:49 Modified: 19 Mar 2009 20:15
Reporter: Pavel Baranov Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.30 OS:Linux (64Bit)
Assigned to: CPU Architecture:Any
Tags: KILL, queries, Query cache related lockup: many connections

[6 Jan 2009 18:49] Pavel Baranov
Description:
2 problems (sort of related):

1. Mysql Master Server locks up, I cannot kill any queries it just hangs with Status: Killed
I managed to get Show InnoDB status; while this was happening. (See Attached File)

2. Tried to log in as root got this:
ERROR 1040 (HY000): Too many connections
HOW THE HELL ??? I thought root has at least one reserved connection!!!!???

How to repeat:
1. No idea
2. Fill up the server and try to log in as root

Suggested fix:
n/a
[6 Jan 2009 18:51] Pavel Baranov
innodb status

Attachment: node6-innodb-status.txt (text/plain), 61.81 KiB.

[11 Jan 2009 18:31] Valeriy Kravchuk
Thank you for a problem report. Please, send your my.cnf and error log content for this period, if you have it.
[14 Jan 2009 21:02] Pavel Baranov
Error log has absolutely nothing in that period of time.
[15 Jan 2009 16:10] Valeriy Kravchuk
KILL does not work immediately (see http://dev.mysql.com/doc/refman/5.0/en/kill.html). Maybe your system was just overloaded? If this will ever happen again, please, get als othe results of

vmstat 10 10

during that period.

Please, send also the results of:

free

Linux command. Some my.cnf settings look weird enough for me.
[21 Jan 2009 17:33] Pavel Baranov
well, it seems like that it's out of memory problem. based on the formual 
key_buffer = (sort + read) * max_con ....
+ innodb_buffer_pool(8000MB) ... becomes way over 16GB that the system has.

The only question that I have is why 5.0.x didn't have that problem, it would just become extremely slow but still worked.
[22 Jan 2009 19:09] Pavel Baranov
master (node6) vs. slave (node05), node05 never crashed

Attachment: node6_vs_node5.zip (application/zip, text), 2.17 KiB.

[26 Jan 2009 22:53] Pavel Baranov
I have changed max_connections to 250
and 
sort_buffer_size = 6M
read_buffer_size = 3M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 356M
key_buffer = 512M
innodb_buffer_pool_size = 6000M

SAME PROBLEM!!!! All of a sudden everything locks up, cannot connect with root ( max conns. error ), and cannot kill queries.

the only way is till kill with -9 !!!

this is insane!

We've just subscribed to MySQL Enterprise Basic, please help !
[24 Feb 2009 1:28] Ian White
I'm seeing a similar problem, posted another bug: http://bugs.mysql.com/bug.php?id=42951
Though it's my slave that's locking up. 5.0 had no problems, 5.1 is locking up and can't kill queries.
[24 Feb 2009 15:04] Pavel Baranov
I'm still trying to figure out what the problem is.
All those other bugs seem related.

One thing I've noticed:
When we moved to 5.1 - we partitioned 5 tables (by range).
The locking kept happening over and over again, and the only way to "unlock" mysql was to kill -9 ... not a great solution.

So I removed partitioning from 3 tables. Now the locking is happening once in a while BUT i'm able to kill some of the queries and the master restores to normal 
:)
this is a step forward.
I'm thinking removing partitioning completely and test again.
[16 Mar 2009 17:38] Pavel Baranov
Partitioning was removed, everything has become more stable BUT locking is still happening!

so two issues here
1. Everything locks and I'm unable to kill any query... the status changes to "Killed" and just hangs forever.
2. Max connections is reached in NO TIME! Once that happens I'm unable to connect with "root" - how the hell is this even possible... in your documentation it clearly states 1 connection is reserved for root!
[19 Mar 2009 9:08] Sergei Golubchik
The manual doesn't say that one connection is reserved for root, it says that one connection is reserved for a user with SUPER privileges. If all your accounts have SUPER, then of course root doesn't have any advantage here.
[19 Mar 2009 19:23] James Day
If you think you may be affected by this, turn off the query cache with SET GLOBAL query_cache_size = 0; and please tell us here whether it solves your problem. We'll be adding a note about a new bug report once we've produced a suitable test case for it.
[19 Mar 2009 19:31] Ian White
Would the suggested fix work for this bug http://bugs.mysql.com/bug.php?id=42951 ?
[19 Mar 2009 19:35] Ian White
Just got the comment on the other thread. Seems it might :)
I'm running gdb now.
[19 Mar 2009 20:07] Pavel Baranov
Its been 3 days since:
"If you think you may be affected by this, turn off the query cache with SET GLOBAL
query_cache_size = 0; and please tell us here whether it solves your problem. We'll be
adding a note about a new bug report once we've produced a suitable test case for it."

no problems what so ever... pushing the server to run 800 threads - no hanging, no nothing - just the way it was before with 5.0 :)
[19 Mar 2009 20:15] James Day
The bug that is specific to the query cache has now been opened, see bug #43758. With that bug opened, I'm now closing this as a duplicate.

Pavel, good to read that it's still looking good for you. With luck this will close three different existing bug reports that we have been investigating.
[8 Nov 2012 23:41] Mannoj S
Sometimes this works - 
UNLOCK tables;

Ensure no table is in_use/locked in "show open tables;"

Just escaped from intruding from a nightmare ..