MySQL 5.6 new features: the user’s perspective

This is a yet-another compilation of the new MySQL 5.6 feature set. It is not a complete drill down. This list reflects what I believe to be the interesting new features user and usability -wise.

For example, I won’t be listing InnoDB’s split of kernel mutex. I’m assuming it can have a great impact on overall performance due to reducing lock contention; but usability-wise, this is very internal.

The complication is an aggregate of the many announcements and other complications published earlier on. See a reference at the end of this post.

Do note I am not using 5.6 as yet; it is in RC, not GA. I am mostly excited just to write down this list.

InnoDB

• Online ALTER TABLE: if there is one major new feature in 5.6 you would want to upgrade for, this would be it. Add columns, drop columns, rename columns, add indexes, drop indexes – now online, while your SELECT, INSERT, UPDATE and DELETE statements are running.
• Transportable tablespace files: copy+paste your_table.ibd files with FLUSH TABLE FOR EXPORT and ALTER TABLE … IMPORT TABLESPACE.
• FULLTEXT: for many, the one thing holding them back from leaving MyISAM behind. Now available in InnoDB with same syntax as with MyISAM.
• Memcached API: access InnoDB data via memcahced protocol, and skip the SQL interface.
• User defined table location: place your tables in your pre-defined location. Place other tables elsewhere. This is something I’ve been asked about for ages.

Replication

• Global Transaction IDs: I’m still to fully understand the limitations: MyISAM and temporary tables make a mess; this could be a killer feature when it comes to replication: no more master log file, master log pos, relay master log file, relay log pos, read master log file, read master log pos (if I wake you up at 3:00am, will you be able to cite the difference?). Just one single unique identifier for each transaction in the binary log, so it’s much easier for slaves to connect to master, or to switch over to replicate another server.
• Multi threaded slaves: with a thread-per-schema, and assuming complete isolation of schemas
• Delayed replication: a must-have, in my opinion, on any replication topology using 3-4 servers.
• Checksums: verify shipment of binary logs from master to slave by adding a checksum on log entries.
• Crash safe slaves: forget about master.info not syncing to disk. Now using InnoDB for that.

Partitioning

• Partition-to-table, table-to-partition: I’ve encountered the need for this in the past. In one case, it was the issue of backing up single partitions for archiving, then restoring particular partitions from the past into the existing table. Some Oracle users, upon hearing about the 5.1, 5.5 partition features, were wondering about this missing feature. Their words: “MySQL is still in the very early stages of managing partitions. As it grows it will have to include partition-to-table, as well as other already-standard-in-Oracle features”. They were right.
• Reduced locking: don’t you hate it when you partition by date, INSERT into the last partition, only to find out you actually acquired locks for all partitions? Hopefully this is gone now (hoping I’m not wrong on this?)
• Choose partitions in query à la SELECT * FROM my_table PARTITION (p7)

Query Execution Plan

• EXPLAIN for UPDATE, DELETE, INSERT: pretty sure everyone wanted that.
• Optimizing subqueries (the notorious WHERE IN (SELECT …)): the “MySQL does WHAT with subqueries?!?!?!” invoking behavior of subquery execution is now hopefully met. No longer “execute the subquery for each row in the outer query”.
• Index merge optimization: this optimization was rare, in my experience. The new improvements are expected to make it more common.
• EXPLAIN FORMAT=JSON: I like this kind of stuff. The Json format is much more verbose, and shows the tree-structure of the query execution plan. This would make for a great analysis tool for GUI editors!

PERFORMANCE_SCHEMA

• A gazillion more tables. I am yet to drill down into P_S. However I can already verify that 5.6 introduces a lot of new tables I’ve been longing for. Some are actually more fitting in INFORMATION_SCHEMA. A lot of new metadata tables. Will write more in the future.

Some of the above features already exists in earlier releases of Google Patches for MySQL, Percona Server and MariaDB. For example, global transaction IDs and binlog checksums first appeared three and a half years ago in Google Patches, and only now re-created in MySQL. Sometimes the community is far ahead of the MySQL development. But then Oracle does its thing and makes for a great release.

No doubt the above is an impressive list of enhancements to the server. Some make it a significant step into the “things are getting serious here” realm. I can do well with online alter table, auto replication recovery, execution plan improvements, and the many performance boosts not listed here. Not everything will work in all scenarios; but this makes for one release of MySQL I’m anxious to use.

References, chronologically DESC

• My personal list of new features in MySQL 5.6 – Mikael Ronström
• Failover and Flexible Replication Topologies in MySQL 5.6 – Sven
• Index merge annoyances fixed in MySQL 5.6 – Jørgen Løland
• MySQL Connect 2012 – Lars Thalmann
• MySQLConnect — Auditing, Online DDL, FK in Cluster and More – Lee Stigile
• My Thoughts About MySQL 5.6 – Sheeri K. Cabral
• Online ALTER TABLE in MySQL 5.6  – Oracle
• What’s New in MySQL 5.6 Release Candidate – Oracle
• A summary of changes in MySQL 5.6  – Baron Schwartz
• What’s New in MySQL 5.6 – Oracle