-- Environment df -Th $PWD Filesystem Type Size Used Avail Use% Mounted on /dev/sdb1 xfs 2.0T 870G 1.2T 44% /export/home # System Summary Report ###################### Date | 2024-03-25 14:51:17 UTC (local TZ: CET +0100) Hostname | xx Uptime | 794 days, 21:20, 4 users, load average: 0.00, 0.00, 0.03 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 | 12.4G Used | physical = 2.3G, swap allocated = 8.0G, swap used = 3.7G, virtual = 6.0G Shared | 9.5G Buffers | 102.7G Caches | 104.5G Dirty | 1840 kB UsedRSS | 2.1G Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 Locator Size Speed Form Factor Type Type Detail -- mysqld build cat docs/INFO_SRC commit: 4e7d422ffbb4fff9342a568b999e44eccc9630dd date: 2023-12-12 19:05:31 +0100 build-date: 2023-12-12 18:11:09 +0000 short: 4e7d422ffbb branch: mysql-8.0.36-release MySQL source 8.0.36 -- default buffer pool --innodb-buffer-pool-size=128M rm -rf 114468/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/114468 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/114468 --core-file --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/114468/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --local-infile=1 --skip-log-bin --innodb-flush-method=O_DIRECT --innodb-flush-log-at-trx-commit=0 --innodb-redo-log-capacity=2G --innodb-parallel-read-threads=1 2>&1 & bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database sbtest; Query OK, 1 row affected (0.01 sec) mysql> mysql> use sbtest Database changed -- bin/sysbench ./share/sysbench/oltp_common.lua --mysql_storage_engine=innodb --table-size=10000000 --tables=1 --mysql-user=root --db-driver=mysql --mysql-socket=/tmp/mysql.sock prepare sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 10000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'... -- CLI bin/mysql -uroot -S /tmp/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 8.0.36 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database sbtest; Query OK, 1 row affected (0.01 sec) mysql> use sbtest Database changed mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 129152 | | Innodb_buffer_pool_read_requests | 32979131 | | Innodb_buffer_pool_reads | 138745 | +---------------------------------------+----------+ 5 rows in set (0.01 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (7.93 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 136448 | | Innodb_buffer_pool_read_ahead_evicted | 129152 | | Innodb_buffer_pool_read_requests | 34364223 | | Innodb_buffer_pool_reads | 139380 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (8.74 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 268416 | | Innodb_buffer_pool_read_ahead_evicted | 129205 | | Innodb_buffer_pool_read_requests | 35746472 | | Innodb_buffer_pool_reads | 141648 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (8.69 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 400384 | | Innodb_buffer_pool_read_ahead_evicted | 129258 | | Innodb_buffer_pool_read_requests | 37128721 | | Innodb_buffer_pool_reads | 143915 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> system ls -lh 114468/sbtest/* -rw-r----- 1 umshastr common 2.3G Mar 25 15:59 114468/sbtest/sbtest1.ibd mysql> mysql> -- After optimizing, the count speed results are much different mysql> mysql> optimize table sbtest1; +----------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+----------+----------+-------------------------------------------------------------------+ | sbtest.sbtest1 | optimize | note | Table does not support optimize, doing recreate + analyze instead | | sbtest.sbtest1 | optimize | status | OK | +----------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (2 min 28.21 sec) mysql> system ls -lh 114468/sbtest/* -rw-r----- 1 umshastr common 2.6G Mar 25 16:04 114468/sbtest/sbtest1.ibd mysql> mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 400384 | | Innodb_buffer_pool_read_ahead_evicted | 134250 | | Innodb_buffer_pool_read_requests | 38451611 | | Innodb_buffer_pool_reads | 279476 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 25.03 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 402943 | | Innodb_buffer_pool_read_ahead_evicted | 136328 | | Innodb_buffer_pool_read_requests | 39838438 | | Innodb_buffer_pool_reads | 416020 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 22.20 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 405438 | | Innodb_buffer_pool_read_ahead_evicted | 138469 | | Innodb_buffer_pool_read_requests | 41223135 | | Innodb_buffer_pool_reads | 550495 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 22.48 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 407933 | | Innodb_buffer_pool_read_ahead_evicted | 140610 | | Innodb_buffer_pool_read_requests | 42607832 | | Innodb_buffer_pool_reads | 684970 | +---------------------------------------+----------+ 5 rows in set (0.01 sec) mysql> -- Rebuilding via noop alter does not change anything mysql> mysql> alter table sbtest1 engine=innodb; Query OK, 0 rows affected (2 min 32.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 407933 | | Innodb_buffer_pool_read_ahead_evicted | 147835 | | Innodb_buffer_pool_read_requests | 43930219 | | Innodb_buffer_pool_reads | 820391 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 25.67 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 410492 | | Innodb_buffer_pool_read_ahead_evicted | 149913 | | Innodb_buffer_pool_read_requests | 45317059 | | Innodb_buffer_pool_reads | 956948 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 23.35 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 412987 | | Innodb_buffer_pool_read_ahead_evicted | 152054 | | Innodb_buffer_pool_read_requests | 46701221 | | Innodb_buffer_pool_reads | 1090872 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 22.60 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+----------+ | Variable_name | Value | +---------------------------------------+----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 415482 | | Innodb_buffer_pool_read_ahead_evicted | 154195 | | Innodb_buffer_pool_read_requests | 48084987 | | Innodb_buffer_pool_reads | 1224417 | +---------------------------------------+----------+ 5 rows in set (0.00 sec) mysql> -- create a shadow copy mysql> mysql> create table sbtest11 like sbtest1; Query OK, 0 rows affected (0.04 sec) mysql> insert into sbtest11 select * from sbtest1; Query OK, 10000000 rows affected (5 min 56.72 sec) Records: 10000000 Duplicates: 0 Warnings: 0 mysql> system ls -lh 114468/sbtest/* -rw-r----- 1 umshastr common 2.4G Mar 25 16:27 114468/sbtest/sbtest11.ibd -rw-r----- 1 umshastr common 2.6G Mar 25 16:14 114468/sbtest/sbtest1.ibd mysql> mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 417978 | | Innodb_buffer_pool_read_ahead_evicted | 154384 | | Innodb_buffer_pool_read_requests | 141974382 | | Innodb_buffer_pool_reads | 1373358 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest11; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (11.04 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 554618 | | Innodb_buffer_pool_read_ahead_evicted | 154384 | | Innodb_buffer_pool_read_requests | 143658190 | | Innodb_buffer_pool_reads | 1373788 | +---------------------------------------+-----------+ 5 rows in set (0.01 sec) mysql> select count(*) from sbtest11; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (7.85 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 691258 | | Innodb_buffer_pool_read_ahead_evicted | 154443 | | Innodb_buffer_pool_read_requests | 145043282 | | Innodb_buffer_pool_reads | 1374196 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest11; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (7.81 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 827898 | | Innodb_buffer_pool_read_ahead_evicted | 154502 | | Innodb_buffer_pool_read_requests | 146428374 | | Innodb_buffer_pool_reads | 1374604 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec) mysql> mysql> -- SELECT queries using a secondary index are WAAY faster on the optimized table (explain is identical for both): mysql> mysql> explain select avg(k) from sbtest1; +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | sbtest1 | NULL | index | NULL | k_1 | 4 | NULL | 9864360 | 100.00 | Using index | +----+-------------+---------+------------+-------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 827898 | | Innodb_buffer_pool_read_ahead_evicted | 154502 | | Innodb_buffer_pool_read_requests | 146428374 | | Innodb_buffer_pool_reads | 1374604 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec) mysql> select avg(k) from sbtest1 -> ; +--------------+ | avg(k) | +--------------+ | 5009290.5886 | +--------------+ 1 row in set (6.94 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 828026 | | Innodb_buffer_pool_read_ahead_evicted | 154561 | | Innodb_buffer_pool_read_requests | 146544013 | | Innodb_buffer_pool_reads | 1382795 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec) mysql> select avg(k) from sbtest11; +--------------+ | avg(k) | +--------------+ | 5009290.5886 | +--------------+ 1 row in set (7.32 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+-----------+ | Variable_name | Value | +---------------------------------------+-----------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 828026 | | Innodb_buffer_pool_read_ahead_evicted | 154561 | | Innodb_buffer_pool_read_requests | 146664711 | | Innodb_buffer_pool_reads | 1391404 | +---------------------------------------+-----------+ 5 rows in set (0.00 sec) mysql> -- ^^ Not much diff at my end compared to Przemyslaw's results in the above case mysql> mysql> ################ restart instance and repeat steps mysql> mysql> restart; Query OK, 0 rows affected (0.00 sec) mysql> Restarting mysqld... 2024-03-25T15:31:23.308930Z mysqld_safe Number of processes running now: 0 2024-03-25T15:31:23.311273Z mysqld_safe mysqld restarted mysql> use sbtest No connection. Trying to reconnect... Connection id: 7 Current database: *** NONE *** Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 0 | | Innodb_buffer_pool_read_ahead_evicted | 0 | | Innodb_buffer_pool_read_requests | 16937 | | Innodb_buffer_pool_reads | 2685 | +---------------------------------------+-------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 25.24 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+---------+ | Variable_name | Value | +---------------------------------------+---------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 2560 | | Innodb_buffer_pool_read_ahead_evicted | 2831 | | Innodb_buffer_pool_read_requests | 1403793 | | Innodb_buffer_pool_reads | 139266 | +---------------------------------------+---------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 25.83 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+---------+ | Variable_name | Value | +---------------------------------------+---------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 5056 | | Innodb_buffer_pool_read_ahead_evicted | 4973 | | Innodb_buffer_pool_read_requests | 2787675 | | Innodb_buffer_pool_reads | 272660 | +---------------------------------------+---------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (1 min 25.52 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+---------+ | Variable_name | Value | +---------------------------------------+---------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 7552 | | Innodb_buffer_pool_read_ahead_evicted | 7115 | | Innodb_buffer_pool_read_requests | 4171150 | | Innodb_buffer_pool_reads | 405913 | +---------------------------------------+---------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest11; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (7.91 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+---------+ | Variable_name | Value | +---------------------------------------+---------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 144192 | | Innodb_buffer_pool_read_ahead_evicted | 7178 | | Innodb_buffer_pool_read_requests | 5556242 | | Innodb_buffer_pool_reads | 406333 | +---------------------------------------+---------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest11; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (7.89 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+---------+ | Variable_name | Value | +---------------------------------------+---------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 280832 | | Innodb_buffer_pool_read_ahead_evicted | 7237 | | Innodb_buffer_pool_read_requests | 6941334 | | Innodb_buffer_pool_reads | 406753 | +---------------------------------------+---------+ 5 rows in set (0.00 sec) mysql> select count(*) from sbtest11; +----------+ | count(*) | +----------+ | 10000000 | +----------+ 1 row in set (7.84 sec) mysql> show status like 'Innodb_buffer_pool_read%'; +---------------------------------------+---------+ | Variable_name | Value | +---------------------------------------+---------+ | Innodb_buffer_pool_read_ahead_rnd | 0 | | Innodb_buffer_pool_read_ahead | 417472 | | Innodb_buffer_pool_read_ahead_evicted | 7296 | | Innodb_buffer_pool_read_requests | 8326426 | | Innodb_buffer_pool_reads | 407173 | +---------------------------------------+---------+ 5 rows in set (0.00 sec)