An easy way to describe MySQL's Binary Log Group Commit

It struck me today; there is an easy way to describe MySQL's Binary Log group commit improvements from MySQL 5.0-5.7 by using the example of a single ferry trying to ship passengers from point A to point B:

Ferry from Point A to point B

MySQL 5.0 Behaviour

In MySQL 5.0, the ferry will pick up the next passenger in line from point A, and transfer them to point B. The trip between A and B takes about 10 minutes return trip, so it's possible that several new passengers will arrive while the ferry is in transit. That doesn't matter; when the ferry arrives back at point A, it will only pick up the very next passenger in line.

MySQL 5.6 Behaviour

In MySQL 5.6, the ferry will pick up all passengers from the line at point A, and then transfer them to point B. Each time it returns to point A to pick up new passengers, it will collect everyone who is waiting and transfer them across to point B.

This is measurably better performance in real-life situations where many passengers tend to arrive while waiting for the ferry to arrive back at point A, and the trip between A and B tends to take some time. It is not so measurable in naive benchmarks that run in a single-thread.

There is no configuration necessary to enable group commit in 5.6. It works by default.

MySQL 5.7 Behaviour

MySQL 5.7 behaves similarly to 5.6 in that it will pick up all waiting passengers from point A and transfer them to point B, but with one notable enhancement!

When the ferry arrives back at point A to pick up waiting passengers, it can be configured to wait just a little bit longer with the knowledge that new passengers will likely arrive. For example: if you know the trip between point A and point B is 10 minutes in duration, why not wait an extra 30 seconds at point A before departing? This may save you on roundtrips and improve the overall number of passengers that can be transported.

The configuration variables for artificial delay are binlog-group-commit-sync-delay (delay in microseconds) and binlog-group-commit-sync-no-delay-count (number of transactions to wait for before deciding to abort waiting).

Conclusion

In this example passengers are obviously transactions, and the ferry is an expensive fsync operation. It's important to note that there is just one ferry in operation (a single set of ordered binary logs), so being able to tune this in 5.7 provides a nice level of advanced configuration.

MySQL 5.6.22 Community Release Notes

Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.22.

In particular:

  • Thank you to Ramesh Sivaraman for reporting that setting the innodb_limit_optimistic_insert_debug debug configuration option to 1 caused an infinite B-tree page split. Ramesh also provided a minimal testcase to reproduce. Bug #74605.
  • Thank you to Jeremy Cole for reporting that InnoDB pages with a checksum value of zero were incorrectly treated as empty pages. Bug #73689.
  • Thank you to Zhai Weixiang for reporting that the MySQL server would fail to shutdown when started with innodb-force-recovery = 6. Bug #73341.
  • Thank you to Bart Butler for reporting that InnoDB would shutdown unclean on systems with lower_case_table_names=2 and containing tables using uppercase. Bug #72043.
  • Thank you to Laurynas Biveinis for reporting two assertions that could occur in InnoDB using debug builds. Bug #71343, Bug #68116.
  • Thank you to Stewart Smith for reporting that InnoDB was missing a memory barrier when setting up asynchronous I/O (AIO). Stewart was also kind enough to provide a patch addressing the issue. Bug #72809.
  • Thank you to Jean-François Gagné for reporting an out of memory situation when using row-based replication and having different data types on the slave. Jean-François also provided an example shell script, which saved us a lot of time in reproducing the issue. Bug #72610.
  • Thank you to Jean-François Gagné for reporting that relay log recovery did not correctly handle the case where Relay_Master_Log_File could be empty. Bug #73039.
  • Thank you to Santosh Praneeth Banda for reporting that replication masters did not correctly handle the case when slaves requested GTIDs which had already been purged. Bug #73032.
  • Thank you to Domas Mituzas for reporting that mysqlbinlog incurred significant kernel mutex contention by calling localtime() for every event read. We have since modified mysqlbinlog to use localtime_r instead. Bug #72701.
  • Thank you to Jeremy Cole for reporting that the variable SQL_LOG_BIN having global scope is dangerous. We agree with Jeremy and have modified the behaviour so that it may only be set on a session basis. Bug #67433.
  • Thank you to Jean-François Gagné for reporting that the mysqld startup script would fail on CentOS when the socket option was relative. Bug #74111.
  • Thank you to Eugene Zheganin for reporting that MySQL would not compile with DTrace on Solaris 11.2. Bug #73826.
  • Thank you to Nikola Glavinceski for reporting that certain queries for which subquery materialization or UNION DISTINCT was used together with a hash index on a temporary table could produce incorrect results or cause a server exit. Bug #73368.
  • Thank you to Hartmut Holzgraefe for reporting that the ENABLED_LOCAL_INFILE cmake option was incorrectly enabled by default. Bug #72106.
  • Thank you to Jason Rider for reporting that the use of ODBC-format date literals could produce incorrect query results. Bug #69233.
  • Thank you to Nicholas Bamber for reporting that mysql_setpermission failed to properly quote user names in SQL statements that it generated. Bug #66317.

