################### Environment Date | 2023-12-05 07:41:41 UTC (local TZ: CET +0100) Hostname | support-cluster04 Uptime | 683 days, 9:05, 5 users, load average: 0.06, 0.79, 2.66 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 | 1.0G Used | physical = 106.1G, swap allocated = 8.0G, swap used = 5.1G, virtual = 111.1G Shared | 6.4M Buffers | 10.3G Caches | 10.2G Dirty | 216 kB UsedRSS | 105.8G Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 -- uname -an Linux support-cluster04 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 -- 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 -- df -Th /export/home/tmp/ushastry Filesystem Type Size Used Avail Use% Mounted on /dev/sdb1 xfs 2.0T 674G 1.3T 34% /export/home -- HammerDB version in use ./hammerdbcli --version HammerDB CLI v4.2 Copyright (C) 2003-2021 Steve Shaw Type "help" for a list of commands ####################################### 8.0 ####################################### - build (binary tarball) 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 -- Start up cat 113312.cnf [mysqld] innodb_log_file_size=64G innodb_buffer_pool_size=92G innodb_adaptive_hash_index=1 innodb_adaptive_hash_index_parts=8 rm -rf 113312/ bin/mysqld --defaults-file=./113312.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/113312 --log-error-verbosity=3 bin/mysqld_safe --defaults-file=./113312.cnf --mysqld-version='' --basedir=$PWD --datadir=$PWD/113312 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/113312/log.err --mysqlx-port=33330 --mysqlx-socket=/tmp/mysql_x_ushastry.sock --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 2>&1 & -- Followed exact steps from the report and once tests are done then 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> show processlist; +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 27 | Waiting on empty queue | NULL | | 8 | root | localhost | NULL | Query | 0 | init | show processlist | +----+-----------------+-----------+------+---------+------+------------------------+------------------+ 2 rows in set, 1 warning (0.00 sec) -- Only thing done mysql> set global max_connections=500; Query OK, 0 rows affected (0.00 sec) mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 4500 buffer(s) Hash table size 24437351, node heap has 1 buffer(s) Hash table size 24437351, node heap has 1 buffer(s) Hash table size 24437351, node heap has 150 buffer(s) Hash table size 24437351, node heap has 1 buffer(s) Hash table size 24437351, node heap has 1 buffer(s) Hash table size 24437351, node heap has 1 buffer(s) Hash table size 24437351, node heap has 17 buffer(s) 1 row in set (0.01 sec) PAGER set to 'grep "Hash table"' mysql> mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 29952 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 180 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 60 buffer(s) 1 row in set (0.01 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 30483 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 181 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 60 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 45401 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 213 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 61 buffer(s) 1 row in set (0.13 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 56970 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 258 buffer(s) Hash table size 24437351, node heap has 2 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 61 buffer(s) 1 row in set (0.05 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 92184 buffer(s) Hash table size 24437351, node heap has 7 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 743 buffer(s) Hash table size 24437351, node heap has 3 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 64 buffer(s) 1 row in set (0.06 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 102445 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1585 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 65 buffer(s) 1 row in set (0.06 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 101597 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1912 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 65 buffer(s) 1 row in set (0.05 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 102085 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1879 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 103146 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1877 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 103221 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1873 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 102350 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1842 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.01 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 102301 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1842 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 102296 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1841 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 102292 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1841 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 101311 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1827 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 101311 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1827 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 101311 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1827 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 101311 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1827 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 101311 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1827 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 101311 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1827 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) PAGER set to 'grep "Hash table"' mysql> pager grep "Hash table" ; show engine innodb status \G pager; PAGER set to 'grep "Hash table"' Hash table size 24437351, node heap has 101311 buffer(s) Hash table size 24437351, node heap has 8 buffer(s) Hash table size 24437351, node heap has 6 buffer(s) Hash table size 24437351, node heap has 1827 buffer(s) Hash table size 24437351, node heap has 4 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 5 buffer(s) Hash table size 24437351, node heap has 66 buffer(s) 1 row in set (0.00 sec) SET GLOBAL innodb_status_output=ON; SET GLOBAL innodb_status_output_locks=ON; PAGER set to 'grep "Hash table"' mysql> drop table tpcc.stock; Query OK, 0 rows affected (1 min 41.02 sec) ##### ls -lh 113312/log.err -rw-r----- 1 umshastr common 130M Dec 5 08:46 113312/log.err 2023-12-05 08:11:03 140111462381312 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 15 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 1831 srv_active, 0 srv_shutdown, 1710 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5941062 --Thread 140111204435712 has waited at fts0opt.cc line 2698 for 84 seconds the semaphore: Mutex at 0x7f736e98b3a0, Mutex DICT_SYS created dict0dict.cc:1018, locked by 140132772321024 --Thread 140110548289280 has waited at row0purge.cc line 917 for 85 seconds the semaphore: Mutex at 0x7f736e98b3a0, Mutex DICT_SYS created dict0dict.cc:1018, locked by 140132772321024 --Thread 140111172966144 has waited at row0purge.cc line 917 for 85 seconds the semaphore: Mutex at 0x7f736e98b3a0, Mutex DICT_SYS created dict0dict.cc:1018, locked by 140132772321024 --Thread 140111225415424 has waited at srv0srv.cc line 1996 for 85 seconds the semaphore: X-lock on RW-latch at 0x7f736e98b300 created in file dict0dict.cc line 1026 a writer (thread id 140132772321024) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file not yet reserved line 0 Last time write locked in file ../../../mysql-8.0.35/storage/innobase/row/row0mysql.cc line 3770 OS WAIT ARRAY INFO: signal count 1339513 RW-shared spins 0, rounds 0, OS waits 0 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 14336133 Purge done for trx's n:o < 14336133 undo n:o < 0 state: running History list length 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421608785844656, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 421608785843848, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 14336128, ACTIVE 86 sec dropping table 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 8, OS thread handle 140132772321024, query id 343160882 localhost root checking permissions drop table tpcc.stock -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests ((null)) I/O thread 1 state: waiting for completed aio requests (insert buffer thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads: Pending flushes (fsync) log: 0; buffer pool: 0 16206239 OS file reads, 16131956 OS file writes, 8264584 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 256.98 writes/s, 188.32 fsyncs/s -- Couldn't capture, re-test means again long hours to load tables and run tests perf record -ag --pid=26902 sleep 75 perf report --stdio > perf.8.0.35