Transactions on InnoDB

What a busy week! Lots of MySQL 5.5 announcements that just happened to coincide with the MySQL Conference and Expo in Silicon Valley. Here are some highlights of the performance and scalability work that the InnoDB team was involved with.

A good prep for the week of news is the article Introduction to MySQL 5.5, which includes information about the major performance and scalability features. That article will lead you into the MySQL 5.5 manual for general features and the InnoDB 1.1 manual for performance & scalability info.

Then there were the conference presentations from InnoDB team members, which continued the twin themes of performance and scalability:

• InnoDB: Status, Architecture, and New Features: Slides, Rate / leave feedback
• InnoDB Plugin: Performance Features and Benchmarks: Slides, Rate / leave feedback
• What’s New in MySQL 5.5? Performance/Scale Unleashed!: Slides, Rate / leave feedback
• What’s New in MySQL 5.5?: Performance and Scalability Benchmarks: Slides, Rate / leave feedback
• Introduction to InnoDB Monitoring System and Resource & Performance Tuning: Slides, Rate / leave feedback
• Backup Strategies with InnoDB Hot Backup: Slides, Rate / leave feedback

Read the rest of this entry »

Today, the InnoDB team announced the latest release of the InnoDB Plugin, release 1.0.4. Some of the performance gains in this release are quite remarkable!

As noted in the announcement, this release contains contributions from Sun Microsystems, Google and Percona, Inc., for which we are very appreciative. This page briefly describes each of the contributions and the way we treated them. The purpose of this post is to describe the general approach the InnoDB team takes toward third party contributions.

In principle, we appreciate third party contributions. However, we simply don’t have the resources to seriously evaluate every change that someone proposes, but when we do undertake to evaluate a patch, we have some clear criteria in mind:

• The patch has to be technically sound, reliable, and effective
• The change should fit with the architecture, and our overall plans and philosophy for InnoDB
• The contribution must be available to us under a suitable license

Let’s consider, in general terms, what these criteria mean in practice.

Read the rest of this entry »

Some months ago, Google released a patch for InnoDB that boosts performance on multi-core servers. We decided to incorporate the change into the InnoDB Plugin to make everybody happy: users of InnoDB don’t have to apply the patch, and Google no longer has to maintain the patch for new versions of InnoDB. And it makes us at Innobase happy because it improves our product (as you can in this post about InnoDB Plugin release 1.0.3).

However, there are always technical and business issues to address. Given the low-level changes in the patch, was it technically sound? Was the patch stable and as rock solid as is the rest of InnoDB? Although it was written for the built-in InnoDB in MySQL 5.0.37, we needed to adapt it to the InnoDB Plugin. Could we make the patch portable to many platforms? Could we incorporate the patch without legal risk (so it could be licensed under both the GPL and commercial terms)?

Fortunately Google generously donated the patch to us under the BSD license, so there was no concern about intellectual property (so long as we properly acknowledged the contribution, which we do in an Appendix to the manual and in the source code). So, while the folks at Google are known for writing excellent code, we had to thoroughly review and test the patch before it could be incorporated in a release of InnoDB.

The patch improves performance by replacing InnoDB mutex and rw-mutex with atomic memory instructions. The first issue that arose was that the patch assigned the integer value -1 to a pthread_t variable, to refer to a neutral/non-existent thread identifier. This approach worked for Google because they use InnoDB solely on Linux. As it happens, pthread_t is defined as an integer on Linux.

But we had problems when the patch was tested on FreeBSD. We still needed to reference a non-existent thread, but in some environments (e.g. some versions of HPUX, Mac OS X) pthread_t is defined as a
structure, not an integer. As we looked at it, the problem became more complex. For this scheme to work, it must be possible to change thread identifiers atomically, using a Compare-And-Swap instruction. Otherwise, there will be subtle, mysterious and nasty failures.

Read the rest of this entry »

Ooops! Mark Callaghan of Google is one of world’s experts in InnoDB, and a frequent blogger on its performance characteristics. The InnoDB Plugin 1.0.3 is much more scalable on multi-core systems because of the contributions he has made (along with Ben Handy).

Mark will deliver a keynote the on Google’s use of MySQL and InnoDB on Tuesday morning at the MySQL Conference, and another talk on Wednesday. As Mark says, “Although Innodb is not in the title, it is prominent in both of the talks I will do”:

• This is not a web app – the evolution of a MySQL deployment at Google Tues, 9am
• MySQL Performance on EC2 Wed, 3:05pm

Read the rest of this entry »

That should read “Talks, Talks, Talks” … There will be several presentations by InnoDB experts at the upcoming 2009 MySQL Conference and Expo. Whether you’re a newbie or an experienced DBA deeply familiar with InnoDB, you won’t want to miss these important talks about InnoDB:

• Innovative Technologies for Performance and Data Protection by Ken and Heikki, Tues, 11:55am
• Crash Recovery and Media Recovery in InnoDB by Heikki, Wed, 2pm
• InnoDB Internals: InnoDB File Formats & Source Code Structure by Heikki and Calvin, Wed, 5:15pm
• Concurrency Control: How it Really Works by Heikki, Thurs, 2:50pm

Note the new times for the last two talks above. Be sure to check the conference schedule! Not much more to say about this topic, at least not here. Hear it all there!

InnoDB executes a lot of transactions for MySQL users. And users and technical experts in the InnoDB community exchange lots of information about InnoDB … transacting in ideas … all the time. A great opportunity to participate in some of these transactions is the upcoming MySQL Conference and Expo.

Does your InnoDB-based application need more transactions to execute more quickly and reliably? Come hear these noted speakers present information that can help you get the most out of InnoDB:

• Distributed InnoDB Caching w/memcached — Matthew Yonkovit & Yves Trudeau (MySQL), Tue 2pm
• InnoDB database recovery techniques — Peter Zaitsev (MySQL Performance Blog), Wed 3:05pm
• High Availability & Scalability Patches from Google — Ben Handy & Justin Tolmer (Google), Wed 4:25pm
• InnoDB Performance & Usability Patches — Vadim Tkachenko & Yasufumi Kinoshita (Percona), Thu 2pm

Read the rest of this entry »

Why should you care about the latest “early adopter” release of the InnoDB Plugin, version 1.0.3?   One word: performance! The release introduces these features:

• Enhanced concurrency & scalability: the “Google SMP patch” using atomic instructions for mutexing
• More efficient memory allocation: ability to use more scalable platform memory allocator
• Improved out-of-the-box scalability: unlimited concurrent thread execution by default
• Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing

These new performance features can yield up to twice the throughput or more, depending on your workload, platform and other tuning considerations. In another post, we explore some details about these changes, but first, what do these enhancements mean for performance and scalability?

In brief, we’ve tested three different workloads (joins, DBT2 OLTP and a modified sysbench) using a memory-resident database. In all cases, the InnoDB Plugin scales significantly better than the built-in InnoDB in MySQL 5.1. And in some cases, the absolute level of performance is dramatically higher too! The charts below illustrate the kinds of performance gains we’ve measured with release 1.0.3 of the InnoDB Plugin. Your mileage may vary, of course. See the InnoDB website for all the details on these tests.

This release of the InnoDB Plugin incorporates a patch made by Ben Handy and Mark Callaghan at Google to improve multi-core scalability by using more efficient synchronization methods (mutexing and rw-locks) to reduce cpu utilization and contention. We’re grateful for this contribution, and you will be too!

Read the rest of this entry »