Wednesday May 13, 2009

InnoDB Conference Presentations Now Online

Note: this article was originally published on on May 13, 2009 by Calvin Sun.

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:

Friday Apr 17, 2009

Only God can make random selections

Note: this article was originally published on on April 17, 2009 by Vasil Dimov.

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!.

So, on 32-bit machines, at some point the first record from the root page was picked instead of the second one, and this caused some changes in the results produced by ANALYZE TABLE. Yet, on 64-bit machines, for all practical purposes, this “never” happens. By only looking at the symptoms, one would get the impression that the flaw existed only on 64-bit machines and that 32-bit systems were ok.

Well, what about the fix? A possible fix would be to change InnoDB in 64-bit environments to behave the same way it does in 32-bit environments. People who are used to the behavior of InnoDB on 32-bit machines and upgrade to a 64-bit machine might be satisfied, because the problem on 64-bit systems was “solved”. But in reality, this approach in no way would fix the underlying problem. The real solution is to replace the random number generator with a better one (fully realizing that algorithmic random number generators are only pseudo-random number generators).

Yet even that is not so simple. Making any change would have caused changes to index cardinality estimations, thereby causing changes in decisions made by the optimizer, resulting in different execution plans … and different, possibly worse, performance for queries. Because MySQL 5.1 and 5.0 are frozen for such drastic changes, we fixed this bug in the upcoming 1.0.4 release of the InnoDB Plugin.

In order to not break existing applications, and since many people wanted a fix for MySQL 5.0 and 5.1, we implemented this fix for MySQL under the control of a new configuration parameter (innodb_use_legacy_cardinality_algorithm), which is turned on by default, preserving past behavior. Because the “right fix” is to permanently change the random number generator, this new configuration parameter is not present in the InnoDB Plugin, and the “more random” random number generator will always be used.

And that is the end of the case of mistaken identity. It turns out that it is really hard to generate truly random numbers, hence the title of this blog post.

Birds? Feathers? Plug In to a BoF on InnoDB!

Note: this article was originally published on on April 17, 2009 by Calvin Sun.

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!

Tuesday Mar 31, 2009

Software is Hard Sometimes …

Note: this article was originally published on on March 31, 2009 by Vasil Dimov.

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.

We thought about enabling this patch only on Linux. But that approach was not optimal, because the patch could work perfectly well on other platforms where pthread_t is the same size as the machine word, like FreeBSD and Solaris, for example. We could have simply enumerated the operating system names where the patch would be supported, but this too was far from perfect. The ability to use the atomic instructions depends not only on the operating system, but the GCC version (has to be 4.1 or newer) and the capabilities of the CPU (some CPUs do not support Compare-And-Swap!).

So, we developed a dynamic compile-time check for checking whether the target environment (pthread_t type and size, GCC version, CPU capabilities) supports the use of atomic instructions as intended. This seemed to be the best approach, but then another problem arose!

Dynamic compile-time checks (i.e. autoconf) are part of the MySQL’s ./configure script. Innobase does not control this script, and so we must live with what is distributed by MySQL. To make things simple for
users, we wanted to avoid asking the users to re-create ./configure with the autotools. So we simply injected the required checks in the Makefile. This seemed to work fine. Only after release of the InnoDB Plugin 1.0.3 did a small crack in this approach arise. Fortunately this problem turned out to be easy to fix and only occurs if one compiles with “make -j” (in order to perform the make in parallel). See Bug#43740 for details and for a fix.

The bottom line is that sometimes more goes on “behind the scenes” than you might expect when it comes to incorporating a third-party contribution into a product. We are grateful for the Google patch, and are glad that we have been able to include it in the InnoDB Plugin in a way that maximizes its portability, while keeping things as simple as possible for users.

Friday Mar 20, 2009

… and Who Could Forget Mark Callaghan?

Note: this article was originally published on on March 20, 2009 by Calvin Sun.

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”:

Sorry the other post didn’t mention you, Mark. We won’t forget to be at BOTH your sessions, and we look forward to hearing what you have to say!

Thursday Mar 19, 2009

Talk,Talk, Talk: Innobase Speaks

Note: this article was originally published on on March 19, 2009 by Calvin Sun.

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!

People are Talkin’ … about InnoDB

Note: this article was originally published on on March 19, 2009 by Calvin Sun.

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:


This is the InnoDB team blog.


« July 2016