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.
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”.
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.
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)
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!