Mozilla IT & Operations

My Thoughts About MySQL 5.6

If you are reading this blog post, you are probably not at MySQL Connect. You may have heard about today’s new release – MySQL 5.6.7. This is a release candidate quality release, and if Oracle treats MySQL like the rest of its software, that means that there will very likely be a 5.6 GA by the end of 2012.

That all being said, is MySQL 5.6 worth upgrading to, once it’s GA? Probably the most compelling reason to upgrade is InnoDB online DDL – including online add/drop indexes (including foreign keys) and online add/drop/rename columns.

There are some great InnoDB performance enhancements, which you can read about if you are inclined to look further into it. Those are interesting, but it’s hard to say how much improvement any one organization will get until they actually test their system. So I won’t go into it too much until I have had time to see if Mozilla would benefit from it. Similarly, the fact that MySQL can now support parallel threading up to 48 cores is also great – Oracle tested on a 96-core server and got 48 cores working in parallel.

Personally, I am pretty excited about the new security features of MySQL 5.6. The biggest one, which is a pretty big change to watch out for when upgrading, is that secure_auth defaults to on, unless you specify skip-secure-auth in the configuration. This means that when you upgrade, any user in the old password format (the password hash is 16 characters) will be blocked.

MySQL will also warn you when you set a replication password without using SSL, or when it is stored in cleartext. For example, the normal setting of replication’s username and password will generate the following 2 notes:

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
Level: Note
Code: 1760
Message: Storing MySQL user name or password information in the master.info repository is not secure and is therefore not recommended. Please see the MySQL Manual for more about this issue and possible alternatives.
2 rows in set (0.00 sec)

In MySQL 5.6, you can now store replication information in a table, not just in master.info.

I am also excited about having checksums in replication. Using pt-table-checksum can get tedious, and it only finds inconsistencies after the fact, it doesn’t prevent the inconsistencies or give an error exactly when the inconsistency occurs.

There are some nice little touches that show that Oracle is going in the right direction with MySQL – for example, in MySQL 5.6, innodb_file_per_table is enabled by default. And there is a new feature that warns you with a “note” if you create a duplicate index:

mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1831
Message: Duplicate index 'id_2' defined on the table 'version.dup_index'. This is deprecated and will be disallowed in a future release.
1 row in set (0.00 sec)

This note only appears if you make an index with the same fields as another index; if you create an index that’s a prefix subset of another index, there is no warning (e.g. if you have an index on (a,b) and create an index on (a), there is no warning). Still, it is a good step in the right direction.

If you use statements like UPDATE...LIMIT x and fill up your error logs with messages that the transaction is “unsafe”. There is now a warning suppression system, so that after 50 warnings in 50 seconds, the warnings will be aggregated with X warnings in Y seconds.

All in all, MySQL 5.6 is a release to look forward to. I have not covered every change in MySQL 5.6, but the major ones that I am looking forward to. Others may have different priorities and reasons for wanting to move to MySQL 5.6. You can see the full MySQL 5.6.7 changelog, or read about the major changes in MySQL 5.6.

The checksums in the binary log are orthogonal to pt-table-checksum checksums. Binary log checksums protect against binary log corruption, they don’t prevent a non-deterministic statement or accidentally changing data on a slave. You still need pt-table-checksum.

*nod* agreed – however, MySQL warns for non-determinism and at Mozilla we have strict controls on user permissions and the read_only setting, so we could use pt-table-checksum much less frequently than we do now (daily), just as a sanity check (e.g. like testing a backup restore).