openxs@ao756:~/dbs/maria10.5/mysql-test$ cd ../../maria10.6/mysql-test/ openxs@ao756:~/dbs/maria10.6/mysql-test$ cp ../../maria10.5/mysql-test/suite/innodb/t/pt_large.test suite/innodb/t/ openxs@ao756:~/dbs/maria10.6/mysql-test$ export MTR_TESTCASE_TIMEOUT=3600 openxs@ao756:~/dbs/maria10.6/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=128M' --mysqld='--innodb_flush_method=fsync' --suite=innodb pt_large Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=128M --mysqld=--innodb_flush_method=fsync --suite=innodb pt_large VS config: vardir: /home/openxs/dbs/maria10.6/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/openxs/dbs/maria10.6/mysql-test/var'... Checking supported features... MariaDB Version 10.6.13-MariaDB - SSL connections supported Collecting tests... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 set default_storage_engine=innodb; set use_stat_tables=NEVER|| select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 134217728 fsync create table tp (id int, c1 date, c2 int, c3 text, primary key(id, c1)) partition by range (to_days(c1)) (partition p1 values less than (734874), partition p2 values less than (735240), partition prest values less than (1000000))|| select @now := now(6)|| @now := now(6) 2023-05-06 16:59:51.063399 for i in 1..500000 do execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-02", 1, repeat("a", 2000))'); execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-01", 2, repeat("b", 2000))'); end for|| worker[1] Test still running: innodb.pt_large select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1365727655 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 992436 Avg_row_length 130 Data_length 129679360 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 16:59:50 Update_time 2023-05-06 17:22:36 Check_time NULL Collation latin1_swedish_ci Checksum NULL Create_options partitioned Comment Max_index_length 0 Temporary N set global innodb_fast_shutdown = 0; show engine innodb status;; Type InnoDB Name Status ===================================== 2023-05-06 17:22:39 0x7f228d4cb640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 0 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 1 srv_idle srv_master_thread log flush and writes: 1 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000038 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 2055 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 2249 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650238451 Log flushed up to 4650238451 Pages flushed up to 4650238451 Last checkpoint at 4650238439 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 4.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 136314880 Dictionary memory allocated 848424 Buffer pool size 8112 Free buffers 5938 Database pages 2174 Old database pages 822 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 2043, created 131, written 0 2043000.00 reads/s, 131000.00 creates/s, 0.00 writes/s Buffer pool hit rate 0 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 2174, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 134217728 fsync select @now := now(6)|| @now := now(6) 2023-05-06 17:22:39.726372 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 155649.1582, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 19949, "r_rows": 1000000, "r_table_time_ms": 154588.6085, "r_other_time_ms": 1060.532663, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 155442290 select @now := now(6)|| @now := now(6) 2023-05-06 17:25:15.169684 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 119395.6958, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 19949, "r_rows": 1000000, "r_table_time_ms": 118549.8375, "r_other_time_ms": 845.837196, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 119312728 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 17:27:14 0x7f228d4cb640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 35 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 275 srv_idle srv_master_thread log flush and writes: 275 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000039 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f228df63b80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 495866 OS file reads, 2 OS file writes, 2 OS fsyncs 2034.34 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650238451 Log flushed up to 4650238451 Pages flushed up to 4650238451 Last checkpoint at 4650238439 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 136314880 Dictionary memory allocated 855336 Buffer pool size 8112 Free buffers 0 Database pages 8112 Old database pages 3014 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 1, not young 704707 0.00 youngs/s, 2938.60 non-youngs/s Pages read 495852, created 131, written 0 2034.34 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 319 / 1000, young-making rate 0 / 1000 not 983 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 8112, unzip_LRU len: 0 I/O sum[122134]:cur[8688], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 2000000 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 8277.62 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @now := now(6)|| @now := now(6) 2023-05-06 17:27:14.545752 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1697821 innodb.pt_large 'innodb' [ pass ] 1645276 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1645.276 of 1674 seconds executing testcases Completed: All 1 tests were successful. openxs@ao756:~/dbs/maria10.6/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=128M' --mysqld='--innodb_flush_method=O_DIRECT' --suite=innodb pt_large Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=128M --mysqld=--innodb_flush_method=O_DIRECT --suite=innodb pt_large VS config: vardir: /home/openxs/dbs/maria10.6/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/openxs/dbs/maria10.6/mysql-test/var'... Checking supported features... MariaDB Version 10.6.13-MariaDB - SSL connections supported Collecting tests... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 set default_storage_engine=innodb; set use_stat_tables=NEVER|| select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 134217728 O_DIRECT create table tp (id int, c1 date, c2 int, c3 text, primary key(id, c1)) partition by range (to_days(c1)) (partition p1 values less than (734874), partition p2 values less than (735240), partition prest values less than (1000000))|| select @now := now(6)|| @now := now(6) 2023-05-06 17:33:56.303754 for i in 1..500000 do execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-02", 1, repeat("a", 2000))'); execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-01", 2, repeat("b", 2000))'); end for|| worker[1] Test still running: innodb.pt_large select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1273362644 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 1000000 Avg_row_length 4320 Data_length 4320165888 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 17:33:56 Update_time 2023-05-06 17:55:09 Check_time NULL Collation latin1_swedish_ci Checksum NULL Create_options partitioned Comment Max_index_length 0 Temporary N set global innodb_fast_shutdown = 0; show engine innodb status;; Type InnoDB Name Status ===================================== 2023-05-06 17:55:14 0x7f4748320640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 1 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 1 srv_idle srv_master_thread log flush and writes: 1 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000093 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 700 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 6604 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650246715 Log flushed up to 4650246715 Pages flushed up to 4650246715 Last checkpoint at 4650246703 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 4.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 136314880 Dictionary memory allocated 848424 Buffer pool size 8112 Free buffers 7293 Database pages 819 Old database pages 322 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 323 Pending writes: LRU 0, flush list 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 688, created 131, written 0 687.31 reads/s, 130.87 creates/s, 0.00 writes/s Buffer pool hit rate 183 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 819, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 134217728 O_DIRECT select @now := now(6)|| @now := now(6) 2023-05-06 17:55:14.394787 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 278127.1988, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 921656, "r_rows": 1000000, "r_table_time_ms": 276809.8899, "r_other_time_ms": 1317.288959, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 278348620 select @now := now(6)|| @now := now(6) 2023-05-06 17:59:52.744005 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 274775.462, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 921656, "r_rows": 1000000, "r_table_time_ms": 273466.9058, "r_other_time_ms": 1308.537329, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 274541762 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 18:04:27 0x7f4748320640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 13 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 553 srv_idle srv_master_thread log flush and writes: 553 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000094 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f47485b7b80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 495950 OS file reads, 2 OS file writes, 2 OS fsyncs 877.47 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650246715 Log flushed up to 4650246715 Pages flushed up to 4650246715 Last checkpoint at 4650246703 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 136314880 Dictionary memory allocated 855336 Buffer pool size 8112 Free buffers 0 Database pages 8112 Old database pages 3014 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 1, not young 704827 0.00 youngs/s, 1267.67 non-youngs/s Pages read 495936, created 131, written 0 877.47 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 372 / 1000, young-making rate 0 / 1000 not 906 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 8112, unzip_LRU len: 0 I/O sum[53815]:cur[11408], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 2000000 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 3874.32 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @now := now(6)|| @now := now(6) 2023-05-06 18:04:27.288174 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 215140 innodb.pt_large 'innodb' [ pass ] 1831269 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1831.269 of 1855 seconds executing testcases Completed: All 1 tests were successful. openxs@ao756:~/dbs/maria10.6/mysql-test$ free -m total used free shared buff/cache available Mem: 3740 474 2881 44 384 2970 Swap: 979 566 413 openxs@ao756:~/dbs/maria10.6/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=2400M' --mysqld='--innodb_flush_method=O_DIRECT' --suite=innodb pt_large Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=2400M --mysqld=--innodb_flush_method=O_DIRECT --suite=innodb pt_large VS config: vardir: /home/openxs/dbs/maria10.6/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/openxs/dbs/maria10.6/mysql-test/var'... Checking supported features... MariaDB Version 10.6.13-MariaDB - SSL connections supported Collecting tests... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 set default_storage_engine=innodb; set use_stat_tables=NEVER|| select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 2550136832 O_DIRECT create table tp (id int, c1 date, c2 int, c3 text, primary key(id, c1)) partition by range (to_days(c1)) (partition p1 values less than (734874), partition p2 values less than (735240), partition prest values less than (1000000))|| select @now := now(6)|| @now := now(6) 2023-05-06 18:19:59.529553 for i in 1..500000 do execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-02", 1, repeat("a", 2000))'); execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-01", 2, repeat("b", 2000))'); end for|| worker[1] Test still running: innodb.pt_large select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1546926671 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 1000001 Avg_row_length 4285 Data_length 4285562880 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 18:19:59 Update_time 2023-05-06 18:45:44 Check_time NULL Collation latin1_swedish_ci Checksum NULL Create_options partitioned Comment Max_index_length 0 Temporary N set global innodb_fast_shutdown = 0; show engine innodb status;; Type InnoDB Name Status ===================================== 2023-05-06 18:46:11 0x7fa8387ae640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 12 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 12 srv_idle srv_master_thread log flush and writes: 12 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000096 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 38561 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 122 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650336672 Log flushed up to 4650336672 Pages flushed up to 4650336672 Last checkpoint at 4650336660 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 0.33 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2552233984 Dictionary memory allocated 15159336 Buffer pool size 154128 Free buffers 115450 Database pages 38678 Old database pages 14297 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 38547, created 131, written 0 3211.98 reads/s, 10.92 creates/s, 0.00 writes/s Buffer pool hit rate 0 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 38678, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 2550136832 O_DIRECT select @now := now(6)|| @now := now(6) 2023-05-06 18:46:11.279249 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 268574.8223, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 914436, "r_rows": 1000000, "r_table_time_ms": 267274.1648, "r_other_time_ms": 1300.619844, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 268549183 select @now := now(6)|| @now := now(6) 2023-05-06 18:50:39.848707 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 189068.4046, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 914436, "r_rows": 1000000, "r_table_time_ms": 187837.2301, "r_other_time_ms": 1231.152761, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 188990064 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 18:53:48 0x7fa8387ae640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 34 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 469 srv_idle srv_master_thread log flush and writes: 468 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000097 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7fa839763b80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 419689 OS file reads, 2 OS file writes, 2 OS fsyncs 805.62 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650336672 Log flushed up to 4650336672 Pages flushed up to 4650336672 Last checkpoint at 4650336660 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2552233984 Dictionary memory allocated 15166248 Buffer pool size 154128 Free buffers 0 Database pages 154128 Old database pages 56914 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 1, not young 383733 0.00 youngs/s, 1163.58 non-youngs/s Pages read 419675, created 131, written 0 805.62 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 623 / 1000, young-making rate 0 / 1000 not 543 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 154128, unzip_LRU len: 0 I/O sum[49518]:cur[2899], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 2000000 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 5928.36 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @now := now(6)|| @now := now(6) 2023-05-06 18:53:48.840716 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 614837 innodb.pt_large 'innodb' [ pass ] 2029993 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 2029.993 of 2058 seconds executing testcases Completed: All 1 tests were successful. openxs@ao756:~/dbs/maria10.6/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=2400M' --mysqld='--innodb_flush_method=fsync' --suite=innodb pt_large Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=2400M --mysqld=--innodb_flush_method=fsync --suite=innodb pt_large VS config: vardir: /home/openxs/dbs/maria10.6/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/openxs/dbs/maria10.6/mysql-test/var'... Checking supported features... MariaDB Version 10.6.13-MariaDB - SSL connections supported Collecting tests... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 set default_storage_engine=innodb; set use_stat_tables=NEVER|| select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 2550136832 fsync create table tp (id int, c1 date, c2 int, c3 text, primary key(id, c1)) partition by range (to_days(c1)) (partition p1 values less than (734874), partition p2 values less than (735240), partition prest values less than (1000000))|| select @now := now(6)|| @now := now(6) 2023-05-06 18:56:13.763408 for i in 1..500000 do execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-02", 1, repeat("a", 2000))'); execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-01", 2, repeat("b", 2000))'); end for|| worker[1] Test still running: innodb.pt_large select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1357018866 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 1000000 Avg_row_length 4281 Data_length 4281368576 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 18:56:13 Update_time 2023-05-06 19:18:50 Check_time NULL Collation latin1_swedish_ci Checksum NULL Create_options partitioned Comment Max_index_length 0 Temporary N set global innodb_fast_shutdown = 0; show engine innodb status;; Type InnoDB Name Status ===================================== 2023-05-06 19:18:56 0x7f735d758640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 1 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 2 srv_idle srv_master_thread log flush and writes: 2 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000093 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 511 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 9014 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650368175 Log flushed up to 4650368175 Pages flushed up to 4650368175 Last checkpoint at 4650368163 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 4.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2552233984 Dictionary memory allocated 15159336 Buffer pool size 154128 Free buffers 153497 Database pages 631 Old database pages 252 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 1 Pending writes: LRU 0, flush list 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 500, created 131, written 0 499.50 reads/s, 130.87 creates/s, 0.00 writes/s Buffer pool hit rate 545 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 631, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 2550136832 fsync select @now := now(6)|| @now := now(6) 2023-05-06 19:18:56.508231 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 274471.6481, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 913384, "r_rows": 1000000, "r_table_time_ms": 272619.9574, "r_other_time_ms": 1851.664203, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 275840673 select @now := now(6)|| @now := now(6) 2023-05-06 19:23:32.883378 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 203103.4097, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 913384, "r_rows": 1000000, "r_table_time_ms": 201738.0718, "r_other_time_ms": 1328.01946, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 203352497 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 19:26:56 0x7f735d758640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 46 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 481 srv_idle srv_master_thread log flush and writes: 481 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000094 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f735e70db80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 419054 OS file reads, 2 OS file writes, 2 OS fsyncs 1108.72 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650368175 Log flushed up to 4650368175 Pages flushed up to 4650368175 Last checkpoint at 4650368163 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2552233984 Dictionary memory allocated 15166248 Buffer pool size 154128 Free buffers 0 Database pages 154128 Old database pages 56914 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 1, not young 382848 0.00 youngs/s, 1601.25 non-youngs/s Pages read 419040, created 131, written 0 1108.72 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 524 / 1000, young-making rate 0 / 1000 not 686 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 154128, unzip_LRU len: 0 I/O sum[74705]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 2000000 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 6462.97 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @now := now(6)|| @now := now(6) 2023-05-06 19:26:56.615995 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 3739407 innodb.pt_large 'innodb' [ pass ] 1846673 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1846.673 of 1884 seconds executing testcases Completed: All 1 tests were successful. openxs@ao756:~/dbs/maria10.6/mysql-test$ free -m total used free shared buff/cache available Mem: 3740 422 3111 21 207 3086 Swap: 979 688 291 openxs@ao756:~/dbs/maria10.6/mysql-test$ sync openxs@ao756:~/dbs/maria10.6/mysql-test$ sync openxs@ao756:~/dbs/maria10.6/mysql-test$ sync openxs@ao756:~/dbs/maria10.6/mysql-test$ free -m total used free shared buff/cache available Mem: 3740 423 3110 21 207 3085 Swap: 979 688 291 openxs@ao756:~/dbs/maria10.6/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=2800M' --mysqld='--innodb_flush_method=O_DIRECT' --suite=innodb pt_large Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=2800M --mysqld=--innodb_flush_method=O_DIRECT --suite=innodb pt_large VS config: vardir: /home/openxs/dbs/maria10.6/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/openxs/dbs/maria10.6/mysql-test/var'... Checking supported features... MariaDB Version 10.6.13-MariaDB - SSL connections supported Collecting tests... Installing system database... ============================================================================== TEST RESULT TIME (ms) or COMMENT -------------------------------------------------------------------------- worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 set default_storage_engine=innodb; set use_stat_tables=NEVER|| select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 2952790016 O_DIRECT create table tp (id int, c1 date, c2 int, c3 text, primary key(id, c1)) partition by range (to_days(c1)) (partition p1 values less than (734874), partition p2 values less than (735240), partition prest values less than (1000000))|| select @now := now(6)|| @now := now(6) 2023-05-06 19:36:26.155518 for i in 1..500000 do execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-02", 1, repeat("a", 2000))'); execute immediate concat('insert into tp (id, c1, c2, c3) values (', i,', "2023-01-01", 2, repeat("b", 2000))'); end for|| worker[1] Test still running: innodb.pt_large select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1281073041 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 998189 Avg_row_length 341 Data_length 340656128 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 19:36:26 Update_time 2023-05-06 19:57:47 Check_time NULL Collation latin1_swedish_ci Checksum NULL Create_options partitioned Comment Max_index_length 0 Temporary N set global innodb_fast_shutdown = 0; show engine innodb status;; Type InnoDB Name Status ===================================== 2023-05-06 19:58:03 0x7f38af013640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 13 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 13 srv_idle srv_master_thread log flush and writes: 13 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000046 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 44643 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 104 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650206622 Log flushed up to 4650206622 Pages flushed up to 4650206622 Last checkpoint at 4650206610 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 0.31 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2954887168 Dictionary memory allocated 17549736 Buffer pool size 178464 Free buffers 133702 Database pages 44762 Old database pages 16543 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 44631, created 131, written 0 3432.89 reads/s, 10.08 creates/s, 0.00 writes/s Buffer pool hit rate 0 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 44762, unzip_LRU len: 0 I/O sum[0]:cur[1], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @@innodb_buffer_pool_size, @@innodb_flush_method|| @@innodb_buffer_pool_size @@innodb_flush_method 2952790016 O_DIRECT select @now := now(6)|| @now := now(6) 2023-05-06 19:58:03.268411 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 263262.2626, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 70679, "r_rows": 1000000, "r_table_time_ms": 261969.3012, "r_other_time_ms": 1292.934144, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 262561159 select @now := now(6)|| @now := now(6) 2023-05-06 20:02:25.830131 analyze format=json select distinct(c1) from tp partition (prest) as t1|| ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 172024.558, "temporary_table": { "table": { "table_name": "t1", "partitions": ["prest"], "access_type": "index", "key": "PRIMARY", "key_length": "7", "used_key_parts": ["id", "c1"], "r_loops": 1, "rows": 70679, "r_rows": 1000000, "r_table_time_ms": 170806.6444, "r_other_time_ms": 1217.894215, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 171506765 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 20:05:17 0x7f38af013640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 12 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 447 srv_idle srv_master_thread log flush and writes: 447 ---------- SEMAPHORES ---------- ------------ TRANSACTIONS ------------ Trx id counter 2000047 Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION (0x7f38b40e0b80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 406546 OS file reads, 2 OS file writes, 2 OS fsyncs 669.03 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 4650206622 Log flushed up to 4650206622 Pages flushed up to 4650206622 Last checkpoint at 4650206610 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2954887168 Dictionary memory allocated 17556648 Buffer pool size 178464 Free buffers 0 Database pages 178464 Old database pages 65897 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 0 Pending writes: LRU 0, flush list 0 Pages made young 1, not young 329669 0.00 youngs/s, 966.75 non-youngs/s Pages read 406532, created 131, written 0 669.03 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 744 / 1000, young-making rate 0 / 1000 not 369 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 178464, unzip_LRU len: 0 I/O sum[50555]:cur[8029], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 read views open inside InnoDB Process ID=0, Main thread ID=0, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 2000000 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 7250.90 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ select @now := now(6)|| @now := now(6) 2023-05-06 20:05:17.338442 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 266573 innodb.pt_large 'innodb' [ pass ] 1731526 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1731.526 of 1762 seconds executing testcases Completed: All 1 tests were successful.