We’ve finally hit the point at work where we’re ready to upgrade from MySQL 4.0 to 5. Data integrity has been a problem since I started at this job. Part of the reason is that MySQL 4.0 has an odd (to put it lightly) interpretation of not null and we still have a fair amount of legacy Perl code running the administrative portion of the site that lets bad data through. The lack of subselects for ad-hoc querying, aggregation functions, stored procedures, and triggers has also grown more irritating knowing that MySQL 5 has those features.
The first step was to bring in our old systems administrator JD, who now works for MySQL to talk us through his take on the stability of MySQL 5 based on his experience with other high volume clients. He also helped us think through the issues we might run into with an upgrade. I highly recommend JD to anyone on the west coast considering running MySQL on a high traffic site, he really knows his stuff!
In our case we have about 4.5GB of data and the primary read-write server has 55 databases on it (1 for each state and 3 additional ones). To handle the production loads 1 server is the master read-write server and then there are 2 read-only replicant servers. If the main read-write server fails we can quickly make one of the other servers the read-write master. Additionally we have 1 read-only replicant in the office (as opposed to the data center) and our development and staging servers. All in all we’re looking at 6+ upgrades.
To test the process and look for issues we’ll first be upgrading our dev and staging servers carrying out load tests before and after the upgrade to see if there are any significant performance problems to be aware of. If all goes well, I hope we’ll be up and running on MySQL 5 within the next month!