4

BACKGROUND

Occasionally, in my live web app, I'll get a query that locks up the database for 1-2 minutes. Sometimes it is hard to predict which queries will do this until the load gets to a certain point, or a particular user with a particular set of data runs through that query.

The current solution I have for handling this (and being aware of it), is a crude crontab script which checks "Threads_running" in "show global status" of the mysql server. If the number is over 30, then I log out all the queries with "show full processlist"

Im finding that i'll get a long query stuck query about ~ once a week.

THE QUESTION

Once I find this query, sometimes EXPLAIN will help me figure out why its slow. But sometimes, I just want to re-simulate it happening. When I run the query again, it is fast, (because of query cache?). Then I run RESET QUERY CACHE and FLUSH QUERY CACHE, and expect to be able to simulate that slow query, but it is fast still. Why?

ALSO, side note: I'm curious how other people monitor long queries?

1

Can you please share more on what queries are you running? I think, you can make use of InnoDB engine which will do row level locking instead of table level lock. Also you can make you of slow query log which will log all the queries taking more than n seconds (as specified in config)

|improve this answer|||||
  • ahhhh.. yeah um, im not on innodb. need to switch, that would help i suppose. will check the slow query log, thank you for your input – timh Mar 14 '13 at 0:20
1

The query that appears slow may not be your problem. It may be reliant on a table that occasionally gets locked. It may be only when two particular queries are running at the same time that you get your lag issue. This perfect storm scenario may also explain why you only see it once a week or so. And since you're only testing one query at a time, you never achieve the combination that results in a lock.

Look for a pattern of repeating combinations of queries in your log to see if you can nail down the accomplice query.

|improve this answer|||||

Your Answer

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