MySQL Bugs: #99140: 8.0 single thread applying performance regression of update_non

Bug #99140	8.0 single thread applying performance regression of update_non_index
Submitted:	1 Apr 2020 6:53	Modified:	2 Apr 2020 13:02
Reporter:	zkong kong	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	CentOS
Assigned to:		CPU Architecture:	x86

Description:
The apply performance of mysql 8.0.19 is much lower than mysql 5.7.29 when using sysbench update_non_index test on master

How to repeat:
Build one three nodes cluster with master(5.7.29) and  two slaves (5.7.29 and 8.0.19)
The three nodes with configuration:
sync_binlog=0
sync_relay_log=0
innodb_flush_log_at_trx_commit=0
binlog-format=ROW

Sysbench (sysbench 1.1.0) update_non_index write on master:
sysbench   --tables=16 --table-size=1000000 --threads=4   --rand-type=uniform  --percentile=90 --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=sbtest --mysql-password=sbtest --mysql_storage_engine=innodb  --time=360000 --report-interval=1 oltp_update_non_index prepare

sysbench   --tables=16 --table-size=1000000 --threads=4   --rand-type=uniform  --percentile=90 --mysql-host=127.0.0.1 --mysql-port=3307 --mysql-user=sbtest --mysql-password=sbtest --mysql_storage_engine=innodb  --time=360000 --report-interval=1 oltp_update_non_index run

Configure the slave single thread applying.

slave-parallel-workers=0

The slave tps of 8.0.19 is much less than 5.7.29 (8000 vs 14000)

the buffer_pool is 64G

Hi Mr. Kong,

Thank you for your bug report.

We need few more clarifications from you.

When you write "three nodes cluster", are you referring to:

* NDB cluster

* InnoDB cluster

* One master and two slaves over the normal, RBR, replication

Also, did you observe this slowdown only with clustering or did you observe it on a standalone server too ???

Thank you in advance.

Hi Mr. kong,

You have also configured slave to have a single applying thread.

It is very well known that singe-thread performance of 8.0 is lower then on 5.7. We just have to find a bug on that subject, that is verified already.

Hi Mr. kong,

This bug is now a duplicate of the following bug:

https://bugs.mysql.com/bug.php?id=94283

How is this a duplicate? 94283 is not about single-thread replication.

It is a duplicate solely on the basis of the replication performance. Both single- or multi-thread replication are same in this respect.