openxs@ao756:~$ cd dbs/maria10.5/mysql-test/ openxs@ao756:~/dbs/maria10.5/mysql-test$ vi suite/innodb/t/pt_large_restart.test openxs@ao756:~/dbs/maria10.5/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=128MM' --mysqld='--innodb_flush_method=fsync' --suite=innodb pt_large_restart Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=128MM --mysqld=--innodb_flush_method=fsync --suite=innodb pt_large_restart 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.23-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|| show variables like '%version%'|| Variable_name Value in_predicate_conversion_threshold 1000 innodb_version 10.5.23 protocol_version 10 slave_type_conversions system_versioning_alter_history ERROR system_versioning_asof DEFAULT tls_version TLSv1.1,TLSv1.2,TLSv1.3 version 10.5.23-MariaDB-log version_comment Source distribution version_compile_machine x86_64 version_compile_os Linux version_malloc_library system version_source_revision 53499cd1ea1c8092460924224d78a286d617492d version_ssl_library OpenSSL 1.1.1s-dev xx XXX xxxx wsrep_patch_version wsrep_26.22 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-08-30 13:55:37.038991 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)) 108883058 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 112344 Avg_row_length 4115 Data_length 462405632 Max_data_length 0 Index_length 0 Data_free 5242880 Auto_increment NULL Create_time 2023-08-30 13:55:36 Update_time 2023-08-30 13:57:25 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-08-30 13:57:28 0x7fda0d5a5640 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 464014941 Log flushed up to 464014941 Pages flushed up to 464014941 Last checkpoint at 464014929 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 @now := now(6)|| @now := now(6) 2023-08-30 13:57:28.449276 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": 566.973773, "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": 520.4485286, "r_other_time_ms": 46.50044914, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 586418 select @now := now(6)|| @now := now(6) 2023-08-30 13:57:29.036129 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": 455.0813797, "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": 410.1429035, "r_other_time_ms": 44.92700389, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 456172 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-08-30 13:57:29 0x7fda0d5a5640 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 422049491513552, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 46066 OS file reads, 2 OS file writes, 2 OS fsyncs 43979.02 reads/s, 16356 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 464014941 Log flushed up to 464014941 Pages flushed up to 464014941 Last checkpoint at 464014929 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 55076 0.00 youngs/s, 55020.98 non-youngs/s Pages read 46054, created 131, written 0 43979.02 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 390 / 1000, young-making rate 0 / 1000 not 761 / 1000 Pages read ahead 72.93/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[43955], 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-08-30 13:57:29.494348 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 206003 innodb.pt_large_restart 'innodb' [ pass ] 112889 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 112.889 of 129 seconds executing testcases Completed: All 1 tests were successful. 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_restart.test suite/innodb/t openxs@ao756:~/dbs/maria10.6/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=128MM' --mysqld='--innodb_flush_method=fsync' --suite=innodb pt_large_restart Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=128MM --mysqld=--innodb_flush_method=fsync --suite=innodb pt_large_restart 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.16-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|| show variables like '%version%'|| Variable_name Value in_predicate_conversion_threshold 1000 innodb_version 10.6.16 protocol_version 10 slave_type_conversions system_versioning_alter_history ERROR system_versioning_asof DEFAULT tls_version TLSv1.1,TLSv1.2,TLSv1.3 version 10.6.16-MariaDB-log version_comment Source distribution version_compile_machine x86_64 version_compile_os Linux version_malloc_library system version_source_revision 9b1b4a6f69f81530b7fe9f1b4a7c517df68652e2 version_ssl_library OpenSSL 1.1.1s-dev xx XXX xxxx wsrep_patch_version wsrep_26.22 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-08-30 14:13:57.512828 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)) 133252840 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 97550 Avg_row_length 4718 Data_length 460292096 Max_data_length 0 Index_length 0 Data_free 5242880 Auto_increment NULL Create_time 2023-08-30 14:13:57 Update_time 2023-08-30 14:16: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-08-30 14:16:15 0x7f31344fe640 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 200051 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 1437 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 3239 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 464799508 Log flushed up to 464799508 Pages flushed up to 464799508 Last checkpoint at 464799496 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 6557 Database pages 1555 Old database pages 594 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 1424, created 131, written 0 1424000.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: 1555, 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 @now := now(6)|| @now := now(6) 2023-08-30 14:16:15.190187 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": 629.821, "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": 95652, "r_rows": 100000, "r_table_time_ms": 574.091, "r_other_time_ms": 55.715, "r_engine_stats": { "pages_accessed": 25002, "pages_read_count": 23662, "pages_read_time_ms": 306.226 }, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 642573 select @now := now(6)|| @now := now(6) 2023-08-30 14:16:15.833438 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": 513.464, "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": 95652, "r_rows": 100000, "r_table_time_ms": 458.174, "r_other_time_ms": 55.269, "r_engine_stats": { "pages_accessed": 25002, "pages_read_count": 19976, "pages_read_time_ms": 230.02 }, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 514900 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-08-30 14:16:16 0x7f31344fe640 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 200052 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 (0x7f3134f96b80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 45773 OS file reads, 68 OS file writes, 2 OS fsyncs 44291.71 reads/s, 16126 avg bytes/read, 65.93 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 464799508 Log flushed up to 464799508 Pages flushed up to 464799508 Last checkpoint at 464799496 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 0, not young 54553 0.00 youngs/s, 54498.50 non-youngs/s Pages read 45759, created 131, written 66 44290.71 reads/s, 0.00 creates/s, 65.93 writes/s Buffer pool hit rate 113 / 1000, young-making rate 0 / 1000 not 1090 / 1000 Pages read ahead 77.92/s, evicted without access 599.40/s, Random read ahead 0.00/s LRU len: 8112, unzip_LRU len: 0 I/O sum[0]:cur[43708], 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-08-30 14:16:16.349856 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 203099 innodb.pt_large_restart 'innodb' [ pass ] 139116 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 139.116 of 163 seconds executing testcases Completed: All 1 tests were successful.i