Forum Moderators: phranque
I help to run a site which runs on a linux server using Apache and MySQL. Recently, we've been running into site performance issues where occasionally there are timeouts when someone is trying to access a page.
I wrote a little application to retrieve the html content from a page (i.e. it makes a request to the server to deliver a page and spews out the html returned from the server as a string). I find that sometimes, this little app times out - meaning that the problem in this case isn't rendering the content in a browser, it's getting the server to respond.
I've tried to work on cutting down the number of database queries we run everytime a page is loaded - this used to be around 250, but I've manage to cut it down to about over 60. However, I would still like to eliminate all possible causes of these occasional timeouts.
So I would like to ask - what can I do to investigate and resolve this problem? Please forgive me if I'm leaving anything out - I'm kind of new to the web mastering world, and I would be very happy to read articles, documentation, etc. to point me in the right direction.
Thanks,
Kweeri
[edited by: kweeri at 5:50 pm (utc) on Feb. 19, 2008]
Make sure you read the Charter, especially where it mentions not posting URLs.
Caching queries can help, as can making sure all your tables are properly indexed. But even 60 queries is going to cause propblems sometimes, especially as the number of visitors increases. You should really try to re-think your design to limit the number of queries even more.
Thanks for the welcome. Sorry about the url - in other places, where I've posted for help, I've often been asked to provide the url of the site. But I've edited it out now.
Apart from limiting the number of queries, is there anything else I can do to speed up response times? It's kind of difficult to handle this, because we're using a content management system over which we have limited control.
For example, if you have headlines that only get updated once/day. Instead of hitting the database to read those headlines every time the page is loaded, generate a separate text file once/day and include that file each time the page is loaded. (It's sort of a poor man's query caching.)
[edited by: LifeinAsia at 5:59 pm (utc) on Feb. 19, 2008]
I wrote a little application to retrieve the html content from a page (i.e. it makes a request to the server to deliver a page and spews out the html returned from the server as a string). I find that sometimes, this little app times out - meaning that the problem in this case isn't rendering the content in a browser, it's getting the server to respond.
are you talking about a HTTP GET request here?
which server response are you referring to - the web server or the db server?
Yes, I am talking about an HTTP GET request, where I submit the url of a page and the server responds by querying the MySQL database, generating a whole load of html from the results of this query and then serving back this html.
As I said, from time to time, my little client app times out while waiting for a response from the server machine. I don't know whether this happens because the web server is waiting for the db server to cook up the html that it (the web server) will need to generate the page (in which case the problem is the response from the db server), or whether the web server itself is overburdened with requests and so can't respond in time (in which case the problem is the response from the web server), or whether something else is the matter. I'm hoping you can help me in trying to track down and resolve the cause of this extended delay.
First, determine if it's your script, the database, or the server itself. Always look for trouble in your back yard first. :-)
Create a version of this script for testing and begin commenting out all functions that you can and still allow it to run. Open up two browser windows; in one, submit the query on your test, in the other, on your live script. This is essential because at any given time, it may be forces outside your test bed creating the problem (such as the server itself.)
If you can, edit the script so it never queries the database at all. Then comment out the database connection entirely. This will eliminate (or reveal) the database as the problem.
One by one, begin adding elements: the database connection, then most basic queries, on out. You may even find it's one query that is not closing or finishing.
Some things you can extract from this: if the page is still timing out when it's not even making a database connection, you're looking at how it's processing (PHP?) or you're on a dog web server. If it's shared hosting, good luck on that one.
If it boils down to "something in the script," you might begin looking at how your code is written. Unless it's in some sort of loop, 60 queries per page is a lot. Consider ways of breaking down the output, or economizing your script so it does more with less work.
Make use of the limit clause in mysql queries, even as a debugging tool. I've seen web pages output thousands of lines of text from a database. This is insane. It's so easy to use pagination in a query there is no reason to select all records and output them, output them in chunks.
For debugging, go through your select statements and just add limit:
select * from table where somefield ='somevalue' limit 50;
This can immediately tell you if your script is calling up millions of records when you expect 20. :-)
Another problem I see - and this is a point of large debate, so this is the opinion I've formed - is "print while you work" versus print-at-once. (There are probably official geek-speak terms for these ,this is what I call them.)
Most apps I see output as they work:
print "content-type:text/html\n\n";
-Open a database table
-print from database table
-next function
.....
The argument one programmer provided for this was that it empties out server RAM by printing immediately. But the truth is, that output never gets to the browser until it's done anyway, or it sends it in partial chunks which can look a little tacky. Additionally, if the output is rather large, this means many processes at once - querying the database, printing out, back to querying . . .
I usually like to store all output and spit it out at once:
$output = Open a database table
$output .= print from database table
$output .= next function
.....
print "content-type:text/html\n\n";
print $output;
This allows you to do all your work in one process, then output in another, without going back and forth. In very large or complex apps, this makes it very easy to eliminate areas that may be hanging up. The downside is if you have errors along the way that kill the program, especially if the errors print to STDOUT (which gives you a server 500,) you never get to print.
This forces you to be more strict about your code and how you handle errors. In fact, the way you handle errors may even be able to indicate what is timing out, if it's in the script.
