0

A webserver (Apache + MySQL) locked so badly that I had to powercycle it, and immediately logged in to see what was going on afterward. Load average was over 3%, with MySQL hovering between 20% and 80% of the CPU. Curious, I ran SHOW PROCESSLIST; during a spike of activity, only to find...

+-------+--------+-----------+--------+---------+------+-------+------------------+
| Id    | User   | Host      | db     | Command | Time | State | Info             |
+-------+--------+-----------+--------+---------+------+-------+------------------+
| 10762 | root   | localhost | NULL   | Query   |    0 | NULL  | SHOW PROCESSLIST |
| 11754 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11755 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11756 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11758 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11759 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11761 | elba   | localhost | elba   | Sleep   |    0 |       | NULL             |
| 11763 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11764 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11765 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11766 | phorum | localhost | phorum | Sleep   |    0 |       | NULL             |
| 11767 | phorum | localhost | phorum | Sleep   |    0 |       | NULL    

Just for kicks, I enabled the slow query log and there's nothing there, either. Load average (as well as MySQL's resource usage) has dropped back to normal levels since, but it's still unclear what happened.

Any insight as to what happened? Failing that, what should I log to help get to the bottom of it next time?

  • What Version of Mysql you are using and also can you please let us know the Operating System? – Abhishek Anand Amralkar Feb 20 '13 at 10:48
  • MySQL 5.1.58 on CentOS 5.1. – Sudowned Feb 20 '13 at 20:05
  • If you are using 32 bit CentOS then it is known issue , I would rather suggest either use Mysqltuner.pl or mysqlprimer to see what parameters in mysql are not adjusted correctly and set them accordingly. Also if you are on 32 bit OS start planning to migrate to 64 bit OS. – Abhishek Anand Amralkar Feb 21 '13 at 2:11
  • Nope, this is definitely 64-bit. – Sudowned Feb 23 '13 at 1:40
2

Just for kicks, I enabled the slow query log and there's nothing there, either.

Have you enabled the log-queries-not-using-indexes option?

I would also suggest you set the long_query_time to zero, then install the Percona Toolkit and run:

pt-query-digest /path/to/the/slow/log | tee $HOME/mysql-slow_`date +%F`.profile

to see what happens.

  • I'll install Percona and do that. The issue is intermittent and unpredictable, so I won't be able to give it a shot until the server melts again. – Sudowned Feb 20 '13 at 20:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.