Significant Regression in Sysbench Update-Index Microbenchmark Persisting from 8.0.30 to 8.0.40

[wangbin579]

The instant drop/add column feature is a primary cause of performance degradation in 8.0.29. Pinpointing the exact line is challenging due to the patch's tens of thousands of lines.

commit e8f422de8595f65f05f88ea4d4c94c7dfd365afd (HEAD) 
Author: Mayank Prasad <mayank.prasad@oracle.com>
Date:   Mon Dec 20 05:14:49 2021 +0100

    WL#13899 : INSTANT DROP (and ADD) COLUMN
    
    To provide functionality to DROP COLUMN with ALGORITHM=INSTANT from any
    position in table. Also new design enhances existing functionality to
    ADD COLUMN with ALGORITHM=INSTANT by allowing column to be added at any
    position.
    
    RB : 26475
    Approved-by : Debarun Banerjee <debarun.banerjee@oracle.com>
                  Annamalai Gurusami <annamalai.gurusami@oracle.com>

[mdcallag]

Some results are here. I am impressed that Advanced MySQL helps a lot on the update-index microbenchmark.

[stdex]

Summary of problem here:
https://smalldatum.blogspot.com/2023/10/perf-regressions-for-mysqlinnodb-vs.html
https://perconadev.atlassian.net/browse/PS-8822

1. Some functions (rec_get_nth_field and other) not inline in the original solution.
   This was later partially corrected in:
   mysql/mysql-server@dfecee6

2. After that there were more changes, e.g.:
   mysql/mysql-server@d4ecb24
   It is worth noting that validate_rec_offset not inlined.

I suppose that PGO build (if it is used in advancedmysql) could fix this and automatically inline functions.

[wangbin579]

Summary of problem here: https://smalldatum.blogspot.com/2023/10/perf-regressions-for-mysqlinnodb-vs.html https://perconadev.atlassian.net/browse/PS-8822

1. Some functions (rec_get_nth_field and other) not inline in the original solution.
   This was later partially corrected in:
   mysql/mysql-server@dfecee6
2. After that there were more changes, e.g.:
   mysql/mysql-server@d4ecb24
   It is worth noting that validate_rec_offset not inlined.

I suppose that PGO build (if it is used in advancedmysql) could fix this and automatically inline functions.

