A huge increase in traffic working with http://www.loverugbyleague.com has led us to looking at many ways to help improve the way the site works to try and squeeze more performance out of the current server. One of the first things that was noticed was that with the increase in access the database tables were locking up and delaying return of results and causing problems.

A look into the setup of the database exposed an issue as all the tables were using MYISAM which, as far as I know, used to be the default for a phpmyadmin table. I’m sure all but the most savvy of us give little thought to the type of storage engine we use for a table and just stick with the default, however it became apparent MYISAM was causing us a few problems and isn’t really suitable for high traffic, high access sites.
The problem comes with the fact that MYISAM will lock up an entire table when doing anything on a row, including accessing, updating and inserting rows, and as a result requests quickly queue up and start to grind everything to a halt during high traffic periods.

The tables were therefore switched to InnoDB which works with row level locking, meaning that rather locking up the entire table it will lock up only the row in use and allow the rest of the table to be accessed. This means that multiple requests can be made to the same table at the same time provide they are reading/updating different rows. It is much more suited to high access on large tables, especially where different rows are being accessed in the table, if the same row needs to be accessed repeatedly it may be worth looking at some caching of that information.

The change quickly reduced the number of locked requests and things started to run a bit more smoothly. There have been other things looked at to help optimise the site and it’s quickly apparent there is a huge amount to do in helping squeeze out every bit of performance of a site but taking little steps like thinking more about your database storage can really start to help.