After our first two posts on MySQL storage engines and general MySQL tips, our senior sysadmin Nathan March closes the week out with some discussion of Percona Server.

As mentioned in the earlier post, our hosting services make use of the Percona Server with it’s XtraDB engine as a drop-in replacement for MySQL and InnoDB. These tips apply specifically to the Percona fork and will not work on a stock MySQL.

• Percona’s InnoDB engine does adaptive checkpointing meaning it uses spare I/O to checkpoint whenever it feels it can. Generally, as long as the server’s disks are fast enough it shouldn’t be too full of dirty pages. This helps keep shutdown and crash recovery times lower and is recommended. Read more here.
• If you need to restart your server and have a large number of dirty pages, you can force the Percona fork to allow a lower percentage of dirty pages. This will force it to write most of the dirty data to disk. Read more here.


A usage graph from one of our Percona servers, featuring 2900 selects per second!

• We can save or restore the buffer pool that’s in memory, meaning only two or three minutes will be spent restoring a site instead of slowing the site down for half an hour while the database warms up. This is good for scheduled maintenance in which you need to reboot a machine or just restart MySQL for tuning. The dump file is just a set of pointers, so it’s small even for a 60GB buffer pool. Read more here.
• In order to get good database backups of larger clients’ sites (those over 20GB) we use tools called innobackupex and xtrabackup. These work by copying the raw ibd files out of MySQL, then replaying the transaction logs (ib_logfiles) against them in order to get all the tables to an exact consistent point. As part of this, the replication master position for that point is also recorded so that we can use that backup for setting up a replication slave or other disaster recovery method. Since the backup contains a full MySQL instance, recovery time is low and doesn’t require restoring from a mysqldump.