mysql server hanging on 'show processlist;'

Question

I've got a problem where my mysql server gets into a state where basic operations hang forever.

$ mysql -ubuildbot -p -hdbserver
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7406
Server version: 5.5.9-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mydb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show processlist;
[hangs forever]
^C^C^C^C^C^C^C^C^C
[still stuck]

No, it's not out of disk space. CPU usage is low.

iostat output:

Linux 2.6.28-11-server (PSDB102)    04/22/2011  _x86_64_    (4 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           3.65    0.01    0.62    0.09    0.00   95.74

Answer 1

I'd recommend you take a look at the error_log, it might give you some hints on what's going on. If the error_log doesn't output any useful info, then strace comes to the rescue.

Use it like this:

strace -f -o strace.output -p pid_of_mysqld_parent

This will trace all trace all system calls made by the MySQL server process and its children (hence the -f flag) and output them to a file named strace.output. Then, while runnning strace, connect to MySQL and follow the steps above until the process hangs. After it hangs, type CTRL+C on the strace terminal and take a look at the file. This might enlighten the way and give you some clues on what's going on.

The problem might be on the client side as well, so you might want to strace the client if the output above isn't helpful:

 strace -f -o strace.output mysql -ubuildbot -p -hdbserver

Then, you just have to take a look at strace.output after it hangs.

Hope this helps!