Regular readers of this Blog or attendees at the 2010 O’Reilly MySQL Conference may recall a feature that the MySQL Cluster team were working to speed up JOINs (see Jonas’s slides from that conference here). The good news is that work has continued since then and it is now part of the new MySQL Cluster 7.2 Milestone Development Release. This post will step through where to get hold of the binaries and see the benefits for yourself. The examples I try here result in a 25x speedup just by turning the feature on – I’ve also seen a 50x speedup on other queries!

We’re anxious to get feedback on what benefits you see with your application’s JOINs, please respond to this post.

What’s in a name?

If some of this seems familiar but you don’t remember …

We had the opportunity to use a fair amount of machines to run a benchmark to see what throughput MySQL Cluster can achieve on a bit bigger clusters. The benchmark we use is a benchmark we developed for internal testing many years ago and shows very well the performance aspects of MySQL Cluster as discussed in some previous blogs of mine.

The benchmark is called flexAsynch, it's part of an internal series of benchmark we call the flex-series of benchmarks. It's first member was flexBench, this benchmark consisted of the following simple set of operations. First create the table with the set of attributes and the size of the attributes as specified by the startup options. Next step is to create a set of threads as specified by the startup options. Next step is that each thread will execute a number of transactions, the number which is configurable and each transaction can also run one or more operations as configured (one operation is …

For those interested in InnoDB internals, this post tries to explain why the global kernel mutex was required and the new mutexes and rw-locks that now replace it. Along with the long term benefit from this change.

InnoDB’s core sub-systems up to v5.5 are protected by a global mutex called the Kernel mutex. This makes it difficult to do even some common sense optimisations. In the past we tried optimising the code but it would invariably upset the delicate balance that was achieved by tuning of the code that used the global Kernel mutex, leading to unexpected performance regression. The kernel mutex is also abused in several places to cover operations unrelated to the core e.g., some counters in the server thread main loop.

The InnoDB core sub-systems are:

1. The Locking sub-system
2. The Transaction sub-system
3. MVCC  views …

MySQL replication is fast, easy to use, and reliable, but once it breaks, it can be very hard to figure out what the problem is. One of the concerns often raised is that events are corrupted, either through failing hardware, network failure, or software bugs. Even though it is possible to handle errors during transfer over the network using an SSL connection, errors here is rarely the problem. A more common problem (relatively) is that the events are corrupted either due to a software bug, or hardware error.

To be able to better handle corrupted events, the replication team has added replication event checksums to MySQL 5.6 Milestone Development Release.

The replication event checksums are added to each event as it is written to the binary log and are used to check that nothing happened with the event on the way to the slave. Since the checksums are added to …

Background

InnoDB gathers statistics for the data in user tables, which are used by the MySQL optimizer to choose the best query plan. For a long time the imprecision and instability of these statistics have been creating problems for users.

The problem is that these statistics are recalculated at any of the following events:

* When the table is opened

* When the table has changed a lot (1/16th of the table has been updated/deleted or inserted)

* When ANALYZE TABLE is run

* When SHOW TABLE STATUS or SHOW INDEX FROM is run

* When InnoDB Monitor is turned ON

* others

and so their recalculation must be quick an unnoticeable. Thus the quick algorithm just picks 8 random pages and could give a wildly varying results.

…

One of the most important things a database user or DBA wants to know is what columns, indexes etc. a table has. There are a few ways to find these things out, such as show tables. However, to really reveal all of the detailed metadata information in an InnoDB database, many have tried the “innodb_table_monitor” to peek into internal data dictionary. By creating an “innodb_table_monitor” table, InnoDB will print out the contents of metadata periodically. Unfortunately, it is printed out in an unstructured text for each table, and to find out what you need you would have to either carefully scan the output or have your own parser to do some additional analysis on the result if you want to display them systematically.

Well, in the MySQL 5.6 release, “innodb_table_monitor” can become history, and you will no longer need to search the text output to find out system metadata information. Instead, you can query the InnoDB internal …

Last week was rather less newsworthy in Percona Server land, as we spent a lot of time preparing for the MySQL Conference and Collaborate, and Vadim was on vacation. One interesting tidbit: I wrote some scripts analyze our log files and found that we were massively under-counting the number of downloads for Percona Server, due to failing to count all the variations of package names. In fact, we have over 250k downloads for Percona Server. (I’ve requested the dev team to be very careful with consistent naming in the future.) In Percona Server news, we continued polishing up the 5.5 release, which should be ready to download very soon. In XtraBackup news, we should release XtraBackup 1.6 with a large list of new features and improvements very soon, and we worked more on Drizzle support for XtraBackup, exchanging lots of merge proposals with the Drizzle development team.

Sharding is here defined as the ability to partition the data into partitions defined by a condition on a set of fields. This ability is central to the workings of MySQL Cluster. Within a Cluster we automatically partition the tables into fragments (shards in the internet world). By default there is a fixed amount of fragments per node. As mentioned we also use replication inside a Cluster, the replication happens per fragment. We define the number of replicas we want in the Cluster and then the MySQL Cluster SW maintains this number of fragment replicas per fragment. These fragment replicas are all kept in synch. Thus for MySQL Cluster the sharding is automatic and happens inside the Cluster even using commodity hardware.

One of the defining features of MySQL Cluster is to keep the fragments up and running at all times and that they are restored after a Cluster crash. However MySQL Cluster also supports adding nodes to the Cluster …

In MySQL 5.6.2 we have introduced a new background thread named the page_cleaner in InnoDB. Adaptive flushing of modified buffer pool pages in the main background thread, async flushing by a foreground query thread if the log files are near to wrapping around, idle flushing and shutdown flushing are now moved to this thread, leaving only the last resort sync flushing in foreground query threads. We’ve also added counters for these activities.

As page_cleaner is all about the flushing of dirty pages to disk it’ll do you a world of good if you can go through this post where I have explained different types of flushing that happen inside InnoDB and the conditions that trigger flushing. The page_cleaner thread is only concerned with flush_list flushing (this may change in future releases). So let us dig a bit deeper into why …

Great news was announced today at the Collaborate 11 conference today;  the first development milestone release of MySQL 5.6.2 is now available to the MySQL Community!  This is significant because 5.6.2 builds on the momentum generated by the performance, scalability, InnoDB and Replication improvements that rolled out in version 5.5 just last Dec.  It also marks somewhat of a paradigm shift in how the MySQL Engineering team delivers new, defined sets of stable, development complete features (development "milestones") and cutting edge, currently under development, delivered early and often features via http://labs.mysql.com/.  Both options provide a means for Community users to begin using new versions and, more importantly, new features of MySQL very early on ensuring the final product has undergone the highest degree of testing, validation and hardening before we deem it production ready.

 
Some of …