Description:
I have consistently worse results with simple sysbench tests on the most recent 8.0 versions. The regression ranges from ~3% up to ~12% on 8.0.37 vs 8.0.25, depending on the test and settings.
The most visible difference I see is when binlog is disabled, but it happens with any settings (ofc the same on both tested versions).
For example, using 8 threads:
8.0.25 - 4080 TPS
8.0.37 - 3647 TPS
I cannot spot any specific minor version being most responsible; the regression seems to vary and is already clearly visible in, for example, 8.0.28 and 8.0.33. While the difference is almost not visible on 8.0.25 vs 8.0.26.
How to repeat:
Set up plain sandboxes, best with these settings:
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=2
skip-log-bin
And run sysbench:
sysbench /usr/share/sysbench/oltp_read_write.lua --tables=1 --threads=8 --table-size=1000 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --db-driver=mysql --mysql-socket=/tmp/mysql_sandbox8055.sock --mysql-host=localhost prepare
sysbench /usr/share/sysbench/oltp_read_write.lua --tables=1 --threads=8 --table-size=1000 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --db-driver=mysql --mysql-socket=/tmp/mysql_sandbox8055.sock --mysql-host=localhost run
Example results:
MySQL Community 8.0.25
mysql [localhost:8026] {msandbox} ((none)) > select @@innodb_log_file_size,@@innodb_buffer_pool_size,@@log_bin,@@version;
+------------------------+---------------------------+-----------+-----------+
| @@innodb_log_file_size | @@innodb_buffer_pool_size | @@log_bin | @@version |
+------------------------+---------------------------+-----------+-----------+
| 50331648 | 134217728 | 0 | 8.0.25 |
+------------------------+---------------------------+-----------+-----------+
1 row in set (0.00 sec)
1 thread
transactions: 8620 (861.71 per sec.)
transactions: 8483 (848.01 per sec.)
transactions: 8458 (845.50 per sec.)
8 threads
transactions: 40823 (4080.60 per sec.)
transactions: 40851 (4083.17 per sec.)
transactions: 41062 (4104.60 per sec.)
transactions: 40201 (4018.36 per sec.)
MySQL Community 8.0.37
mysql [localhost:8055] {msandbox} ((none)) > select @@innodb_log_file_size,@@innodb_buffer_pool_size,@@log_bin,@@version;
+------------------------+---------------------------+-----------+-----------+
| @@innodb_log_file_size | @@innodb_buffer_pool_size | @@log_bin | @@version |
+------------------------+---------------------------+-----------+-----------+
| 50331648 | 134217728 | 0 | 8.0.37 |
+------------------------+---------------------------+-----------+-----------+
1 row in set (0.00 sec)
1 thread
transactions: 7853 (785.01 per sec.)
transactions: 7881 (787.86 per sec.)
transactions: 7580 (757.74 per sec.)
8 threads
transactions: 36487 (3647.18 per sec.)
transactions: 35636 (3562.30 per sec.)
transactions: 35028 (3501.43 per sec.)
transactions: 36825 (3680.33 per sec.)
Suggested fix:
Reduce performance regression if possible.
Description: I have consistently worse results with simple sysbench tests on the most recent 8.0 versions. The regression ranges from ~3% up to ~12% on 8.0.37 vs 8.0.25, depending on the test and settings. The most visible difference I see is when binlog is disabled, but it happens with any settings (ofc the same on both tested versions). For example, using 8 threads: 8.0.25 - 4080 TPS 8.0.37 - 3647 TPS I cannot spot any specific minor version being most responsible; the regression seems to vary and is already clearly visible in, for example, 8.0.28 and 8.0.33. While the difference is almost not visible on 8.0.25 vs 8.0.26. How to repeat: Set up plain sandboxes, best with these settings: innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=2 skip-log-bin And run sysbench: sysbench /usr/share/sysbench/oltp_read_write.lua --tables=1 --threads=8 --table-size=1000 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --db-driver=mysql --mysql-socket=/tmp/mysql_sandbox8055.sock --mysql-host=localhost prepare sysbench /usr/share/sysbench/oltp_read_write.lua --tables=1 --threads=8 --table-size=1000 --mysql-db=test --mysql-user=msandbox --mysql-password=msandbox --db-driver=mysql --mysql-socket=/tmp/mysql_sandbox8055.sock --mysql-host=localhost run Example results: MySQL Community 8.0.25 mysql [localhost:8026] {msandbox} ((none)) > select @@innodb_log_file_size,@@innodb_buffer_pool_size,@@log_bin,@@version; +------------------------+---------------------------+-----------+-----------+ | @@innodb_log_file_size | @@innodb_buffer_pool_size | @@log_bin | @@version | +------------------------+---------------------------+-----------+-----------+ | 50331648 | 134217728 | 0 | 8.0.25 | +------------------------+---------------------------+-----------+-----------+ 1 row in set (0.00 sec) 1 thread transactions: 8620 (861.71 per sec.) transactions: 8483 (848.01 per sec.) transactions: 8458 (845.50 per sec.) 8 threads transactions: 40823 (4080.60 per sec.) transactions: 40851 (4083.17 per sec.) transactions: 41062 (4104.60 per sec.) transactions: 40201 (4018.36 per sec.) MySQL Community 8.0.37 mysql [localhost:8055] {msandbox} ((none)) > select @@innodb_log_file_size,@@innodb_buffer_pool_size,@@log_bin,@@version; +------------------------+---------------------------+-----------+-----------+ | @@innodb_log_file_size | @@innodb_buffer_pool_size | @@log_bin | @@version | +------------------------+---------------------------+-----------+-----------+ | 50331648 | 134217728 | 0 | 8.0.37 | +------------------------+---------------------------+-----------+-----------+ 1 row in set (0.00 sec) 1 thread transactions: 7853 (785.01 per sec.) transactions: 7881 (787.86 per sec.) transactions: 7580 (757.74 per sec.) 8 threads transactions: 36487 (3647.18 per sec.) transactions: 35636 (3562.30 per sec.) transactions: 35028 (3501.43 per sec.) transactions: 36825 (3680.33 per sec.) Suggested fix: Reduce performance regression if possible.