MySQL: Every detail matters

Some bugs can have a high impact if it causes data corruption, security issues or simply causes MySQL to crash. But what about small bugs and small usability issues?

This entry from the MySQL 5.6.8 release notes is an interesting one:
InnoDB: On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration option and the actual size of the ib_logfile* files that make up the redo log. This behavior required manually removing the redo log files after changing the value of innodb_log_file_size. The fix causes MySQL to write all dirty pages to disk and re-create the redo log files during startup if it detects a size mismatch. (Bug #14596550)

So previously you had to shutdown MySQL without fast shutdown enabled, then move the ib_logfile's away and start MySQL again. This is not too hard or complicated. But I have seen some serious issues related to this procedure.

Someone changed the log file size in my.cnf anticipating to execute the procedure in a day or two, but then someone else restarted MySQL. Then the MySQL storage engine was not registered, so the data was not available. Then someone thought that MySQL was at fault and restored all tables from the latest dump. As the NO_ENGINE_SUBSTITUTION sql_mode was not set the tables were restored as MyISAM tables.

This fix not only prevents issues like this but it also helps to easily automate the resize of the logfiles on multiple machines. And it makes it possible to change the logfile size with puppet.

I would like to see more fixes of small bugs and usability issues, just like the Every Detail Matters project for GNOME. It helps to keep MySQL a user friendly database.

There are more fixes like this in MySQL 5.6, for example the updated defaults.

One of the things I would like to see to be fixed is the combination of features. For example it is not possible to use:

• partitioning and foreign keys
• InnoDB compression with combined table spaces (or raw disk tablespaces)
• spatial indexes and InnoDB 
• Full text parsers plugins and InnoDB

MySQL 5.6 already fixed a number of similar issues. It's now possible to use full text indexes and InnoDB and to use read-only InnoDB.

Instant InnoDB

I've reviewed the Instant InnoDB book. It's a good starting point if you're learning about InnoDB, which is used for most MySQL installations.

As a consultant I've seen many situations where some basic InnoDB setting (buffer pool size, log file size, file per table) can have a huge effect on the performance of the database and make it easier to manage.

The website of the book can be found here.