0

Hey guys, I've got a web server running Apache/PHP and MySQL (CentOS), and MySQL seems to hang once every month or so. As far as I can tell, there are a few slow queries that are being resolved, but other than that I can't really see any reason why MySQL would hang. I'm having problems determining the problem - nothing is showing up in /var/log/mysqld.log, and again there are a few slow queries, but nothing out of the ordinary. Load is average at the time of the crashes...

Can I please get some hints on how to work out the issue? I can't reproduce on our staging environment, so I'm a little stuck.

4 Answers 4

1

This isn't really an answer but a suggestion: Install a monitoring tool like Munin with the MySQL plugin and watch usage and memory, CPU, TCP connections, etc. leading up to and at the time of the crash. This has been invaluable to me in similar situations.

Installing Munin on CentOS

0

Can you define "stuck" ? Ie, is it dead and not responding to any queries what so ever? Can you not even get a process list of what queries it is running?

Or is it chewing huge amounts of CPU/memory and just grinding the server to a halt or continually crashing or what?

What state is the process in ? And can you strace the daemon process ?

Probably need a bit more insight as to what it is doing to give any real good advice.

3
  • When I run the 'top' command, I get mysqld running on 100% CPU... When it's running normally, it doesn't use up any CPU at all! I haven't run a 'SHOW PROCESSLIST;' yet while it's crashing, so that's the next step. I'll also try 'strace', but it's hard to do on a production server where the client is expecting me to get the system up immediately!
    – Anonymous
    Nov 7, 2009 at 7:55
  • 1
    Sure, I can appreciate the doing diagnosis on a production machine is challenge at times. What you really need to communicate to the client, however, is that without doing sufficient diagnosis when the problem is actually occurring and simply restarting the server then the exact same problem is likely to occur again in the future. Given MySQL is chewing 100% CPU time, it is likely that there is a suspect query triggering the event. Remember, slow queries only get logged ONCE they are completed. You're not letting it complete. Nov 7, 2009 at 10:06
  • One thing you might be able to do is to have another mysql server in a slave configuration. Reboot that one daily. Then when the problem happens, swap in the slave so that it's now the primary. Then debug to your heart's content on the (formerly) production server.
    – Kevin M
    Jun 4, 2010 at 23:21
0

You can try using strace or truss against the mysqld process when it happens.

Another solution may be to schedule a weekly bounce of the database, in case this is not a bad query but a slow leak inside MySQL. Is your client 24x7?

0

Are you using InnoDB or MyISAM tables? It sounds like you might be getting into a situation where a table's locked on a slow query, causing subsequent queries to stack up in the queue. As others have mentioned, though, without seeing the MySQL processlist (tee /tmp/outfile; show full processlist\G) , it's really hard to give a good response.

You must log in to answer this question.