Re:Ask HN: Server under heavy load. Any downside to this hack?
by trevelyan on Wednesday September 11, 2013 @11:47AM
(#6367889)
If you haven't already done this, when the mysql server is having trouble make sure you connect through the terminal and try:
> SHOW PROCESSLIST
This will show all active queries and the time they have taken to execute. The fact that the server seems to churn to a halt and then work its way through the problem suggests the issues are related to specific queries you can catch this way. Then use the EXPLAIN command on the slow queries to figure out why they are hanging your server and add indexes or tweak that part of your code (avoid joins on large tables, etc.) as necessary.
> SHOW PROCESSLIST
This will show all active queries and the time they have taken to execute. The fact that the server seems to churn to a halt and then work its way through the problem suggests the issues are related to specific queries you can catch this way. Then use the EXPLAIN command on the slow queries to figure out why they are hanging your server and add indexes or tweak that part of your code (avoid joins on large tables, etc.) as necessary.