[RESOLVED] queries and server load

[MattG]

I'm noticing a high server load due to my pages. The average page has about 14-18 queries but can get as high as 35+ depending on the page.

I'm guessing the fact there are that many queries per page combined with the amount of users online at one time is what is causing the high server load?

Would combining queries with LEFT JOIN instead of having seperate queries reduce the load or would it still provide the same load just in fewer queries?

[troybtj]

If this is a *nix system, run 'top'

I have found that the apache process takes up about 4x more load than the mysql process.

If you have XWindows, run mysql-admin, it will tell you queries/second and cache hits/misses. That can also help.

A forum based on vbulletin that I run spends most of it's time doing PHP things when I look at it in debug mode.

Here is the output of a long page listing from vbulletin. The number of queries is rather high, but the amount of time is still spent in PHP (by a factor of 10):

Page generated in 0.848170995712 seconds with 82 queries, spending 0.079803943634 doing MySQL queries and 0.768367052078 doing PHP things.

[MattG]

So it's likely to be the PHP not the amount of MySQL queries?

Is it possible for you to post the code that prints those stats?

[Sxooter]

It's hard to say what's causing the problem.

Until you run top on it and see.

Most of the time if it's a db problem, it's one query that's usually the issue. One query that does something a little bit inefficient can slow the whole machine to a crawl.

If you can't run top, you can do some simple, cheap query profiling.

Stick some lines like this in your code:

print "<!-- ";
print microtime(true);
print "-->";

and then you can view the source to see the timestamps of each piece of code. wrap your mysql queries in the above and you can then see how long a query is taking.

Also, look at turning on mysql logging of long running queries and you can work on making them faster. If mysql is the problem.

[troybtj]

MattG wrote:

So it's likely to be the PHP not the amount of MySQL queries?

Is it possible for you to post the code that prints those stats?

It is part of vbulletin, commercial forum software (same software this forum runs), so it is split up into several places, and copyrighted. :queasy:

However there is a Benchmark class in PEAR that provides a timer that you can use to set markers at various points in your code, then at the end of the page, display the various times. If you put a mark around each SQL query, then subtract from total time all the increments, you can see which part of your app is causing the load.

From command line:
pear install Benchmark

You can also keep snapshots of memory usage using the memory_get_usage() function, and, again, adding to an array at timepoints, thendoing a bit of math to find out which areas are eating the most memory.

[ahundiak]

One query with several joins is usually faster than several queries without joins. But, as previous posters have indicated, PHP is the most likely culprit.

[MattG]

Thanks guys