###################################### Environment cat /etc/*release Oracle Linux Server release 8.8 NAME="Oracle Linux Server" VERSION="8.8" ID="ol" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="8.8" PLATFORM_ID="platform:el8" PRETTY_NAME="Oracle Linux Server 8.8" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:8:8:server" HOME_URL="https://linux.oracle.com/" BUG_REPORT_URL="https://github.com/oracle/oracle-linux" ORACLE_BUGZILLA_PRODUCT="Oracle Linux 8" ORACLE_BUGZILLA_PRODUCT_VERSION=8.8 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=8.8 Red Hat Enterprise Linux release 8.8 (Ootpa) Oracle Linux Server release 8.8 uname -an Linux ellex07 5.15.0-103.114.4.el8uek.aarch64 #2 SMP Mon Jun 26 10:27:39 PDT 2023 aarch64 aarch64 aarch64 GNU/Linux lscpu | egrep 'Model name|Socket|Thread|NUMA|CPU\(s\)' CPU(s): 224 On-line CPU(s) list: 0-223 Thread(s) per core: 4 Socket(s): 2 NUMA node(s): 2 Model name: ThunderX2 99xx NUMA node0 CPU(s): 0-111 NUMA node1 CPU(s): 112-223 # System Summary Report ###################### Date | 2024-01-11 09:07:24 UTC (local TZ: CET +0100) Hostname | ellex07 Uptime | 150 days, 3:00, 4 users, load average: 1.06, 1.11, 1.74 Platform | Linux Release | Red Hat Enterprise Linux release 8.8 (Ootpa) Kernel | 5.15.0-103.114.4.el8uek.aarch64 Architecture | CPU = 32-bit, OS = 64-bit Threading | NPTL 2.28 SELinux | Disabled Virtualized | No virtualization detected # Processor ################################################## Processors | physical = 224, cores = 0, virtual = 224, hyperthreading = no Speeds | Models | Caches | # Memory ##################################################### Total | 250.0G Free | 128.4G Used | physical = 5.3G, swap allocated = 0.0, swap used = 0.0, virtual = 5.3G Shared | 56.4G Buffers | 116.3G Caches | 186.7G Dirty | 0 kB UsedRSS | 2.5G Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 - more details gcc --version gcc (GCC) 12.2.1 20221121 (Red Hat 12.2.1-7) Copyright (C) 2022 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. cmake --version cmake version 3.22.1 CMake suite maintained and supported by Kitware (kitware.com/cmake). rpm -qa|grep -i jemalloc jemalloc-5.2.1-2.el8.aarch64 rpm -qa|grep -i numa numactl-devel-2.0.12-13.el8.aarch64 numactl-libs-2.0.12-13.el8.aarch64 ## df -h -T /dev/shm Filesystem Type Size Used Avail Use% Mounted on tmpfs tmpfs 126G 16K 126G 1% /dev/shm #################################################################################################################################################### ##################################### 8.0.35 binary tarball - aarch64 ##################################### mysql-8.0.35-linux-glibc2.28-aarch64.tar.xz (pulled from internal repo) #################################################################################################################################################### -- build cat docs/INFO_SRC commit: 7dea9692ebadea572a847f436f62e66a18cd2d74 date: 2023-10-12 13:34:21 +0200 build-date: 2023-10-12 11:46:35 +0000 short: 7dea9692eba branch: mysql-8.0.35-release MySQL source 8.0.35 [I] -- ddl1 - innodb_ddl_threads=1, innodb_parallel_read_threads=1, performance_schema=1 -- my.cnf.cz10addl1_c24r64 - https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/dec23.create_index.innodb/my.cnf.cz10addl1_c24r64 rm -rf data/ mkdir -p data/m/my/data mkdir -p data/m/my/txlogs mkdir -p data/m/my/binlogs/bl bin/mysqld --defaults-file=./113505cnf/my.cnf.cz10addl1_c24r64 --initialize-insecure --basedir=$PWD --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113505cnf/my.cnf.cz10addl1_c24r64 --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --log-error=/dev/shm/mysql-8.0.35/data/m/my/data/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 & -- create schema 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.35 MySQL Community Server - GPL Copyright (c) 2000, 2023, 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 ib; Query OK, 1 row affected (0.00 sec) # export LD_LIBRARY_PATH=/dev/shm/mysql-8.0.35/lib # loads the table with a PK index but no secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --max_rows=80000000 --table_name=pi1 --setup --num_secondary_indexes=0 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703371433 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 34859 34859 0 0 0 0 9428 0 0 34900 0 0 0 0 0 1.0 2.0 37451 36155 0 0 0 0 3615 0 0 72400 0 0 0 0 0 1.0 3.0 37557 36623 0 0 0 0 3601 0 0 110000 0 0 0 0 0 1.0 4.0 37557 36856 0 0 0 0 3626 0 0 147600 0 0 0 0 0 1.0 5.0 37457 36976 0 0 0 0 3624 0 0 185100 0 0 0 0 0 . . 1.0 2191.5 36855 36427 0 0 0 0 2882 0 0 79829100 0 0 0 0 0 1.0 2192.5 36756 36427 0 0 0 0 5066 0 0 79865900 0 0 0 0 0 1.0 2193.5 36655 36427 0 0 0 0 3698 0 0 79902600 0 0 0 0 0 1.0 2194.5 36555 36427 0 0 0 0 5094 0 0 79939200 0 0 0 0 0 1.0 2195.5 36955 36428 0 0 0 0 5102 0 0 79976200 0 0 0 0 0 1.0 2196.5 23772 36422 0 0 0 0 3573 0 0 80000000 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 798445 1555 0 0 0 0 0 0 0.009428 Totals: 2196.5 secs, 36421.9 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry Done # creates the secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --secondary_at_end --max_rows=100 --table_name=pi1 --num_secondary_indexes=3 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703372349 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 100 100 0 0 0 0 3349 0 0 100 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 1 0 0 0 0 0 0 0 0.003349 Created secondary indexes in 1219.5 seconds Totals: 1220.5 secs, 0.1 rows/sec, 100 rows, 0 0 0 insert-delete-query retry Done [II] -- ddl4 - innodb_ddl_threads=4, innodb_parallel_read_threads=4, performance_schema=1 -- my.cnf.cz10addl4_c24r64 - https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/dec23.create_index.innodb/my.cnf.cz10addl4_c24r64 rm -rf data/ mkdir -p data/m/my/data mkdir -p data/m/my/txlogs mkdir -p data/m/my/binlogs/bl bin/mysqld --defaults-file=./113505cnf/my.cnf.cz10addl4_c24r64 --initialize-insecure --basedir=$PWD --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113505cnf/my.cnf.cz10addl4_c24r64 --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --log-error=/dev/shm/mysql-8.0.35/data/m/my/data/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 & -- create schema 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.35 MySQL Community Server - GPL Copyright (c) 2000, 2023, 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 ib; Query OK, 1 row affected (0.00 sec) # export LD_LIBRARY_PATH=/dev/shm/mysql-8.0.35/lib # loads the table with a PK index but no secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --max_rows=80000000 --table_name=pi1 --setup --num_secondary_indexes=0 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703371433 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 34761 34761 0 0 0 0 9292 0 0 34800 0 0 0 0 0 1.0 2.0 37452 36107 0 0 0 0 3616 0 0 72300 0 0 0 0 0 1.0 3.0 37558 36591 0 0 0 0 3616 0 0 109900 0 0 0 0 0 1.0 4.0 37458 36808 0 0 0 0 3618 0 0 147400 0 0 0 0 0 1.0 5.0 37491 36944 0 0 0 0 3605 0 0 184900 0 0 0 0 0 1.0 6.0 37059 36963 0 0 0 0 3696 0 0 222000 0 0 0 0 0 1.0 7.0 36659 36920 0 0 0 0 3675 0 0 258700 0 0 0 0 0 . . 1.0 2200.4 36459 36246 0 0 0 0 5100 0 0 79756500 0 0 0 0 0 1.0 2201.4 36460 36246 0 0 0 0 5095 0 0 79793000 0 0 0 0 0 1.0 2202.4 36360 36246 0 0 0 0 2950 0 0 79829400 0 0 0 0 0 1.0 2203.4 36459 36246 0 0 0 0 5117 0 0 79865900 0 0 0 0 0 1.0 2204.4 36360 36246 0 0 0 0 2974 0 0 79902300 0 0 0 0 0 1.0 2205.4 36260 36246 0 0 0 0 5118 0 0 79938600 0 0 0 0 0 1.0 2206.4 36560 36246 0 0 0 0 5101 0 0 79975200 0 0 0 0 0 1.0 2207.4 24772 36241 0 0 0 0 2952 0 0 80000000 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 798614 1386 0 0 0 0 0 0 0.009293 Totals: 2207.4 secs, 36240.9 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry Done # creates the secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --secondary_at_end --max_rows=100 --table_name=pi1 --num_secondary_indexes=3 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703372349 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 100 100 0 0 0 0 3230 0 0 100 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 1 0 0 0 0 0 0 0 0.003231 Created secondary indexes in 485.9 seconds Totals: 486.9 secs, 0.2 rows/sec, 100 rows, 0 0 0 insert-delete-query retry Done [III] -- ddl8 - innodb_ddl_threads=8, innodb_parallel_read_threads=8, performance_schema=1 -- my.cnf.cz10addl8_c24r64 - https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/dec23.create_index.innodb/my.cnf.cz10addl8_c24r64 rm -rf data/ mkdir -p data/m/my/data mkdir -p data/m/my/txlogs mkdir -p data/m/my/binlogs/bl bin/mysqld --defaults-file=./113505cnf/my.cnf.cz10addl8_c24r64 --initialize-insecure --basedir=$PWD --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113505cnf/my.cnf.cz10addl8_c24r64 --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --log-error=/dev/shm/mysql-8.0.35/data/m/my/data/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 & -- create schema [umshastr@ellex07]/dev/shm/mysql-8.0.35: 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.35 MySQL Community Server - GPL Copyright (c) 2000, 2023, 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 ib; Query OK, 1 row affected (0.00 sec) mysql> # export LD_LIBRARY_PATH=/dev/shm/mysql-8.0.35/lib # loads the table with a PK index but no secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --max_rows=80000000 --table_name=pi1 --setup --num_secondary_indexes=0 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703371433 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 34761 34761 0 0 0 0 9299 0 0 34800 0 0 0 0 0 1.0 2.0 37453 36107 0 0 0 0 3627 0 0 72300 0 0 0 0 0 1.0 3.0 37459 36558 0 0 0 0 3613 0 0 109800 0 0 0 0 0 1.0 4.0 37459 36783 0 0 0 0 3641 0 0 147300 0 0 0 0 0 1.0 5.0 37359 36898 0 0 0 0 3619 0 0 184700 0 0 0 0 0 1.0 6.0 37059 36925 0 0 0 0 3682 0 0 221800 0 0 0 0 0 1.0 7.0 36660 36887 0 0 0 0 3739 0 0 258500 0 0 0 0 0 1.0 8.0 36460 36834 0 0 0 0 3705 0 0 295000 0 0 0 0 0 1.0 9.0 36760 36825 0 0 0 0 3703 0 0 331800 0 0 0 0 0 1.0 10.0 36760 36819 0 0 0 0 3666 0 0 368600 0 0 0 0 0 1.0 11.0 36560 36795 0 0 0 0 5091 0 0 405200 0 0 0 0 0 . . # creates the secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --secondary_at_end --max_rows=100 --table_name=pi1 --num_secondary_indexes=3 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703372349 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 100 100 0 0 0 0 4302 0 0 100 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 0 1 0 0 0 0 0 0 0.004302 Created secondary indexes in 379.5 seconds Totals: 380.5 secs, 0.3 rows/sec, 100 rows, 0 0 0 insert-delete-query retry Done [IV] -- ddl16 - innodb_ddl_threads=16, innodb_parallel_read_threads=16, performance_schema=1 -- my.cnf.cz10addl16_c24r64 - https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/dec23.create_index.innodb/my.cnf.cz10addl16_c24r64 rm -rf data/ mkdir -p data/m/my/data mkdir -p data/m/my/txlogs mkdir -p data/m/my/binlogs/bl bin/mysqld --defaults-file=./113505cnf/my.cnf.cz10addl16_c24r64 --initialize-insecure --basedir=$PWD --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113505cnf/my.cnf.cz10addl16_c24r64 --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --log-error=/dev/shm/mysql-8.0.35/data/m/my/data/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 & -- create schema # export LD_LIBRARY_PATH=/dev/shm/mysql-8.0.35/lib # loads the table with a PK index but no secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --max_rows=80000000 --table_name=pi1 --setup --num_secondary_indexes=0 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703371433 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 34661 34661 0 0 0 0 10072 0 0 34700 0 0 0 0 0 1.0 2.0 37152 35907 0 0 0 0 3739 0 0 71900 0 0 0 0 0 1.0 3.0 37558 36457 0 0 0 0 3727 0 0 109500 0 0 0 0 0 . . 1.0 2186.5 36757 36511 0 0 0 0 3394 0 0 79831600 0 0 0 0 0 1.0 2187.5 36757 36511 0 0 0 0 5066 0 0 79868400 0 0 0 0 0 1.0 2188.5 36857 36511 0 0 0 0 2884 0 0 79905300 0 0 0 0 0 1.0 2189.5 36659 36511 0 0 0 0 5052 0 0 79942000 0 0 0 0 0 1.0 2190.5 37157 36512 0 0 0 0 5062 0 0 79979200 0 0 0 0 0 1.0 2191.5 20776 36504 0 0 0 0 2888 0 0 80000000 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 798578 1421 1 0 0 0 0 0 0.018104 Totals: 2191.5 secs, 36504.4 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry Done # creates the secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --secondary_at_end --max_rows=100 --table_name=pi1 --num_secondary_indexes=3 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703372349 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 100 100 0 0 0 0 3278 0 0 100 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 1 0 0 0 0 0 0 0 0.003279 Created secondary indexes in 313.9 seconds Totals: 314.9 secs, 0.3 rows/sec, 100 rows, 0 0 0 insert-delete-query retry Done #################################################################################################################################################### ##################################### 8.0.35 customer build (removing this line of code - https://github.com/mysql/mysql-server/blob/87307d4ddd88405117e3f1e51323836d57ab1f57/storage/innobase/ddl/ddl0builder.cc#L264) #################################################################################################################################################### BF=" -g1 " CF=" $BF " CXXF=" $BF " scl enable gcc-toolset-12 bash MYSQL_VERSION="mysql-8.0.35" TARGET=/dev/shm/$MYSQL_VERSION rm -rf /dev/shm/$MYSQL_VERSION rm -rf bld/ mkdir bld && cd bld rm -rf CMakeCache.txt cmake .. \ -DCMAKE_BUILD_TYPE=Release \ -DWITH_SSL=system \ -DWITH_ZLIB=bundled \ -DMYSQL_MAINTAINER_MODE=0 \ -DENABLED_LOCAL_INFILE=1 \ -DCMAKE_INSTALL_PREFIX=$TARGET \ -DWITH_BOOST=$PWD/../boost \ -DCMAKE_CXX_FLAGS="$CXXF" -DCMAKE_C_FLAGS="$CF" \ -DWITH_NUMA=ON -DWITH_ROUTER=OFF -DWITH_MYSQLX=OFF -DWITH_UNIT_TESTS=OFF make -j128 make install cd $TARGET -- build details [I] -- ddl1 - innodb_ddl_threads=1, innodb_parallel_read_threads=1, performance_schema=1 -- my.cnf.cz10addl1_c24r64 - https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/dec23.create_index.innodb/my.cnf.cz10addl1_c24r64 rm -rf data/ mkdir -p data/m/my/data mkdir -p data/m/my/txlogs mkdir -p data/m/my/binlogs/bl bin/mysqld --defaults-file=./113505cnf/my.cnf.cz10addl1_c24r64 --initialize-insecure --basedir=$PWD --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113505cnf/my.cnf.cz10addl1_c24r64 --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --log-error=/dev/shm/mysql-8.0.35/data/m/my/data/log.err --log-error-verbosity=3 --secure-file-priv="" 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.35 Source distribution Copyright (c) 2000, 2023, 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 ib; Query OK, 1 row affected (0.07 sec) mysql> # export LD_LIBRARY_PATH=/dev/shm/mysql-8.0.35/lib # loads the table with a PK index but no secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --max_rows=80000000 --table_name=pi1 --setup --num_secondary_indexes=0 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703371433 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 38457 38457 0 0 0 0 9047 0 0 38500 0 0 0 0 0 1.0 2.0 41248 39852 0 0 0 0 3366 0 0 79800 0 0 0 0 0 1.0 3.0 41254 40320 0 0 0 0 3377 0 0 121100 0 0 0 0 0 1.0 4.0 41355 40578 0 0 0 0 3401 0 0 162500 0 0 0 0 0 1.0 5.0 41355 40734 0 0 0 0 3378 0 0 203900 0 0 0 0 0 1.0 6.0 40156 40637 0 0 0 0 3454 0 0 244100 0 0 0 0 0 1.0 7.0 40056 40554 0 0 0 0 3463 0 0 284200 0 0 0 0 0 1.0 8.0 40356 40529 0 0 0 0 3439 0 0 324600 0 0 0 0 0 1.0 9.0 40356 40510 0 0 0 0 3444 0 0 365000 0 0 0 0 0 1.0 10.0 40256 40485 0 0 0 0 4804 0 0 405300 0 0 0 0 0 1.0 11.0 40156 40455 0 0 0 0 4823 0 0 445500 0 0 0 0 0 1.0 12.0 40155 40430 0 0 0 0 3003 0 0 485700 0 0 0 0 0 1.0 13.0 40255 40416 0 0 0 0 4874 0 0 526000 0 0 0 0 0 1.0 14.0 40055 40391 0 0 0 0 4873 0 0 566100 0 0 0 0 0 1.0 15.0 40155 40375 0 0 0 0 2968 0 0 606300 0 0 0 0 0 1.0 16.0 40055 40355 0 0 0 0 4883 0 0 646400 0 0 0 0 0 . . 1.0 1984.2 40155 40213 0 0 0 0 4857 0 0 79791800 0 0 0 0 0 1.0 1985.2 40055 40213 0 0 0 0 2729 0 0 79831900 0 0 0 0 0 1.0 1986.2 40055 40213 0 0 0 0 4863 0 0 79872000 0 0 0 0 0 1.0 1987.2 40055 40213 0 0 0 0 4861 0 0 79912100 0 0 0 0 0 1.0 1988.2 40155 40213 0 0 0 0 2665 0 0 79952300 0 0 0 0 0 1.0 1989.2 40355 40213 0 0 0 0 4866 0 0 79992700 0 0 0 0 0 1.0 1990.2 7292 40196 0 0 0 0 2477 0 0 80000000 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 798582 1418 0 0 0 0 0 0 0.009047 Totals: 1990.2 secs, 40196.1 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry Done # creates the secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --secondary_at_end --max_rows=100 --table_name=pi1 --num_secondary_indexes=3 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703372349 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 100 100 0 0 0 0 3135 0 0 100 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 1 0 0 0 0 0 0 0 0.003135 Created secondary indexes in 1036.1 seconds Totals: 1037.1 secs, 0.1 rows/sec, 100 rows, 0 0 0 insert-delete-query retry Done [II] -- ddl4 - innodb_ddl_threads=4, innodb_parallel_read_threads=4, performance_schema=1 -- my.cnf.cz10addl4_c24r64 - https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/dec23.create_index.innodb/my.cnf.cz10addl4_c24r64 rm -rf data/ mkdir -p data/m/my/data mkdir -p data/m/my/txlogs mkdir -p data/m/my/binlogs/bl bin/mysqld --defaults-file=./113505cnf/my.cnf.cz10addl4_c24r64 --initialize-insecure --basedir=$PWD --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113505cnf/my.cnf.cz10addl4_c24r64 --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --log-error=/dev/shm/mysql-8.0.35/data/m/my/data/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 & -- create schema 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.35 Source distribution Copyright (c) 2000, 2023, 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 ib; Query OK, 1 row affected (0.01 sec) mysql> # export LD_LIBRARY_PATH=/dev/shm/mysql-8.0.35/lib # loads the table with a PK index but no secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --max_rows=80000000 --table_name=pi1 --setup --num_secondary_indexes=0 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703371433 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 39255 39255 0 0 0 0 9065 0 0 39300 0 0 0 0 0 1.0 2.0 42245 40751 0 0 0 0 3327 0 0 81600 0 0 0 0 0 1.0 3.0 42452 41318 0 0 0 0 3294 0 0 124100 0 0 0 0 0 1.0 4.0 42153 41526 0 0 0 0 3335 0 0 166300 0 0 0 0 0 1.0 5.0 42153 41652 0 0 0 0 3347 0 0 208500 0 0 0 0 0 1.0 6.0 41254 41585 0 0 0 0 3379 0 0 249800 0 0 0 0 0 1.0 7.0 41054 41509 0 0 0 0 3391 0 0 290900 0 0 0 0 0 . . 1.0 1968.2 40455 40537 0 0 0 0 2669 0 0 79785100 0 0 0 0 0 1.0 1969.2 40155 40537 0 0 0 0 4866 0 0 79825300 0 0 0 0 0 1.0 1970.2 40355 40537 0 0 0 0 4861 0 0 79865700 0 0 0 0 0 1.0 1971.2 40455 40537 0 0 0 0 2665 0 0 79906200 0 0 0 0 0 1.0 1972.2 40454 40537 0 0 0 0 4822 0 0 79946700 0 0 0 0 0 1.0 1973.2 41054 40537 0 0 0 0 4829 0 0 79987800 0 0 0 0 0 1.0 1974.2 12186 40522 0 0 0 0 2627 0 0 80000000 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 798532 1468 0 0 0 0 0 0 0.009065 Totals: 1974.2 secs, 40522.4 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry Done # creates the secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --secondary_at_end --max_rows=100 --table_name=pi1 --num_secondary_indexes=3 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703372349 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 100 100 0 0 0 0 3007 0 0 100 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 1 0 0 0 0 0 0 0 0.003007 Created secondary indexes in 327.7 seconds Totals: 328.7 secs, 0.3 rows/sec, 100 rows, 0 0 0 insert-delete-query retry Done [III] -- ddl8 - innodb_ddl_threads=8, innodb_parallel_read_threads=8, performance_schema=1 -- my.cnf.cz10addl8_c24r64 - https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/dec23.create_index.innodb/my.cnf.cz10addl8_c24r64 rm -rf data/ mkdir -p data/m/my/data mkdir -p data/m/my/txlogs mkdir -p data/m/my/binlogs/bl bin/mysqld --defaults-file=./113505cnf/my.cnf.cz10addl8_c24r64 --initialize-insecure --basedir=$PWD --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113505cnf/my.cnf.cz10addl8_c24r64 --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --log-error=/dev/shm/mysql-8.0.35/data/m/my/data/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 & -- create schema 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.35 Source distribution Copyright (c) 2000, 2023, 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 ib; Query OK, 1 row affected (0.00 sec) mysql> # export LD_LIBRARY_PATH=/dev/shm/mysql-8.0.35/lib # loads the table with a PK index but no secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --max_rows=80000000 --table_name=pi1 --setup --num_secondary_indexes=0 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703371433 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 38456 38456 0 0 0 0 9113 0 0 38500 0 0 0 0 0 1.0 2.0 41148 39802 0 0 0 0 3386 0 0 79700 0 0 0 0 0 1.0 3.0 41451 40352 0 0 0 0 3384 0 0 121200 0 0 0 0 0 1.0 4.0 41351 40602 0 0 0 0 3363 0 0 162600 0 0 0 0 0 1.0 5.0 41252 40732 0 0 0 0 3361 0 0 203900 0 0 0 0 0 1.0 6.0 40353 40669 0 0 0 0 3458 0 0 244300 0 0 0 0 0 1.0 7.0 40456 40638 0 0 0 0 3448 0 0 284800 0 0 0 0 0 1.0 8.0 40353 40603 0 0 0 0 3429 0 0 325200 0 0 0 0 0 1.0 9.0 40256 40564 0 0 0 0 3467 0 0 365500 0 0 0 0 0 1.0 10.0 40255 40533 0 0 0 0 4825 0 0 405800 0 0 0 0 0 1.0 11.0 40156 40499 0 0 0 0 4879 0 0 446000 0 0 0 0 0 1.0 12.0 40355 40487 0 0 0 0 2992 0 0 486400 0 0 0 0 0 1.0 13.0 40356 40477 0 0 0 0 4857 0 0 526800 0 0 0 0 0 1.0 14.0 40056 40447 0 0 0 0 4892 0 0 566900 0 0 0 0 0 1.0 15.0 40255 40434 0 0 0 0 2949 0 0 607200 0 0 0 0 0 1.0 16.0 40255 40423 0 0 0 0 4922 0 0 647500 0 0 0 0 0 1.0 17.0 40156 40407 0 0 0 0 4895 0 0 687700 0 0 0 0 0 1.0 18.0 40356 40404 0 0 0 0 2951 0 0 728100 0 0 0 0 0 . . # creates the secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --secondary_at_end --max_rows=100 --table_name=pi1 --num_secondary_indexes=3 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703372349 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 100 100 0 0 0 0 3027 0 0 100 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 1 0 0 0 0 0 0 0 0.003028 Created secondary indexes in 120.3 seconds Totals: 121.3 secs, 0.8 rows/sec, 100 rows, 0 0 0 insert-delete-query retry Done [IV] -- ddl16 - innodb_ddl_threads=16, innodb_parallel_read_threads=16, performance_schema=1 -- my.cnf.cz10addl16_c24r64 - https://github.com/mdcallag/mytools/blob/master/bench/conf/arc/dec23.create_index.innodb/my.cnf.cz10addl16_c24r64 rm -rf data/ mkdir -p data/m/my/data mkdir -p data/m/my/txlogs mkdir -p data/m/my/binlogs/bl bin/mysqld --defaults-file=./113505cnf/my.cnf.cz10addl16_c24r64 --initialize-insecure --basedir=$PWD --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113505cnf/my.cnf.cz10addl16_c24r64 --mysqld-version='' --basedir=$PWD --socket=/tmp/mysql.sock --log-error=/dev/shm/mysql-8.0.35/data/m/my/data/log.err --log-error-verbosity=3 --secure-file-priv="" 2>&1 & -- create schema [umshastr@ellex07]/dev/shm/mysql-8.0.35: 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.35 Source distribution Copyright (c) 2000, 2023, 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 ib; Query OK, 1 row affected (0.00 sec) mysql> # export LD_LIBRARY_PATH=/dev/shm/mysql-8.0.35/lib # loads the table with a PK index but no secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --max_rows=80000000 --table_name=pi1 --setup --num_secondary_indexes=0 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703371433 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 37758 37758 0 0 0 0 9924 0 0 37800 0 0 0 0 0 1.0 2.0 40746 39252 0 0 0 0 3518 0 0 78600 0 0 0 0 0 1.0 3.0 40853 39785 0 0 0 0 3512 0 0 119500 0 0 0 0 0 1.0 4.0 40655 40003 0 0 0 0 3525 0 0 160200 0 0 0 0 0 1.0 5.0 40753 40153 0 0 0 0 3532 0 0 201000 0 0 0 0 0 1.0 6.0 39656 40070 0 0 0 0 3628 0 0 240700 0 0 0 0 0 1.0 7.0 39555 39996 0 0 0 0 3598 0 0 280300 0 0 0 0 0 1.0 8.0 39656 39954 0 0 0 0 3612 0 0 320000 0 0 0 0 0 1.0 9.0 39755 39932 0 0 0 0 3644 0 0 359800 0 0 0 0 0 1.0 10.0 39654 39904 0 0 0 0 5259 0 0 399500 0 0 0 0 0 1.0 11.0 39458 39863 0 0 0 0 5341 0 0 439000 0 0 0 0 0 1.0 12.0 39757 39855 0 0 0 0 2749 0 0 478800 0 0 0 0 0 1.0 13.0 39556 39832 0 0 0 0 5327 0 0 518400 0 0 0 0 0 1.0 14.0 39453 39805 0 0 0 0 5399 0 0 557900 0 0 0 0 0 1.0 15.0 39654 39795 0 0 0 0 2780 0 0 597600 0 0 0 0 0 . . 1.0 1986.3 40154 40097 0 0 0 0 4815 0 0 79644100 0 0 0 0 0 1.0 1987.3 39954 40097 0 0 0 0 4847 0 0 79684100 0 0 0 0 0 1.0 1988.3 40354 40098 0 0 0 0 2506 0 0 79724500 0 0 0 0 0 1.0 1989.3 40455 40098 0 0 0 0 4835 0 0 79765000 0 0 0 0 0 1.0 1990.3 40355 40098 0 0 0 0 4827 0 0 79805400 0 0 0 0 0 1.0 1991.3 40353 40098 0 0 0 0 2665 0 0 79845800 0 0 0 0 0 1.0 1992.3 40354 40098 0 0 0 0 4842 0 0 79886200 0 0 0 0 0 1.0 1993.3 40256 40098 0 0 0 0 4855 0 0 79926500 0 0 0 0 0 1.0 1994.3 40653 40098 0 0 0 0 2499 0 0 79967200 0 0 0 0 0 1.0 1995.3 32762 40095 0 0 0 0 4850 0 0 80000000 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 798591 1408 1 0 0 0 0 0 0.018016 Totals: 1995.3 secs, 40094.8 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry Done # creates the secondary indexes (bug113505) [umshastr@ellex07]~/setups/tools/bench/ibench: python3 iibench.py --dbms=mysql --db_name=ib --secs_per_report=1 --db_host=127.0.0.1 --db_user=root --db_password= --engine=innodb --engine_options= --unique_checks=1 --bulk_load=0 --secondary_at_end --max_rows=100 --table_name=pi1 --num_secondary_indexes=3 --data_length_min=10 --data_length_max=20 --rows_per_commit=100 --inserts_per_second=0 --query_threads=0 --seed=1703372349 --dbopt=none --my_id=1 i_sec t_sec i_ips t_ips i_dps t_dps i_qps t_qps max_i max_d max_q t_ins t_del t_query retry_i retry_d retry_q 1.0 1.0 100 100 0 0 0 0 3192 0 0 100 0 0 0 0 0 Insert rt: 256us 1ms 4ms 16ms 64ms 256ms 1s 4s 16s gt max Insert rt: 0 0 1 0 0 0 0 0 0 0 0.003193 Created secondary indexes in 150.2 seconds Totals: 151.2 secs, 0.6 rows/sec, 100 rows, 0 0 0 insert-delete-query retry Done #################################################################################################################################################### ################################## To sum up #################################################################################################################################################### Case: When performance_schema=1 then the fixed build is ~2X faster than the as-is build ===================================================== Conf As-is build Source build with MarkC fix ===================================================== ddl1 1220.5 secs 1037.1 secs ddl4 486.9 secs 328.7 secs ddl8 380.5 secs 121.3 secs ddl16 314.9 secs 151.2 secs =====================================================