CODEWITHSUNDEEP
mysqlmysql-slow-query-log
Astha
Astha

Reputation: 1736

Identify slow query without slow query logs in mysql server

I am wondering is there any other way to to check our slow queries without logging slow query. Suppose, I have a highily busy server can't afford to log much to save memory and I/Os. Then, is there any other way available to check if I have a slow query? I know, we can do profiling of the query but still not sure what exactly to do to identify which query is the one taking most of the time and memory.

Just started mysql administration and not sure how to handle this. Any guidance will be highly appreciated.

Upvotes: 1

Views: 1349

Answers (1)

Rick James
Rick James

Reputation: 142198

I find the slow to be the best source of what is bogging down the server.

Suggest you start will a moderately high value of long_query_time. This will minimize the I/O and disk space. Fix the queries that it finds, if you can.

When you have exhausted that, lower that setting to find more.

Use pt-query-digest to find the "worst" queries.

Keep in mind that a high value finds only long queries, even if they are rare. A lower value will find fast queries that are run frequently, and these may be the real villains.

Another approach is to turn on the slowlog only briefly or during a relatively idle time. What you find may still be very useful.

Anecdote: In one system there was a query where the WHERE was misformulated such that it was running 100% CPU. A one-line fix dropped the CPU to 2%.

Another anecdote: A particular query was fast, but running frequently. A little bit of arithmetic said that the query should not have issued as frequently as was seen. The query was inside a loop when it could have been outside the loop.

Back to the direction you were taking... You could manually do SHOW PROCESSLIST to see what is frequently or slowly running. It is tedious, and hit or miss. But it may catch both (1) slow, infrequent, queries, and (2) fast, frequent, queries.

If you would like to post SHOW VARIABLES; and SHOW GLOBAL STATUS;, I will check a couple hundred formulas that may lead to more insight into inefficiencies.

Upvotes: 1

Related Questions