Installing MySQL 5.6 on Ubuntu 14.10 (Utopic Unicorn)

Installing ‘mysql’ on Ubuntu 14.10 will default to version 5.5, but also included is MySQL 5.6 is an option.

These are the steps I ran through to install it on a fresh Digital Ocean droplet. On a local machine, you will need to add sudo:


root@ubuntu1410:~# apt-get install mysql-server-5.6
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
linux-headers-3.16.0-21 linux-headers-3.16.0-21-generic linux-image-3.16.0-21-generic linux-image-extra-3.16.0-21-generic
Use 'apt-get autoremove' to remove them.
The following extra packages will be installed:
libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libterm-readkey-perl mysql-client-5.6
mysql-client-core-5.6 mysql-common mysql-common-5.6 mysql-server-core-5.6
Suggested packages:
libclone-perl libmldbm-perl libnet-daemon-perl libsql-statement-perl libipc-sharedcache-perl mailx tinyca
The following NEW packages will be installed:
libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl libmysqlclient18 libterm-readkey-perl mysql-client-5.6
mysql-client-core-5.6 mysql-common mysql-common-5.6 mysql-server-5.6 mysql-server-core-5.6
0 upgraded, 12 newly installed, 0 to remove and 3 not upgraded.
Need to get 21.8 MB of archives.
After this operation, 165 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://mirrors.digitalocean.com/ubuntu/ utopic/main libaio1 amd64 0.3.110-1 [6,454 B]
Get:2 http://mirrors.digitalocean.com/ubuntu/ utopic-updates/main mysql-common all 5.5.41-0ubuntu0.14.10.1 [13.5 kB]
Get:3 http://mirrors.digitalocean.com/ubuntu/ utopic-updates/main libmysqlclient18 amd64 5.5.41-0ubuntu0.14.10.1 [603 kB]
Get:4 http://mirrors.digitalocean.com/ubuntu/ utopic/main libdbi-perl amd64 1.631-3build1 [770 kB]
Get:5 http://mirrors.digitalocean.com/ubuntu/ utopic/main libdbd-mysql-perl amd64 4.028-2 [88.6 kB]
Get:6 http://mirrors.digitalocean.com/ubuntu/ utopic/main libterm-readkey-perl amd64 2.32-1build1 [25.0 kB]
Get:7 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-client-core-5.6 amd64 5.6.19-1~exp1ubuntu2 [4,061 kB]
Get:8 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-client-5.6 amd64 5.6.19-1~exp1ubuntu2 [5,654 kB]
Get:9 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-server-core-5.6 amd64 5.6.19-1~exp1ubuntu2 [4,777 kB]
Get:10 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-server-5.6 amd64 5.6.19-1~exp1ubuntu2 [5,710 kB]
Get:11 http://mirrors.digitalocean.com/ubuntu/ utopic/main libhtml-template-perl all 2.95-1 [65.5 kB]
Get:12 http://mirrors.digitalocean.com/ubuntu/ utopic/universe mysql-common-5.6 all 5.6.19-1~exp1ubuntu2 [13.5 kB]
Fetched 21.8 MB in 4s (5,436 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libaio1:amd64.
(Reading database ... 115597 files and directories currently installed.)
Preparing to unpack .../libaio1_0.3.110-1_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-1) ...
Selecting previously unselected package mysql-common.
Preparing to unpack .../mysql-common_5.5.41-0ubuntu0.14.10.1_all.deb ...
Unpacking mysql-common (5.5.41-0ubuntu0.14.10.1) ...
Selecting previously unselected package libmysqlclient18:amd64.
Preparing to unpack .../libmysqlclient18_5.5.41-0ubuntu0.14.10.1_amd64.deb ...
Unpacking libmysqlclient18:amd64 (5.5.41-0ubuntu0.14.10.1) ...
Selecting previously unselected package libdbi-perl.
Preparing to unpack .../libdbi-perl_1.631-3build1_amd64.deb ...
Unpacking libdbi-perl (1.631-3build1) ...
Selecting previously unselected package libdbd-mysql-perl.
Preparing to unpack .../libdbd-mysql-perl_4.028-2_amd64.deb ...
Unpacking libdbd-mysql-perl (4.028-2) ...
Selecting previously unselected package libterm-readkey-perl.
Preparing to unpack .../libterm-readkey-perl_2.32-1build1_amd64.deb ...
Unpacking libterm-readkey-perl (2.32-1build1) ...
Selecting previously unselected package mysql-client-core-5.6.
Preparing to unpack .../mysql-client-core-5.6_5.6.19-1~exp1ubuntu2_amd64.deb ...
Unpacking mysql-client-core-5.6 (5.6.19-1~exp1ubuntu2) ...
Selecting previously unselected package mysql-client-5.6.
Preparing to unpack .../mysql-client-5.6_5.6.19-1~exp1ubuntu2_amd64.deb ...
Unpacking mysql-client-5.6 (5.6.19-1~exp1ubuntu2) ...
Selecting previously unselected package mysql-server-core-5.6.
Preparing to unpack .../mysql-server-core-5.6_5.6.19-1~exp1ubuntu2_amd64.deb ...
Unpacking mysql-server-core-5.6 (5.6.19-1~exp1ubuntu2) ...
Processing triggers for man-db (2.7.0.2-2) ...
Setting up mysql-common (5.5.41-0ubuntu0.14.10.1) ...
Selecting previously unselected package mysql-server-5.6.
(Reading database ... 115966 files and directories currently installed.)
Preparing to unpack .../mysql-server-5.6_5.6.19-1~exp1ubuntu2_amd64.deb ...
Unpacking mysql-server-5.6 (5.6.19-1~exp1ubuntu2) ...
Selecting previously unselected package libhtml-template-perl.
Preparing to unpack .../libhtml-template-perl_2.95-1_all.deb ...
Unpacking libhtml-template-perl (2.95-1) ...
Selecting previously unselected package mysql-common-5.6.
Preparing to unpack .../mysql-common-5.6_5.6.19-1~exp1ubuntu2_all.deb ...
Unpacking mysql-common-5.6 (5.6.19-1~exp1ubuntu2) ...
Processing triggers for man-db (2.7.0.2-2) ...
Processing triggers for ureadahead (0.100.0-16) ...
Setting up libaio1:amd64 (0.3.110-1) ...
Setting up libmysqlclient18:amd64 (5.5.41-0ubuntu0.14.10.1) ...
Setting up libdbi-perl (1.631-3build1) ...
Setting up libdbd-mysql-perl (4.028-2) ...
Setting up libterm-readkey-perl (2.32-1build1) ...
Setting up mysql-client-core-5.6 (5.6.19-1~exp1ubuntu2) ...
Setting up mysql-client-5.6 (5.6.19-1~exp1ubuntu2) ...
Setting up mysql-server-core-5.6 (5.6.19-1~exp1ubuntu2) ...
Setting up mysql-server-5.6 (5.6.19-1~exp1ubuntu2) ...
2015-03-09 09:55:55 0 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
2015-03-09 09:55:55 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
mysql start/running, process 19834
Setting up libhtml-template-perl (2.95-1) ...
Setting up mysql-common-5.6 (5.6.19-1~exp1ubuntu2) ...
Processing triggers for libc-bin (2.19-10ubuntu2.3) ...
Processing triggers for ureadahead (0.100.0-16) ...

