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

[1 Apr 2020 6:53] zkong kong
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)
[1 Apr 2020 7:01] zkong kong
the buffer_pool is 64G
[2 Apr 2020 12:49] MySQL Verification Team
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.
[2 Apr 2020 12:53] MySQL Verification Team
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.
[2 Apr 2020 13:02] MySQL Verification Team
Hi Mr. kong,

This bug is now a duplicate of the following bug:

https://bugs.mysql.com/bug.php?id=94283
[16 May 2020 11:49] Federico Razzoli
How is this a duplicate? 94283 is not about single-thread replication.
[18 May 2020 12:10] MySQL Verification Team
It is a duplicate solely on the basis of the replication performance. Both single- or multi-thread replication are same in this respect.