What would be considered average or high load for a website using mysql on the backend?

Question

I'm roughly 10% into my website project and I'm trying to figure out what sort of load I'm putting on the database. When a user is logged in, I have many functions that trigger every minute, and every time someone visits the page, a number of elements, such as area code lists, states and countries are pulled to build a registration page. I'm sure I can move some of that to PHP so the database isn't involved.

In 6 days, 14 hours, 57 minutes and 58 seconds, I show 120,998,563 queries, averaging 12.69 k per minute and 211.43 per second. I'm listing maximum concurrent connections of 79. These last two don't make sense. Received averages 133 MiB per hour and Sent averages 1,997 MiB per minute.

Answer 1

The best thing to look at is the number of queries per page, then also investigate what types of queries they are. Simple SELECT statements for example are very fast, but if you are joining 3 tables together, are very slow. It also depends on the indexes and limits in your database tables.

Essentially, we really don't have enough information to tell you. Even with what you gave, without any idea of the number of users at a time it's pretty useless.

Answer 2

More than the number of queries, what probably matters is what they do, and how : if you have millions of rows in your tables, and are not using the right indexes, you server will fall... If your queries are ultra-optimized, with the right indexes, and/or you don't have much data, you server will live.

You might want to use EXPLAIN on the queries that are the most used, to see if at least those are optimized / using indexes ;-)

Then, you will probably want to add so kind of caching mecanism, like APC or memcached ; at least if you can...
For instance, the lists states and countries probably never change : it could be cached, to not hit the database thousands of times, but just, say, once a day or once an hour.

Answer 3

You might want to enable and start monitoring the slow query log ( http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html ). The number of queries doesn't necessarily equate to high load - as long as they are hitting indexes, or even better the query cache. A few poorly designed queries can kill your server though - and the slow query log will point those out so you can spend your time optimizing them.

Answer 4

As others have said, the number of queries doesn't really matter, it is more the type of query and how well your database is indexed. Also, as someone else mentioned take a look at a caching mechanism like APC or memcache, but I would also reccommend a php class caching system. I know the zend framework has one and I personally use Cache_Lite from the PEAR library. You can cache db queries that don't need to be absolutely the latest updated information at the php level of things. So if your page runs, say 10 queries but really only 2 or 3 of them need to be fresh information you can cache the other queries for 5 or ten minutes. Even a one minute cache will save you lots of transaction on a high volume site.

Answer 5

some more tips:

A - make sure you don't run nXn runtime queries (or at least try to avoid it as much as you can). What i mean by that is don't go:

-query -while(query) - query2 - while (query2) - end while query2 -end while query

and definitely don't go into third level (n^3)...

B - another thing about the speed: when you don't have to - don't go and select * from . if you need only first name and last name, select only these... the data will come back faster. and you'll be able to go through it faster.

Answer 6

Another factor is your hosting environment. On a lot of shared hosts, there's a limit to the number of permitted simultaneous database connection, and this limit can be surprisingly low. Statistically it's usually fine, assuming that visitors are spread out evenly through the day, but if you have a large enough audience who expect content to come out at a certain time, you could end up maxing out your available connections if they're all requesting scripts (and opening database connections) in a narrow time frame.