Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

My server keeps crashing from OOM errors cause by MySQL. I am convinced there is a query on this site that is wrong, too long and causes this issue. My server and MySQL logs show very little strain until the spike and issue happens. I would really, really like to identify this problematic query and fix the query. I feel like mysql tuning an otherwise good production server to survive a bad query, which I want to remove anyways, is a weird way to go about it.

I have slow query logs set up but this problem query never shows up in the log, I believe because the query never finishes, the server crashes first. How can I log or identify this problematic query?

FYI There were other queries on this site that caused this issue and I did identify them and fix them. They typically happened because of clauses like "BETWEEN ::startTime AND ::endTime" and by mistake, ::startTime was being set to 0, making the timespan huge.

I have been advised to use EXPLAIN, which I understand, the problem is I don't know what query to "EXPLAIN" :).

Any suggestions in how I can identify this query would be appreciated. Thanks!

share|improve this question
    
Have you enabled the general query log? That would be my recommendation for a next step towards identifying the problematic query. Beware, though, that every query (including non-modifying queries) will be logged, so if you have a busy server, it could cause performance issues or fill up your disk if you're not careful. –  EEAA Apr 9 '12 at 17:43
1  
@maestrojed you may get a better response to the question over on DBA.SE. If you'd like I can move the post over to there where there are a lot more database specific folks. –  mrdenny Apr 9 '12 at 17:44
add comment

3 Answers

I have a wild suggestion for you.

There is a post I wrote in the DBA StackExchange to poll the processlist and tell you the 20 worst-performing queries for any interval you choose. This is better than the slow query log because the slow query log will only records completed queries. My method will catch currently running queries in the act.

I implemented this some time in the middle of last year based on this YouTube Video.

share|improve this answer
    
Thank you all. After this post, I found the problem query. I have not tested your recommended ideas. I feel like a jerk to have asked the question and then fairly soon afterwards not needed the solution. –  maestrojed Apr 11 '12 at 16:45
add comment

To complement other answers I would point to Percona Toolkit which is my tool no 1 for MySQL performance analysis. Please have a look at pt-query-digest

share|improve this answer
    
Thank you all. After this post, I found the problem query. I have not tested your recommended ideas. I feel like a jerk to have asked the question and then fairly soon afterwards not needed the solution. –  maestrojed Apr 11 '12 at 16:45
add comment

I created the pt-stalk tool in Percona Toolkit to help diagnose problems that happen when you are not looking. It will not only help you catch the problematic query, it will gather enough information for you to determine whether the query is the problem -- and if not, what else might be.

share|improve this answer
    
The name pt-stalk sounds creepy. I guess all DBAs who troubleshoot are stalkers at heart. +1 !!! –  RolandoMySQLDBA Apr 10 '12 at 17:39
    
Thank you all. After this post, I found the problem query. I have not tested your recommended ideas. I feel like a jerk to have asked the question and then fairly soon afterwards not needed the solution. –  maestrojed Apr 11 '12 at 16:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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