1

What is a good/healthy mysql query execution time?

To my calculations:

The site gets 1000 unique visitors per hr

10 page view for each unique visitor

5 minutes for each unique visitor (I'm ignoring this. I should, right?)

1000*10=10000/(60*60) = 2.7 views per second.

I have 3 queries on every page. 2.7*3 = 8.1 queries per second

1 second/8.1 = 0.123

So average query execution time must be less than 0.123.

Assuming visits don't increase, can we say anything less than 0.123 works for a healthy database?

  • 1
    The question is somewhat vague. Take a look at the answer below for some suggestions as to where to start. – ConcernedOfTunbridgeWells Jun 26 '14 at 9:48
  • Assuming each query is returns only a few rows from a couple of tables you should be aiming for execution times nearer to 10 milliseconds that 100ms each. – Michael Green Jun 26 '14 at 10:22
  • I'm sorry I didn't understand "10 milliseconds that 100ms each" part. Could you please elaborate? – user3722246 Jun 26 '14 at 10:25
  • That should read "10 milliseconds than 100ms each", i.e. 1/100s rather than 1/10s. – Vérace Jun 26 '14 at 13:22
  • 1
    This also depends greatly on table type, query caching, how many resources your server uses in terms of RAM. Resource planning isn't quite as easy as that unfortunately. You might want to install mysqltuner, see what it recommends. It is not a 100% accurate but it will certainly help. – Mark D Jun 26 '14 at 14:38
2

The question is a bit vague, but I think this answer (a) may provide some useful guidance and (b) is too big to fit in a comment.

Capacity planning a server is a bit more complex than that. You need to take some basic queuing theory into account. Your model would describe a server at 100% capacity, which is actually likely to cause performance issues. In practice you would want to aim for the server running perhaps 25-50% capacity at peak load if you want your site to have consistent response times.

Queuing models state that the average wait time increases hyperbolically as the system approaches saturation.

  • A system that is 50% saturated has two requests waiting service on average
  • A system that is 90% saturated has 10 queued requests on average
  • A system that is 99% saturated has 100 queued requests on average

You should benchmark your queries under load to see how much resource they actually take up. Then apply a view of your peak load to see how much capacity you need for the system to remain responsive.

  • Thanks so much! Saturation is not linearly inreasing. So how can I describe a server at 25-50% capacity? I think 0.123/3 won't be enough :) – user3722246 Jun 26 '14 at 10:11
  • Take a look at the query plans and then estimate the I/O needed to satisfy each query. For a small number of queries this shouldn't be too hard. Then (under load) look at the disk buffer cache hit rate and tune the DBMS accordingly if necessary. On a modern server you should be able to get buffer cache hit rate percentages in the high 90's. Apply that to your reads. Make an estimate of your write traffic. That should give you a back-of-fag-packet IOPS figure. – ConcernedOfTunbridgeWells Jun 26 '14 at 10:27
  • Thank you again. This is beyond my capacity since I'm just starting. There won't be updates in this table. Only >200 inserts/day and lots of reads. I'm trying to find helpful documents using the keywords you wrote now :) – user3722246 Jun 26 '14 at 10:32

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.