openxs@ao756:~/dbs/maria10.5/mysql-test$ export MTR_TESTCASE_TIMEOUT=3600 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 14:55:44.564117 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|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1043642599 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 938551 Avg_row_length 4910 Data_length 4608524288 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 14:55:44 Update_time 2023-05-06 15:13:08 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 15:13:10 0x7fc7d2780640 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 2000034 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 2044 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 3319 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 4643108638 Log flushed up to 4643108638 Pages flushed up to 4643108638 Last checkpoint at 4643108626 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 5902 Database pages 2163 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 2032, created 131, written 0 2029.97 reads/s, 130.87 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: 2163, 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 15:13:10.090597 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": 154980.2253, "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": 875009, "r_rows": 1000000, "r_table_time_ms": 153924.3465, "r_other_time_ms": 1055.852292, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 154933228 select @now := now(6)|| @now := now(6) 2023-05-06 15:15:45.024847 analyze format=json select distinct(c1) from tp partition (prest) as t1|| worker[1] Test still running: innodb.pt_large ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 113801.6701, "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": 875009, "r_rows": 1000000, "r_table_time_ms": 112999.3756, "r_other_time_ms": 802.2722966, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 113831267 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 15:17:38 0x7fc7d2780640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 13 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 270 srv_idle srv_master_thread log flush and writes: 270 ---------- 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 2000034 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 421971194122448, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 496062 OS file reads, 2 OS file writes, 2 OS fsyncs 2267.59 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 4643108638 Log flushed up to 4643108638 Pages flushed up to 4643108638 Last checkpoint at 4643108626 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 705051 0.00 youngs/s, 3275.06 non-youngs/s Pages read 496050, created 131, written 0 2267.67 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 333 / 1000, young-making rate 0 / 1000 not 962 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 8065, unzip_LRU len: 0 I/O sum[132827]:cur[29482], 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, 9421.35 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 15:17:38.935999 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1340362 innodb.pt_large 'innodb' [ pass ] 1315942 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1315.942 of 1330 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 15:22:54.690181 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|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1043023630 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 938958 Avg_row_length 4875 Data_length 4578115584 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 15:22:54 Update_time 2023-05-06 15:40:17 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 15:40:20 0x7f6d10911640 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 2000034 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 1356 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 4992 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 4643076811 Log flushed up to 4643076811 Pages flushed up to 4643076811 Last checkpoint at 4643076799 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 6590 Database pages 1475 Old database pages 564 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 490 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 854, created 131, written 0 854000.00 reads/s, 131000.00 creates/s, 0.00 writes/s Buffer pool hit rate 403 / 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: 1475, 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 15:40:20.646848 analyze format=json select distinct(c1) from tp partition (prest) as t1|| worker[1] Test still running: innodb.pt_large ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 271433.9297, "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": 812509, "r_rows": 1000000, "r_table_time_ms": 270101.1754, "r_other_time_ms": 1332.729391, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 271331770 select @now := now(6)|| @now := now(6) 2023-05-06 15:44:51.979280 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": 268807.7637, "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": 812509, "r_rows": 1000000, "r_table_time_ms": 267487.9559, "r_other_time_ms": 1319.786644, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 268257701 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 15:49:20 0x7f6d10911640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 60 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 540 srv_idle srv_master_thread log flush and writes: 540 ---------- 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 2000034 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 421581393928400, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 496063 OS file reads, 2 OS file writes, 2 OS fsyncs 914.87 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 4643076811 Log flushed up to 4643076811 Pages flushed up to 4643076811 Last checkpoint at 4643076799 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 705066 0.00 youngs/s, 1321.54 non-youngs/s Pages read 496051, created 131, written 0 914.88 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 321 / 1000, young-making rate 0 / 1000 not 979 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 8065, unzip_LRU len: 0 I/O sum[55393]:cur[13327], 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, 3735.49 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 15:49:20.238641 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 222355 innodb.pt_large 'innodb' [ pass ] 1585979 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1585.979 of 1599 seconds executing testcases Completed: All 1 tests were successful. openxs@ao756:~/dbs/maria10.5/mysql-test$ free -m total used free shared buff/cache available Mem: 3740 645 2417 123 677 2690 Swap: 979 350 629 openxs@ao756:~/dbs/maria10.5/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.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 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 16:00:24.863249 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|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1041485670 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 880544 Avg_row_length 5087 Data_length 4479549440 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 16:00:24 Update_time 2023-05-06 16:17:46 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 16:17:55 0x7fd606707640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 6 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 6 srv_idle srv_master_thread log flush and writes: 6 ---------- 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 2000034 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 22610 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 300 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, 7.00 non-hash searches/s --- LOG --- Log sequence number 4643101078 Log flushed up to 4643101078 Pages flushed up to 4643101078 Last checkpoint at 4643101066 0 pending log flushes, 0 pending chkp writes 4 log i/o's done, 0.67 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 2552233984 Dictionary memory allocated 15165024 Buffer pool size 153235 Free buffers 130506 Database pages 22729 Old database pages 8410 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 104 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 22494, created 131, written 0 3748.38 reads/s, 21.83 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: 22729, 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 2550136832 O_DIRECT select @now := now(6)|| @now := now(6) 2023-05-06 16:17:55.478334 analyze format=json select distinct(c1) from tp partition (prest) as t1|| worker[1] Test still running: innodb.pt_large ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 259845.7448, "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": 1000009, "r_rows": 1000000, "r_table_time_ms": 258528.438, "r_other_time_ms": 1317.291095, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 262022785 select @now := now(6)|| @now := now(6) 2023-05-06 16:22:17.502314 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": 183421.3625, "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": 1000009, "r_rows": 1000000, "r_table_time_ms": 182170.9708, "r_other_time_ms": 1250.369794, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 183093932 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 16:25:20 0x7fd606707640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 15 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 451 srv_idle srv_master_thread log flush and writes: 451 ---------- 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 2000034 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 422032198070480, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 417935 OS file reads, 2 OS file writes, 2 OS fsyncs 755.75 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 4643101078 Log flushed up to 4643101078 Pages flushed up to 4643101078 Last checkpoint at 4643101066 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 15167448 Buffer pool size 153235 Free buffers 0 Database pages 153235 Old database pages 56584 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 382454 0.00 youngs/s, 1089.79 non-youngs/s Pages read 417923, created 131, written 0 755.75 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 744 / 1000, young-making rate 0 / 1000 not 368 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 153235, unzip_LRU len: 0 I/O sum[51117]:cur[265], 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, 8192.85 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 16:25:20.597683 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 890183 innodb.pt_large 'innodb' [ pass ] 1496836 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1496.836 of 1508 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=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.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 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 16:28:17.647777 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|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 1074101650 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 1000012 Avg_row_length 4614 Data_length 4614815744 Max_data_length 0 Index_length 0 Data_free 6291456 Auto_increment NULL Create_time 2023-05-06 16:28:17 Update_time 2023-05-06 16:46:11 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 16:46:16 0x7f850f454640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 2 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 2000034 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 566 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 11930 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, 20.99 non-hash searches/s --- LOG --- Log sequence number 4643095843 Log flushed up to 4643095843 Pages flushed up to 4643095843 Last checkpoint at 4643095831 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 15165024 Buffer pool size 153235 Free buffers 152550 Database pages 685 Old database pages 272 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 553, created 131, written 0 552.45 reads/s, 130.87 creates/s, 0.00 writes/s Buffer pool hit rate 614 / 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: 685, 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 2550136832 fsync select @now := now(6)|| @now := now(6) 2023-05-06 16:46:16.172075 analyze format=json select distinct(c1) from tp partition (prest) as t1|| worker[1] Test still running: innodb.pt_large ANALYZE { "query_block": { "select_id": 1, "r_loops": 1, "r_total_time_ms": 261837.0193, "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": 937509, "r_rows": 1000000, "r_table_time_ms": 259887.0085, "r_other_time_ms": 1949.98442, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 263056734 select @now := now(6)|| @now := now(6) 2023-05-06 16:50:39.616270 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": 212447.4348, "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": 937509, "r_rows": 1000000, "r_table_time_ms": 211046.5793, "r_other_time_ms": 1329.18352, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 212557350 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-05-06 16:54:12 0x7f850f454640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 42 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 477 srv_idle srv_master_thread log flush and writes: 477 ---------- 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 2000034 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 421684524015824, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 417989 OS file reads, 2 OS file writes, 2 OS fsyncs 825.98 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 4643095843 Log flushed up to 4643095843 Pages flushed up to 4643095843 Last checkpoint at 4643095831 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 15167448 Buffer pool size 153235 Free buffers 0 Database pages 153235 Old database pages 56584 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 382601 0.00 youngs/s, 1192.92 non-youngs/s Pages read 417977, created 131, written 0 825.98 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 590 / 1000, young-making rate 0 / 1000 not 591 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 153235, unzip_LRU len: 0 I/O sum[60391]:cur[7108], 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, 5585.37 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 16:54:12.611470 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 3739380 innodb.pt_large 'innodb' [ pass ] 1558909 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 1558.909 of 1584 seconds executing testcases Completed: All 1 tests were successful.