At the moment I’m juggling 3 MySQL databases. It could very easily be more.

I usually use phpMyAdmin for backing them up but restrictions on port forwarding has meant I can’t do this whilst at work¹. So, I wrote myself a script to do this. And optimize the tables as well.

An extract of the script is below… mine allows me to choose the database and compression type and then populates the appropriate database fields (username, password, etc) but for generic usage purposes, the script below should be fine for other people to use and build upon.

if ($compression=="None") {$comp_cmd=""; $comp_ext="sql";}
if ($compression=="Gzip") {$comp_cmd=" | gzip"; $comp_ext="gzip";}
if ($compression=="Zip") {$comp_cmd=" | zip"; $comp_ext="zip";}    

mysql_connect($host,$user,$password);
mysql_select_db($database);
$query="SHOW TABLE STATUS FROM ".$database;
$result=mysql_query($query);
$num=mysql_numrows($result);
$compressed=0;
$i=0;
while ($i < $num) {
   $table=mysql_result($result,$i,"Name");
   $gain=mysql_result($result,$i,"Data_free");
   if ($gain!=0) {
       if ($compressed==0) {echo "<br/>Compressing tables...<br/><br/>\n";}
       echo "Table ".$table." - ".$gain." bytes gained<br>\n";
       $query="OPTIMIZE TABLE ".$table;
       $optimise=mysql_query($query);
       $compressed=$compressed+$gain;
   }
   $i++;
}
if ($compressed!=0) {
   echo "<br/>".$compressed." bytes gained in total<br/>";
} else {
   echo "<br/>No tables were optimised.<br/>";
}
$backupFile = "backup/".$name."_".date("Ymd").'.'.$comp_ext;
$command = "mysqldump --opt --extended-insert --complete-insert --hex-blob
--host=".$host." --user=".$user." --password='".$password."' ".$database.
$comp_cmd." > $backupFile";
exec($command, $ret_arr, $ret_code);
if ($ret_code==0) {
   echo "<br/>Database backed up: <a href=\"".$backupFile."\">download</a>.\n";
} else {
   echo "<br/>The database could not be backed up - the return code was ".
$ret_code.".\n";
};

Before running the above, you need to populate the following fields…

$database – your MySQL database
$user – your MySQL username
$password – your MySQL password
$host – your MySQL host name
$name – used to name your backup
$compression – this is the type of compression you wish to apply and should be either None, Zip or Gzip.

When run any tables that require optimizing will be, well, optimized and the details output. Finally a backup is made of the database and a link displayed so that you can download it.

Downloads are placed in a folder named /download, so make sure you add write permissions for this folder. The filename will be xxx_yymmdd, where xxx is the name you specified in $name and yymmdd is the date. Obviously the extension will be based on the type of compression you requested.

Whilst trying to write this code I came across various scripts and resources which did something similar but often didn’t work (grrr) for one reason or another. In particular, often the backup files are create empty if you have the MySQLdump parameters wrong. One thing you might spot that I’ve worked around – and here’s my tip for the day – is that I’ve placed single quotes around the password. This was because one of my passwords had an ampersand in it and, well, MySQLdump doesn’t like it.

Enjoy, play and let me know how you get on!

1. and I have a tendency to forget if I leave it until the evening at home [↩]

I often wondered how sites detected the number of users online. After some digging I’ll admit to be no closer to the answer. The problem is that once a page is delivered to someone you have no idea what they’re doing unless, and if, they then do something – go to another page of yours, click a button, etc. They could have immediately left the site and gone elsewhere… you simply don’t get back the level of interaction that would tell you this.

So my solution was based on simple maths. Using Google Analytics and the like I work out how long an average user spends looking at a page on the site. Let’s say it’s 90 seconds.

I’m using PHP and MySQL for this example. So, create a new table named users_online and create two fields for it – the first named timestamp and the second ip_address.

With your database already open execute the following PHP, pre-setting the variable $online_seconds with the figure we discussed a couple of sentences ago (in my example, 90). This should be at the top of each page.

$timestamp = time();
$timeout = $timestamp-$online_seconds;
$insert=mysql_query("INSERT INTO users_online VALUES('$timestamp','$REMOTE_ADDR')");
$delete=mysql_query("DELETE FROM users_online WHERE timestamp<'$timeout'");
$result=mysql_query("SELECT DISTINCT ip_address FROM users_online");
$user_num=mysql_num_rows($result);

