Proposal to change MySQL replication defaults

In the MySQL team @ Oracle, we've been discussing changing the default values for MySQL replication so that they are safer to use.

Specifically:

Setting Current Value Proposed New Value
sync_binlog 0 1
master-info-repository FILE TABLE
relay-log-info-repository FILE TABLE

I have written about all of these settings before in my post on deciding whether or not to make MySQL durable. The short version is that:

  • By default, if MySQL crashes you could lose data.
  • You don't get to chose what you lose, it could be a $10 order, or a $10M order.
  • Independent of losing orders, DBA time is expensive. Having to perform post-failure investigation to handle discrepancies has a high cost.
  • The cost of durability is much lower with SSDs. In a comment, Daniël van Eeden also correctly said that the cost is much lower in 5.6 with group commit (thanks Daniël!).
  • I recommend most users should be running with MySQL as fully durable.

Now to explain each of these proposed changes:

Sync_binlog

By setting sync_binlog=1 you are ensuring that any changes written to the binary log will not be lost in the event of a crash. This is a problem because with the current default of sync_binlog=0, a replication master crashing may lose events and require all slaves to be re-imaged with a fresh copy of data to be consistent.

It's important to note that changing this setting has a downside: syncing the binary log causes a performance impact. As I mentioned above, this was reduced significantly in MySQL 5.6 with group commit, but it will still be evident in some workloads. The most unfortunate may be in single-threaded performance on systems with hard-drives and no RAID controller with a battery backed write cache.

Master-info-repository/relay-log-info-repository

These two options were first introduced in MySQL 5.6 as part of a feature called transactional replication. When both set to TABLE, they store the internal replication coordinates in an InnoDB table, and COMMIT changes as part of the same transaction as the replication events being applied.

What this means in practical terms, is that slaves are in a consistent state when they crash. Replication can just resume processing events from the point of failure, and data does not need to be re-imaged from the master or another slave.

Conclusion

