Consensus: How many sql queries on a single php page?

[Fark_Maniac]

I was reading an article on digg about scripting, which got sidetracked with having a mysql counter.

So that's got me thinking. With a page that may authenticate the user via a sql query...maybe possible retrieve content via mysql...data verification...with counters. The number of queries may start to stack up.

Now here's my question that has no correct answer. On a given page, how many queries/inserts are too many? What is the consensus on this?

 

[deuce868]

Depends on the use of the page. I have a single page I use to clean out user accounts once a year that performs something like 35000 queries on a load (depends on the number of users in the system). For the most part I try to keep general use pages below 5 and things that need to be darn quick...2.

You can have hundreds on a decent machine with a low traffic site and no one will know better.

 

[mikeblas]

What it ends up depending on is the response time you want to guarantee for the user base of your site.

Say it takes you 0.100 seconds to render your page, queries included. That means that you can only serve 10 pages per second. Maybe less, if the queries have to lock things in the database against concurrent access and will need to re-execute queries, commit a transaction, and so on, to deal with the concurrent load.

10 pages per second sounds slow, but it's 36,000 hits per hour and 864,000 hits per day. That's fine for most sites that amateurs will develop. It's not quite enough for sites that will see "professional" usage, like the front page of any commercial site that you would name off the top of your head.

Given the response time numbers, you can start doing some capacity planning. If you don't mind up to a 0.5 second average wait, you can have 10 users hitting once per second, say. Less users, less time and more hits.

If you add more queries, you'll make the response time slower and the maximum serve rate faster. Note that other pages start to affect eachother; if you're only reading from a table, you don't need locks. If you have another page that updates the table, you do need locks both on the reading page and the writing page, and the writing page will get individual ownership (by locking out) other readers.

There really is a correct answer for your question, then. Most people don't want to wait more than one second for a page to load. The number of SQL queries you have depends on how long they execute, how long they affect eachother, and how fast your hardware is -- but you can't really go over that one second wait limit. If you do so consistently, people will think your site is slow and stop using it.

This analysis sounds complicated, and it can be tricky -- but the math isn't that hard. What amuses the crap out of me are threads where someone wants to buy a server and asks for recommendations; then gets very specific responses, down to the model of CPU and disk drives and contorllers and motherbords. But has never provided any usage scenarios, capacity numbers, and so on. It's a pretty foolish practice, but it happens again and again on this forum.

 

[deuce868]

Just because it takes .10 for a page to generate does not mean it can't be generating another page during that time. .10s != 10 pages per second.


edit...I wish I could type...I really do sometimes

 

[mikeblas]

Just because it takes .10 for a page to generate does not mean it can't be generating another page during that time. .10s != 1 pages per second.

I said as much in my post, in two or three different ways. (And, by the way, the assertion you're refuting is not that 0.100s is not equal to 1 page per second; it's that it's not equal to 10 pages per second.)

You might have another processor, which you can work on another request concurrently. You might have an efficient server implementation, too, which can can keep a single processor busy while some part of the first request is blocking. You really want this, since processors are so much faster than any of the I/O they'll do.

Then again, you might have a query which locks; even a page counter should do some locking. Such temporal dependencies aren't desireable, but they're unavoidable in nontrivial systems.