This will add the period of time (90 seconds) to the current timestamp and insert a row into the table with that as a “timeout”. We then delete any rows where this timeour has now passed. Finally, we read the number of distinct IP addresses – this is the number of users currently online and is returned as $user_num.

How does this work? Well, the idea is that after 90 seconds if the user hasn’t moved onto another page or refreshed the current one then they’re gone. If they do, a new row with their IP address is created and they’re counted once more as still being active. Quite simple really. Possibly not the most accurate but without using host-side JavaScript, for instance, then I’m not sure how you would.

If nothing else it’s handy to have an idea as to how many people are online before you upgrade your site and potentially break it

Earlier this year I made the decision to stop updating the BMTG site as-and-when and more formally as packages. Yesterday I launched the 4th such package – Deoxys.

Unlike some of my other packages which have had more visible changes, this is more “back end”. Having said that, to me, it’s exciting stuff. The site is performing more caching and I’ve completely re-written most of the MySQL – it’s more efficient and protected against SQL injection (I hope).

There’s also detection of MySQL failures – when this happens a message bar appears at the top of each page indicating that a problem is occuring and that the site may be affected. The site pages are then designed to degrade gracefully – in other words, either cope with the lack of database access (easier for some pages) or simply display an appropriate message to indicate that no information is currently unavailable. In the past any database problems have simply lead to the site erroring and generally looking shabby.

Additionally, I’ve added some flags to the back-end database. These indicate either a major problem or that the site is undergoing maintenace – I can then flick these flags on when required and a message will appear on the site. The maintenance is particularly handy to turn on whilst I’m upgrading the site.

Lastly, and one of the few visible changes, I updated the member profiles – they’re a bit plainer than before but I think they work a lot more effectively now, as they’re less “fussy”.

Now I’ve started work on the Eevee package – I’ve already written the maintenance changes (including the width of the pages being increased, whilst still ensuring that a horizontal scrollbar doesn’t appear on 800 pixel wide monitors), but the main changes is to the online shop, which helps to generate some much-needed cash for the society.

Meantime, if you can guess (no looking on Wikipedia!) the naming convention I’m using then… you’re sad. Ok, that makes me sad too. And Catherine Furber, who also knew it.

I’m in the process, at the moment, of some interesting changes to the BMTG site. Instead of each show/concert page being individually written (with a few calls to scripts to display photos, etc) I’m creating a totally automated page generation system – simply tell it the show name, call the generator script and you get a show page out the other end. This includes the automatic detection of photos (just drag and drop a new photo into the appropriate folder and it then appears on the page) and the fetching of descriptions from the JPG data. Nice (if I do say so myself).

However, all of this has had an impact of server times. In the past I’ve not measured server times for performance monitoring, as they’ve been so insignificant. Now, I’m having to for the show pages. In fact the changes have pretty much negated recent performance improvements.

The next package, though, should help with this as I bring in some further performance improvements (including image caching and sorting out some shoddy MySQL programming of mine).

However, going forward (and I hate that expression), performance is something I will look at it with each package update.

After that there are packages due to SEO and Accessibility. So, they too will become part of the “standard” package check. Indeed, I have 4 things on my radar…

1. Accessibility
2. SEO
3. Security
4. Performance

Security, which will be tackled in the next package along with performance, is mainly around MySQL injection and the like – making the site as hacker-proof as possible.

It’s actually quite exciting as SEO and Accessibility is not something I’ve really concentrated on in the past, so I’m looking forward to the research and learning that will go with this.

Website performance is becoming a bit of a lost art. As more and more people get broadband, and that broadband gets quicker, then website owners spend less time looking at performance. After all, when you have a quick connection, why bother?

What these people forgot is that not everybody is on the same super-quick broadband. But, even if that’s not the case, we all know that we prefer a quick website over something slower. Then factor in the fact that many people have hosts that limit bandwidth, and the need for efficiencies becomes more apparent.

In this article, I’m going to be using my own websites for demonstration purposes, so I will be talking about HTML (or rather XHTML), PHP and MySQL. I’ve provided details in a past post about useful tools for reducing images in size, so don’t intend to cover this again.

The aim is simple… to look at performance and reduce loading times and bandwidth.

So, where to start.

