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

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.