Thank you again to all the community contributors listed above. In addition, the MySQL team would like to thank the community for their feedback on Bug #67433.

If I missed a name here, please let me know!

- Morgan

Proposal to deprecate collation_database and character_set_database settings

In the MySQL team, we are currently discussing deprecating the ability to change the collation_database and character_set_database settings. As part of our plan, mysql clients will still be able to access these variables, but they will be read-only.

Introduction

The MySQL manual summarizes these two variables as saying:

This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

This is to say that usage is already discouraged, but these two variables originally had a purpose of allowing a DBA to change the default character-set temporarily for the current session only. For example:

SET SESSION character_set_database=latin5;
LOAD DATA infile 'foo.txt' ... ;
-- foo.txt is interpreted as if it was in latin5 character set

The most obvious use case for this is in loading data from a text file which contains an arbitrary character set. However, it is worth noting that the LOAD DATA INFILE syntax has since added the ability to specify a character-set.

Known Issues

The functionality of collation_database and character_set_database differs somewhat from the original design goal, and presents usability issues for our users. Specifically:

  1. Both variables can be set GLOBAL; yet it mostly does not apply

    The original feature was designed for these two variables to be set on a per-session basis. However, an undocumented feature is that they can both be set on a GLOBAL basis without error. For example:

    mysql> SET GLOBAL character_set_database=latin5;
    Query OK, 0 rows affected (0.01 sec)
    

    However, when set on a GLOBAL basis these variables will have largely no effect. New sessions will always copy character set information from the current database unless a database is not selected:

     SET GLOBAL @@character_set_database=latin5;
    
     [disconnect]
     [connect, without choosing the current database]
    
     SELECT @@character_set_database; -- session counterpart takes
     its initial value from the global counterpart
    

    We consider any behaviour which will only apply in specific circumstances like this as confusing, and potentially harmful to user experience.

  2. Behaviour broken by fix for Bug #2326

    As part of the fix for Bug #2326, the collation_database variable no longer affects the CREATE TABLE statement. That is to say that setting character_set_database and collation_database does not apply for all statements!

  3. Variables are not replicated prior to fix for Bug#15126

    Before Bug#15126 was fixed, setting of a local variable would not be replicated; thus relying on this feature would break replication. While this is no longer a current issue, it helps support our current understanding that the usage of this feature is minimal.

  4. Does not apply to stored programs

    Setting these variables has no effect on stored programs (triggers, functions, procedures), base tables or views -- all these objects use the character set and collation of the database they belong to.

Known bugs:

  • Bug #35357 - "character_set_database does not effects CREATE DATABASE without character set"
  • Bug #27208 If no current database, character_set_database !=character_set_server
  • Bug #27687 ALTER TABLE ... CONVERT TO uses @@collation_database

Our Proposal

Our plan is to deprecate both SESSION and GLOBAL variables collation_database and character_set_database starting with MySQL 5.7. In MySQL 5.8, we plan to remove the GLOBAL variables and make accessing the SESSION variables read-only.

We are currently seeking feedback relating to this plan:

  • Do you currently use the collation_database and character_set_database variables?
  • Do you have a use-case that we have not currently considered?

Please get in touch! You can either leave a comment, or contact me via email!

New Data Dictionary - upgrading from earlier MySQL releases

In July, I first wrote that we are currently working on a new data dictionary for MySQL. We have since shipped a labs release where you can try it out for yourself and then send us feedback!

Today, I wanted to write about our upgrade plan from the old data dictionary to the new data dictionary. Or more specifically, explain one of the nuances that we currently have with encoding characters that are not safe on all filesystems.

To quote from the MySQL manual:

For example, if a database in MySQL 5.0 has the name a-b-c, the name contains instances of the - (dash) character. In MySQL 5.0, the database directory is also named a-b-c, which is not necessarily safe for all file systems. In MySQL 5.1 and later, the same database name is encoded as a@002db@002dc to produce a file system-neutral directory name.

In order to focus our efforts, we are planning to only support upgrading from the 5.1+ database directory encoding. That is to say that users with data directories originating from 5.0 or earlier and have special characters will need to:

  • mysqldump and restore their data
  • or Step through an intermediate release and upgrade to the newer encoding:

    ALTER DATABASE #mysql50#a-b-c UPGRADE DATA DIRECTORY NAME;

It is worth noting that the ALTER TABLE .. UPDATE DATA DIRECTORY NAME step is automatically run by mysql_upgrade which has always been a recommended step to perform following any upgrade.

We are seeking feedback regarding the limitations of our proposed upgrade path. If this will impact you, please leave a comment, or get in touch!