CODEWITHSUNDEEP
mysqldatabase
dhalsumit
dhalsumit

Reputation: 143

Inconsistent response time in mysql select query

We have lot of MySQL select queries for some reporting need. Most are little complex and they generally include 1. five-six join statements 2. three-four inner queries in select clause.

All the indexes are properly in place in the production environment. We have checked with explain query syntax multiple time and they are OK.

Some of the query behave very strangely in in terms of response time. The same query returns in less than 500 milli secs at times (which shows all index working fine), and when we run it after 1 min or so - it gives result with a high response time (varying from five-six seconds to 30 seconds.) Some time (around once in 20 times..) it gives a time out error.

This might be due to server load - but the high variance is so frequent that we think there is something else to set to solve it.

Can some one please show me some direction on what else to do!

Thanks,

Sumit

Upvotes: 1

Views: 1434

Answers (1)

Neville Kuyt
Neville Kuyt

Reputation: 29629

This kind of behaviour is usually caused by a bottleneck in your stack.

It's like a rotating door in a building - the door can handle 1 person at a time, and each person takes 3 seconds; as long as people don't arrive at a rate over 1 person every 3 seconds, you don't know it's a bottleneck. If people arrive at a faster rate for a short period of time, the queue grows a little but disappears quickly. If people arrive at a rate of 1 person every 2.5 seconds for an hour, the queue becomes unmanageable, and can take far longer than that 1 hour to disappear.

Your database system is made up of a long corridor with rotating doors - most doors can operate in parallel, but they are all limited.

(Sorry for the rubbish analogy, but I find it helps to visualize these things with real-world images).

If the queries are showing a high degree of variance in their performance profile, I'd look at the system performance monitor (top in Linux, Perfmon in windows) and try to correlate slow performance with the behaviour of the system. If you see a sudden spike in CPU utilization when the queries slow down, that's likely to be your bottleneck; if you see a sudden spike in disk throughput, you might look there.

Once you have a hypothesis about the bottleneck, you can look at ways of resolving them - throwing hardware at the problem is usually cheapest.

Upvotes: 5

Related Questions