3

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!

maestrojed
  • 221
  • 2
  • 2
  • 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 09 '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 09 '12 at 17:44

3 Answers3

1

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.

Community
  • 1
RolandoMySQLDBA
  • 16,544
  • 3
  • 48
  • 84
  • 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
1

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.

  • 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
0

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

Piotr
  • 599
  • 5
  • 3
  • 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