Some additional notes:

  • You will be prompted for a password for the root user. You can change it later, but best to think of something meaningful and write it down.
  • The two WARNINGS that you see are quite safe. The default Ubuntu configuration uses a deprecated name for a configuration setting (Bug #1362393), and MySQL is warning that the behavior of TIMESTAMP columns may change in the future.
  • The MySQL server should start automatically on boot. If you can not connect to MySQL, please check the file /var/log/messages. The most common reason it will not start is that the default configuration requires a server with 1GB of RAM. I have an you can use if you need it to start with less RAM.

Plan to remove sql-bench from MySQL 5.7

In the MySQL team, we are planning to remove sql-bench from the MySQL 5.7 sources.

As the README file denotes, SQL Bench is a benchmark utility that supports MySQL 3.20 and 3.21, PostgreSQL 6.#, mSQL 2.# and Solid Server 2.2. It has not been well maintained for recent versions, and we are no longer using it internally as part of our testing process.

Our aim in making this change is to:

  • Improve the manageability of our git repository by reducing its size.
  • By keeping unmaintained code bundled with MySQL, it can lead users into negative impressions of our product. We have worked very hard at improving stability, and want to remove lurking pitfalls for our users.

The source code to sql-bench will remain in the 5.6 and earlier source trees.

Will you be affected by this change? Please get in touch!

Proposal to deprecate “compatibility” SQL Modes

In the MySQL team, we are currently discussing deprecating several of the SQL mode options which are used by mysqldump to change the output format. From the mysqldump command:

$ mysqldump --help
..
--compatible=name
 Change the dump to be compatible with a given mode. By
 default tables are dumped in a format optimized for
 MySQL. Legal modes are: ansi, mysql323, mysql40,
 postgresql, oracle, mssql, db2, maxdb, no_key_options,
 no_table_options, no_field_options. One can use several
 modes separated by commas. Note: Requires MySQL server
 version 4.1.0 or higher. This option is ignored with
 earlier server versions.

To explain the rationale for this proposal:

  1. The options mysql323, mysql40 are designed to allow mysqldump to create an output format that can be restored on a MySQL Server of version 3.23 or 4.0. While we aim to support the upgrade case from these versions, supporting a downgrade is not something we support, as restoring data to a 10 year old release poses a number of challenges.
  2. The options postgresql, oracle, mssql, db2, maxdb are ‘special’ SQL modes, in that they are not really modes themselves but aliases to switch on other SQL modes. For example:
    mysql> set sql_mode = 'mssql';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@sql_mode\G
    *************************** 1. row ***************************
    @@sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
    1 row in set (0.00 sec)
    

    Having aliases creates a number of interesting challenges:

    • It complicates training and support as users may have multiple options in order to achieve the same outcome (either listing all sql modes individually or via one of the aliases). In a similar example we removed unique option prefixes in MySQL 5.7 to reduce this confusion.
    • The options that we enable for each alias imply (but have not measurably offered) compatibility with the other database that is mentioned. Furthermore, as other databases will release newer versions, the singularity of the alias name does not account for this.
    • Related to the above; if newer versions of other database products desire new sql modes enabled, it is more flexible (and future proof) to have the list of which behaviour options that should be enabled for each other database in documentation or client programs rather than the server itself. This allows us to not change behavior in a server GA release.
  3. The options no_key_options, no_field_options and no_table_options remove MySQL-specific meta data which I have highlighted below in bold:
    # no_key_options
    CREATE TABLE `t` (
      `i` int(11) NOT NULL AUTO_INCREMENT,
      `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
      KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    # no_field_options
    CREATE TABLE `t` (
      `i` int(11) NOT NULL AUTO_INCREMENT,
      `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
      KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    # no_table_options
    CREATE TABLE `t` (
      `i` int(11) NOT NULL AUTO_INCREMENT,
      `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
      KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    

    Note that no_field_options did not remove the character set attribute for the column b and thus is incomplete in its current implementation. no_field_options also disables the same meta-data which is disabled by no_key_options.

    The no_key_options and no_table_options remain useful, although it should be noted that information_schema may better support a custom view of schema to match the capabilities of the destined database. We are working on making information_schema queries much faster in the future, via our native data dictionary project.

To summarize this proposal:

  • Our plan is to deprecate the following options in MySQL 5.7, for removal in a later version:
    mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb.
  • We are also seeking input from those that use the following SQL modes, as we may decide to deprecate these in the future:
    no_field_options, no_key_options, no_table_options.

Will these changes impact you? Please leave a comment or get in touch!

My voices of the ElePHPant Interview :)

Here is an interview of me talking about EXPLAIN FORMAT=JSON, optimizer trace, cost model, MySQL Workbench and even a little Performance Schema.

Some links that I recommend checking out:

Proposal to change additional defaults in MySQL 5.7 (February Edition)

Following on from my two earlier posts, in the MySQL team we are proposing a new set of changes to defaults for MySQL 5.7:

Setting Old Default New Default
log_slow_admin_statements OFF ON
log_slow_slave_statements OFF ON
long-query-time 10 2
log-queries-not-using-indexes OFF ON
min-examined-row-limit 0 1000
MySQL Command Line Client +show_warnings
group_concat_max_len 1024 1M
max_allowed_packet 4M 64M
symbolic-links OFF
temp-pool 1 0
table_open_cache_instances 1 16

To explain these changes in more details:

  • The slow query log remains disabled by default, but when enabled our hope is that we can make it immediately useful. A query exceeding 2 seconds will now be considered slow, and since queries that do not use indexes have the highest chance of causing future problems, they will also be logged unless they examine fewer than 1000 rows. The number of 1000 was chosen because it should eliminate the majority of false positives from queries retrieving a static set of records such as navigation menu items, which are well suited to tablescans.

    In proposing this change, we considered the defaults that other products use for considering a query slow. A popular web performance tool uses a default of 0.5 seconds for an acceptable time to render a full page server side, with 2 seconds (4x) slow enough to warrant logging. Our own MySQL Enterprise Monitor considers 100ms acceptable, and 400ms (4x) as slow. We also considered that setting the min-examined-row-limit to a non zero value will now require an additional step for those that set their long-query-time to zero seconds. We would like to thank Daniel Black for suggesting we change our slow query log options.

  • The MySQL command line client has the ability to automatically print warnings out to screen as they occur. We feel like ON is the more useful default, as a user will typically not intend to execute statements that cause warnings. This behavior applies to both interactive and batch modes, and can be disabled with show_warnings=0. We would like to thank Ryuta Kamizono for this suggestion.
  • We find the GROUP_CONCAT() function in MySQL to be incredibly useful for summarizing results in an aggregate query. We also believe that the default maximum length for grouping values has not kept pace with the larger amounts of memory available on modern hardware. We would like to thank Shlomi Noach for this suggestion.
  • In MySQL 5.6 we increased the max_allowed_packet from 1M to a conservative 4M. We have received a lot of feedback on this change, and are proposing to increase the default to 64M for MySQL 5.7. We would like to thank Shlomi Noach for this suggestion.
  • Many of our configuration files for MySQL packages (as well as those that ship with Linux distributions) default to disabling symbolic links for security reasons. We feel that changing a de facto default to a compiled default will improve user experience (as well as security). We would like to thank Honza Horak for this suggestion.
  • The temp-pool option was originally created as a means to work around potential filesystem issues on Linux when using internal MyISAM based temp tables. The underlying OS issues have since been resolved, and disabling this option removes mutex contention for all disk based temp table engines (MyISAM and InnoDB).
  • We are proposing to increase the default table_open_cache_instances to 16 in order to reduce contention on internal mutexes when opening tables.

For those of you who would like to test out these changes (along with previous changes proposed), I have sample configuration files available:

Please let us know what you think of these changes!
You can leave a comment here, or get in touch via email.

Changing my blog to use MySQL 5.7 proposed defaults

In my recent blog post, I announced a set of proposed changes to defaults for MySQL 5.7.

I’m now using these settings on my WordPress blog, which is powered by MyQL 5.7.5. The supplimental config file comes from my GitHub project called mysql-compatibility-config.

Here were the steps required:

[root@mysqldb2 ~]# cd /etc
[root@mysqldb2 etc]# wget https://raw.githubusercontent.com/morgo/mysql-compatibility-config/master/mysql-57/mysql-57-proposed.cnf
--2015-01-23 12:29:00--  https://github.com/morgo/mysql-compatibility-config/blob/master/mysql-57/mysql-57-proposed.cnf
Resolving github.com (github.com)... 192.30.252.128
Connecting to github.com (github.com)|192.30.252.128|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]
Saving to: ‘mysql-57-proposed.cnf’

    [ <=>                                                                                                                                        ] 34,049      --.-K/s   in 0.01s

2015-01-23 12:29:00 (3.26 MB/s) - ‘mysql-57-proposed.cnf’ saved [34049]

[root@mysqldb2 etc]# vi /etc/my.cnf
# Add a line to !include /etc/mysql-57-proposed.cnf
[root@mysqldb2 etc]# service mysqld restart
Redirecting to /bin/systemctl restart  mysqld.service

Proposal to change additional defaults in MySQL 5.7

Following on from my earlier proposal to change Replication + InnoDB settings, in the MySQL team, we are proposing to make the following additional changes to defaults in MySQL 5.7:

Setting Old Default New Default
binlog_error_action IGNORE_ERROR ABORT_SERVER
innodb_checksum_algorithm INNODB CRC32
innodb_page_cleaners 1 4
innodb_purge_threads 1 4
innodb_strict_mode OFF ON
innodb_log_file_size 48M 128M
innodb_buffer_pool_dump_at_shutdown OFF ON
innodb_buffer_pool_load_at_startup OFF ON
innodb_buffer_pool_dump_pct 100 25
innodb_file_format Antelope Barracuda
innodb_large_prefix OFF ON
binlog_gtid_recovery_simplified OFF ON
sync_binlog 0 1
slave_net_timeout 3600 60
P_S config Enable events_statements_history and events_transactions_history consumers by default
MySQL CLI Prompt mysql> prompt=”\u@\h [\d] > “
sql-mode +NO_AUTO_CREATE_USER

To explain the motivation behind these changes:

  • The binlog_error_action variable was introduced in MySQL 5.6 after the GA release, and for compatibility defaulted to previous behavior (ignore errors). Since some applications depend on reading from slaves after writing to masters, we feel that the better behavior is to abort.
  • The InnoDB CRC32 checksum was introduced in MySQL 5.6, and offers CPU accelerated page checksum computation. Since previous versions of MySQL were unable to read the new checksum, enabling this feature would have restricted downgrades to dump-and-restore. With MySQL 5.6 supporting the CRC32 checksum, the timing now makes sense to make this the default in 5.7.
  • In some workloads the InnoDB purge and page cleaning operations will not be able to keep up with modifications made inside InnoDB. In combination with newer storage devices that offer very high throughput and accommodate parallel writes, we feel that having multiple threads is a better default.
  • Currently the InnoDB storage engine ignores CREATE TABLE options that it does not understand (innodb_strict_mode=0). We feel this negatively impacts user experience, and are proposing to enable InnoDB strict mode in advance. The downside of this change is that some DDL statements that worked in 5.6 failing by default in 5.7. Thank you to Daniël van Eeden for providing this suggestion.
  • Since the InnoDB buffer pool now supports online resizing (and the innodb log files do not), we believe it makes sense to increase the size of the log files slightly in anticipation of an increased buffer pool size. This comes at a small cost of additional disk space usage. We felt that 128M was a good balance in size, since while larger log files are better for performance, there is also a trade off point where the log files benefit from filesystem caches. Thank you to Justin Swanhart for providing this suggestion.
  • With the increased availability of large memory, waiting for caches to warm back up after restarts has been an increased operational concern. MySQL 5.6 introduced a feature to be able to dump buffer pool page addresses (at a small fraction of total size) to assist in warming caches after restarts. MySQL 5.7 introduces an additional feature to dump only a percentage of the buffer pool, and we felt that by limiting this to 25% was a good tradeoff to enable this by default. Thank you to Daniel Black and Daniël van Eeden for this suggestion.
  • The InnoDB Barracuda file format was first introduced in the InnoDB plugin for MySQL 5.1, and enables InnoDB to use the new DYNAMIC row format (with improved BLOB handling) and COMPRESSED tables. Setting the InnoDB file format to Barracuda depended on innodb-file-per-table=1 (default changed in MySQL 5.6), and introduced an incompatibility that would prevent downgrades to earlier releases. With both MySQL 5.5 and 5.6 supporting Barracuda, the timing now makes sense to enable this by default. Thank you to Daniël van Eeden for providing this suggestion.
  • The innodb_large_prefix prefix option was introduced for Barracuda tables as an option to support much longer index key prefixes (up to 3072 bytes). It was disabled by default so that users did not accidentally create Barracuda tables that would be incompatible on Antelope systems (which would not allow downgrading). With Barracuda becoming the default, and our friends at WordPress and Drupal wanting to create much larger indexes to support utf8mb4 encoded columns, we decided to change this to default to on.
  • The binlog_gtid_recovery_simplified option was released after MySQL 5.6 was released as GA, and defaulted to OFF for backwards compatibility. We feel that ON is the better default, and plan to make this change for MySQL 5.7.
  • With the addition of binary log group commit in MySQL 5.6, the impact from enabling sync_binlog=1 on many non-synthetic workloads should be reduced considerably. This change makes MySQL binary logs durable/crash safe by default.
  • The slave_net_timeout defaults to one hour, which is too long for most modern systems. In addition: MySQL 5.7 now uses this value divided by two to configure the default master heartbeat period. We believe that a one minute default is more appropriate. Thank you to Miguel Angel Nieto for providing this suggestion.
  • In MySQL 5.7, performance_schema can now instrument transactions, which can be incredibly useful when enabled by default. In addition, enabling statements history (introduced in 5.6) provides a lot of useful meta-data for diagnosing performance problems. Thank you to Daniël van Eeden for providing this suggestion.
  • The MySQL command line client has long supported the ability to change the default prompt from “mysql >” to include additional details such as the username and database selected. This is mostly a cosmetic change, but it is useful when you are managing multiple servers. Thank you to Daniël van Eeden for providing this suggestion.
  • In MySQL 5.7 we have already made changes to the default SQL mode: enabling ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES by default. There is a long standing usability issue with user grants, where a typo in the username could result in a new user being created. We have decided that it is best to disable this behavior by enabling the NO_AUTO_CREATE_USER sql mode by default. Thank you to Daniël van Eeden for providing this suggestion.

In addition to these changes, we have decided to withdraw our proposal to change the default isolation level to READ-COMMITTED. We will re-evaluate this decision for future releases.

For those wishing to test out these new configuration settings, I have configuration file available to try out:

Please let us know what you think of these changes!
You can leave a comment here, or get in touch via email.

Proposal to change Replication and InnoDB Settings in MySQL 5.7

In the MySQL Team, we are currently evaluating potential changes to the default server configuration for MySQL 5.7. For more context, please see my earlier post here.

One of the specific changes we would like to make is with the following three related replication and InnoDB settings:

Setting Old Default New Default
binlog_format STATEMENT ROW
transaction_isolation REPEATABLE-READ READ-COMMITTED
innodb_autoinc_lock_mode 1 (“consecutive” lock mode) 2 (“interleaved” lock mode)

We are considering these changes as a package, for the following reasons:

  • Enabling row based binary logging is the safer option for new applications. It allows all types and combinations of statements to be replicated safely (deterministically), and makes slaves more crash-resilient when using features such as temporary tables.

  • InnoDB requires row-based replication in order to provide the READ-COMMITTED isolation level. In making these two changes together, InnoDB performance is improved:

    • There is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition (link to the manual here).

    • INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record without a gap lock on each row inserted into T. If the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

      CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

      When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s.” (link to the manual here)

    It is important to note that READ-COMMITTED represents a relaxed consistency over REPEATABLE-READ, but is also the default isolation level chosen by SQL Server, PostgreSQL and Oracle.

    For some additional context, Peter Zaitsev recently blogged about the advantages of READ-COMMITTED.

  • Setting innodb_autoinc_lock_mode to 2 also requires row based replication, and improves concurrency since the Auto Increment number no longer needs to be locked to provide a consecutive set of numbers for a bulk insert operation. The MySQL manual describes this new lock mode as “interleaved”.

It is important to remember that these represent changes to the default configuration, and reflect what we believe is the best default choice for new applications. We will of course continue to fully support the previous configuration settings should a user prefer these.

With that being said, we are seeking feedback from the community: Do you agree that these represent the best set of defaults for new applications?

Please leave a comment, or get in touch!

Update Jan 23 2015: We have decided to withdraw our proposal to change the default isolation level to READ-COMMITTED for MySQL 5.7. We will reevaluate this decision in the the future.

What defaults would you like to see changed in MySQL 5.7?

In MySQL 5.6, one of the most well received changes was improving the default configuration to be safer, and easier to use.

We are seeking community feedback for improvements that can be made to the default configuration in MySQL 5.7. Please leave a comment, or get in touch with me via email.

For elimination of confusion, please state both the setting you would like changed, and the new desired value.

Thanks!

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.