## Env # System Summary Report ###################################### Date | 2025-07-03 09:24:58 UTC (local TZ: CEST +0200) Hostname | bug118403 Uptime | 3 days, 3:41, 4 users, load average: 3.20, 2.97, 2.05 Platform | Linux Release | Red Hat Enterprise Linux release 9.6 (Plow) Kernel | 5.15.0-309.180.4.el9uek.x86_64 Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.34 Compiler | GNU CC version 11.5.0 20240719 (Red Hat 11.5.0-5.0.1). SELinux | Disabled Virtualized | No virtualization detected # Processor ################################################## Processors | physical = 1, cores = 8, virtual = 16, hyperthreading = yes Speeds | 16x2445.404 Models | 16xAMD EPYC 7J13 64-Core Processor Caches | 16x512 KB # Memory ##################################################### Total | 251.2G Free | 210.0G Used | physical = 7.1G, swap allocated = 4.0G, swap used = 0.0, virtual = 7.1G Shared | 174.8M Buffers | 36.5G Caches | 244.1G Dirty | 1044 kB UsedRSS | 4.9G Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 Locator Size Speed Form Factor Type Type Detail ========= ======== ================= ============= ============= =========== -- build and conf used [umshastr@bug118403:/bv/ushastry/Work/Binaries/mysql-8.0.42]$ cat docs/INFO_SRC commit: bc4163b5ffc52b396409822b8a43cde2c9175d64 date: 2025-03-31 09:39:51 +0200 build-date: 2025-03-31 08:21:03 +0000 short: bc4163b5ffc5 branch: mysql-8.0.42-release MySQL source 8.0.42 [umshastr@bug118403:/bv/ushastry/Work/Binaries/mysql-8.0.42]$ cat my.cnf [mysqld] # 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 [umshastr@bug118403:/bv/ushastry/Work/Binaries/mysql-8.0.42]$ BugNumber=118411 rm -rf $BugNumber/ bin/mysqld --defaults-file=my.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/$BugNumber --log-error-verbosity=3 bin/mysqld --defaults-file=my.cnf --basedir=$PWD --datadir=$PWD/$BugNumber --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/$BugNumber/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & 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) -- How to repeat: 1. Create 10 million row table with sysbench. umshastr@bug118403:/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=10000000 \ --threads=1 prepare sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3) Creating table 'sbtest1'... Inserting 10000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'... 2. Add few composite indexes and enable 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 (17 min 47.13 sec) Records: 0 Duplicates: 0 Warnings: 0 Resulting sbtest1.ibd size is 3.8 GB. Every 2.0s: ls -lh 118411/sbtest/ bug118403: Thu Jul 3 12:17:45 2025 total 3.8G -rw-r----- 1 umshastr common 3.8G Jul 3 11:33 sbtest1.ibd ## 3. Delete approximately half (~5 million) rows using sysbench, the deletes should be random rows. -- ran couple of times bin/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=10000000 --threads=8 --db-ps-mode=disable --report-interval=1 --rate=0 --time=600 run Row count after this step are >5 million and .ibd size is still 3.8G. mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 5058184 | +----------+ 1 row in set (2.13 sec) mysql> -- Every 2.0s: ls -lh 118411/sbtest/ bug118403: Thu Jul 3 12:54:19 2025 total 3.8G -rw-r----- 1 umshastr common 3.8G Jul 3 12:54 sbtest1.ibd 4. Run oltp_write_only workload and observe the tps, qps and latency. [umshastr@bug118403:/bv/ushastry/Work/Utilities/sysbench]$ 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=10000000 --threads=4 --db-ps-mode=disable --skip_trx=on --delete_inserts=2 --index_updates=5 --report-interval=1 --rate=0 --time=900 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! [ 1s ] thds: 4 tps: 62.93 qps: 651.31 (r/w/o: 0.00/398.58/252.73) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00 [ 2s ] thds: 4 tps: 103.05 qps: 1028.54 (r/w/o: 0.00/630.33/398.21) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00 [ 3s ] thds: 4 tps: 104.00 qps: 1043.05 (r/w/o: 0.00/620.03/423.02) lat (ms,95%): 68.05 err/s: 0.00 reconn/s: 0.00 [ 4s ] thds: 4 tps: 102.99 qps: 1015.88 (r/w/o: 0.00/609.93/405.95) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00 [ 5s ] thds: 4 tps: 104.01 qps: 1046.13 (r/w/o: 0.00/610.07/436.05) lat (ms,95%): 63.32 err/s: 0.00 reconn/s: 0.00 [ 6s ] thds: 4 tps: 104.00 qps: 1034.99 (r/w/o: 0.00/626.99/408.00) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00 [ 7s ] thds: 4 tps: 103.00 qps: 1030.01 (r/w/o: 0.00/625.01/405.01) lat (ms,95%): 64.47 err/s: 0.00 reconn/s: 0.00 [ 8s ] thds: 4 tps: 99.00 qps: 1000.99 (r/w/o: 0.00/616.99/384.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00 [ 9s ] thds: 4 tps: 106.00 qps: 1051.00 (r/w/o: 0.00/623.00/428.00) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00 [ 10s ] thds: 4 tps: 105.00 qps: 1058.96 (r/w/o: 0.00/640.97/417.98) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00 [ 11s ] thds: 4 tps: 105.00 qps: 1037.05 (r/w/o: 0.00/631.03/406.02) lat (ms,95%): 59.99 err/s: 0.00 reconn/s: 0.00 . . [ 891s ] thds: 4 tps: 133.00 qps: 1322.00 (r/w/o: 0.00/801.00/521.00) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00 [ 892s ] thds: 4 tps: 118.00 qps: 1184.00 (r/w/o: 0.00/758.00/426.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00 [ 893s ] thds: 4 tps: 120.00 qps: 1201.01 (r/w/o: 0.00/736.00/465.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00 [ 894s ] thds: 4 tps: 132.00 qps: 1324.95 (r/w/o: 0.00/802.97/521.98) lat (ms,95%): 42.61 err/s: 0.00 reconn/s: 0.00 [ 895s ] thds: 4 tps: 126.00 qps: 1260.04 (r/w/o: 0.00/771.02/489.01) lat (ms,95%): 49.21 err/s: 0.00 reconn/s: 0.00 [ 896s ] thds: 4 tps: 128.00 qps: 1265.00 (r/w/o: 0.00/781.00/484.00) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00 [ 897s ] thds: 4 tps: 126.00 qps: 1266.00 (r/w/o: 0.00/777.00/489.00) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00 [ 898s ] thds: 4 tps: 126.00 qps: 1257.02 (r/w/o: 0.00/771.01/486.01) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00 [ 899s ] thds: 4 tps: 131.00 qps: 1305.99 (r/w/o: 0.00/807.99/497.99) lat (ms,95%): 45.79 err/s: 0.00 reconn/s: 0.00 [ 900s ] thds: 4 tps: 126.00 qps: 1271.01 (r/w/o: 0.00/788.00/483.00) lat (ms,95%): 46.63 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 700435 other: 448615 total: 1149050 transactions: 114905 (127.67 per sec.) queries: 1149050 (1276.69 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 127.6687 time elapsed: 900.0247s total number of events: 114905 Latency (ms): min: 8.09 avg: 31.33 max: 217.33 95th percentile: 50.11 sum: 3599863.75 Threads fairness: events (avg/stddev): 28726.2500/37.56 execution time (avg/stddev): 899.9659/0.01 -- Every 2.0s: ls -lh 118411/sbtest/ bug118403: Thu Jul 3 13:15:29 2025 total 3.8G -rw-r----- 1 umshastr common 3.8G Jul 3 13:15 sbtest1.ibd mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 5198442 | +----------+ 1 row in set (4.52 sec) 5. Rebuild table to reclaim space. mysql> ALTER TABLE sbtest.sbtest1 ENGINE=INNODB; Query OK, 0 rows affected (9 min 53.95 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 5198442 | +----------+ 1 row in set (6.13 sec) mysql> ## The .ibd file shrinks to ~2GB. Every 2.0s: ls -lh 118411/sbtest/ bug118403: Thu Jul 3 13:27:24 2025 total 2.0G -rw-r----- 1 umshastr common 2.0G Jul 3 13:26 sbtest1.ibd 6. Re-run the same oltp_write_only workload and observe the tps, qps and latency. [umshastr@bug118403:/bv/ushastry/Work/Utilities/sysbench]$ 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=10000000 --threads=4 --db-ps-mode=disable --skip_trx=on --delete_inserts=2 --index_updates=5 --report-interval=1 --rate=0 --time=900 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! [ 1s ] thds: 4 tps: 87.92 qps: 889.24 (r/w/o: 0.00/558.52/330.72) lat (ms,95%): 92.42 err/s: 0.00 reconn/s: 0.00 [ 2s ] thds: 4 tps: 121.04 qps: 1224.45 (r/w/o: 0.00/736.27/488.18) lat (ms,95%): 44.98 err/s: 0.00 reconn/s: 0.00 [ 3s ] thds: 4 tps: 123.00 qps: 1219.01 (r/w/o: 0.00/738.01/481.01) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00 [ 4s ] thds: 4 tps: 122.00 qps: 1216.95 (r/w/o: 0.00/744.97/471.98) lat (ms,95%): 51.02 err/s: 0.00 reconn/s: 0.00 [ 5s ] thds: 4 tps: 117.01 qps: 1175.06 (r/w/o: 0.00/750.04/425.02) lat (ms,95%): 55.82 err/s: 0.00 reconn/s: 0.00 [ 6s ] thds: 4 tps: 124.99 qps: 1258.94 (r/w/o: 0.00/745.97/512.98) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00 [ 7s ] thds: 4 tps: 122.00 qps: 1215.02 (r/w/o: 0.00/767.01/448.01) lat (ms,95%): 53.85 err/s: 0.00 reconn/s: 0.00 . . [ 898s ] thds: 4 tps: 146.00 qps: 1450.00 (r/w/o: 0.00/914.00/536.00) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00 [ 899s ] thds: 4 tps: 144.00 qps: 1433.99 (r/w/o: 0.00/918.99/515.00) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00 [ 900s ] thds: 4 tps: 145.00 qps: 1463.01 (r/w/o: 0.00/901.01/562.00) lat (ms,95%): 44.17 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 0 write: 754826 other: 462394 total: 1217220 transactions: 121722 (135.24 per sec.) queries: 1217220 (1352.44 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) Throughput: events/s (eps): 135.2444 time elapsed: 900.0153s total number of events: 121722 Latency (ms): min: 2.65 avg: 29.57 max: 356.82 95th percentile: 55.82 sum: 3599842.70 Threads fairness: events (avg/stddev): 30430.5000/46.29 execution time (avg/stddev): 899.9607/0.01