Downtime

As any webmaster will know, downtime is evil. It’s to be avoided at all costs. When it happens, you look bad, you lose visitors, and if you’re a commercial entity, advertising revenue.

The Nouse site was down for a few hours around midday today, and although as much I hope to be able to say “it’s not my fault” and blame external factors, it was. Problems I did not foresee during what should have been a lightweight procedure essentially crippled our MySQL server, until I figured out the problem and solved it.

Last night I started an overhaul of our databases. Due to a legacy issue with WordPress, our database stored UTF-8 text, but it was identified as Latin-1. This caused a huge number of internal problems – backups are double encoded UTF-8 (which is horrible, every quote is replaced with three characters) and the databases actually held incorrect data, which made doing anything outside of WordPress complex – MySQL would convert any UTF-8 data incoming to Latin-1, despite us actually wanting UTF-8.

The solution was deceptively simple, and thanks to blogger Alex King, I solved this last night.

The motivation to solve this is that this character encoding issue was blocking me from introducing something I wanted to do, and mentioned a few entries ago – migrating the original Nouse site over to WordPress.

It’s this migration that took the site down today. Our central “wp_posts” table hits 213 MB in size, with the vast majority of that being fulltext search indexes for the search function and the related posts plugin. It’s these indexes that started to kill the site. An INSERT was taking up to 2 minutes, which was fine on the wp_posts table in our testing site because nothing else was trying to read that at the same time, but what I did not realise was when I ran the operation live on the server, it would start blocking normal wp_post reads, tying up Apache processes and quickly hitting the connection limit.

It took me quite a long time to realise the cause of the long inserts. Once I did, a simple ALTER TABLE wp_posts DISABLE KEYS solved it, re-enabling at the end to enable the fulltext searching once again. This change was dramatic, instead of running for an hour and only getting a sixth of the way through the conversion, by the time I had switched back to the other terminal, the job had completed.

So, I apologise for the downtime earlier – I had a few people e-mail me to complain about the site being down, but think of the silver lining on the cloud – we now have an additional 800 or so articles in our archive, stretching back to 2003.

The articles back then are written in a different style, less “broadsheet”, more of an “us against them” mentality (Nouse wasn’t redesigned into it’s current form until 2005 – look at the old PDFs for comparison!), but for people like me who like seeing the way things were and with posterity, they’re fascinating.