-- Apply Herman Lee's patch on top of 8.0.24 scl enable devtoolset-9 bash MYSQL_VERSION="8024_103523" TARGET=/home/umshastr/work/src/$MYSQL_VERSION rm -rf /home/umshastr/work/src/$MYSQL_VERSION rm -rf bld/ mkdir bld && cd bld rm -rf CMakeCache.txt /home/umshastr/work/binaries/utils/cmake-3.18/bin/cmake .. \ -DBUILD_CONFIG=mysql_release \ -DCMAKE_INSTALL_PREFIX=$TARGET \ -DWITH_BOOST=../boost \ -DCOMPILATION_COMMENT=`date +"%m-%d-%Y"` \ -DWITH_MYSQLX=OFF \ -DWITH_ROUTER=OFF -DCMAKE_C_COMPILER=/opt/rh/devtoolset-9/root/usr/bin/gcc -DCMAKE_CXX_COMPILER=/opt/rh/devtoolset-9/root/usr/bin/g++ make -j 16 make install - cat docs/INFO_SRC commit: dbf8bcd1064fd7a5f1dc956d0209c45818e2a04a date: 2021-03-22 08:43:28 +0100 build-date: 2021-03-22 08:45:49 +0000 short: dbf8bcd1064 branch: mysql-8.0.24-release MySQL source 8.0.24 - env cat /etc/*release Oracle Linux Server release 7.8 NAME="Oracle Linux Server" VERSION="7.8" ID="ol" ID_LIKE="fedora" VARIANT="Server" VARIANT_ID="server" VERSION_ID="7.8" PRETTY_NAME="Oracle Linux Server 7.8" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:oracle:linux:7:8: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.8 ORACLE_SUPPORT_PRODUCT="Oracle Linux" ORACLE_SUPPORT_PRODUCT_VERSION=7.8 Red Hat Enterprise Linux Server release 7.8 (Maipo) Oracle Linux Server release 7.8 -- free -g total used free shared buff/cache available Mem: 117 45 0 0 71 70 Swap: 7 0 7 rm -rf 103523/ bin/mysqld --initialize-insecure --basedir=$PWD --datadir=$PWD/103523 --log-error-verbosity=3 bin/mysqld --no-defaults --basedir=$PWD --datadir=$PWD/103523 --core-file --socket=/tmp/mysql_ushastry.sock --port=3333 --log-error=$PWD/103523/log.err --log-error-verbosity=3 --secure-file-priv=/tmp/ 2>&1 & # Step 2 - Create the following four tables in the test database of a mysql instance: create database if not exists test; use test; create table t1 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, `col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, `col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, `col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY (`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY (`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY (`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY (`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY (`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY (`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY (`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY (`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY (`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY (`col29`), FULLTEXT KEY (`col30`)) engine=innodb; create table t2 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, `col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, `col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, `col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY (`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY (`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY (`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY (`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY (`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY (`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY (`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY (`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY (`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY (`col29`), FULLTEXT KEY (`col30`)) engine=innodb; create table t3 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, `col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, `col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, `col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY (`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY (`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY (`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY (`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY (`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY (`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY (`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY (`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY (`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY (`col29`), FULLTEXT KEY (`col30`)) engine=innodb; create table t4 ( `id` int unsigned NOT NULL AUTO_INCREMENT, `col01` text, `col02` text, `col03` text, `col04` text, `col05` text, `col06` text, `col07` text, `col08` text, `col09` text, `col10` text, `col11` text, `col12` text, `col13` text, `col14` text, `col15` text, `col16` text, `col17` text, `col18` text, `col19` text, `col20` text, `col21` text, `col22` text, `col23` text, `col24` text, `col25` text, `col26` text, `col27` text, `col28` text, `col29` text, `col30` text, PRIMARY KEY (`id`), FULLTEXT KEY (`col01`), FULLTEXT KEY (`col02`), FULLTEXT KEY (`col03`), FULLTEXT KEY (`col04`), FULLTEXT KEY (`col05`), FULLTEXT KEY (`col06`), FULLTEXT KEY (`col07`), FULLTEXT KEY (`col08`), FULLTEXT KEY (`col09`), FULLTEXT KEY (`col10`), FULLTEXT KEY (`col11`), FULLTEXT KEY (`col12`), FULLTEXT KEY (`col13`), FULLTEXT KEY (`col14`), FULLTEXT KEY (`col15`), FULLTEXT KEY (`col16`), FULLTEXT KEY (`col17`), FULLTEXT KEY (`col18`), FULLTEXT KEY (`col19`), FULLTEXT KEY (`col20`), FULLTEXT KEY (`col21`), FULLTEXT KEY (`col22`), FULLTEXT KEY (`col23`), FULLTEXT KEY (`col24`), FULLTEXT KEY (`col25`), FULLTEXT KEY (`col26`), FULLTEXT KEY (`col27`), FULLTEXT KEY (`col28`), FULLTEXT KEY (`col29`), FULLTEXT KEY (`col30`)) engine=innodb; - top - 08:12:50 up 44 days, 20:10, 6 users, load average: 1.00, 1.49, 3.17 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.6 us, 0.3 sy, 0.0 ni, 98.3 id, 0.7 wa, 0.0 hi, 0.0 si, 0.1 st KiB Mem : 12287464+total, 39396608 free, 23078776 used, 60399264 buff/cache KiB Swap: 8388604 total, 8195580 free, 193024 used. 97746176 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 32673 umshastr 20 0 2029368 448232 30104 S 1.3 0.4 0:03.19 mysqld mysql> show global status like 'innodb_fts%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | Innodb_fts_optimize_queue_count | 0 | +---------------------------------+-------+ 1 row in set (0.00 sec) # Step 3 - Run mysqlslap to generate traffic to the tables: bin/mysqlslap -u root -S /tmp/mysql_ushastry.sock --create-schema='test' --query='insert into t1 ( `col01`, `col02`, `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`) VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()))' --concurrency=2 --iterations=1000000 bin/mysqlslap -u root -S /tmp/mysql_ushastry.sock --create-schema='test' --query='insert into t2 ( `col01`, `col02`, `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`) VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()))' --concurrency=2 --iterations=1000000 bin/mysqlslap -u root -S /tmp/mysql_ushastry.sock --create-schema='test' --query='insert into t3 ( `col01`, `col02`, `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`) VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()))' --concurrency=2 --iterations=1000000 bin/mysqlslap -u root -S /tmp/mysql_ushastry.sock --create-schema='test' --query='insert into t4 ( `col01`, `col02`, `col03`, `col04`, `col05`, `col06`, `col07`, `col08`, `col09`, `col10`, `col11`, `col12`, `col13`, `col14`, `col15`, `col16`, `col17`, `col18`, `col19`, `col20`, `col21`, `col22`, `col23`, `col24`, `col25`, `col26`, `col27`, `col28`, `col29`, `col30`) VALUES ( MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()), MD5(RAND()))' --concurrency=2 --iterations=1000000 # Step 4 - observe the size of the optimize queue keeps increasing show global status like 'innodb_fts%'; mysql> show global status like 'innodb_fts%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | Innodb_fts_optimize_queue_count | 0 | +---------------------------------+-------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | Innodb_fts_optimize_queue_count | 35389 | +---------------------------------+-------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+--------+ | Variable_name | Value | +---------------------------------+--------+ | Innodb_fts_optimize_queue_count | 103975 | +---------------------------------+--------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+--------+ | Variable_name | Value | +---------------------------------+--------+ | Innodb_fts_optimize_queue_count | 121015 | +---------------------------------+--------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+--------+ | Variable_name | Value | +---------------------------------+--------+ | Innodb_fts_optimize_queue_count | 372379 | +---------------------------------+--------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+--------+ | Variable_name | Value | +---------------------------------+--------+ | Innodb_fts_optimize_queue_count | 687890 | +---------------------------------+--------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+--------+ | Variable_name | Value | +---------------------------------+--------+ | Innodb_fts_optimize_queue_count | 967483 | +---------------------------------+--------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+---------+ | Variable_name | Value | +---------------------------------+---------+ | Innodb_fts_optimize_queue_count | 1173052 | +---------------------------------+---------+ 1 row in set (0.00 sec) . mysql> show global status like 'innodb_fts%'; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | Innodb_fts_optimize_queue_count | 19693743 | +---------------------------------+----------+ 1 row in set (0.00 sec) mysql> . mysql> show global status like 'innodb_fts%'; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | Innodb_fts_optimize_queue_count | 19693743 | +---------------------------------+----------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | Innodb_fts_optimize_queue_count | 23064111 | +---------------------------------+----------+ 1 row in set (0.00 sec) mysql> mysql> show global status like 'innodb_fts%'; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | Innodb_fts_optimize_queue_count | 27417945 | +---------------------------------+----------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | Innodb_fts_optimize_queue_count | 27417945 | +---------------------------------+----------+ 1 row in set (0.00 sec) . mysql> show global status like 'innodb_fts%'; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | Innodb_fts_optimize_queue_count | 33267569 | +---------------------------------+----------+ 1 row in set (0.00 sec) . mysql> show global status like 'innodb_fts%'; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | Innodb_fts_optimize_queue_count | 36457394 | +---------------------------------+----------+ 1 row in set (0.00 sec) mysql> show global status like 'innodb_fts%'; +---------------------------------+----------+ | Variable_name | Value | +---------------------------------+----------+ | Innodb_fts_optimize_queue_count | 48494141 | +---------------------------------+----------+ 1 row in set (0.00 sec) -- top - 10:04:17 up 44 days, 22:02, 7 users, load average: 2.62, 2.76, 2.72 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.6 us, 0.6 sy, 0.0 ni, 95.3 id, 3.3 wa, 0.0 hi, 0.1 si, 0.1 st KiB Mem : 12287464+total, 20141808 free, 36832324 used, 65900516 buff/cache KiB Swap: 8388604 total, 8195580 free, 193024 used. 83992592 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 32673 umshastr 20 0 15.8g 13.5g 30936 S 9.7 11.5 31:30.68 mysqld . top - 10:27:24 up 44 days, 22:25, 7 users, load average: 2.78, 3.02, 2.91 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 1.7 us, 0.9 sy, 0.0 ni, 92.9 id, 4.4 wa, 0.0 hi, 0.1 si, 0.1 st KiB Mem : 12287464+total, 16728720 free, 39258420 used, 66887508 buff/cache KiB Swap: 8388604 total, 8195580 free, 193024 used. 81566288 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 32673 umshastr 20 0 17.9g 15.8g 30936 S 26.9 13.5 37:17.89 mysqld . top - 10:57:08 up 44 days, 22:55, 7 users, load average: 2.16, 2.81, 2.81 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.8 us, 0.7 sy, 0.0 ni, 94.1 id, 4.2 wa, 0.0 hi, 0.1 si, 0.1 st KiB Mem : 12287464+total, 14096972 free, 40187488 used, 68590192 buff/cache KiB Swap: 8388604 total, 8195580 free, 193024 used. 80637424 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 32673 umshastr 20 0 19.2g 16.7g 30936 S 10.0 14.2 45:10.76 mysqld . top - 11:08:08 up 44 days, 23:06, 7 users, load average: 2.87, 2.83, 2.80 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.7 us, 0.6 sy, 0.0 ni, 94.2 id, 4.4 wa, 0.0 hi, 0.1 si, 0.1 st KiB Mem : 12287464+total, 12899580 free, 41134856 used, 68840208 buff/cache KiB Swap: 8388604 total, 8195580 free, 193024 used. 79690064 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 32673 umshastr 20 0 20.0g 17.6g 30936 S 8.6 15.0 48:06.90 mysqld . top - 11:41:55 up 44 days, 23:40, 7 users, load average: 3.08, 2.68, 2.60 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.6 us, 0.6 sy, 0.0 ni, 95.7 id, 2.8 wa, 0.0 hi, 0.0 si, 0.1 st KiB Mem : 12287464+total, 9418328 free, 43940268 used, 69516048 buff/cache KiB Swap: 8388604 total, 8195580 free, 193024 used. 76884656 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 32673 umshastr 20 0 22.7g 20.2g 30936 S 8.3 17.3 55:27.23 mysqld . top - 13:48:11 up 45 days, 1:46, 7 users, load average: 1.64, 2.61, 2.60 Tasks: 1 total, 0 running, 1 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.8 us, 0.8 sy, 0.0 ni, 94.2 id, 4.0 wa, 0.0 hi, 0.1 si, 0.1 st KiB Mem : 12287464+total, 1030752 free, 47149020 used, 74694880 buff/cache KiB Swap: 8388604 total, 8195580 free, 193024 used. 73675904 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 32673 umshastr 20 0 26.2g 23.3g 30936 S 9.3 19.9 86:06.26 mysqld