| Bug #118411 | Performance degradation in write workload after rebuilding table with row_format=compressed | ||
|---|---|---|---|
| Submitted: | 10 Jun 15:52 | Modified: | 21 Jul 11:44 |
| Reporter: | Alok Pathak | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
| Version: | 8.0.37, 8.0.42 | OS: | CentOS |
| Assigned to: | CPU Architecture: | x86 | |
[23 Jun 9:52]
MySQL Verification Team
Hello Alok, Thank you for the report and feedback. I attempted twice but didn't observe reported behaviour(only diff was I was checking against 8.0.42). Let me retry later tonight and get back to if anything further needed. Thank you. regards, Umesh
[3 Jul 12:01]
MySQL Verification Team
Hello Alok, Thank you, my apologies for the delay as I attempted to reproduce multiple times but not seeing any issues(I'll be joining test results shortly, with environment details etc.). Ami I missing anything? Please let us know. Also, I tried to reproduce on current GA 8.0.42 as we don't fix/backport in old versions(compared to 8.0.37 for which you reported), could you please check against 8.0.42 + I need exact environment details where you have seen this. Thank you. regards, Umesh
[3 Jul 12:02]
MySQL Verification Team
8.0.42 test results
Attachment: 118411_8.0.42.results (application/octet-stream, text), 13.44 KiB.
[15 Jul 18:36]
Alok Pathak
Hello Umesh,
I repeated the benchmarking with MySQL 8.0.42 (Community Server) and was able to reproduce the performance issue. Below are the environment details, steps, and observations.
Environment details are attached in a file system.txt
Steps to repeat
- Create a sysbench database and table with 1 million rows.
sysbench oltp_common --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=8042 --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --tables=1 --table-size=1000000 --threads=1 prepare
- Add a few secondary indexes and enable InnoDB compression
use sbtest;
ALTER TABLE sbtest1
ADD INDEX idx_k_c (k, c),
ADD INDEX idx_c_k (c, k),
ADD INDEX idx_pad_k (pad, k),
ROW_FORMAT=COMPRESSED;
mysql [localhost:8042] {msandbox} (sbtest) > select count(*) from sbtest.sbtest1;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.24 sec)
$ ls -lh
total 396M
-rw-r-----. 1 .... 396M Jul 15 17:09 sbtest1.ibd
- Delete approximately half (500k) rows using sysbench, the deletes should be random rows.
sysbench oltp_delete --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=8042 --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --tables=1 --table-size=1000000 --threads=4 --db-ps-mode=disable --report-interval=1 --rate=0 --time=1500 run
mysql [localhost:8042] {msandbox} (sbtest) > select count(*) from sbtest.sbtest1;
+----------+
| count(*) |
+----------+
| 499892 |
+----------+
1 row in set (0.07 sec)
$ ls -lh
total 396M
-rw-r-----. 1 ... 396M Jul 15 17:34 sbtest1.ibd
- Run oltp_write_only workload and observe the tps, qps and latency.
sysbench oltp_write_only --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=8042 --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --tables=1 --table-size=1000000 --threads=1 --db-ps-mode=disable --skip_trx=on --delete_inserts=2 --index_updates=5 --report-interval=1 --rate=0 --time=600 run
SQL statistics:
queries performed:
read: 0
write: 1227319
other: 288171
total: 1515490
transactions: 151549 (252.58 per sec.)
queries: 1515490 (2525.80 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0020s
total number of events: 151549
Latency (ms):
min: 1.09
avg: 3.96
max: 111.53
95th percentile: 6.91
sum: 599660.89
Threads fairness:
events (avg/stddev): 151549.0000/0.00
execution time (avg/stddev): 599.6609/0.00
- Rebuild table to reclaim space.
ALTER TABLE sbtest.sbtest1 ENGINE=INNODB;
$ ls -lh
total 236M
-rw-r-----. 1 ... 236M Jul 15 17:45 sbtest1.ibd
- Re-run the same oltp_write_only workload and observe the tps, qps and latency.
sysbench oltp_write_only --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=8042 --mysql-user=xxx --mysql-password=xxx --mysql-db=sbtest --tables=1 --table-size=1000000 --threads=1 --db-ps-mode=disable --skip_trx=on --delete_inserts=2 --index_updates=5 --report-interval=1 --rate=0 --time=600 run
[Run-1]
SQL statistics:
queries performed:
read: 0
write: 545463
other: 90357
total: 635820
transactions: 63582 (105.97 per sec.)
queries: 635820 (1059.69 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0054s
total number of events: 63582
Latency (ms):
min: 2.49
avg: 9.43
max: 148.84
95th percentile: 15.55
sum: 599830.09
Threads fairness:
events (avg/stddev): 63582.0000/0.00
execution time (avg/stddev): 599.8301/0.00
[Run-2]
SQL statistics:
queries performed:
read: 0
write: 663125
other: 96905
total: 760030
transactions: 76003 (126.67 per sec.)
queries: 760030 (1266.70 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 600.0077s
total number of events: 76003
Latency (ms):
min: 2.67
avg: 7.89
max: 121.31
95th percentile: 11.65
sum: 599790.67
Threads fairness:
events (avg/stddev): 76003.0000/0.00
execution time (avg/stddev): 599.7907/0.00
Observations:
1. Performance before table rebuild - 2526 qps.
2. Performance dropped after table rebuild: 1060 –1267 qps, despite reduced data and smaller .ibd file size (236 MB).
3. This confirms that table rebuilds with InnoDB compression can lead to notable performance degradation under write-heavy workloads.
I’ve also attached a few PMM graphs corresponding to each of the benchmark. The graphs are arranged in the same order as the workload runs.
[16 Jul 4:40]
MySQL Verification Team
Hello Alok, Thank you, let me try at my end and get back to you if anything needed. Sincerely, Umesh
[21 Jul 11:44]
MySQL Verification Team
Hello Alok, Thank you, I'm not seeing exact performance degradation(>50%) as you are seeing but in my multiple attempts consistently seeing close to the below: ## -- [umshastr@bug118411:/bv/ushastry/Work/Binaries/mysql-8.0.42]$ bin/mysql -uroot -S/tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.42 MySQL Community Server - GPL Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database sbtest; Query OK, 1 row affected (0.01 sec) -- -- [umshastr@bug118411:/bv/ushastry/Work/Utilities/sysbench]$ /bv/ushastry/Work/Utilities/sysbench/bin/sysbench /bv/ushastry/Work/Utilities/sysbench/share/sysbench/oltp_common.lua --db-driver=mysql \ --mysql-host=localhost \ --mysql-socket=/tmp/mysql.sock \ --mysql-user=root --mysql-password='' \ --mysql-db=sbtest --tables=1 --table-size=1000000 \ --threads=1 prepare sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3) Creating table 'sbtest1'... Inserting 1000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'... - Add a few secondary indexes and enable InnoDB compression mysql> use sbtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> ALTER TABLE sbtest1 -> ADD INDEX idx_k_c (k, c), -> ADD INDEX idx_c_k (c, k), -> ADD INDEX idx_pad_k (pad, k), -> ROW_FORMAT=COMPRESSED; Query OK, 0 rows affected (1 min 20.41 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select count(*) from sbtest.sbtest1; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (0.19 sec) mysql> -- ls -lh 118411/sbtest/ total 404M -rw-r----- 1 umshastr common 404M Jul 21 13:03 sbtest1.ibd - Delete approximately half (500k) rows using sysbench, the deletes should be random rows. (1500 was too much, 90-100 was enough in my delete runs) [umshastr@bug118411:/bv/ushastry/Work/Utilities/sysbench]$ [umshastr@bug118411:/bv/ushastry/Work/Utilities/sysbench]$ [umshastr@bug118411:/bv/ushastry/Work/Utilities/sysbench]$ /bv/ushastry/Work/Utilities/sysbench/bin/sysbench /bv/ushastry/Work/Utilities/sysbench/share/sysbench/oltp_delete.lua --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password='' --mysql-db=sbtest --tables=1 --table-size=1000000 --threads=4 --db-ps-mode=disable --report-interval=1 --rate=0 --time=85 run sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3) Running the test with following options: Number of threads: 4 Report intermediate results every 1 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! mysql> select count(*) from sbtest.sbtest1; +----------+ | count(*) | +----------+ | 470555 | +----------+ 1 row in set (0.06 sec) ## file size didn't change - Run oltp_write_only workload and observe the tps, qps and latency. bin/sysbench share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password='' --mysql-db=sbtest --tables=1 --table-size=1000000 --threads=1 --db-ps-mode=disable --skip_trx=on --delete_inserts=2 --index_updates=5 --report-interval=1 --rate=0 --time=600 run . [ 599s ] thds: 1 tps: 359.00 qps: 3593.96 (r/w/o: 0.00/2532.97/1060.99) lat (ms,95%): 4.03 err/s: 0.00 reconn/s: 0.00 [ 600s ] thds: 1 tps: 352.00 qps: 3521.96 (r/w/o: 0.00/2500.97/1020.99) lat (ms,95%): 4.10 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 1193318 other: 653452 total: 1846770 transactions: 184677 (307.79 per sec.) queries: 1846770 (3077.94 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 307.7943 time elapsed: 600.0013s total number of events: 184677 Latency (ms): min: 0.94 avg: 3.25 max: 15.15 95th percentile: 5.57 sum: 599787.89 Threads fairness: events (avg/stddev): 184677.0000/0.00 execution time (avg/stddev): 599.7879/0.00 - Rebuild table to reclaim space. mysql> ALTER TABLE sbtest.sbtest1 ENGINE=INNODB; Query OK, 0 rows affected (50.59 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> Every 2.0s: ls -lh 118411/sbtest/ bug118411: Mon Jul 21 13:09:34 2025 total 268M -rw-r----- 1 umshastr common 268M Jul 21 13:09 sbtest1.ibd - Re-run the same oltp_write_only workload and observe the tps, qps and latency bin/sysbench share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock --mysql-user=root --mysql-password='' --mysql-db=sbtest --tables=1 --table-size=1000000 --threads=1 --db-ps-mode=disable --skip_trx=on --delete_inserts=2 --index_updates=5 --report-interval=1 --rate=0 --time=600 run [ 599s ] thds: 1 tps: 292.99 qps: 2938.87 (r/w/o: 0.00/2263.90/674.97) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00 [ 600s ] thds: 1 tps: 282.01 qps: 2816.13 (r/w/o: 0.00/2189.10/627.03) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 974690 other: 338710 total: 1313400 transactions: 131340 (218.90 per sec.) queries: 1313400 (2188.99 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 218.8992 time elapsed: 600.0023s total number of events: 131340 Latency (ms): min: 1.29 avg: 4.57 max: 54.37 95th percentile: 8.74 sum: 599857.45 Threads fairness: events (avg/stddev): 131340.0000/0.00 execution time (avg/stddev): 599.8575/0.00 Sincerely, Umesh
Description: After running ALTER TABLE ... ENGINE=InnoDB to rebuild a large table (10 million rows) with ROW_FORMAT=COMPRESSED, we observed a ~50% drop in sysbench write-only workload throughput, despite reduced .ibd file size and identical table structure and indexes. Prior to the rebuild, the table had undergone heavy deletions (~50%), suggesting potential fragmentation. Server Version : MySQL 8.0.37 Configuration: [mysqld] user = mysql port = 3306 bind-address = 0.0.0.0 datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock pid-file = /var/run/mysqld/mysqld.pid open_files_limit = 65535 default_authentication_plugin = mysql_native_password # InnoDB settings for Galera default_storage_engine = InnoDB innodb_buffer_pool_size = 1G innodb_autoinc_lock_mode = 2 innodb_redo_log_capacity = 2G innodb_monitor_enable=all innodb_flush_log_at_trx_commit=0 innodb_flush_method=O_DIRECT innodb_use_fdatasync=ON innodb_sync_array_size=4 innodb_adaptive_hash_index=OFF performance_schema=OFF innodb_doublewrite=OFF skip-log-bin How to repeat: 1. Create 10 million row table with sysbench. sysbench oltp_common --db-driver=mysql --mysql-host=IP --mysql-port=3306 --mysql-user=sbtest --mysql-password='xxx' --mysql-db=sbtest --tables=1 --table-size=10000000 --threads=1 prepare 2. Add few composite indexes and enable compression. use sbtest; ALTER TABLE sbtest1 ADD INDEX idx_k_c (k, c), ADD INDEX idx_c_k (c, k), ADD INDEX idx_pad_k (pad, k), ROW_FORMAT=COMPRESSED; Resulting sbtest1.ibd size is 3.8 GB. mysql> \! ls -lh /var/lib/mysql/sbtest/sbtest1.ibd -rw-r-----. 1 mysql mysql 3.8G Jun 10 12:22 /var/lib/mysql/sbtest/sbtest1.ibd 3. Delete approximately half (~5 million) rows using sysbench, the deletes should be random rows. sysbench oltp_delete --db-driver=mysql --mysql-host=IP --mysql-port=3306 --mysql-user=sbtest --mysql-password='xxxx' --mysql-db=sbtest --tables=1 --table-size=10000000 --threads=8 --db-ps-mode=disable --report-interval=1 --rate=0 --time=3600 run Row count after this step are ~5 million and .ibd size is still 3.8G. 4. Run oltp_write_only workload and observe the tps, qps and latency. sysbench oltp_write_only --db-driver=mysql --mysql-host=IP --mysql-port=3306 --mysql-user=sbtest --mysql-password='xxx' --mysql-db=sbtest --tables=1 --table-size=10000000 --threads=4 --db-ps-mode=disable --skip_trx=on --delete_inserts=2 --index_updates=5 --report-interval=1 --rate=0 --time=900 run SQL statistics: queries performed: read: 0 write: 468675 other: 218915 total: 687590 transactions: 68759 (76.40 per sec.) queries: 687590 (763.97 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 76.3967 time elapsed: 900.0253s total number of events: 68759 Latency (ms): min: 17.82 avg: 52.35 max: 1402.39 95th percentile: 89.16 sum: 3599572.35 Threads fairness: events (avg/stddev): 17189.7500/207.89 execution time (avg/stddev): 899.8931/0.01 5. Rebuild table to reclaim space. ALTER TABLE sbtest.sbtest1 ENGINE=INNODB; The .ibd file shrinks to ~2GB. 6. Re-run the same oltp_write_only workload and observe the tps, qps and latency. sysbench oltp_write_only --db-driver=mysql --mysql-host=IP --mysql-port=3306 --mysql-user=sbtest --mysql-password='xxx' --mysql-db=sbtest --tables=1 --table-size=10000000 --threads=4 --db-ps-mode=disable --skip_trx=on --delete_inserts=2 --index_updates=5 --report-interval=1 --rate=0 --time=900 run SQL statistics: queries performed: read: 0 write: 227643 other: 104457 total: 332100 transactions: 33210 (36.90 per sec.) queries: 332100 (368.97 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 36.8974 time elapsed: 900.0623s total number of events: 33210 Latency (ms): min: 29.39 avg: 108.40 max: 1245.15 95th percentile: 204.11 sum: 3599916.41 Threads fairness: events (avg/stddev): 8302.5000/42.63 execution time (avg/stddev): 899.9791/0.02 It is understood that after a table rebuild, the B-Tree structure of indexes becomes more compact. This can lead to an increased rate of index page splits under write-intensive workloads. Each page split creates more room within the index tree, allowing future writes to complete with less overhead. As more page splits occur during normal workload, the index structure gradually becomes more balanced and efficient. Over time, the frequency of page splits decreases, and write performance stabilizes. This is fine for small or medium sized tables, but for large tables (hundreds of GBs to several TBs), it can take days or even weeks of regular write activity before the index tree reaches a steady state. During this period, write performance may remain significantly degraded compared to pre-rebuild levels. Suggested fix: Rebuilding the table (without modifying schema or indexes) should ideally improve performance (by defragmenting pages and reclaiming space), or at least preserve baseline throughput.