MySQL Bugs: #86246: binlog_group_commit_sync_delay causing performance drop

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:	MySQL Verification Team	CPU Architecture:	Any
Tags:	binlog broken group commit

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.

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.

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

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'