dcsimg

XtraDB: InnoDB on Steroids

Percona's XtraDB is a fork of InnoDB with a ton of extra options and enhancements. Here's a look at what it can do for your busy database.

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.

The full list of additions to SHOW STATUS in XtraDB is here.

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.

The documentation for the new data in INFORMATION_SCHEMA here.

What’s Next?

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.

Comments on "XtraDB: InnoDB on Steroids"

hello there and thanks to your info ? I have certainly picked up anything new from right here. I did then again expertise a few technical points the use of this website, since I experienced to reload the web site many times previous to I could get it to load properly. I had been considering if your web host is OK? No longer that I’m complaining, however slow loading circumstances occasions will often have an effect on your placement in google and can damage your high-quality rating if advertising and marketing with Adwords. Well I’m including this RSS to my e-mail and can look out for a lot extra of your respective intriguing content. Ensure that you update this once more very soon..

Reply

Looking forward to reading more. Great article post.Really looking forward to read more. Really Cool.

Reply

Very informative blog.Much thanks again. Really Cool.

Reply

I enjoy what you guys are usually up too. This sort of clever work and coverage! Keep up the good works guys I’ve added you guys to my personal blogroll.

Reply

whoah this blog is wonderful i love reading your posts. Keep up the great work! You know, a lot of people are searching around for this info, you could help them greatly.

Reply

Normally I don’t read post on blogs, but I wish to say that this write-up very forced me to try and do it! Your writing style has been amazed me. Thanks, very nice post.

Reply

Hey there. I discovered your site by means of Google even as searching for a similar topic, your website came up. It seems great. I have bookmarked it in my google bookmarks to come back then.

Reply

Wow! This could be one particular of the most helpful blogs We have ever arrive across on this subject. Basically Fantastic. I am also an expert in this topic so I can understand your effort.

Reply

Here are several of the web sites we advocate for our visitors.

Reply

Great post. I am facing a couple of these problems.

Reply

Just beneath, are many entirely not connected web sites to ours, however, they are surely really worth going over.

Reply

eaIuVt I will right away grab your rss as I can at to find your email subscription hyperlink or newsletter service. Do you have any? Please allow me realize so that I may subscribe. Thanks.

Reply

Its like you read my mind! You appear to know so much about this, like you wrote the book in it or something. I think that you could do with a few pics to drive the message home a little bit, but instead of that, this is great blog. A fantastic read. I’ll definitely be back.

Reply

We came across a cool website which you may get pleasure from. Take a search should you want.

Reply

We like to honor quite a few other net web-sites around the web, even if they aren?t linked to us, by linking to them. Beneath are some webpages worth checking out.

Reply

Really enjoyed this blog post, is there any way I can get an update sent in an email when you write a fresh post?

Reply

Although websites we backlink to beneath are considerably not associated to ours, we feel they are in fact really worth a go by way of, so possess a look.

Reply

Wonderful story, reckoned we could combine several unrelated information, nonetheless actually really worth taking a look, whoa did one find out about Mid East has got much more problerms as well.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>