InnoDB team is pleased to announce the 2012 Spring labs release, with several much anticipated new features and performance enhancements. Please download mysql-5.6-labs-april-2012 from MySQL Labs and give a try. Do not forget to provide your feedback.

The 2012 Spring labs release on MySQL Labs consists of the following InnoDB new features, which are not in the newly released MySQL 5.6.5 DMR yet:

• Online DDL: some of the DDLs are now truly online, including ADD INDEX, SET DEFAULT, and DROP FOREIGN KEY.
• Memcached plugin: with additional features, such as SASL support.
• Transportable tablespace: allow user to export data files and import them into another MySQL instance.
• Persistent statistics ON/OFF switch: the ability of controlling persistent statistics on table …

MySQL 5.6.5 Development Milestone Release has a whole new set of algorithms for processing subqueries. It is based on transforming a subquery into a semi-join operation, and then treating semi-join like another join operation throughout the optimizer.

A subquery can be transformed to a semi-join if it matches these criteria:

• The subquery is part of an IN or =ANY predicate. It cannot be e.g. NOT IN.
• The subquery consists of a single query block (it must not contain UNION).
• The subquery does not contain GROUP BY or HAVING.
• The subquery is not implicitly grouped (it contains no aggregate functions).
• The subquery predicate is part of a WHERE clause.
• The subquery predicate must not be part of a disjunctive nor a negated search condition.
• Neither query block contains the …

New feature: structured EXPLAIN

Probably, most of us tried to read the output of MySQL EXPLAIN command at least once.
And probably many of us have decided, that it isn't so readable and understandable as it has to be.
Some of us even tried to create or use external pretty-printing programs and other complicated converters to make EXPLAIN's output less cryptic, for example nice Percona's Visual Explain script.
But from now we have a native MySQL command that explains query execution plan in a better human-readable and machine-readable way: EXPLAIN FORMAT=JSON!

A trivial example, SELECT from a single table:

mysql> CREATE TABLE t1 (i INT, j INT);
Query OK, 0 rows affected (0.00 sec)
 …

A MySQL team focused on performance recently met in an internal meeting to discuss and work on MySQL scalability issues. We had gathered specialists on InnoDB and all its aspects of performance including scalability, adaptive flushing and other aspects of InnoDB, we had also participants from MySQL support to help us understand what our customers need and a number of generic specialists on computer performance and in particular performance of the MySQL software.

The fruit of this meeting can be seen in the MySQL 5.6 labs release april 2012 released today. We have a new very interesting solution to the adaptive flushing problem. We also made a significant breakthrough in MySQL scalability. On one of our lab machines we were able to increase performance of the Sysbench OLTP RO test case by more than 50% by working together to find the issues and then quickly coming up with the solution to the issues. Actually in one particular test case …

Having been rather desired for a while now, this feature is finally available as of MySQL Server version 5.6.5. It started out as the innocuous bug #27645 back in 2007, not really commanding much attention from anyone. But since, the page has seen around a hundred posts from users. This is a lot of posts for a bug page.
When I got to work on this, I started out looking at how functions in the default clause worked for the one supported case, CURRENT_TIMESTAMP (a.k.a. NOW() in MySQL) for TIMESTAMP columns. It turned out to be a little more complex than it had to, the TIMESTAMP type has a lot of special rules attached to it that no other types have, not even DATETIME.
One thing that struck me as a little odd was that you can only have one column with a function in the default or on update clause. So I had to take the decision how to deal with that restriction as we introduced DATETIME DEFAULT CURRENT_TIMESTAMP. Should we …

A few customers with rather extreme needs have contacted us about a performance issue with the range optimizer. Our solution to the problem is to introduce a new variable in MySQL 5.6, eq_range_index_dive_limit, which can be used to control whether or not the range optimizer will a) do index dives, or b) use index statistics when estimating the number of rows in the ranges of the query. The former method gives a far more accurate estimate while the latter costs a lot less to compute.

This is what the help text has to tell about the variable:

The optimizer will use existing index statistics instead of doing index dives for equality ranges if the number of equality ranges for the index is larger than or equal to [the value of variable]. If set to 0, index dives are always used."Equality range" means predicates using operators IN() or =, and it's important to notice that the number of such ranges is counted on a per index …

Global Transaction Identifiers are in!
I am very happy and especially proud to announce that the replication team has delivered global transaction identifiers to MySQL 5.6.5 Development Milestone Release (DMR). It is a very useful, big, impressive and game-changing feature that will make life easier for many of our users. With this feature in place, it is much simpler to track replication progress through the replication topology thus it removes part of the burden of deploying and administering complex multi-tier replication topologies. Actually, as stated before, this feature is an enabler, as it gives the user so much more flexibility when it comes to deploying replication and tracking the data that is replicated. In particular, it is a foundation for reliable and automated fail/switch over with slave promotion, reducing the need for 3rd party HA infrastructure (which adds cost and complexity). In fact, …

Just yesterday, I’m sure many saw Twitter opensourcing their MySQL implementation. It is based on MySQL 5.5 and the code is on Github.

For reference, the database team at Facebook has always been actively blogging, and keeping up their code available on Launchpad. Its worth noting that the implementation there is based on MySQL 5.0.84 and 5.1.

At Twitter, most of everything persistent is stored in MySQL – interest graphs, timelines, user data and those precious tweets themselves! At Facebook, its pretty similar – all user interactions like likes, shares, status updates, requests, etc. are all stored in MySQL ( …

Recently running yet one of series of benchmark on MySQL 5.6, I've met the following case:

• the workload is Read+Write, 256 concurrent user sessions running non-stop..
• Performance Schema (PFS) is enabled and Synch events are monitored (both counted and timed)..
• accidentally I've not fully disabled the query cache (QC) in MySQL config (query_cache_type=0 was missed)
• and at once PFS reported it to be the top wait event during this workload:

Test #1 -- @5.6 with PFS=on, default query_cache_type:
NOTE: we're reaching in this configuration ~100K QPS..

So, of course, I've expected to see much more higher QPS once I've set query_cache_type=0, don' you?.. ;-)
The second graphs are representing the result obtained with these changes:


Test #2 -- @5.6 with PFS=on, query_cache_type=0:


…

I work for realestate.com.au and we're an agile delivery shop. We're trying to catch up to some of the best of the best web-shops like Etsy and so on through continuous delivery. Millions of deploys a day!

The big kicker with continuous delivery is around schema changes. I'm curious to know how everyone is performing MySQL (InnoDB) schema changes in a agile continuous delivery shop. So please comment and share your thoughts.

How do we do ours? First off we don't have a good story yet about schema changes though I think we're ahead of the pack

• Limit schema changes to column additions / index changes / new tables
• Perform our schema changes online using Master Master Active Standby replication (using hardware load balancer out the front of our databases - Citrix Netscaler)
• Statement based replication 
• Application fault tolerance (to a degree) - so …