Skip navigation links

MySQL Forums :: Performance :: Problematic Query identification at the time of load on server


Advanced Search

Re: Problematic Query identification at the time of load on server
Posted by: Rick James ()
Date: May 22, 2012 09:51AM

If this is a production system, and there are no "new" queries hitting it, then there should be no problem. That is, after you have optimized the worst queries, they should stay optimized. If not, let's look at them specifically.

If you allow "new" queries, you run the risk of performance problems. But there is no way to catch them until they are already causing trouble. Perhaps you can set up a "staging" environment or an offline Slave for testing new queries.

I believe in the SlowLog as being the best tool for discovering which queries to work on.

Some other possible metrics (and my opinions):
* "Load average" -- often false alarms.
* Threads_running -- when > 10, you _may_ be in serious trouble.
* SHOW PROCESSLIST with some threads "Locked" -- some other thread is hogging something.
* >90% CPU -- almost certainly bad queries/indexes. But this is redundant with the SlowLog.
* >90% of one core -- since MySQL won't use multiple cores in a single connection, this indicates an inefficient query. (Eg, 12% overall on an 8-core box is probably consuming one core.)
* >90% I/O -- tuning, overall schema design, missing index, etc.

There are times when some of these metrics are alarming, yet the "users" are seeing no impact.
There are times when none of these metrics are alarming, yet the "users" are complaining.

So, the best alarm might be one that simulates a "user". That is, it times a 'typical' SELECT and alerts if it is more than some threshold.

The problem with the alarms is that they don't tell me what to fix. The SlowLog does tell me that. Sometimes looking back at various metrics is useful in that it confirms a theory about what was happening when, and for how long.

Options: ReplyQuote


Subject Views Written By Posted
Problematic Query identification at the time of load on server 1130 Zafar Malik 05/14/2012 01:58AM
Re: Problematic Query identification at the time of load on server 559 René Olivo 05/14/2012 10:56AM
Re: Problematic Query identification at the time of load on server 474 Zafar Malik 05/21/2012 09:11AM
Re: Problematic Query identification at the time of load on server 497 Rick James 05/22/2012 09:51AM
Re: Problematic Query identification at the time of load on server 625 Aftab Khan 06/01/2012 03:25AM
Re: Problematic Query identification at the time of load on server 457 Zafar Malik 06/24/2012 01:37PM
Re: Problematic Query identification at the time of load on server 470 Aftab Khan 06/24/2012 02:17PM
Re: Problematic Query identification at the time of load on server 473 Rick James 06/25/2012 09:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.