Bug #86246 binlog_group_commit_sync_delay causing performance drop
Submitted: 9 May 2017 14:50 Modified: 1 Jun 2017 14:59
Reporter: Holger Thiel Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S5 (Performance)
Version:5.7.17 OS:Red Hat
Assigned to: Bogdan Kecman CPU Architecture:Any
Tags: binlog broken group commit

[9 May 2017 14:50] Holger Thiel
Description:
binlog_group_commit_sync_delay was set to a different value (500000). sync_binlog was set to "0". 

Instead of a better performance there was a great performance drop.

It seems this feature causes the problem known as "broken group commit" again. The setting of "innodb_flush_log_at_trx_commit" has no influence (tested with 1 and 2)

How to repeat:
mysql> SET sync_binlog=0;
mysql> SET GLOBAL binlog_group_commit_sync_delay=500000;

Make an OLTP test.

Suggested fix:
Update/refine the description of this feature.
[23 May 2017 2:02] zhai weixiang
Currently it will delay even sync_binlog is 0. I think it should skip waiting as non fsync will be performed with sync_binlog = 0;

quoted code from MYSQL_BIN_LOG::ordered_commit (5.7.18)

  if (!flush_error && (sync_counter + 1 >= get_sync_period()))
    stage_manager.wait_count_or_timeout(opt_binlog_group_commit_sync_no_delay_count,
                                        opt_binlog_group_commit_sync_delay,
                                        Stage_manager::SYNC_STAGE);

It can even skip sync stage  when sync_binlog = 0.
[31 May 2017 14:25] Bogdan Kecman
Hi,

I'm not seeing any "serious performance drop" doing this test, nor I'm seeing a bug here. You might miss-configured something else. Note that our support team is always happy to help you properly configure your system and help you get maximum performance out of your system :)

all best
Bogdan
[1 Jun 2017 14:59] Holger Thiel
I have tried it with the default configuration and had the same problems/results.

HammerDB with 8 clients.
SET GLOBAL binlog_group_commit_sync_delay=0      => about 700 000 TPM
SET GLOBAL binlog_group_commit_sync_delay=500000 => about  10 000 TPM

I have checked it with a Ubuntu-VM with ext4 as file system, too.
Same bevavior as Red Hat with XFS.

Test configuration

xyz@mysql01:/etc/mysql$ cat /proc/version
Linux version 4.4.0-71-generic (buildd@lcy01-22) (gcc version 4.8.4 (Ubuntu 4.8.4-2ubuntu1~14.04.3) ) #92~14.04.1-Ubuntu SMP Fri Mar 24 15:22:50 UTC 2017

MySQL Configuration (default)

[mysqld]
server-id                                = 1
log-bin                                  = binlog
basedir                                  = /usr
datadir                                  = /var/lib/mysql
pid-file                                 = /var/run/mysqld/mysqld.pid
socket                                   = /var/run/mysqld/mysqld.sock
symbolic-links                           = OFF
tmpdir                                   = /tmp
lc-messages-dir                          = /usr/share/mysql
general_log_file                         = general.log
log-error                                = /var/log/mysql/error.log
slow_query_log_file                      = slow.log
innodb_data_file_path                    = 'ibdata1:12M:autoextend'