Transactions on InnoDB

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 »

Well, it took us a little while (we’ve been busy !), but we’ve now posted our presentations on InnoDB from the MySQL Conference and Expo 2009. You can download these presentations by Heikki Tuuri, Ken Jacobs and Calvin Sun from the InnoDB website, as follows:

• Ken and Heikki: InnoDB: Innovative Technologies for Performance and Data Protection
• Heikki: Crash Recovery and Media Recovery in InnoDB
• Heikki: Concurrency Control: How it Really Works
• Calvin and Heikki: InnoDB File Formats and Source Code Structure

The description of these and other presentations about InnoDB are available here.

Recently, it was reported (see MySQL bug #43660) that “SHOW INDEXES/ANALYZE does NOT update cardinality for indexes of InnoDB table”. The problem appeared to happen only on 64-bit systems, but not 32-bit systems. The bug turns out to be a case of mistaken identity. The real criminal here wasn’t the SHOW INDEXES or the ANALYZE command, but something else entirely. It wasn’t specific to 64-bit platforms, either. Read on for the interesting story about this mystery and its solution …

InnoDB estimates statistics for the query optimizer by picking random pages from an index. Upon detailed analysis, we found that the algorithm that picks random pages for estimation always picked the same page, thus producing the same result every time. This made it appear that the index cardinality was not updated by ANALYZE TABLE. Going deeper, the reason the algorithm always selected the same page was that the random number generator always generated numbers that, when divided by 3, always gave the same remainder (2).

The sampling algorithm selects a random leaf page by starting from the root page and then selecting a random record from it, descending into its child page and so on until it reaches a leaf page. In the particular case that was reported in the bug report, the root page contained only 3 records and the tree height was only 2 (i.e., the leaf pages were all just below the root page).

You can already guess what happened. The “random” numbers generated, not being so random, caused the algorithm to always pick the same record from the root page (the second one) and then descend to the leaf page below it. Every time. So, the 8 random pages that were sampled in order to get an estimate of the whole picture were in fact the same page, even in isolated ANALYZE TABLE runs.

So, clearly there was a problem with the random number generator. But why didn’t this problem seem to appear on 64-bit platforms? It would have, had we only enough time to wait. The random number generator, always generating numbers like 3k+2 of type unsigned long, at some point wrapped around 4 billion on 32-bit machines and started generating numbers like 3k+1. On 64-bit machines, where unsigned long is much bigger, this wrap did not occur. But it would have occurred if we ran the test for 1000 years!.

Read the rest of this entry »

There is going to be a lot of InnoDB news at the MySQL Conference and Expo … including some surprises! Wear your bird costume (or not), but please join us for a Birds of a Feather session about InnoDB products and technology at 7:30pm Wednesday, in Ballroom C.

You’ll have a chance to dig deeper into the topics you’ve learned about for the first time at the conference, and can chat with Heikki Tuuri, Calvin Sun and Ken Jacobs and others about all things InnoDB.

Largely unstructured and open, we’ll discuss the latest version of the InnoDB Plugin and answer your technology questions about the newest InnoDB products. It’s also a great opportunity to network with other users and experts in InnoDB technology and learn about how to get the best out of MySQL for your application. Come ask questions and get answers about this technology that is so critical to YOUR success!

See you there!

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 »

Well, here we are … the first post to the InnoDB blog.   Now there is a blog dedicated solely to InnoDB products and technology. The Innobase team will be posting here regularly on all manner of topics regarding the InnoDB storage engine.  We plan to provide timely updates and important technical information about InnoDB-related products including the built-in InnoDB distributed by MySQL, the InnoDB Plugin and InnoDB Hot Backup.  We invite you to visit regularly and post your comments.

We’ve borrowed the name “Transactions on” from the computer-science journal Transactions on Database Systems, published by the ACM society for computing professionals.  Like that journal, this blog will cover a wide range of database topics, specifically as they relate to InnoDB.

Users of InnoDB know a transaction is an atomic all-or-nothing set of changes made to a collection of data.  But according to Webster’s Dictionary, a transaction also is “a communicative action or activity involving two parties or things that reciprocally affect or influence each other”.  So, a transaction is also an exchange of ideas.

Welcome to this place to transact in ideas about InnoDB!