*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 ...
18.104.22.168 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"
22.214.171.124 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 articlesS156 - Interfacing Applications to MySQL Databases 
mysql_connect or mysql_pconnect in PHP? - (2004-10-30) 
Using a MySQL database from Perl - (2006-03-13) 
Python to MySQL - (2006-03-31) 
Viewing images held in a MySQL database via PHP - (2006-05-17) 
Using a MySQL database to control mod_rewrite via PHP - (2007-10-06) 
Easy selection of multiple SQL conditions from PHP - (2007-11-30) 
Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25) 
Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02) 
Hiding a MySQL database behind a web page - (2008-11-15) 
Mysqldump fails as a cron job - a work around - (2009-06-30) 
Checking the database connection manually - (2009-08-28) 
Connecting Python to sqlite and MySQL databases - (2010-04-28) 
Joining a MySQL table from within a Python program - (2010-06-02) 
How to display information from a database within a web page - (2010-11-07) 
Perl - database access - DBD, DBI and DBIx modules - (2010-12-22) 
MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24) 
Accessing a MySQL database from Python with mysql.connector - (2015-02-21)H309 - PHP - Maps, Graphics and Geographics 
Ordnance Survey - using a 'Get a map' - (2005-05-22) 
Merging pictures using PHP and GD - (2006-01-13) 
PHP Image viewing application - (2006-04-01) 
Display an image from a MySQL database in a web page via PHP - (2006-11-22) 
Drawing dynamic graphs in PHP - (2007-03-09) 
Drawing hands on a clock face - PHP - (2007-05-19) 
Controlling and labelling Google maps via PHP - (2007-10-13) 
Converting from postal address to latitude / longitude - (2007-10-13) 
Ordnance Survey Grid Reference to Latitude / Longitude - (2007-10-14) 
Gant charts - drawing them with a PHP script - (2008-05-03) 
addslashes v mysql_real_escape_string in PHP - (2008-07-27) 
All around the world? - (2008-08-03) 
Dynamic maps / geographics in PHP - (2008-08-13) 
Ever had One of THOSE mornings? - (2008-08-16) 
Making it all worthwhile - (2008-12-04) 
Images for Christmas - (2008-12-21) 
World Flags in your PHP pages - (2009-08-10) 
Geocoding - converting address to latitude / Longitude with PHP via Google - (2009-08-14) 
Dynamic / changing images on your web page - (2009-09-01) 
Reducing image size on digital photos - PHP - (2010-01-17) 
Redirecting to your main domain for correct security keys - (2010-03-13) 
Uploading a document or image to its own URL via a browser - (2010-04-18) 
Protecting your images from use out of context - (2010-08-29) 
Server logs - drawing a graph of gathered data - (2010-11-03) 
An image from a website that occasionally comes out as hyroglyphics - (2011-01-14) 
Finding and diverting image requests from rogue domains - (2011-03-08) 
Computer Graphics in PHP - World (incoming data) to Pixel (screen) conversion - (2011-03-24) 
UK Mapping Data - and more to come - under government Open Data measures - (2011-12-03) 
QR codes - graphics images that provide quick phone links - (2012-01-18) 
QR codes with marketing logos embedded - (2012-05-16) 
Fpdf - generating .pdf documents easily from your PHP program - (2012-07-24) 
Where are you? How to write a geosensitive application - (2013-09-18) 
The changing face of Christmas - (2014-12-26) 
Adding a PHP build option, rotating an image based on camera data, and a new look at thumbnails in PHP - (2015-02-22) 
Image indexer / thumbnail display scripts in PHP - (2016-02-25)A606 - Web Application Deployment - Apache httpd - log files and log tools 
What brings people to my web site? - (2005-07-13) 
What proportion of our web traffic is robots? - (2007-06-19) 
Web page (http) error status 405 - (2008-01-12) 
Every link has two ends - fixing 404s at the recipient - (2008-04-02) 
Be careful of misreading server statistics - (2008-05-28) 
Logging Cookies with the Apache httpd web server - (2008-08-20) 
Server overloading - turns out to be feof in PHP - (2008-09-01) 
libwww-perl and Indy Library in your server logs? - (2008-09-13) 
Logging the performance of the Apache httpd web server - (2010-10-25) 
Apache httpd Server Status - monitoring your server - (2010-10-28) 
Making the most of critical emails - reading behind the scene - (2010-12-16) 
Getting more log information from the Apache http web server - (2011-09-16) 
Who is knocking at your web site door? Are you well set up to deal with allcomers? - (2011-10-21) 
Learning more about our web site - and learning how to learn about yours - (2011-12-17) 
Reading Google Analytics results, based on the relative populations of countries - (2012-03-24) 
TV show appearance - how does it effect your web site? - (2013-01-13) 
20 minutes in to our 15 minutes of fame - (2013-01-20) 
Identifying and clearing denial of service attacks on your Apache server - (2014-09-27) 
Which (virtual) host was visited? Tuning Apache log files, and Python analysis - (2015-01-23) 
Web Server Admin - some of those things that happen, and solutions - (2015-05-10)
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