Bug #109822 MySQL8 innodb cluster writes more frequently than MySQL5.7
Submitted: 27 Jan 2023 14:14 Modified: 30 Jan 2023 14:52
Reporter: Liang Cheng Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0.31 OS:CentOS
Assigned to: MySQL Verification Team CPU Architecture:x86

[27 Jan 2023 14:14] Liang Cheng
Description:
We are comparing MySQL5.7 (5.7.38) innodb cluster and MySQL8 (8.0.31) innodb cluster (write) performance.
We setup environments on AWS ec2 instances. Following are the detail information
1. Both clusters includes one primary db and 2 slave dbs
2. The instance type of these mysql nodes is m4.xlarge (4core, 16G ram), and the ebs volume is gp3 (3000 IOPS, 125M/s)
So we make suree that the hardware (cpu, ram, disk) is same between 2 clusters.

Following are the mysql8 variables we configured so far
#innodb_log_file_size = 250M
max_allowed_packet = 512M
innodb_buffer_pool_size = 4096M
max_connections=2500
transaction-isolation=READ-COMMITTED
auto_increment_increment=7

#new in mysql 8.0.31, innodb_log_file_size is deprecated.
innodb_redo_log_capacity=536870912
innodb_use_fdatasync=1
innodb_log_writer_threads=ON
innodb_flush_neighbors=1
innodb_max_dirty_pages_pct=75
innodb_max_dirty_pages_pct_lwm=0
innodb_flush_method=O_DIRECT_NO_FSYNC

We run the sysbench write_only test case with following 2 commands. 
sysbench \
        --db-driver=mysql \
        --mysql-user="sbtest_user" \
        --mysql-password="$password" \
        --mysql-db="sbtest" \
        --mysql-host="$host" \
        --mysql-port="6446" \
        --tables="16" \
        --table-size="100000" \
        /usr/share/sysbench/oltp_write_only.lua prepare

sysbench \
        --db-driver=mysql \
        --mysql-user="sbtest_user" \
        --mysql-password="$password" \
        --mysql-db="sbtest" \
        --mysql-host="$host" \
        --mysql-port="6446" \
        --tables="16" \
        --table-size="100000" \
		--threads=16 --events=0 --report-interval=10 --time=86400 /usr/share/sysbench/oltp_write_only.lua

		
While running the test, we monitor some metrics every one minute. E.g. how much data is written, how many writes/fsyncs calls, etc.
MySQL5.7
 --------|--------|----- Innodb row operation -----|-- Buffer Pool operation -------|------   InnoDB file i/o    ------|--- InnoDB log  i/o   -------------|
---Time---|---QPS---| read inserted updated deleted | logical_r  physical_r logical_w|  reads   writes   fsyncs  written| logical  physical  fsyncs  written
 08:14:00 |   404968|197499    67281  131012   65514|   3177974          0    1457236|      0    34405    22498   609955| 198724    19655    19665   162015
 08:15:00 |   405244|197728    67328  131201   65559|   3176589          0    1458232|      0    34499    22758   604927| 198986    19952    19961   162939
 08:16:00 |   405210|197787    67319  131205   65609|   3169262          0    1459142|      0    34432    22801   602426| 199184    19911    19924   161846

 
 MySQL8
  --------|--------|----- Innodb row operation -----|-- Buffer Pool operation -------|------   InnoDB file i/o    ------|--- InnoDB log  i/o   -------------|
---Time---|---QPS---| read inserted updated deleted | logical_r  physical_r logical_w|  reads   writes   fsyncs  written| logical  physical  fsyncs  written
 03:10:24 |   404224|196775    67203  130533   65294|   3099647          0    1390588|      0    68268    23696   416716|1181308    49599    23689   120168
 03:11:24 |   404048|196756    67172  130539   65270|   3105626          0    1387081|      0    67869    23448   416295|1179545    49163    23441   119955
 03:12:24 |   405410|197563    67402  131051   65563|   3120064          0    1396455|      0    67925    23398   417775|1185506    49129    23344   120459
 
 NOTE: For Innodb log i/o, logical means Innodb_log_write_requests, physical means Innodb_log_writes. 

On MySQL8, even though we configure innodb_flush_method to O_DIRECT_NO_FSYNC, mysql8 still always has more Innodb_log_write_requests (5x) and Innodb_log_writes (2.5x) compared to mysql5.7.  We suspect that it may degrade MySQL8's 
write performance. We have another performance test environment running with our application, we notiice that the physical writes count gap is even bigger, and in that environment, mysql8's write performance degrades more than 50% after running 24 hours. 

MySQL8
----------|---------|----- Innodb row operation -----|-- Buffer Pool operation -------|------   InnoDB file i/o    ------|--- InnoDB log  i/o   -------------|
---Time---|---QPS---|  read inserted updated deleted | logical_r  physical_r logical_w|  reads   writes   fsyncs  written| logical  physical  fsyncs  written
 01:31:36 |    40467|2748785     4997    1960     233|   3441105          0      61034|      0    18163     5349   109629|  40118    11930     5346    24054
 01:32:36 |    66965|5603891    12105    3029     287|   7326149          0     163523|      0    35364     9839   205640|  86133    26959     9831    81783
 01:33:36 |    44494|2170395    10277    2528     194|   2914932          0      98634|      0    30992     8472   207199|  59464    19159     8465    43481
 01:34:36 |    28848|673555      4565    1492     165|    877579          0      42660|      0    17144     4307   128153|  28137     8702     4305    16158
 01:35:36 |    36669|2373326     3070    2031     170|   3078528          0      66007|      0    18554     5153   118131|  34282    12388     5150    34145

MySQL5.7
----------|---------|----- Innodb row operation -----|-- Buffer Pool operation -------|------   InnoDB file i/o    ------|--- InnoDB log  i/o   -------------|
---Time---|---QPS---|  read inserted updated deleted | logical_r  physical_r logical_w|  reads   writes   fsyncs  written| logical  physical  fsyncs  written
 01:31:57 |    52245|2990732    10216    2626     269|   1384559          0      92903|      0     7674     3281   198917|  84350     2228     2256    38822
 01:32:57 |    66275|5794001    12636    3133     296|   2640655          0     146281|      0    10257     4453   287838| 164546     3169     3197    79294
 01:33:57 |    32014|1223206     7145    1910     131|    699440          0      64576|      0     9053     2589   266321|  70149      995     1026    30050
 01:34:57 |    28201|1247419     2794    1553     194|    605975          0      36323|      0     7904     2189   217063|  29854     1072     1106    13783
 01:35:57 |    29440|1251741     2939    1869     122|    697259          0      48412|      0     7039     2222   204203|  60888     1050     1082    28251

And we wonder if there is any variables could be tuned to reduce the write frequency on MySQL8 so that we can verify if it has impact on the write performance.

How to repeat:
We run the sysbench write_only test case with following 2 commands. 
sysbench \
        --db-driver=mysql \
        --mysql-user="sbtest_user" \
        --mysql-password="$password" \
        --mysql-db="sbtest" \
        --mysql-host="$host" \
        --mysql-port="6446" \
        --tables="16" \
        --table-size="100000" \
        /usr/share/sysbench/oltp_write_only.lua prepare

sysbench \
        --db-driver=mysql \
        --mysql-user="sbtest_user" \
        --mysql-password="$password" \
        --mysql-db="sbtest" \
        --mysql-host="$host" \
        --mysql-port="6446" \
        --tables="16" \
        --table-size="100000" \
		--threads=16 --events=0 --report-interval=10 --time=86400 /usr/share/sysbench/oltp_write_only.lua
[30 Jan 2023 14:52] MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.