MySQL Bugs: #41901: Unable to kill queries

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

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

innodb status

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

Thank you for a problem report. Please, send your my.cnf and error log content for this period, if you have it.

Error log has absolutely nothing in that period of time.

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.

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.

master (node6) vs. slave (node05), node05 never crashed

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

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 !

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.

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.

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!

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.

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.

Would the suggested fix work for this bug http://bugs.mysql.com/bug.php?id=42951 ?

Just got the comment on the other thread. Seems it might :)
I'm running gdb now.

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 :)

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.

Sometimes this works - 
UNLOCK tables;

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

Just escaped from intruding from a nightmare ..