As with previous proposals, we are seeking feedback on how these changes will impact you.

  • Do you currently use sync_binlog and transactional replication?
  • Have you considered these features, but decided not to switch? (This is a case where we'd really love to hear from you).
  • Do you agree that these changes will make MySQL safer, and easier to administer?

Please leave a comment, or get in touch!

Installing MySQL 5.7 DMR3 with the official yum repos

In case you missed Norvald's post, the official yum repos now support the 5.7 development releases!

The MySQL manual documents how you can enable 5.7, but I wanted to also provide a quick demonstration since using yum sub-repositories is a new concept to me.

Installing the repo

All versions of MySQL are downloaded via a single repo file, which can be downloaded from: http://dev.mysql.com/downloads/repo/.

You can copy the URL from the download page directly into a yum localinstall command. For example on Enterprise Linux 6, the command is:

sudo yum localinstall http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm

Activating MySQL 5.7

The default version of MySQL enabled with the repo will be MySQL 5.6 GA. The documented way to change this to 5.7 is:

shell> sudo yum-config-manager --disable mysql56-community
shell> sudo yum-config-manager --enable mysql57-community-dmr

You also have the option to manually edit the sources.list file, which if you run the above commands will now contain:
{% raw %}

[root@mysqlrpm ~]# cat /etc/yum.repos.d/mysql-community.repo
[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.5
[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Note: MySQL 5.7 is currently in development. For use at your own risk.
# Please read with sub pages: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/
[mysql57-community-dmr]
name=MySQL 5.7 Community Server Development Milestone Release
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

{% endraw %}

From there, a yum install will show MySQL 5.7 as the intended version to be installed:
{% raw %}

[root@mysqlrpm ~]# yum install mysql-community-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: ftp.osuosl.org
 * extras: ftp.osuosl.org
 * updates: ftp.osuosl.org
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:5.7.3-0.1.m13.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================================
 Package                                     Arch                        Version                                   Repository                                  Size
====================================================================================================================================================================
Installing:
 mysql-community-server                      x86_64                      5.7.3-0.1.m13.el6                         mysql57-community-dmr                       62 M

Transaction Summary
====================================================================================================================================================================
Install       1 Package(s)

Total download size: 62 M
Installed size: 281 M
Is this ok [y/N]:

{% endraw %}

Update: yum-config-manager is part of yum-utils. You may need to install it first with yum install yum-utils.

Testing the UNION ALL Optimization in MySQL 5.7 DMR3

When MySQL 5.7 DMR3 was released, I couldn't wait to try out the new UNION ALL optimization that no longer requires data to be materialized in a temporary table.

This new optimization can be seen via EXPLAIN, but it is also nice to run it through SHOW PROFILES, which breaks down query execution step by step. However, this feature is now deprecated (since it overlaps with performance_schema), and will be removed in a future version.

So today I wanted to show you a combination of:
* What a UNION ALL statement looks like in MySQL 5.6 (EXPLAIN, SHOW PROFILES).
* How it is improved in MySQL 5.7 (EXPLAIN, SHOW PROFILES).
* How you can easily emulate the SHOW PROFILES feature with performance_schema + ps_helper :)

Initial Setup

The UNION ALL query I am going to demonstrate uses the following fake sample data:

use test;
CREATE TABLE `table_a` (
  `col1` int(11) NOT NULL AUTO_INCREMENT,
  `col2` char(255) DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB;

CREATE TABLE `table_b` (
  `col1` int(11) NOT NULL AUTO_INCREMENT,
  `col2` char(255) DEFAULT NULL,
  PRIMARY KEY (`col1`)
) ENGINE=InnoDB;

INSERT INTO table_a (col2) VALUES ('A'), ('AA'), ('AAA');
INSERT INTO table_b (col2) VALUES ('B'), ('BB'), ('BBB');

The query that I want to use is this one:

mysql> SELECT * FROM table_a UNION ALL SELECT * FROM table_b;
+------+------+
| col1 | col2 |
+------+------+
|    1 | A    |
|    2 | AA   |
|    3 | AAA  |
|    1 | B    |
|    2 | BB   |
|    3 | BBB  |
+------+------+
6 rows in set (0.00 sec)

MySQL 5.6

We can see from EXPLAIN that MySQL 5.6 requires a step to insert the rows into a temporary table, from which the results can be returned:

mysql> EXPLAIN SELECT * FROM table_a UNION ALL SELECT * FROM table_b\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: table_a
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: table_b
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: NULL
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary
3 rows in set (0.00 sec)

Via SHOW PROFILES, we can also see there are multiple stages of Sending data. I believe the best way to describe this state as 'shipping rows around in the server and between storage engines':

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00039200 | SELECT * FROM table_a UNION ALL SELECT * FROM table_b |
+----------+------------+-------------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000059 |
| checking permissions | 0.000005 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000091 |
| System lock          | 0.000011 |
| optimizing           | 0.000004 |
| statistics           | 0.000012 |
| preparing            | 0.000009 |
| optimizing           | 0.000002 |
| statistics           | 0.000005 |
| preparing            | 0.000004 |
| executing            | 0.000003 |
| Sending data         | 0.000053 |
| executing            | 0.000002 |
| Sending data         | 0.000029 |
| optimizing           | 0.000006 |
| statistics           | 0.000006 |
| preparing            | 0.000004 |
| executing            | 0.000002 |
| Sending data         | 0.000019 |
| removing tmp table   | 0.000007 |
| Sending data         | 0.000002 |
| query end            | 0.000007 |
| closing tables       | 0.000010 |
| freeing items        | 0.000019 |
| cleaning up          | 0.000016 |
+----------------------+----------+
26 rows in set, 1 warning (0.00 sec)

MySQL 5.7

The output from EXPLAIN now shows the query executing in two steps instead of three. The warning here is benign. It is caused by EXPLAIN EXTENDED now being permanently enabled:

mysql> EXPLAIN SELECT * FROM table_a UNION ALL SELECT * FROM table_b\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: table_a
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: table_b
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

And using SHOW PROFILES we can see that there are now only two Sending data steps:

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00038900 | SELECT * FROM table_a UNION ALL SELECT * FROM table_b |
+----------+------------+-------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000094 |
| checking permissions | 0.000013 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000068 |
| System lock          | 0.000012 |
| optimizing           | 0.000015 |
| statistics           | 0.000012 |
| preparing            | 0.000010 |
| optimizing           | 0.000003 |
| statistics           | 0.000005 |
| preparing            | 0.000006 |
| executing            | 0.000002 |
| Sending data         | 0.000046 |
| executing            | 0.000003 |
| Sending data         | 0.000014 |
| query end            | 0.000010 |
| removing tmp table   | 0.000012 |
| query end            | 0.000003 |
| closing tables       | 0.000010 |
| freeing items        | 0.000027 |
| cleaning up          | 0.000016 |
+----------------------+----------+
21 rows in set, 1 warning (0.00 sec)

You will notice that there is in fact still a temporary table, denoted by the step removing tmp table. The release notes explain this point very carefully:

"The server no longer uses a temporary table for UNION statements that
meet certain qualifications. Instead, it retains from temporary table
creation only the data structures necessary to perform result column
typecasting. The table is not fully instantiated and no rows are
written to or read from it; rows are sent directly to the client. As
a result, The result is reduced memory and disk requirements, and
smaller delay before the first row is sent to the client because the
server need not wait until the last query block is executed. EXPLAIN
and optimizer trace output will change: The UNION RESULT query block
will not be present because that block is the part that reads from
the temporary table."

Example using ps_helper

And now to demonstrate how to replace SHOW PROFILES with performance_schema! To do this, I decided to base my scripts on ps_helper by Mark Leith.

There is no specific reason you need to do this, but I admire the way ps_helper works and its something I've wanted to try extending for a while. It also includes useful helper functions to intelligently truncate/format SQL, and convert times to a human readable format.

So the steps are:

Not specifically a limitation of performance_schema, but one small difference with my script is that it is enabled globally as follows:

call ps_helper.enable_profiling();

In any new session, you can then use the profiling feature as follows:

mysql> SELECT * FROM table_a UNION ALL SELECT * FROM table_b;
+------+------+
| col1 | col2 |
+------+------+
|    1 | A    |
|    2 | AA   |
|    3 | AAA  |
|    1 | B    |
|    2 | BB   |
|    3 | BBB  |
+------+------+
6 rows in set (0.00 sec)

mysql> call ps_helper.show_profiles();
+----------+-----------+-------------------------------------------------------+
| Event_ID | Duration  | Query                                                 |
+----------+-----------+-------------------------------------------------------+
|       58 | 58.57 us  | select @@version_comment limit 1                      |
|       70 | 73.19 us  | select USER()                                         |
|       82 | 297.47 us | SELECT * FROM table_a UNION ALL SELECT * FROM table_b |
+----------+-----------+-------------------------------------------------------+
3 rows in set (0.04 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> call ps_helper.show_profile_for_event_id(82);
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| init                 | 55.55 us  |
| checking permissions | 2.15 us   |
| checking permissions | 3.29 us   |
| Opening tables       | 68.30 us  |
| System lock          | 7.43 us   |
| optimizing           | 2.34 us   |
| statistics           | 9.97 us   |
| preparing            | 7.53 us   |
| optimizing           | 606.00 ns |
| statistics           | 2.99 us   |
| preparing            | 2.62 us   |
| executing            | 591.00 ns |
| Sending data         | 73.23 us  |
| executing            | 484.00 ns |
| Sending data         | 23.93 us  |
| query end            | 7.46 us   |
| removing tmp table   | 3.37 us   |
| closing tables       | 7.68 us   |
| freeing items        | 15.27 us  |
| cleaning up          | 804.00 ns |
+----------------------+-----------+
20 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I renamed the command SHOW PROFILE FOR QUERY x to show_profile_for_event_id, since this better maps to performance_schema naming, but otherwise it behaves pretty much the same.

For some reason, there is a second query end state in the SHOW PROFILES version after removing the temporary table. I'm not sure why it is, and if not including it will be an issue.

Conclusion

It's great to see these optimizations introduced into MySQL - I can think of a number of users who can benefit from a better UNION ALL.

I implemented my own SHOW PROFILES feature really as an educational step for myself to make sure I knew performance_schema, and it was actually quite a quick and joyful experience. I plan to work with the maintainer of ps_helper and see if this can be included in future versions.

The MySQL error log in MySQL 5.7

The MySQL error log has received some attention in MySQL 5.7, with a new setting called log_error_verbosity.

There are three possible values, as documented in the manual:

Verbosity Value Message Types Logged
1 Errors only
2 Errors and warnings
3 Errors, warnings, and notes (default)

As Giuseppe has written about previously, writing notes or "informational events" can create debugging problems because they reduce the signal to noise ratio. There is now an easy way to reduce the logging to be only warnings and errors!

MySQL 5.6.15 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.15.

In particular:

  • Thanks to Yoshinori Matsunobu, who reported that the performance_schema did not instrument locks related to semisync replication. Bug #70577.
  • Thanks to Sebastian Strzelczyk, who reported excessive memory usage when querying INFORMATION_SCHEMA.INNODB_FT_INDEX. Sebastian uploaded a number of reports of memory usage, which was helped us generate a good reproducible testcase. Bug #70329.
  • Thanks to Inaam Rana, who suggested improvements to InnoDB counters in information_schema, and contributed a patch! Bug #70241.
  • Thanks to Yoshinori Matsunobu, who reported inefficiency in InnoDB memcached API. Yoshinori's feedback was also part of the 1 million QPS record set for 5.7. Bug #70172.
  • Thanks to Nizameddin Ordulu, who identified a case where crash recovery might not be possible with InnoDB. Nizameddin pointed out where in the code the bug existed, and we were quickly able to identify that this was a regression from an earlier bug fix. Bug #69122.
  • Thanks to Elena Stepanova, who identified a case where SELECT COUNT queries would run very slowly when run concurrently with a "LOAD DATA" operation. Elena also provided a testcase! Bug #69141.
  • Thanks to Daniël van Eeden, who reported a confusing/incorrect error message when using the InnoDB memcached API. Bug #68684.
  • Thanks to Monty Widenius and Michal Sulik, who both reported an issue where a unique composite index would return wrong results. Bug #70038, Bug #60220.
  • Thanks to Edward Dore, who reported an issue when converting tables from MyISAM to InnoDB. It was from a table that Edward provided that we were able to reproduce the issue! Bug #50366.
  • Thanks to Elena Stepanova who reported an error when deleting from a partitioned table, and provided a testcase! Bug #67982.
  • Thanks to Santosh Praneeth Banda for reporting a security issue. Bug #70349.
  • Thanks to Vlad Lesin, who reported that certain LOAD DATA statements would appear in the binary log twice, and provided a testcase! Bug #70277.
  • Thanks to Yoshinori Matsunobu, who reported that transactions could be lost when starting/stopping slaves with GTIDs. Yoshinori also provided a small program which we were able to use to reproduce the issue. Bug #69943.
  • Thanks to raolh rao for reporting a security issue. #70045.
  • Thanks to Miguel Angel Nieto, who reported that LAST_INSERT_ID is replicated incorrectly when replication filters are used. #69861.
  • Thanks to Santosh Praneeth Banda, who reported an issue with GTIDs and a patch with a suggested fix! Bug #69618.
  • Thanks to Maciej Dobrzanski, who reported an issue where replication would break following invalid grant statements. Bug #68892.
  • Thanks to Rich Prohaska and Przemyslaw Malkowski, who both reported a problem with BINARY columns and online DDL. Bugs #67141 and #69580.
  • Thanks to Jan Staněk, who submitted a patch with code improvements following the result of a Coverity scan. Bug #68918.
  • Thanks to Alexey Kopytov, who reported an issue in debug builds of MySQL, Bug #69653.
  • Thanks to Arthur O'Dwyer, who pointed out that COUNT DISTINCT would incorrectly count NULL rows. Bug #69841.
  • Thanks to Lance Ivy for discovering an issue where UPDATE does not update rows in the correct order. Bug #68656.
  • Thanks to Elena Stepanova for pointing out that mysql_upgrade will fail with the InnoDB storage engine disabled. Bug #70152.
  • Thanks to Arthur O'Dwyer, who reported that prefix indexes on text columns could return wrong results. Bug #70341.
  • Thanks to Yoshinori Matsunobu who pointed out that PERFORMANCE_SCHEMA overhead was very high in frequent connect/disconect scenarios. Bug #70018.
  • Thanks to Moritz Schuepp, who reported that host_cache_size set in a configuration file was ignored. Bug #70552.
  • Thanks to Saverio Miroddi, who reported an issue with InnoDB FULLTEXT and using savepoints. Bug #70333.
  • Thanks to Hartmut Holzgraefe, who reported that users with a legacy hostname may not be possible to drop. Hartmut also provided a patch! Bug #62255, #62254.
  • Thanks to Honza Horak, who reported that MySQL incorrectly assumed all ethernet interfaces start with ethX. Honza also provided a patch! Bug #63055.
  • Thanks to Honza Horak, who reported a number of code improvements (and patches) resulting from Coverity analysis. Bug #70591.
  • Thanks to Jan Staněk, who suggested code improvements (and a patch) resulting from Coverity analysis. Bug #68896.
  • Thanks to Roderik van der Veer, who reported that MySQL would not compile on Mac OS X 10.9. Thanks also to David Peixotto who provided an example patch of how to fix the problem. While we didn't end up using David's patch, we certainly value his contribution. Bug #70542.
  • Thanks to Olaf van der Spek and Simon Mudd, who both reported an issue that affected server shutdown. Bugs #18256, #69975.

Thank you again all the names listed above. In particular, I would like to call out the names that appear more than once:

Yoshinori Matsunobu (4), Elena Stepanova (3), Santosh Praneeth Banda (2), Jan Staněk (2), Arthur O’Dwyer (2), Honza Horak (2).

And the names of those who contributed patches:

Inaam Rana, Santosh Praneeth Banda, Hartmut Holzgraefe, Honza Horak, Jan Staněk

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

- Morgan