What may hang even SHOW PROCESSLIST?

MySQL & MariaDB Other MySQL® Questions
1

As this is my 1st post, first of all hello everyone!

I am already planning to contact Percona for consulting services, but first there’s one thing I’d like to ask.

FACT 1:
To try monitoring a very rare and random slowdown issue I’ve created a special page that performs various checks and display results: the very first one is connecting to the MySql server and echoing the output of “SHOW PROCESSLIST”.

FACT 2:
I’ve max_connections=1000, and in PhpMyAdmin I can see that max concurrent connections is 249. So, unless I’m misunderstanding something, it looks like I never ran out of connections.

FACT 3:
The rare times I was able to “be there” while the slowdown was happening, I observed a delay between connection to MySql and the result of the SHOW PROCESSLIST query to be > 6 seconds. Normally it’s 0.1.
Slowdowns solve with no action in less than a minute normally, so there’s simply not enough time to do any extra manual investigation (there are 6 separate webservers connecting to that MySql machine)

THE QUESTION:
With MyISAM I know slowdowns could happen because of table locks.
What may be the cause of such a delay for SHOW PROCESSLIST, as AFAIK it should not be affected by table locks?
Being sort of “system query” I was expecting it to be always responsive, while for some reason in such rare events it isn’t.
I ran out of ideas here… what I know:

  • max connections not reached
  • disks are not full
  • there’s free memory
  • CPU is not overwhelmed

The behavior seems really the same as table locks for MyISAM writes… any idea about which scenarios may cause such a sporadic slowdown even when trying to execute SHOW PROCESSLIST?

Thanks in advance, and Happy 2013!

2

Try pt-stalk instead of using your own monitoring tools.

Some easy guesses are swapping or high disk utilization (iops).

3

I do not consider myself a newbie, so this is for whoever is like me.
We often forget to check basic things, while figuring the most complicated issues!

Long story short, the issue was caused by reverse DNS. My bad, I forgot to add skip-name-resolve in my.cnf
The sporadic slowdown was because another server behind the same router was under DOS attacks and this prevented the non-private connectivity of my MySql server too.

It’s really a “check if the cable is plugged in first”-like issue, so I hope this thread will at least be of help to anyone else having a similar problem.

Thanks again!