MySQL FreeBSD 7 ZFS Beta update & MySQL upgrades

Some time ago, we announced a beta test of MySQL running on FreeBSD 7 using the experimental ZFS filesystem. We’ve now concluded that beta test, and I wanted to let you know the results.

Here’s the executive summary: FreeBSD 7 is, by and large, great for us and we will be aggressively deploying it throughout our network in the near future. ZFS is also great, but while it definitely has a future on our network someday, this is not that day.

FreeBSD 7 offers significant benefits for SMP scalability, which is pretty important to us since most MySQL processes and member sites run on eight-core servers. The performance benefits on MySQL are very real, particularly on CPU-intensive workloads (which tend to be common if your tables are indexed properly). The performance benefits for member sites are less pronounced for some pretty complicated reasons, but they’re still there and they’re still worth pursuing.

ZFS is a really neat technology. By and large, it works pretty well. It’s not a great match for a MySQL server, though, since it seems to want a lot of CPU and RAM at the same time MySQL wants lots of CPU and RAM, because it works hard when asked for data and MySQL asks for data when it’s working hard. Thus, we noticed a little bit of contention and we were concerned about what would happen in low memory conditions, such as if a bunch of MySQL processes happened to surge busily at once. ZFS seems best suited to sticking on super-tightly controlled file servers that have gigs of RAM free and don’t do anything else. Even so, we’re not 100% sold on its stability, so we’ll revisit it sometime in the future and do further testing before we start loading it up on mission-critical file servers.

All of our MySQL FreeBSD 7 ZFS beta volunteers have been migrated to production FreeBSD 7 (non-ZFS) MySQL servers. They’re just the vanguard, though; we will be aggressively upgrading almost all member MySQL processes over the next few days.

The actual process is a bit complicated, but here are the essentials: we’ve brought some new hardware online running the target environment (FreeBSD 7, MySQL 5.0.67). Call that “Server A.” We’ve moved all the MySQL processes from Server B (one of the older ones) onto server A. Then, we reinstall Server B. Then we move everyone on Server C to Server B and reinstall server C. And so on, down the line.

The upgrade requires no action on your part and entails MySQL downtime averaging less than five minutes; a significant percentage of our MySQL-using members will notice only one to two minutes of disruption, if they notice it at all. Even so, we’re limiting it to low-usage periods (certain times during weekends, late nights, and early mornings). Due to the number of processes being upgraded and our need to schedule upgrades based on specific server assignments, we will not be able to coordinate individual process upgrades.

We do still have some longtime members hanging around on MySQL 4.1 running on FreeBSD 4. Since MySQL 4.1 to MySQL 5.0 isn’t always a seamless migration, we’ll be contacting those affected to let them know their situation and try to coordinate their journey out of the stone age before we do anything there.

If you want to see what version of FreeBSD or MySQL is powering your MySQL process, you can use the SQL command “SHOW GLOBAL VARIABLES” and look at the “version” and “version_compile_os” variables, or access them in queries as “SELECT @@version” or “SELECT @@version_compile_os.” The former shows the MySQL server version (which is also displayed in phpMyAdmin and by the MySQL command line client), and the latter shows the OS version: “portbld-freebsd6.3” for FreeBSD 6.3 and “portbld-freebsd7.0” for FreeBSD 7.0.

We also have a significant RAM and CPU power upgrade planned for MySQL once this upgrade is complete. That will require a brief off-hours downtime for some MySQL processes, which we will schedule and announce at a future time.

Finally, though MySQL processes will never be able to flit around our network the way web requests do, we are taking baby steps in the direction of SAN-based MySQL storage in order to decrease the lock-tight correlation between MySQL processes and specific frontend hardware.

2 Comments

RSS feed for comments on this post.

1. Any timetable for this to be done? A month from now, maybe? Less? More? Could you give a rough estimate?

   Comment by Tyler — September 29, 2008 #

2. This is great news! My MySQL databases are already running on FreeBSD 7.0.

   Thank you guys for the great work. Keep it up!

   Comment by Vasilis — September 29, 2008 #

Sorry, the comment form is closed at this time.