XtraDB: InnoDB on Steroids

Last week I spent a bit of time looking at The State of MySQL and mentioned some of the “outside innovation” taking place in the community. This week I’d like to focus on one of the more significant developments: XtraDB and xtrabackup, both developed by Percona.

Now you may not have heard much about Percona before. Founded in 2006 by Peter Zaitsev and Vadim Tkachenko with the goal of providing consulting for MySQL users, the company has grown in the last few years to employ some of the leading experts in MySQL and InnoDB internals. Several of the employees work on enhancing MySQL and InnoDB to meet the needs of their [paying] customers as well as the community at large. They’ve contributed numerous patches and ideas as well as writing on their popular MySQL Performance Blog and presenting at various conferences.

Background

Late last year they surprised the community when they announced XtraDB as fork of InnoDB with numerous performance and reporting enhancements, not the least of which is binary compatibility with InnoDB database files. In doing so, they’ve made the cost of giving XtraDB a test drive very, very low.

As you may recall, MySQL 5.1 introduced a Plugin API that made it possible to install and use separately built plugins without recompiling MySQL itself. The InnoDB storage engine was the first to take advantage of this and release the InnoDB plugin. XtraDB is, quite literally, a drop-in replacement for the InnoDB plug-in.

xtrabackup is Percona’s answer to the InnoDB hot backup product. It provides on-line backup capabilities without shutting down the database or incurring unnecessary locking overhead. If you end up using XtraDB in any serious capacity, xtrabackup is a must-have tool–far more efficient than using mysqldump for backups.

Best of all, both XtraDB and xtrabackup are available under the GPL, and source code is available on Launchpad. Percona provides paid support for both products and there are no licensing fees.

Let’s take a few minutes to look at some of the features that distinguish XtraDB from basic InnoDB.

Behind the Scenes Fixes

The hardware world has changed quite a bit since InnoDB was created. As we’ve begun to use servers with more and more CPU cores, numerous glass ceilings have been exposed along with assumptions that need to be re-thought. Over time, people have been discovering and fixing these problems in InnoDB. Percona has done some of that work themselves as well as incorporating ideas and code from the MySQL team at Google and Jeremy Cole of Proven Scaling.

Some of these changes in XtraDB revolve around locking and lock contention for particularly “hot” areas of the code and frequently accessed data. XtraDB contains more granular locking for access to InnoDB’s buffer pool, which makes it possible for a higher number of threads to make changes at the same time. Better yet, the actual lock implementation has been made more efficient as well, causing fewer context switches and higher overall throughput. Some of this work, which initiated at Google, has since been worked into the mainline InnoDB engine by Oracle as well.

I/O Related Enhancements

As detailed here XtraDB contains numerous fixes to provide more control over disk I/O. These are mainly useful when you have a server with heavy I/O workloads, but they may also help in other busy workloads or on more exotic hardware (Amazon EC2 running RAID across many EBS volumes, for example).

By default, InnoDB provides a single read and a single write thread for handling disk I/O. However, on disk arrays with substantial I/O capabilities (or SSDs), it’s worth increasing the number of threads. The read thread fetches pages from disk and stores them in the buffer pool, while the write thread flushes dirty pages from the buffer pool to disk. The transaction log I/O is handled elsewhere. In XtraDB you can set innodb_read_io_threads and innodb_write_io_threads each to values from 1 to 64.

Simply adding I/O threads may not be the solution to an under-utilizied I/O subsystem. By default, InnoDB assumes that a single server can handle 100 I/O operations (reads or writes) per second. That makes a lot of sense if you’re running InnoDB on a machine with a single commodity hard disk. But if you have many more disks or SSDs, InnoDB ends up throttling itself back far more than necessary. This hard-coded value can be adjusted by setting innodb_io_capacity to a higher value.

Speaking of SSDs and data flushing, the parameter innodb_flush_neighbor_pages can be set to either 0 or 1 to tell InnoDB if it should flush adjacent pages at the same time. This is normally done to reduce disk seeks on write, since neighboring pages are likely to be in neighboring sectors on disk as well. But SSDs don’t have a seek penalty. That same may be true of SANs and other high-end storage systems.

Reporting Enhancements

One of the biggest issues heavy InnoDB users eventually encounter is a lack of transparency. Once you go below the basics of what’s exposed in the output of SHOW ENGINE INNODB STATUS, InnoDB starts to feel a lot like a black box. You have a general idea of what’s going on and you can develop some intuition about where bottlenecks might be, but it’s largely guesswork. XtraDB provies two things to address this.

First, XtraDB has some additional engine status enabled by default. It will report on the sizes of InnoDB’s internal hash tables, including the adaptive hash index, page hash, dictionary cache, and several others. By setting innodb_show_verbose_locks to 1 and innodb_show_locks_held to a numeric value, you can instruct InnoDB to include more detail about locks. It will display up to innodb_show_locks_held locked records per transaction. This can be incredibly useful in diagnosing deadlock conditions or poor concurrency.

XtraDB also adds to the information available in the INFORMATION_SCHEMA tables as well. It adds an XTRADB_ENHANCEMENTS table and provides extra detail in other tables to give you more insight into the buffer pool pages, rollback segments, table statistics, and index statistics.

Believe it or not, this only scratches the surface of the changes and enhancements available in XtraDB. There are a surprising number of tunable parameters that can help you squeeze that last 5-10% out of your busy, seemingly over-worked servers. XtraDB is still in active development, and feedback from end users and folks like Mark Callaghan and his team at Google are providing ample feedback for future development.

If you’re looking for “a little bit more” from InnoDB, I’d encourage you to download XtraDB and give it a try–at least on one of your slaves to start.

I like the valuable information you provide for your articles. I’ll bookmark your blog and test again right here frequently. I’m rather sure I will be informed plenty of new stuff proper here! Best of luck for the following!

Do you mind basically if i quote a few of the posts so long as I provide credit
and sources returning to your webpage? My website is within the exact same area of interest as yours and my users would genuinely make use
of the vast majority of information you provide here.
Please inform me if this type of ok along. Many thanks!

Thank you for another informative web site. Where else could I get that kind of info written in such a perfect way? I’ve a project that I am just now working on, and I’ve been on the look out for such information.