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?
Best Answer
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.
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.