*Something* on our web server has been causing the loading to rocket a couple of times a day recently, and it's been a bit of a problem to find out what that something is. Rather like searching for a needle in a haystack - except that when you find something that you think may be the problem, you can't necessarily be sure - it's not really a shiny needle to look at.
Analysis - look at the symptoms, and note that that server has started very seriously swapping at the point that the load has built up. So the problem seems to lie more in memory than in cpu utilisation. And once swapping starts, the throughput drops which means that the server can't cope with the regular load. Come along to a shop and see a long queue, and you'll go elsewhere; come to a webserver and you'll join the queue because you don't realise how long it is, so once there's an overload it's not going to be naturally self-righting.
So - with over 100,000 accesses to our web server each day, how can I find which are the one or two that are causing the problem?
a) I instigated logging as described the other day - [here]
. That tells me the process number for each request, and also the time it takes to complete.
b) I instigated a top
report to run every minute on the server, logging the memory usage:
top -b -d 60 >> logfile
c) About a day later, I looked for large server threads in the top
log and, low and behold:
-bash-3.2$ grep '[0-9][0-9][0-9]m' toplog
1955 apache 20 0 489m 479m 3108 S 4 54.3 0:04.01 httpd
4180 apache 20 0 418m 409m 2972 S 3 46.3 0:02.31 httpd
25587 apache 20 0 195m 188m 3456 S 2 21.3 0:01.75 httpd
25587 apache 20 0 564m 557m 3456 S 4 63.1 0:03.98 httpd
29989 apache 20 0 410m 400m 3132 S 4 45.4 0:02.91 httpd
That's a couple of individual threads which exceeded half a gigabyte ... when the normal size doesn't exceed 25 Mbytes.
d) An examination of the requests served by threads 4180, 25587 and 29989 ...
grep 25587 extra_log
revevealed a commonality of records ...
22.214.171.124 www.wellho.net 20110917.181334 200 200 917852 225 25587 X "en-gb"
"gzip, deflate" "GET /demo/imgcatalog.php HTTP/1.1" "-" "http://www.google.com/
"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_7_1) AppleWebKit/534.48.3
(KHTML, like Gecko) Version/5.1 Safari/534.48.3"
126.96.36.199 www.wellho.net 20110917.195838 200 200 918861 49 29989 - "-"
"-" "GET /demo/imgcatalog.php HTTP/1.0" "-" "http://www.wellho.net/demo/imgcatalog.php"
"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322;
.NET CLR 2.0.50727)"
And that's a very suspicious request in that it seems that the URL it's pointing to is always taking a very long time indeed to serve. So it's time to look in detail at the code.
The script turns out to include the following MySQL query:
$q = mysql_query("select * from im_library order by iid desc");
which looks simple and easy ... except that the im_library table contains most of the images we use on our web site - there are now some 7,500 of them - and the query was written in such a way that it was returning all the text data and each complete image too
to the PHP script - and that's a huge amount of data.
e) rewrite the query to return only the fields needed:
$q = mysql_query("select filename,descriptor,iid from im_library order by iid desc");
(and test to make sure that the script still performs, of course). And then bounce on the link a few times to check that it's rather more robust.
Problem - I THINK - solved.
Moral of story: Check your database queries to ensure they're not returning far more than they need to!
I'm now fairly sure the problem has been fixed. Looking at various server graphs, there's a distinct change in pattern over the past 24 hours since I put the fix in. And I've been having a good "bash" today too - in fact improving things so that the script now links to a page that will search out when the images are used too ...
The image catalogue may be found [here]
. I'm sufficiently sure that it's fixed to encourage you to visit that page. And you can link through from there to details of any image. You can also find a random image, and details about it, [here]
. I have a private PHP course
coming up this week, so I expect you may find some enhancements to the scripts while I'm in PHP mode ;-)
Images accompanying this article? Random ones from our database ... (written 2011-09-18)
Associated topics are indexed as below, or enter http://melksh.am/nnnn for individual articlesA606 - Web Application Deployment - Apache httpd - log files and log tools 
20 minutes in to our 15 minutes of fame - (2013-01-20) 
TV show appearance - how does it effect your web site? - (2013-01-13) 
Reading Google Analytics results, based on the relative populations of countries - (2012-03-24) 
Learning more about our web site - and learning how to learn about yours - (2011-12-17) 
Who is knocking at your web site door? Are you well set up to deal with allcomers? - (2011-10-21) 
Getting more log information from the Apache http web server - (2011-09-16) 
Making the most of critical emails - reading behind the scene - (2010-12-16) 
Server logs - drawing a graph of gathered data - (2010-11-03) 
Apache httpd Server Status - monitoring your server - (2010-10-28) 
Logging the performance of the Apache httpd web server - (2010-10-25) 
libwww-perl and Indy Library in your server logs? - (2008-09-13) 
Server overloading - turns out to be feof in PHP - (2008-09-01) 
Logging Cookies with the Apache httpd web server - (2008-08-20) 
Be careful of misreading server statistics - (2008-05-28) 
Every link has two ends - fixing 404s at the recipient - (2008-04-02) 
Web page (http) error status 405 - (2008-01-12) 
What proportion of our web traffic is robots? - (2007-06-19) 
What brings people to my web site? - (2005-07-13)H309 - PHP - Maps, Graphics and Geographics 
Where are you? How to write a geosensitive application - (2013-09-18) 
Fpdf - generating .pdf documents easily from your PHP program - (2012-07-24) 
QR codes with marketing logos embedded - (2012-05-16) 
QR codes - graphics images that provide quick phone links - (2012-01-18) 
UK Mapping Data - and more to come - under government Open Data measures - (2011-12-03) 
Computer Graphics in PHP - World (incoming data) to Pixel (screen) conversion - (2011-03-24) 
Finding and diverting image requests from rogue domains - (2011-03-08) 
An image from a website that occasionally comes out as hyroglyphics - (2011-01-14) 
Protecting your images from use out of context - (2010-08-29) 
Uploading a document or image to its own URL via a browser - (2010-04-18) 
Redirecting to your main domain for correct security keys - (2010-03-13) 
Reducing image size on digital photos - PHP - (2010-01-17) 
Dynamic / changing images on your web page - (2009-09-01) 
Geocoding - converting address to latitude / Longitude with PHP via Google - (2009-08-14) 
World Flags in your PHP pages - (2009-08-10) 
Images for Christmas - (2008-12-21) 
Making it all worthwhile - (2008-12-04) 
Ever had One of THOSE mornings? - (2008-08-16) 
Dynamic maps / geographics in PHP - (2008-08-13) 
All around the world? - (2008-08-03) 
addslashes v mysql_real_escape_string in PHP - (2008-07-27) 
Gant charts - drawing them with a PHP script - (2008-05-03) 
Ordnance Survey Grid Reference to Latitude / Longitude - (2007-10-14) 
Converting from postal address to latitude / longitude - (2007-10-13) 
Controlling and labelling Google maps via PHP - (2007-10-13) 
Drawing hands on a clock face - PHP - (2007-05-19) 
Drawing dynamic graphs in PHP - (2007-03-09) 
Display an image from a MySQL database in a web page via PHP - (2006-11-22) 
PHP Image viewing application - (2006-04-01) 
Merging pictures using PHP and GD - (2006-01-13) 
Ordnance Survey - using a 'Get a map' - (2005-05-22)S156 - Interfacing Applications to MySQL Databases 
MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24) 
Perl - database access - DBD, DBI and DBIx modules - (2010-12-22) 
How to display information from a database within a web page - (2010-11-07) 
Joining a MySQL table from within a Python program - (2010-06-02) 
Connecting Python to sqlite and MySQL databases - (2010-04-28) 
Checking the database connection manually - (2009-08-28) 
Mysqldump fails as a cron job - a work around - (2009-06-30) 
Hiding a MySQL database behind a web page - (2008-11-15) 
Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02) 
Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25) 
Easy selection of multiple SQL conditions from PHP - (2007-11-30) 
Using a MySQL database to control mod_rewrite via PHP - (2007-10-06) 
Viewing images held in a MySQL database via PHP - (2006-05-17) 
Python to MySQL - (2006-03-31) 
Using a MySQL database from Perl - (2006-03-13) 
mysql_connect or mysql_pconnect in PHP? - (2004-10-30)
Some other Articles
Why would you want to use a Perl hash?A threat in the post? Poor marketing practise from Smiletrain?Apache Internal Dummy Connection - what is it and what should I do with it?Checking all the systems on a subnet, using Expect and TkNeedle in a haystack - finding the web server overloadAwk v PerlPerl and CGI - simple form, and monitoring script.Take the dog on a lead - do not carry her. Perl references.A demonstration of how many Python facilities work together