Executive Summary

Fast indexing requires the leaves of a Fractal Tree® Index to be big. But some queries require the leaves to be small in order to get any reasonable performance. Basements nodes are our way to achieve these conflicting goals, and here I’ll explain how.

Big Leaves

On many occasions, we at Tokutek have pointed out that TokuDB is write optimized, which means TokuDB indexes data much faster than a B-tree solution such as InnoDB. As with any write-optimized data structure, Fractal Tree indexes need to bundle up lots of small writes into a few big writes. Otherwise, there’d be no way to beat a B-tree. So the question is, how big do the writes have to be?

Consider how long it takes to write k bytes to a disk. First, there is the seek time s, which we can assume to be independent of k. Next, once we’ve moved the disk head somewhere, we need to write the bytes, which …

Many do not need any further introduction to this Monty tradition at MySQL events. For the New York Effective MySQL Meetup group this was a new experience for many that I had the opportunity to share at our recent meeting. In 12 months the group has grown to over 280 members, and now recent attendees have experienced black vodka first hand.

A special thanks to Monty Program AB and Colin Charles for providing the alcohol.

Once MySQL is deployed inside a datacenter environment (i.e. forms a cloud ;-), major feature in it becomes replication. It is used to maintain hot copies, standby copies, read-only copies, invalidate external systems, replicate to external systems, etc. If this functionality is broken, datacenter is broken – components are not synchronized anymore, invalidations not done, data not consistent.

From performance perspective, replication not working properly results in unusable slaves so load cannot be spread. This results in higher load on other machines, including master (especially on master, if environment needs stronger consistency guarantees).

Judging on replication importance in MySQL deployments, it should attract performance engineering as much as InnoDB and other critical pieces. Though slave replication performance is being increased in 5.6, master side is not (well, group commit may help a bit, but not as much).

…

As many of you may know the 2012 UEFA European Football Championship is just around the corner1. For the occasion I developed a web-based betting game to introduce a little bit of competition between me and my friends as well as make the Championships even more exciting. But then I thought “Hey, why not to do this for a bigger community as well”? So dear MySQLers, I invite you to join the special edition of my game here at dba square!

The game is available at http://www.dbasquare.com/euro2012/ or you can simply get there from the blog’s main menu at the top of each page.

We are offering you:

• a little bit of fun
• a free gambling opportunity
• some friendly competition

The game is simple and works similarly to the real sports bookmaking, although there is …

Some time ago I finally had the chance to test the built-in load balancing feature in MySQL's JDBC driver together with a 3 node Galera cluster. I have used this feature at a MySQL Cluster customer many years ago, so I knew it worked and I knew it was great, but I didn't know if it would work with Galera. Galera sometimes returns some error states that are different from what MySQL Cluster does and the main point of the test was to see how the loadbalancing in the JDBC driver reacts to that.

read more

Today was a great day. The MySQL Innovation Day had a great turn out. The  room was packed and numerous others watching the webcast. Many thanks to the MySQL community for coming out to support this event. 

Here is a few quick highlights from MySQL Innovation Day:

The GPL Community version of the MySQL server will have:

    -- MySQL Replication has a lot of different enhancements, the most ever in a release.
         -- High Availability and Fail-over
            -- Global Transaction Ids
            -- Replication Administration and Fail-over Utilities
         -- Better Data Integrity
           -- Crash-Safe …

Back in 2006 we became aware of problems storing large numbers of rows in a single table in cluster. Johan Andersson and Yves Trudeau have each blogged about the problem and the common workaround here and here.  We've since then done some cleanup to provide a more proper "Table is full" error message when running into this problem.

As explained in the referenced blog posts, the problem is the result of a limitation on the size of the hash index of each partition. The hash index for each partition would allow at most ~49 million records. By default an ndbd or ndbmtd node have only 1 local query handler (LQH) block and thus 1 partition per node.  The ndbmtd nodes having MaxNoOfExecutionThreads = 4 or 8 cluster will have 2 or 4 LQH per node respectively. So, the …

Performance Schema (PFS) in MySQL 5.6 is coming yet more with many awesome new features. But, as it was already discussed in the past, enabling PFS instrumentation may create an additional overhead within your MySQL server, and as a result, decrease an overall performance.. The good news is that in MySQL 5.6 things become better and better ;-))

Well, you should understand as well, there is no miracle.. - some of most "hot" events within MySQL are happening over several millions(!) or tens/hundreds millions per second(!) -- so, it's sure, once such kind of event is traced, the overall overhead of the MySQL code may only be increased.. What is important that every instrumentation can be enabled or disabled dynamically, so you can go in depth progressively when tracing your events or bottlenecks, and stop …

MySQL and Continuent Tungsten at Constant Contact - How We Architected Our Replication StrategyThursday, June 14th10:00 am PDT/1:00 pm EDT19:00 CEST/18:00 BSTReserve your seat!Constant Contact is a provider of marketing services for over 500,000 small businesses and organizations worldwide, helping them to drive engagement and build relationships with current and prospective customers.As the

Stalls During DDL, Faster DROP TABLE Courtesy of the InnoDB Team

Mark Callaghan mentioned a simple issue on DDL operation. I found the back porting interesting, but not always a possible option. I have nothing to comment on top of what was already stated there, but I’ll mention it because it is interesting to read.

Why do threads sometimes stay in ‘killed’ state in MySQL?

Customers often ask me (and I believe all MySQL DBAs can relate): “Why did that query remain in a KILL state and does not go away?”

As we know, KILLed queries remain in MySQL until a clean up takes place, given that the action is not done synchronously. I found this article to be a good description of such events …