openxs@ao756:~/dbs/maria10.5/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.5/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/openxs/dbs/maria10.5/mysql-test/var'... Checking supported features... MariaDB Version 10.5.20-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 13:33:15.920249 for i in 1..50000 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|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 102144177 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 111757 Avg_row_length 3949 Data_length 441401344 Max_data_length 0 Index_length 0 Data_free 5242880 Auto_increment NULL Create_time 2023-05-06 13:33:15 Update_time 2023-05-06 13:34:58 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 13:34:59 0x7f860d0ff640 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 ---------- OS WAIT ARRAY INFO: reservation count 0 OS WAIT ARRAY INFO: signal count 0 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 200034 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 2043 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 3321 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, 42000.00 non-hash searches/s --- LOG --- Log sequence number 464006057 Log flushed up to 464006057 Pages flushed up to 464006057 Last checkpoint at 464006045 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 854112 Buffer pool size 8065 Free buffers 5903 Database pages 2162 Old database pages 818 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 2031, created 131, written 0 2031000.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: 2162, unzip_LRU len: 0 I/O sum[0]:cur[3], 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 13:34:59.593208 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": 496.357046, "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": 87509, "r_rows": 100000, "r_table_time_ms": 448.6348187, "r_other_time_ms": 47.69616581, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 514624 select @now := now(6)|| @now := now(6) 2023-05-06 13:35:00.108411 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": 391.5635888, "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": 87509, "r_rows": 100000, "r_table_time_ms": 346.3142021, "r_other_time_ms": 45.23702335, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 392090 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 13:35:00 0x7f860d0ff640 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 ---------- OS WAIT ARRAY INFO: reservation count 0 OS WAIT ARRAY INFO: signal count 0 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 200034 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 421688709316816, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 46067 OS file reads, 2 OS file writes, 2 OS fsyncs 43980.02 reads/s, 16381 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, 5.99 non-hash searches/s --- LOG --- Log sequence number 464006057 Log flushed up to 464006057 Pages flushed up to 464006057 Last checkpoint at 464006045 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 856536 Buffer pool size 8065 Free buffers 0 Database pages 8065 Old database pages 2996 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 55081 0.00 youngs/s, 55025.97 non-youngs/s Pages read 46055, created 131, written 0 43980.02 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 390 / 1000, young-making rate 0 / 1000 not 762 / 1000 Pages read ahead 6.99/s, evicted without access 608.39/s, Random read ahead 0.00/s LRU len: 8065, unzip_LRU len: 0 I/O sum[0]:cur[44022], 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 200000 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 199800.20 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 13:35:00.502132 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 206819 innodb.pt_large 'innodb' [ pass ] 104998 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 104.998 of 114 seconds executing testcases Completed: All 1 tests were successful. openxs@ao756:~/dbs/maria10.5/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.5/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/openxs/dbs/maria10.5/mysql-test/var'... Checking supported features... MariaDB Version 10.5.20-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 13:35:52.496207 for i in 1..50000 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|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 104208995 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 88180 Avg_row_length 5041 Data_length 444547072 Max_data_length 0 Index_length 0 Data_free 5242880 Auto_increment NULL Create_time 2023-05-06 13:35:52 Update_time 2023-05-06 13:37: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 13:37:38 0x7f7954438640 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 ---------- OS WAIT ARRAY INFO: reservation count 0 OS WAIT ARRAY INFO: signal count 0 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 200034 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 1266 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 5359 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, 41.96 non-hash searches/s --- LOG --- Log sequence number 464005743 Log flushed up to 464005743 Pages flushed up to 464005743 Last checkpoint at 464005731 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 854112 Buffer pool size 8065 Free buffers 6680 Database pages 1385 Old database pages 531 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 419 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 834, created 131, written 0 834000.00 reads/s, 131000.00 creates/s, 0.00 writes/s Buffer pool hit rate 417 / 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: 1385, unzip_LRU len: 0 I/O sum[0]:cur[3], 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 13:37:38.650469 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": 25937.55648, "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": 100009, "r_rows": 100000, "r_table_time_ms": 25807.65852, "r_other_time_ms": 129.8731095, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 26246092 select @now := now(6)|| @now := now(6) 2023-05-06 13:38:04.896887 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": 22702.75002, "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": 100009, "r_rows": 100000, "r_table_time_ms": 22573.73098, "r_other_time_ms": 129.0077896, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 22683911 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 13:38:27 0x7f7954438640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 49 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 50 srv_idle srv_master_thread log flush and writes: 50 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 0 OS WAIT ARRAY INFO: signal count 0 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 200034 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 421634069303504, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 46068 OS file reads, 2 OS file writes, 2 OS fsyncs 914.31 reads/s, 16099 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.12 non-hash searches/s --- LOG --- Log sequence number 464005743 Log flushed up to 464005743 Pages flushed up to 464005743 Last checkpoint at 464005731 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 856536 Buffer pool size 8065 Free buffers 0 Database pages 8065 Old database pages 2996 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 55086 0.02 youngs/s, 1124.18 non-youngs/s Pages read 46055, created 131, written 0 922.86 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 374 / 1000, young-making rate 0 / 1000 not 762 / 1000 Pages read ahead 0.00/s, evicted without access 12.39/s, Random read ahead 0.00/s LRU len: 8065, unzip_LRU len: 0 I/O sum[40276]:cur[3752], 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 200000 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 4081.55 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 13:38:27.583191 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 215369 innodb.pt_large 'innodb' [ pass ] 155552 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 155.552 of 166 seconds executing testcases Completed: All 1 tests were successful. openxs@ao756:~/dbs/maria10.5/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=1280M' --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=1280M --mysqld=--innodb_flush_method=O_DIRECT --suite=innodb pt_large VS config: vardir: /home/openxs/dbs/maria10.5/mysql-test/var Checking leftover processes... Removing old var directory... Creating var directory '/home/openxs/dbs/maria10.5/mysql-test/var'... Checking supported features... MariaDB Version 10.5.20-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 1342177280 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 13:41:32.387168 for i in 1..50000 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|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 97648671 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 100012 Avg_row_length 4686 Data_length 468697088 Max_data_length 0 Index_length 0 Data_free 5242880 Auto_increment NULL Create_time 2023-05-06 13:41:32 Update_time 2023-05-06 13:43:10 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 13:43:13 0x7f643b3fd640 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 ---------- OS WAIT ARRAY INFO: reservation count 0 OS WAIT ARRAY INFO: signal count 0 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 200034 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 853 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 7935 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, 41.96 non-hash searches/s --- LOG --- Log sequence number 464008964 Log flushed up to 464008964 Pages flushed up to 464008964 Last checkpoint at 464008952 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 1344274432 Dictionary memory allocated 7993344 Buffer pool size 80650 Free buffers 79678 Database pages 972 Old database pages 378 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 470 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 371, created 131, written 0 370.63 reads/s, 130.87 creates/s, 0.00 writes/s Buffer pool hit rate 741 / 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: 972, unzip_LRU len: 0 I/O sum[0]:cur[3], 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 1342177280 O_DIRECT select @now := now(6)|| @now := now(6) 2023-05-06 13:43:13.062853 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": 9243.675492, "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": 87509, "r_rows": 100000, "r_table_time_ms": 9175.725282, "r_other_time_ms": 67.93465292, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 9711872 select @now := now(6)|| @now := now(6) 2023-05-06 13:43:22.775041 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": 102.082341, "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": 87509, "r_rows": 100000, "r_table_time_ms": 64.10702418, "r_other_time_ms": 37.9634428, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 102891 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 13:43:22 0x7f643b3fd640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 9 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 10 srv_idle srv_master_thread log flush and writes: 10 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 0 OS WAIT ARRAY INFO: signal count 0 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 200034 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 421543452606672, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 25325 OS file reads, 2 OS file writes, 2 OS fsyncs 2718.81 reads/s, 3443 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.67 non-hash searches/s --- LOG --- Log sequence number 464008964 Log flushed up to 464008964 Pages flushed up to 464008964 Last checkpoint at 464008952 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 1344274432 Dictionary memory allocated 7995768 Buffer pool size 80650 Free buffers 55206 Database pages 25444 Old database pages 9412 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 25313, created 131, written 0 2771.03 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 654 / 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: 25444, unzip_LRU len: 0 I/O sum[0]:cur[5145], 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 200000 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 22219.75 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 13:43:22.879228 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 130731 innodb.pt_large 'innodb' [ pass ] 110834 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 110.834 of 120 seconds executing testcases Completed: All 1 tests were successful.