After reading the specific details at this link (https://bugs.mysql.com/bug.php?id=111538), I finally understand what you mean(sorry, at first, I thought it was a new problem). I'll check if the PGO optimization can resolve this issue.

[wangbin579]

Based on our tests under identical conditions, MySQL 8.0.27 outperforms 8.0.40 by 15% with standard compilation. After applying PGO optimization to both, 8.0.27 still shows about 6% higher performance than 8.0.40.

[wangbin579]

The conclusion above is based on a single SQL test result. Further testing shows that the results vary with different dataset sizes. The larger the data volume, the smaller the performance gap after applying PGO, with 8.0.40 even outperforming 8.0.27 in some cases.
@stdex @mdcallag

[wangbin579]

Test results for the PGO-optimized MySQL 8.0.27 version:

# Time: 2024-11-05T06:21:30.817988-00:00
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    14
# Query_time: 14.059849  Lock_time: 0.000424 Rows_sent: 5000000  Rows_examined: 6087915
SET timestamp=1730787676;
SELECT *  FROM bmsql_order_line AS e LEFT JOIN bmsql_stock AS s ON e.ol_i_id = s.s_i_id LIMIT 5000000;
# Time: 2024-11-05T06:23:06.621859-00:00
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    15
# Query_time: 3.232393  Lock_time: 0.000442 Rows_sent: 1000000  Rows_examined: 6027876
SET timestamp=1730787783;
SELECT *  FROM bmsql_order_line AS e LEFT JOIN bmsql_stock AS s ON e.ol_i_id = s.s_i_id LIMIT 1000000;
# Time: 2024-11-05T06:25:30.805732-00:00
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    17
# Query_time: 0.354701  Lock_time: 0.000439 Rows_sent: 100000  Rows_examined: 845869
SET timestamp=1730787930;
SELECT *  FROM bmsql_order_line AS e LEFT JOIN bmsql_stock AS s ON e.ol_i_id = s.s_i_id LIMIT 100000;
# Time: 2024-11-05T06:35:52.084712-00:00
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    18
# Query_time: 27.478906  Lock_time: 0.000439 Rows_sent: 10000000  Rows_examined: 6171959
SET timestamp=1730788524;
SELECT *  FROM bmsql_order_line AS e LEFT JOIN bmsql_stock AS s ON e.ol_i_id = s.s_i_id LIMIT 10000000;

Test results for the PGO-optimized MySQL 8.0.40 version:

# Time: 2024-11-05T06:20:40.957722-00:00
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    10
# Query_time: 13.167529  Lock_time: 0.000005 Rows_sent: 5000000  Rows_examined: 6087949
SET timestamp=1730787627;
SELECT *  FROM bmsql_order_line AS e LEFT JOIN bmsql_stock AS s ON e.ol_i_id = s.s_i_id LIMIT 5000000;
# Time: 2024-11-05T06:22:55.078840-00:00
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    11
# Query_time: 3.281110  Lock_time: 0.000005 Rows_sent: 1000000  Rows_examined: 6027791
SET timestamp=1730787771;
SELECT *  FROM bmsql_order_line AS e LEFT JOIN bmsql_stock AS s ON e.ol_i_id = s.s_i_id LIMIT 1000000;
# Time: 2024-11-05T06:25:23.946116-00:00
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    13
# Query_time: 0.373206  Lock_time: 0.000005 Rows_sent: 100000  Rows_examined: 845468
SET timestamp=1730787923;
SELECT *  FROM bmsql_order_line AS e LEFT JOIN bmsql_stock AS s ON e.ol_i_id = s.s_i_id LIMIT 100000;
# Time: 2024-11-05T06:27:55.863055-00:00
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    14
# Query_time: 25.397629  Lock_time: 0.000005 Rows_sent: 10000000  Rows_examined: 6172168
SET timestamp=1730788050;
SELECT *  FROM bmsql_order_line AS e LEFT JOIN bmsql_stock AS s ON e.ol_i_id = s.s_i_id LIMIT 10000000;

[wangbin579]

Based on in-depth research, PGO can indeed alleviate the issues mentioned above, but it cannot solve all problems. Some optimizations cannot be addressed by PGO. This part of the fix will be released once testing is complete.

[wangbin579]

@stdex @mdcallag

The updates are up on GitHub (https://github.com/advancedmysql/mysql-8.0.40)! Join operations are now performing pretty close to version 8.0.27. Give it a test and see if you notice any improvements.

[mdcallag]

I have been using 4 server types for my sysbench testing in the past and tested this change on 3 of them. I will test on the fourth server in a few days, as the fourth server gets the worst regression for the update-index microbenchmark.

All of this is done without PGO builds.

What I see on the 3 servers that I tested:

• it doesn't improve throughput for update-index
• it does improve throughput for the scan microbenchmark

So I like the change, but it doesn't fix this issue. But it does a lot to fix upstream bug 111538 which unfortunately was closed without really being fixed.

[wangbin579]

My recent modifications are focused on join read operations and do not affect the update-index microbenchmark. I'll look into this issue next week.

[wangbin579]

Potential for improving join performance:

[mdcallag]

The fix (issue 1) removes much of the perf regression during scans but the benefit depends on the server type and concurrency level. Regardless, this is a great improvement. Thank you.

A blog post is here.

[stdex]

Hola!

It looks like some of your fixes have been adopted into recent Percona MySQL releases.

Percona Server for MySQL 8.0.43-34 (2025-08-28)
Percona Server for MySQL 8.4.6-6 (2025-09-08)

percona/percona-server#5609
percona/percona-server#5610
percona/percona-server#5611
percona/percona-server#5643