###################################### Environment

cat /etc/*release
Oracle Linux Server release 7.9
NAME="Oracle Linux Server"
VERSION="7.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL="https://linux.oracle.com/"
BUG_REPORT_URL="https://bugzilla.oracle.com/"

ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9
Red Hat Enterprise Linux Server release 7.9 (Maipo)
Oracle Linux Server release 7.9


uname -an
Linux support-cluster03 5.4.17-2102.206.1.el7uek.x86_64 #2 SMP Wed Oct 6 16:40:40 PDT 2021 x86_64 x86_64 x86_64 GNU/Linux

# System Summary Report ######################
      Uptime | 713 days, 23:31,  3 users,  load average: 0.17, 5.37, 6.31
    Platform | Linux
     Release | Red Hat Enterprise Linux Server release 7.9 (Maipo)
      Kernel | 5.4.17-2102.206.1.el7uek.x86_64
Architecture | CPU = 64-bit, OS = 64-bit
   Threading | NPTL 2.17
    Compiler | GNU CC version 4.8.5 20150623 (Red Hat 4.8.5-44.0.3).
     SELinux | Disabled
 Virtualized | VMWare
# Processor ##################################################
  Processors | physical = 1, cores = 8, virtual = 16, hyperthreading = yes
      Speeds | 16x2445.406
      Models | 16xAMD EPYC 7J13 64-Core Processor
      Caches | 16x512 KB
# Memory #####################################################
       Total | 117.4G
        Free | 16.3G
        Used | physical = 1.2G, swap allocated = 8.0G, swap used = 5.0G, virtual = 6.3G
      Shared | 4.0G
     Buffers | 99.8G
      Caches | 111.0G
       Dirty | 180 kB
     UsedRSS | 1.3G
  Swappiness | 60
 DirtyPolicy | 20, 10
 DirtyStatus | 0, 0
 
 

lscpu | egrep 'Model name|Socket|Thread|NUMA|CPU\(s\)'
CPU(s):                16
On-line CPU(s) list:   0-15
Thread(s) per core:    2
Socket(s):             1
NUMA node(s):          1
Model name:            AMD EPYC 7J13 64-Core Processor
NUMA node0 CPU(s):     0-15

echo "Threads/core: $(nproc --all)"
Threads/core: 16

  rpm -qa|grep -i jemalloc
jemalloc-5.2.1-2.el7.x86_64


## Insert Benchmark - pls use the link provided by MarkC - https://github.com/mdcallag/mytools/blob/master/bench/ibench/iibench.py
## Non privilegeded environment and hence had choice but to go with the virtual environment which was created on top of an existing Python installation

python3.8 -m venv bug113505
[umshastr@support-cluster03:~/work/binaries/utils/tools/bench/ibench]$ source bug113505/bin/activate
(bug113505) [umshastr@support-cluster03:~/work/binaries/utils/tools/bench/ibench]$
(bug113505) [umshastr@support-cluster03:~/work/binaries/utils/tools/bench/ibench]$
 pip3 install --proxy=http://www-proxy.us.oracle.com:80 mysql-connector-python
 pip3 install --proxy=http://www-proxy.us.oracle.com:80 mysqlclient (pls ensure mysql_config is in path)




# Please refer https://smalldatum.blogspot.com/2023/12/create-innodb-indexes-2x-faster-with.html for all the related conf files


###############################################################################################################
##################################### 8.0.35 release build(binary tarball pulled from internal repo)
###############################################################################################################

- build details

 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=/export/home/tmp/ushastry/mysql-8.0.35/data/m/my/data/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- create schema (didn't try if this is mandatory, thought just like sysbench these are pre-requisites)

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>

# loads the table with a PK index but no secondary indexes

(bug113505) [umshastr@support-cluster03:~/work/binaries/utils/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     87706   87706   0       0       0       0       31250   0       0       87800   0       0       0       0       0
1.0     2.0     98585   93146   0       0       0       0       5940    0       0       186500  0       0       0       0       0
1.0     3.0     96991   94428   0       0       0       0       6217    0       0       283600  0       0       0       0       0

.
.
.

1.0     851.8   100589  93059   0       0       0       0       18510   0       0       79269800        0       0       0       0       0
1.0     852.8   100310  93067   0       0       0       0       18409   0       0       79370200        0       0       0       0       0
1.0     853.8   101289  93077   0       0       0       0       17628   0       0       79471600        0       0       0       0       0
1.0     854.8   99690   93084   0       0       0       0       18195   0       0       79571400        0       0       0       0       0
1.0     855.8   99930   93092   0       0       0       0       16739   0       0       79671400        0       0       0       0       0
1.0     856.8   99191   93100   0       0       0       0       16832   0       0       79770700        0       0       0       0       0
1.0     857.8   92698   93099   0       0       0       0       19159   0       0       79863500        0       0       0       0       0
1.0     858.8   96694   93103   0       0       0       0       17652   0       0       79960300        0       0       0       0       0
1.0     859.8   39656   93041   0       0       0       0       17256   0       0       80000000        0       0       0       0       0
Insert rt:     256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
Insert rt:         0    629304    169096       316      1087       188         9         0         0         0    0.375783
Totals: 859.8 secs, 93041.0 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry
Done

# creates the secondary indexes

(bug113505) [umshastr@support-cluster03:~/work/binaries/utils/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       1539    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.001540
Created secondary indexes in 1365.4 seconds
Totals: 1366.4 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=/export/home/tmp/ushastry/mysql-8.0.35/data/m/my/data/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- create schema (didn't try if this is mandatory, thought just like sysbench these are pre-requisites)

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>

# loads the table with a PK index but no secondary indexes

(bug113505) [umshastr@support-cluster03:~/work/binaries/utils/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     86807   86807   0       0       0       0       30510   0       0       86900   0       0       0       0       0
1.0     2.0     101481  94144   0       0       0       0       6066    0       0       188500  0       0       0       0       0
1.0     3.0     101583  96622   0       0       0       0       8961    0       0       290100  0       0       0       0       0


.
.
.
1.0     830.8   98891   95663   0       0       0       0       17056   0       0       79478300        0       0       0       0       0
1.0     831.8   93196   95660   0       0       0       0       17457   0       0       79571600        0       0       0       0       0
1.0     832.8   100087  95665   0       0       0       0       17066   0       0       79671800        0       0       0       0       0
1.0     833.8   97392   95667   0       0       0       0       17824   0       0       79769300        0       0       0       0       0
1.0     834.8   94695   95666   0       0       0       0       49191   0       0       79864100        0       0       0       0       0
1.0     835.8   100287  95672   0       0       0       0       16341   0       0       79964500        0       0       0       0       0
1.0     836.8   35460   95600   0       0       0       0       16275   0       0       80000000        0       0       0       0       0
Insert rt:     256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
Insert rt:         0    681842    116745       232      1166         8         7         0         0         0    0.293197
Totals: 836.8 secs, 95599.5 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry
Done



# creates the secondary indexes

 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       1752    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.001752
Created secondary indexes in 1068.7 seconds
Totals: 1069.7 secs, 0.1 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

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  --log-error=/export/home/tmp/ushastry/mysql-8.0.35/data/m/my/data/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- create schema (didn't try if this is mandatory, thought just like sysbench these are pre-requisites)

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>

# loads the table with a PK index but no secondary indexes

 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     87007   87007   0       0       0       0       32625   0       0       87100   0       0       0       0       0
1.0     2.0     86199   86603   0       0       0       0       6180    0       0       173400  0       0       0       0       0
1.0     3.0     95091   89432   0       0       0       0       6431    0       0       268600  0       0       0       0       0
1.0     4.0     92298   90149   0       0       0       0       32221   0       0       361000  0       0       0       0       0
1.0     5.0     95293   91178   0       0       0       0       18067   0       0       456400  0       0       0       0       0
1.0     6.0     99889   92629   0       0       0       0       17486   0       0       556400  0       0       0       0       0
1.0     7.0     105782  94507   0       0       0       0       17998   0       0       662200  0       0       0       0       0
1.0     8.0     103682  95654   0       0       0       0       18063   0       0       766000  0       0       0       0       0
.
.


1.0     830.8   90800   95895   0       0       0       0       18159   0       0       79671600        0       0       0       0       0
1.0     831.8   91999   95890   0       0       0       0       17262   0       0       79763700        0       0       0       0       0
1.0     832.8   79012   95870   0       0       0       0       18920   0       0       79842800        0       0       0       0       0
1.0     833.8   87502   95860   0       0       0       0       19566   0       0       79930400        0       0       0       0       0
1.0     834.8   69521   95828   0       0       0       0       18440   0       0       80000000        0       0       0       0       0
Insert rt:     256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
Insert rt:         0    687590    111002       118      1272         6        12         0         0         0    0.327517
Totals: 834.8 secs, 95828.2 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry
Done

# creates the secondary indexes

(bug113505) [umshastr@support-cluster03:~/work/binaries/utils/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       1599    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.001600
Created secondary indexes in 1111.4 seconds
Totals: 1112.4 secs, 0.1 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

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=/export/home/tmp/ushastry/mysql-8.0.35/data/m/my/data/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- create schema (didn't try if this is mandatory, thought just like sysbench these are pre-requisites)

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>

# loads the table with a PK index but no secondary indexes

 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     86107   86107   0       0       0       0       32823   0       0       86200   0       0       0       0       0
1.0     2.0     96186   91147   0       0       0       0       6319    0       0       182500  0       0       0       0       0
.
.

1.0     832.8   100187  95612   0       0       0       0       16814   0       0       79627200        0       0       0       0       0
1.0     833.8   99990   95617   0       0       0       0       16425   0       0       79727300        0       0       0       0       0
1.0     834.8   93196   95614   0       0       0       0       18124   0       0       79820600        0       0       0       0       0
1.0     835.8   96194   95615   0       0       0       0       16397   0       0       79916900        0       0       0       0       0
1.0     836.8   83008   95600   0       0       0       0       16355   0       0       80000000        0       0       0       0       0
Insert rt:     256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
Insert rt:         0    686137    112458       149      1241         3        12         0         0         0    0.332152
Totals: 836.8 secs, 95599.5 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry
Done



# creates the secondary indexes

 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       1470    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.001471
Created secondary indexes in 1275.3 seconds
Totals: 1276.3 secs, 0.1 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)
####################################################################################################################################################
--
 gcc --version
gcc (GCC) 11.2.1 20220127 (Red Hat 11.2.1-9)
Copyright (C) 2021 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.19.0-rc1

CMake suite maintained and supported by Kitware (kitware.com/cmake).


scl enable devtoolset-11 bash
MYSQL_VERSION="mysql-8.0.35_fixed"
TARGET=/export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf /export/home/tmp/ushastry/src/$MYSQL_VERSION
rm -rf bld/
mkdir bld && cd bld
rm -rf CMakeCache.txt
cmake .. \
  -DCMAKE_BUILD_TYPE=Release \
  -DWITH_BOOST=$PWD/../boost \
  -DCMAKE_INSTALL_PREFIX=$TARGET \
  -DCMAKE_C_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/gcc -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-11/root/usr/bin/g++            
  
make -j16
make install
cd $TARGET

[I]

-- ddl1 - innodb_ddl_threads=1, innodb_parallel_read_threads=1, performance_schema=1
-- 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=/export/home/tmp/ushastry/mysql-8.0.35/data/m/my/data/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- create schema (didn't try if this is mandatory, thought just like sysbench these are pre-requisites)

 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>


# loads the table with a PK index but no secondary indexes
 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     95826   95826   0       0       0       0       30596   0       0       95900   0       0       0       0       0
1.0     2.0     113092  104460  0       0       0       0       6024    0       0       209100  0       0       0       0       0
1.0     3.0     112276  107066  0       0       0       0       5835    0       0       321500  0       0       0       0       0
1.0     4.0     110200  107849  0       0       0       0       15782   0       0       431800  0       0       0       0       0
1.0     5.0     87785   103839  0       0       0       0       16160   0       0       519600  0       0       0       0       0
1.0     6.0     79412   99766   0       0       0       0       16761   0       0       599100  0       0       0       0       0

.
.
.

1.0     760.7   110477  104651  0       0       0       0       16138   0       0       79610800        0       0       0       0       0
1.0     761.7   106379  104653  0       0       0       0       16115   0       0       79717300        0       0       0       0       0
1.0     762.7   109177  104659  0       0       0       0       15941   0       0       79826600        0       0       0       0       0
1.0     763.7   103087  104657  0       0       0       0       54384   0       0       79929700        0       0       0       0       0
1.0     764.7   70219   104612  0       0       0       0       17408   0       0       80000000        0       0       0       0       0
Insert rt:     256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
Insert rt:         0    747379     51218       332      1054        14         3         0         0         0    0.271883
Totals: 764.7 secs, 104611.9 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry
Done


# creates the secondary indexes

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       1323    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.001324
Created secondary indexes in 1191.4 seconds
Totals: 1192.4 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

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=/export/home/tmp/ushastry/mysql-8.0.35/data/m/my/data/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- create schema (didn't try if this is mandatory, thought just like sysbench these are pre-requisites)

 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>


# loads the table with a PK index but no secondary indexes

(bug113505) [umshastr@support-cluster03:~/work/binaries/utils/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     97295   97295   0       0       0       0       30434   0       0       97400   0       0       0       0       0
1.0     2.0     113665  105480  0       0       0       0       9575    0       0       211200  0       0       0       0       0
1.0     3.0     110676  107212  0       0       0       0       6040    0       0       322000  0       0       0       0       0
1.0     4.0     108067  107426  0       0       0       0       16437   0       0       430100  0       0       0       0       0
1.0     5.0     115471  109035  0       0       0       0       16320   0       0       545700  0       0       0       0       0
1.0     6.0     110178  109225  0       0       0       0       47974   0       0       656000  0       0       0       0       0
.
.

1.0     765.8   89952   103777  0       0       0       0       24033   0       0       79467500        0       0       0       0       0
1.0     766.8   101586  103774  0       0       0       0       17517   0       0       79569200        0       0       0       0       0
1.0     767.8   111876  103785  0       0       0       0       16049   0       0       79681200        0       0       0       0       0
1.0     768.8   110272  103793  0       0       0       0       18242   0       0       79791600        0       0       0       0       0
1.0     769.8   112379  103805  0       0       0       0       36806   0       0       79904000        0       0       0       0       0
1.0     770.8   95893   103794  0       0       0       0       17538   0       0       80000000        0       0       0       0       0
Insert rt:     256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
Insert rt:         0    747449     51149       266      1117        15         4         0         0         0    0.285291
Totals: 770.8 secs, 103794.3 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry
Done




# creates the secondary indexes

 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       1354    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.001355
Created secondary indexes in 927.4 seconds
Totals: 928.4 secs, 0.1 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

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  --log-error=/export/home/tmp/ushastry/mysql-8.0.35/data/m/my/data/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- create schema (didn't try if this is mandatory, thought just like sysbench these are pre-requisites)

 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>


# loads the table with a PK index but no secondary indexes

 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     87007   87007   0       0       0       0       32625   0       0       87100   0       0       0       0       0
1.0     2.0     86199   86603   0       0       0       0       6180    0       0       173400  0       0       0       0       0
1.0     3.0     95091   89432   0       0       0       0       6431    0       0       268600  0       0       0       0       0
1.0     4.0     92298   90149   0       0       0       0       32221   0       0       361000  0       0       0       0       0
1.0     5.0     95293   91178   0       0       0       0       18067   0       0       456400  0       0       0       0       0
1.0     6.0     99889   92629   0       0       0       0       17486   0       0       556400  0       0       0       0       0
1.0     7.0     105782  94507   0       0       0       0       17998   0       0       662200  0       0       0       0       0
1.0     8.0     103682  95654   0       0       0       0       18063   0       0       766000  0       0       0       0       0
.
.


1.0     761.7   89101   103652  0       0       0       0       39456   0       0       78954600        0       0       0       0       0
1.0     762.7   93946   103639  0       0       0       0       17666   0       0       79048600        0       0       0       0       0
1.0     763.7   86833   103617  0       0       0       0       24008   0       0       79135500        0       0       0       0       0
1.0     764.7   87702   103596  0       0       0       0       16361   0       0       79223300        0       0       0       0       0
1.0     765.7   105682  103599  0       0       0       0       16663   0       0       79329100        0       0       0       0       0
1.0     766.7   104184  103600  0       0       0       0       17497   0       0       79433400        0       0       0       0       0
1.0     767.7   98889   103593  0       0       0       0       16965   0       0       79532400        0       0       0       0       0
1.0     768.7   110278  103602  0       0       0       0       17416   0       0       79642800        0       0       0       0       0
1.0     769.7   110677  103611  0       0       0       0       17081   0       0       79753600        0       0       0       0       0
1.0     770.7   105964  103614  0       0       0       0       17272   0       0       79859600        0       0       0       0       0
1.0     771.7   103686  103614  0       0       0       0       15978   0       0       79963400        0       0       0       0       0
1.0     772.7   36559   103528  0       0       0       0       16007   0       0       80000000        0       0       0       0       0
Insert rt:     256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
Insert rt:         0    736816     61778       289      1099        16         2         0         0         0    0.275613
Totals: 772.7 secs, 103527.6 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry
Done


# creates the secondary indexes

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       1466    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.001467
Created secondary indexes in 994.5 seconds
Totals: 995.5 secs, 0.1 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

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=/export/home/tmp/ushastry/mysql-8.0.35/data/m/my/data/log.err --mysqlx=0 --log-error-verbosity=3  --secure-file-priv="" --local-infile=1  2>&1 &

-- create schema (didn't try if this is mandatory, thought just like sysbench these are pre-requisites)

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>

# loads the table with a PK index but no secondary indexes

 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
.

1.0     758.7   111153  104663  0       0       0       0       16606   0       0       79411100        0       0       0       0       0
1.0     759.7   109776  104670  0       0       0       0       17449   0       0       79521000        0       0       0       0       0
1.0     760.7   91198   104652  0       0       0       0       17909   0       0       79612300        0       0       0       0       0
1.0     761.7   112281  104662  0       0       0       0       16294   0       0       79724600        0       0       0       0       0
1.0     762.7   106281  104664  0       0       0       0       17079   0       0       79831000        0       0       0       0       0
1.0     763.7   107880  104668  0       0       0       0       34824   0       0       79939000        0       0       0       0       0
1.0     764.7   60930   104611  0       0       0       0       16255   0       0       80000000        0       0       0       0       0
Insert rt:     256us       1ms       4ms      16ms      64ms     256ms        1s        4s       16s        gt         max
Insert rt:         0    749703     48886       257      1138        12         4         0         0         0    0.277558
Totals: 764.7 secs, 104610.9 rows/sec, 80000000 rows, 0 0 0 insert-delete-query retry
Done




# creates the secondary indexes

(bug113505) [umshastr@support-cluster03:~/work/binaries/utils/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       1332    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.001332
Created secondary indexes in 1200.6 seconds
Totals: 1201.6 secs, 0.1 rows/sec, 100 rows, 0 0 0 insert-delete-query retry
Done





############# During one of attempt, captured perf data for "As Is" build, excerpt from the report:

   98.24%     0.00%  connection       libpthread-2.17.so   [.] start_thread
            |
            ---start_thread
               pfs_spawn_thread
               handle_connection
               do_command
               dispatch_command
               dispatch_sql_command
               mysql_execute_command
               Sql_cmd_alter_table::execute
               mysql_alter_table
               mysql_inplace_alter_table
               ha_innobase::inplace_alter_table
                                     |          |                     |          |
                                     |          |                     |          |--17.10%--cmp_rec_rec_simple_field
                                     |          |                     |          |
                                     |          |                     |          |--3.10%--rec_get_nth_field_offs
                                     |          |                     |          |
                                     |          |                     |           --1.53%--rec_offs_nth_sql_null
                                     |          |                     |
                                     |          |                      --0.57%--cmp_rec_rec_simple_field
                                     |          |
                                     |           --11.30%--ddl::File_cursor::next
                                     |                     |
                                     |                      --10.61%--ddl::File_reader::next
                                     |                                |
                                     |                                 --8.23%--rec_init_offsets_comp_ordinary
                                     |
                                     |--24.48%--ddl::File_cursor::fetch
                                     |          |
                                     |          |--17.25%--ddl::File_cursor::fetch
                                     |          |
                                     |           --5.99%--__round
                                     |
                                     |--14.25%--ddl::Merge_cursor::fetch
                                     |          |
                                     |          |--10.73%--ddl::Merge_cursor::pop
                                     |          |          |
                                     |          |           --9.19%--std::__adjust_heap<__gnu_cxx::__normal_iterator<ddl::File_cursor**, std::vector<ddl::File_cursor*, ut::allocator<ddl::File_cursor*, ut::detail::allocator_base_pfs<dd
l::File_cursor*> > > >, long, ddl::File_cursor*, __gnu_cxx::__ops::_Iter_comp_iter<ddl::Merge_cursor::Compare> >
                                     |          |                     |
                                     |          |                      --1.18%--std::__push_heap<__gnu_cxx::__normal_iterator<ddl::File_cursor**, std::vector<ddl::File_cursor*, ut::allocator<ddl::File_cursor*, ut::detail::allocator_ba
se_pfs<ddl::File_cursor*> > > >, long, ddl::File_cursor*, __gnu_cxx::__ops::_Iter_comp_val<ddl::Merge_cursor::Compare> >
                                     |          |
                                     |          |--1.19%--ddl::Context::check_state_of_online_build_log
                                     |          |
                                     |           --1.15%--ddl::Builder::check_state_of_online_build_log
                                     |
                                     |--2.92%--ddl::Merge_file_sort::Output_file::write
                                     |
                                     |--0.95%--ddl::File_cursor::next
                                     |
                                      --0.70%--__memmove_ssse3


### To sumup

Case: When performance_schema=1 then the fixed build is ~2X faster than the as-is build


Conf	      As Is build   MarkC fixed build
====================================================
ddl1		1366.4 secs   1192.4 secs
ddl4		1069.7 secs   928.4 secs
ddl8		1112.4 secs   995.5 secs
ddl16		1276.3 secs   1201.6 secs

====================================================
ddl1		1390.4 secs   1210.4 secs
ddl4		1250.5 secs   1128.1 secs
ddl8		1162.4 secs   1050.5 secs
ddl16		1176.3 secs   1130.2 secs

====================================================
ddl1		1200.1 secs   1082.4 secs
ddl4		1069.5 secs   978.4 secs
ddl8		1212.2 secs   1095.5 secs
ddl16		1276.6 secs   1201.6 secs