Well, there are various tweaks we can do to Apache servers and PHP and MySQL installs to improve such things but, if like me, you’re getting hosting from a third party you’re unlikely to be able to do anything about this, so I’ll be concentrating on coding. However, changes to site configuration files – such as .htaccess – are usually allowed so I will include these.

The first thing to do, and excuse me if this sounds obvious, is to try and identify where the slowdown occurs. Is it the host server (possibly PHP or MySQL scripts) or client side (HTML, CSS, JavaScript, Images, etc.)?

When it comes to code, there are 3 basic rules on how we can improve this…

1. Compress (or, as it’s called elsewhere, “minify”) so that the files are smaller
2. Reduce by removing redundant code
3. Improve code so that is works more efficiently

PHP

How we code our PHP can make a difference in the speed. Here is a useful list of tips on how to code more efficiently in PHP…

http://reinholdweber.com/?p=3

This is a pretty good list and is pretty much all I can say on the subject, other than – and this applies to all other coding – to look through your code and try and remove redundancy.

MySQL

The general advise is..

1. Do as little SQL processing as possible. Reduce down what the SELECT fetches, if you can

2. One of the biggest things is select *. If you don’t need all the columns, only ask for the ones you need. If you don’t need all the rows, ask for the ones you need, but ask in a way that exploits the indexes already there.

3. Use Indexes wherever possible

And whilst I’m at it, don’t forget to look at SQL Injection. It won’t help with speed, but is essential for site security.

HTML/XHTML

Other than some of the tips mentioned previously (reduce code where appropriate, etc), there is no magic bullet for this one. Compression programs give very little benefit (reducing code by a couple of percent). There are some that make huge improvements but these come with decompression routines and can slow down the display of pages, as they are decompressed browser-side.

Additionally, many compression routines will strip out characters that will then mean the page can’t be validated without errors.

JavaScript

Unlike HTML/XHTML, I’ve found compression of JavaScript to be beneficial. My particular favourite is the one provided on the Creativyst website. The Litebox script, for example, will compress to 67% of its original size, saving a third of the total size. Unlike other sites and tools, I’ve never found the Creativyst version to break a script.

CSS

Like JavaScript, compression can help with CSS. Be wary, though, of various “hacks” that are used in CSS for cross-browser compatibility. Compression may break these.

Therefore, I put any such “hacks” in a separate CSS file, which I don’t then compress.

My recommendation is the online compressor at CSSdrive, which can provide some impressive results (a 28% saving on the CSS that I tried). Using this compresses the main stylesheet for the BMTG website down to 72% of its original size – a saving of 28% on pretty much each page.

I’d recommend you set the following options…

Having said that, I’ve achieved even greater compression using manual search/replaces (but doing it for each stylesheet every time I change them is laborious). One day I might write a program to do it for me!

.HTACCESS

There are two options, which can be set via your .htaccess file, which will help further.

1. GZIP – assuming your host server has GZIP installed, this will activate this compression functionality
2. Far_future_expires – this adds an “expires” header to your files

Both of these options should be used carefully and fully tested. The latter option, Far_future_expires, should be of particularly note. This caches JS and CSS the first time a user visits. It’s only then an advantage when they return again. Bear in mind that if you then change of these files you’ll need to give them a different name, so document control will be useful here.

Both of these, plus a minify option, is also available via a script named PHP_SPEEDY. I will be covering this in a second article, however.

Server Calls

The number of calls you make to the server for files to make up a resultant web page can have a big impact on load time. Combining images, stylesheets, etc, is the best solution.

Now, you can do this manually, or there’s a rather useful and clever script for automating this on the takaz blog.

Yahoo! Exceptional Performance

Yahoo! Have a section of their Developer Network titled Exceptional Performance. It states that their “team evangelizes best practices for improving web performance”.

First of all, I’d recommend their list of performance rules. This includes full explanations of each recommendation.

Secondly, and to back this up, they’ve introduced a Firefox plugin that will check a website for performance issues, using their aforementioned rules. This is named Yslow and requires the Firebug developer plugin as well.

Now, some of these recommendations are going to be for large-scale commercial sites, including having a number of interspersed host servers, etc., so I suspect it’s doubtful that anyone with a small scale site is going to be able to achieve a high rating.

If you’ve found this article of interest, then a PDF version is available for download below. I’m happy for you to use and distribute this, but please don’t edit it, sell it or generally be beastly.

improving-website-performance.pdf