## Env # System Summary Report ###################### Date | 2024-01-24 08:51:55 UTC (local TZ: CET +0100) Hostname | support-cluster03 Uptime | 733 days, 15:20, 2 users, load average: 0.00, 0.00, 0.05 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 | 95.4G Used | physical = 897.7M, swap allocated = 8.0G, swap used = 7.6G, virtual = 8.5G Shared | 1.6G Buffers | 21.1G Caches | 113.8G Dirty | 376 kB UsedRSS | 847.6M Swappiness | 60 DirtyPolicy | 20, 10 DirtyStatus | 0, 0 ### https://dev.mysql.com/doc/refman/8.0/en/performance-schema-query-profiling.html ### Binaries pulled from internal repo (binary tarball) -- Sysbench prepare bin/sysbench --threads=1 --time=600 --report-interval=100 --db-driver=mysql --mysql-user=root --mysql-db=sysbench --mysql-socket=/tmp/mysql.sock --tables=1 --table-size=10000000 --secondary_start=0 --secondary_end=10000000 --delta=65536 share/sysbench/select_mrr_test.lua 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'... ######################################## 8.0.36 -- 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 rm -rf 113711/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/113711 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/113711 --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/113711/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --local-infile=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 13 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> use sysbench 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> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> set session optimizer_switch="mrr=on"; Query OK, 0 rows affected (0.00 sec) mysql> set session read_rnd_buffer_size=33445532; Query OK, 0 rows affected (0.00 sec) mysql> select /*+ MRR(test) */ * from sbtest1 where k between 0 and 500000 limit 500000; . . | 9999998 | 213015 | 37390897335-42541318641-47475651654-64440007081-84796012759-25834278954-10025531252-83025119921-04346948174-02696383816 | 61876832855-93974861132-40349719122-39707515247-74387063657 | +---------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 499113 rows in set, 1 warning (9 min 45.52 sec) mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT -> FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%5000%'; +----------+----------+----------------------------------------------------------------------------------+ | EVENT_ID | Duration | SQL_TEXT | +----------+----------+----------------------------------------------------------------------------------+ | 96 | 585.5184 | select /*+ MRR(test) */ * from sbtest1 where k between 0 and 500000 limit 500000 | +----------+----------+----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration -> FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=96; +------------------------------------------------+----------+ | Stage | Duration | +------------------------------------------------+----------+ | stage/sql/starting | 0.0002 | | stage/sql/Executing hook on transaction begin. | 0.0000 | | stage/sql/starting | 0.0000 | | stage/sql/checking permissions | 0.0000 | | stage/sql/Opening tables | 0.0000 | | stage/sql/init | 0.0000 | | stage/sql/System lock | 0.0000 | | stage/sql/optimizing | 0.0000 | | stage/sql/statistics | 0.0001 | | stage/sql/preparing | 0.0000 | | stage/sql/executing | 585.5179 | | stage/sql/end | 0.0000 | | stage/sql/query end | 0.0000 | | stage/sql/waiting for handler commit | 0.0000 | | stage/sql/closing tables | 0.0000 | | stage/sql/freeing items | 0.0000 | | stage/sql/cleaning up | 0.0000 | +------------------------------------------------+----------+ 17 rows in set (0.00 sec) mysql> ######################################## 8.0.35 -- build used 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 rm -rf 113711/ bin/mysqld --no-defaults --initialize-insecure --basedir=$PWD --datadir=$PWD/113711 --log-error-verbosity=3 bin/mysqld_safe --no-defaults --mysqld-version='' --basedir=$PWD --datadir=$PWD/113711 --socket=/tmp/mysql.sock --port=3306 --log-error=$PWD/113711/log.err --mysqlx=0 --log-error-verbosity=3 --secure-file-priv="" --local-infile=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 9 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> use sysbench 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> set profiling=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> set session optimizer_switch="mrr=on"; Query OK, 0 rows affected (0.00 sec) mysql> set session read_rnd_buffer_size=33445532; Query OK, 0 rows affected (0.00 sec) mysql> select /*+ MRR(test) */ * from sbtest1 where k between 0 and 500000 limit 500000; . . | 9999934 | 90650 | 58404387584-25812047577-70990032278-82215229991-87985658582-96392509643-08236665885-64417876236-17371164215-44929992862 | 20082257242-71798613012-87214656999-14927685665-52892481652 | | 9999939 | 498090 | 44715008922-09502273659-51075963553-66200152963-61922630421-13771982591-56774757518-23878257362-51777101087-03970388659 | 63571188641-65305922914-23217800771-15239569006-72445256704 | | 9999943 | 21797 | 99654207930-81209696665-12193811940-47463343343-42500748556-54503915076-74073001385-12151168895-40319706635-21749516164 | 50919981705-40210032129-27610848877-73781205258-60242346592 | | 9999998 | 213015 | 37390897335-42541318641-47475651654-64440007081-84796012759-25834278954-10025531252-83025119921-04346948174-02696383816 | 61876832855-93974861132-40349719122-39707515247-74387063657 | +---------+--------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+ 499113 rows in set, 1 warning (3.01 sec) . . mysql> SELECT event_name AS Stage, TRUNCATE(TIMER_WAIT/1000000000000,6) AS Duration FROM performance_schema.events_stages_history_long WHERE NESTING_EVENT_ID=103; +------------------------------------------------+----------+ | Stage | Duration | +------------------------------------------------+----------+ | stage/sql/starting | 0.0002 | | stage/sql/Executing hook on transaction begin. | 0.0000 | | stage/sql/starting | 0.0000 | | stage/sql/checking permissions | 0.0000 | | stage/sql/Opening tables | 0.0000 | | stage/sql/init | 0.0000 | | stage/sql/System lock | 0.0000 | | stage/sql/optimizing | 0.0000 | | stage/sql/statistics | 0.0001 | | stage/sql/preparing | 0.0000 | | stage/sql/executing | 3.0053 | | stage/sql/end | 0.0000 | | stage/sql/query end | 0.0000 | | stage/sql/waiting for handler commit | 0.0000 | | stage/sql/closing tables | 0.0000 | | stage/sql/freeing items | 0.0000 | | stage/sql/cleaning up | 0.0000 | +------------------------------------------------+----------+ 17 rows in set (0.01 sec) mysql>