Valerii Kravchuk added a comment - 2023-04-03 09:36 It turned out that History list length does NOT matter much, as we can see with the following modified test (on the same slow I/O bout platform): openxs@ao756:~/dbs/maria10.5/mysql-test$ cat suite/innodb/t/pt_large.test source include/have_innodb.inc; source include/have_partition.inc; set default_storage_engine=innodb; delimiter ||; set use_stat_tables=NEVER||   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)||   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))||   --query_vertical show table status like 'tp';   delimiter ;||   # try to force restart with flushing pages to disk set global innodb_fast_shutdown = 0;   --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --shutdown_server 300 --source include/wait_until_disconnected.inc   --enable_reconnect --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --source include/wait_until_connected_again.inc   --query_vertical show engine innodb status;   #SELECT variable_name, variable_value #FROM information_schema.global_status #WHERE variable_name like 'INNODB_BUFFER_POOL_PAGES_%';   delimiter ||; select @now := now(6)|| analyze format=json select distinct(c1) from tp partition (prest) as t1|| select timestampdiff(microsecond, @now, now(6))||   select @now := now(6)|| analyze format=json select distinct(c1) from tp partition (prest) as t1|| select timestampdiff(microsecond, @now, now(6))||   --query_vertical show engine innodb status;   select @now := now(6)|| drop table tp|| select timestampdiff(microsecond, @now, now(6))|| The idea is to force slow shutdown, flush to disk etc and restart of server before running the queries. On 10.5.20 we see: openxs@ao756:~/dbs/maria10.5/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=128M' --suite=innodb pt_large Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=128M --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|| 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-04-03 12:27:43.424711 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)) 103444271 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 87753 Avg_row_length 5245 Data_length 460292096 Max_data_length 0 Index_length 0 Data_free 5242880 Auto_increment NULL Create_time 2023-04-03 12:27:43 Update_time 2023-04-03 12:29:26 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-04-03 12:29:28 0x7fc3a51d2640 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 464002612 Log flushed up to 464002612 Pages flushed up to 464002612 Last checkpoint at 464002600 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-04-03 12:29:28.511571 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.7287184, "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": 93759, "r_rows": 100000, "r_table_time_ms": 449.9474815, "r_other_time_ms": 46.75662559, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 513072 select @now := now(6)|| @now := now(6) 2023-04-03 12:29:29.025214 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": 394.4953433, "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": 93759, "r_rows": 100000, "r_table_time_ms": 350.1508022, "r_other_time_ms": 44.33199987, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 394410 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-04-03 12:29:29 0x7fc3a51d2640 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 421953253392592, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 46063 OS file reads, 2 OS file writes, 2 OS fsyncs 43976.02 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, 5.99 non-hash searches/s --- LOG --- Log sequence number 464002612 Log flushed up to 464002612 Pages flushed up to 464002612 Last checkpoint at 464002600 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 55074 0.00 youngs/s, 55018.98 non-youngs/s Pages read 46051, created 131, written 0 43976.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 0.00/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[44024], 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-04-03 12:29:29.420947 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 237882 innodb.pt_large 'innodb' [ pass ] 106435 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 106.435 of 115 seconds executing testcases   Completed: All 1 tests were successful. While on 10.6: openxs@ao756:~/dbs/maria10.6/mysql-test$ ./mtr --mysqld='--innodb_flush_log_at_trx_commit=0' --mysqld='--innodb_buffer_pool_size=128M' --suite=innodb pt_large Logging: ./mtr --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_buffer_pool_size=128M --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|| 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-04-03 12:22:11.874366 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)) 124212858 show table status like 'tp';|| Name tp Engine InnoDB Version 10 Row_format Dynamic Rows 97522 Avg_row_length 4773 Data_length 465551360 Max_data_length 0 Index_length 0 Data_free 5242880 Auto_increment NULL Create_time 2023-04-03 12:22:11 Update_time 2023-04-03 12:24:16 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-04-03 12:24:19 0x7f17b44e8640 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 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 810 OS file reads, 2 OS file writes, 2 OS fsyncs 0.00 reads/s, 8336 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 464734981 Log flushed up to 464734981 Pages flushed up to 464734981 Last checkpoint at 464734969 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 7185 Database pages 927 Old database pages 362 Modified db pages 0 Percent of dirty pages(LRU & free pages): 0.000 Max dirty pages percent: 90.000 Pending reads 491 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 796, created 131, written 0 796000.00 reads/s, 131000.00 creates/s, 0.00 writes/s Buffer pool hit rate 299 / 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: 927, 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-04-03 12:24:19.910352 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": 27885.85776, "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": 96653, "r_rows": 100000, "r_table_time_ms": 27748.48687, "r_other_time_ms": 137.3572726, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 27874349 select @now := now(6)|| @now := now(6) 2023-04-03 12:24:47.785130 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": 23906.68441, "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": 96653, "r_rows": 100000, "r_table_time_ms": 23770.49161, "r_other_time_ms": 136.1816679, "filtered": 100, "r_filtered": 100, "using_index": true } } } } select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 23888900 show engine innodb status;|| Type InnoDB Name Status ===================================== 2023-04-03 12:25:11 0x7f17b44e8640 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 52 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 52 srv_idle srv_master_thread log flush and writes: 52 ---------- 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 (0x7f17b4790b80), not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- FILE I/O -------- Pending flushes (fsync) log: 0; buffer pool: 0 46058 OS file reads, 2 OS file writes, 2 OS fsyncs 870.14 reads/s, 15932 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 464734981 Log flushed up to 464734981 Pages flushed up to 464734981 Last checkpoint at 464734969 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 54991 0.02 youngs/s, 1057.50 non-youngs/s Pages read 46044, created 131, written 0 870.14 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 373 / 1000, young-making rate 0 / 1000 not 761 / 1000 Pages read ahead 0.00/s, evicted without access 11.79/s, Random read ahead 0.00/s LRU len: 8112, unzip_LRU len: 0 I/O sum[38108]:cur[5894], 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, 3846.08 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-04-03 12:25:11.675455 drop table tp|| select timestampdiff(microsecond, @now, now(6))|| timestampdiff(microsecond, @now, now(6)) 146517 innodb.pt_large 'innodb' [ pass ] 180017 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 180.017 of 200 seconds executing testcases   Completed: All 1 tests were successful.

Marko Mäkelä added a comment - 2023-04-18 09:57 I can reproduce a measurable difference between the currently latest 10.5 and 10.6 RelWithDebInfo when running the test on RAM disk: version run time 10.5 9.5 to 10.3 seconds 10.6 12.4 to 14.0 seconds This includes the test setup time. I’ll drill down to the actual difference.

Marko Mäkelä added a comment - 2023-04-18 13:23 To narrow this down, I ported InnoDB from 10.6 to MariaDB 10.5 as follows: git checkout 6c196090c8c265bfd93df1e2ee6b18cda2b1d2d8 git checkout 485a1b1f116f0c5e73fce3a97ffdac84c861b3c2 tpool storage/innobase extra include/aligned.h include/my_atomic_wrapper.h git checkout HEAD extra/comp_err.c patch -p1<<"EOF" diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index f102789d7ab..66125ac5d52 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -1989,45 +1989,6 @@ static int innobase_wsrep_get_checkpoint(handlerton* hton, XID* xid); #define normalize_table_name(a,b) \ normalize_table_name_c_low(a,b,IF_WIN(true,false)) -ulonglong ha_innobase::table_version() const -{ - /* This is either "garbage" or something that was assigned - on a successful ha_innobase::prepare_inplace_alter_table(). */ - return m_prebuilt->trx_id; -} - -#ifdef UNIV_DEBUG -/** whether the DDL log recovery has been completed */ -static bool ddl_recovery_done; -#endif - -static int innodb_check_version(handlerton *hton, const char *path, - const LEX_CUSTRING *version, - ulonglong create_id) -{ - DBUG_ENTER("innodb_check_version"); - DBUG_ASSERT(hton == innodb_hton_ptr); - ut_ad(!ddl_recovery_done); - - if (!create_id) - DBUG_RETURN(0); - - char norm_path[FN_REFLEN]; - normalize_table_name(norm_path, path); - - if (dict_table_t *table= dict_table_open_on_name(norm_path, false, - DICT_ERR_IGNORE_NONE)) - { - const trx_id_t trx_id= table->def_trx_id; - DBUG_ASSERT(trx_id <= create_id); - dict_table_close(table); - DBUG_PRINT("info", ("create_id: %llu trx_id: %llu", create_id, trx_id)); - DBUG_RETURN(create_id != trx_id); - } - else - DBUG_RETURN(2); -} - /** Drop any garbage intermediate tables that existed in the system after a backup was restored. @@ -2037,6 +1998,7 @@ normal DDL recovery will not run due to the lack of the log file, at least some #sql-alter- garbage tables may remain in the InnoDB data dictionary (while the data files themselves are missing). We will attempt to drop the tables here. */ +#if 0 static void drop_garbage_tables_after_restore() { btr_pcur_t pcur; @@ -2158,6 +2120,7 @@ static void innodb_ddl_recovery_done(handlerton*) srv_wake_purge_thread_if_not_active(); } } +#endif /********************************************************************//** Converts an InnoDB error code to a MySQL error code and also tells to MySQL @@ -3071,7 +3034,6 @@ ha_innobase::ha_innobase( | HA_CAN_TABLES_WITHOUT_ROLLBACK | HA_CAN_ONLINE_BACKUPS | HA_CONCURRENT_OPTIMIZE - | HA_CAN_SKIP_LOCKED | (srv_force_primary_key ? HA_REQUIRE_PRIMARY_KEY : 0) ), m_start_of_scan(), @@ -4195,9 +4157,7 @@ static int innodb_init(void* p) HTON_SUPPORTS_EXTENDED_KEYS | HTON_SUPPORTS_FOREIGN_KEYS | HTON_NATIVE_SYS_VERSIONING | HTON_WSREP_REPLICATION | - HTON_REQUIRES_CLOSE_AFTER_TRUNCATE | - HTON_TRUNCATE_REQUIRES_EXCLUSIVE_USE | - HTON_REQUIRES_NOTIFY_TABLEDEF_CHANGED_AFTER_COMMIT; + HTON_REQUIRES_CLOSE_AFTER_TRUNCATE; #ifdef WITH_WSREP innobase_hton->abort_transaction=wsrep_abort_transaction; @@ -4206,9 +4166,6 @@ static int innodb_init(void* p) innobase_hton->disable_internal_writes=innodb_disable_internal_writes; #endif /* WITH_WSREP */ - innobase_hton->check_version = innodb_check_version; - innobase_hton->signal_ddl_recovery_done = innodb_ddl_recovery_done; - innobase_hton->tablefile_extensions = ha_innobase_exts; innobase_hton->table_options = innodb_table_option_list; @@ -5088,17 +5045,6 @@ ha_innobase::table_flags() const return(flags | HA_BINLOG_STMT_CAPABLE); } -/****************************************************************//** -Returns the table type (storage engine name). -@return table type */ - -const char* -ha_innobase::table_type() const -/*===========================*/ -{ - return(innobase_hton_name); -} - /****************************************************************//** Returns the index type. @return index type */ @@ -7818,7 +7764,6 @@ bool ha_innobase::is_read_only(bool altering_to_supported) const !innodb_read_only_compressed) return false; - ib_senderrf(m_user_thd, IB_LOG_LEVEL_WARN, ER_UNSUPPORTED_COMPRESSED_TABLE); return true; } @@ -8553,6 +8498,8 @@ wsrep_calc_row_hash( @retval true on failure */ ATTRIBUTE_COLD bool wsrep_append_table_key(MYSQL_THD thd, const dict_table_t &table) { + return true; +#if 0 char db_buf[NAME_LEN + 1]; char tbl_buf[NAME_LEN + 1]; ulint db_buf_len, tbl_buf_len; @@ -8575,6 +8522,7 @@ ATTRIBUTE_COLD bool wsrep_append_table_key(MYSQL_THD thd, const dict_table_t &ta } return false; +#endif } #endif /* WITH_WSREP */ @@ -11217,7 +11165,6 @@ create_table_info_t::create_options_are_invalid() /* Check if a non-zero KEY_BLOCK_SIZE was specified. */ if (has_key_block_size) { if (is_temp || innodb_read_only_compressed) { - my_error(ER_UNSUPPORTED_COMPRESSED_TABLE, MYF(0)); return("KEY_BLOCK_SIZE"); } @@ -11273,7 +11220,6 @@ create_table_info_t::create_options_are_invalid() switch (row_format) { case ROW_TYPE_COMPRESSED: if (is_temp || innodb_read_only_compressed) { - my_error(ER_UNSUPPORTED_COMPRESSED_TABLE, MYF(0)); return("ROW_FORMAT"); } if (!m_allow_file_per_table) { @@ -13511,7 +13457,7 @@ int ha_innobase::delete_table(const char *name) trx_t *trx= parent_trx; dberr_t err= DB_SUCCESS; if (!trx->lock.table_locks.empty() && - thd_ddl_options(trx->mysql_thd)->is_create_select()) + false) { /* CREATE TABLE...PRIMARY KEY...SELECT ought to be dropping the table because a duplicate key was detected or a timeout occurred. @@ -15722,7 +15668,6 @@ ha_innobase::extra( reset_template(); trx->duplicates = 0; /* fall through */ - case HA_EXTRA_IGNORE_INSERT: /* HA_EXTRA_IGNORE_INSERT is very similar to HA_EXTRA_IGNORE_DUP_KEY, but with one crucial difference: we want !trx->duplicates for INSERT IGNORE so that @@ -16484,7 +16429,6 @@ ha_innobase::store_lock( } else if ((lock_type == TL_READ && in_lock_tables) || (lock_type == TL_READ_HIGH_PRIORITY && in_lock_tables) || lock_type == TL_READ_WITH_SHARED_LOCKS - || lock_type == TL_READ_SKIP_LOCKED || lock_type == TL_READ_NO_INSERT || (lock_type != TL_IGNORE && sql_command != SQLCOM_SELECT)) { @@ -16545,8 +16489,7 @@ ha_innobase::store_lock( m_prebuilt->select_lock_type = LOCK_NONE; m_prebuilt->stored_select_lock_type = LOCK_NONE; } - m_prebuilt->skip_locked= (lock_type == TL_WRITE_SKIP_LOCKED || - lock_type == TL_READ_SKIP_LOCKED); + m_prebuilt->skip_locked= false; if (!trx_is_started(trx) && (m_prebuilt->select_lock_type != LOCK_NONE diff --git a/storage/innobase/handler/ha_innodb.h b/storage/innobase/handler/ha_innodb.h index 1f42bf180a8..5f1b67bb249 100644 --- a/storage/innobase/handler/ha_innodb.h +++ b/storage/innobase/handler/ha_innodb.h @@ -59,16 +59,10 @@ class ha_innobase final : public handler ha_innobase(handlerton* hton, TABLE_SHARE* table_arg); ~ha_innobase() override; - /** @return the transaction that last modified the table definition - @see dict_table_t::def_trx_id */ - ulonglong table_version() const override; - /** Get the row type from the storage engine. If this method returns ROW_TYPE_NOT_USED, the information in HA_CREATE_INFO should be used. */ enum row_type get_row_type() const override; - const char* table_type() const override; - const char* index_type(uint key_number) override; Table_flags table_flags() const override; diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index 7e4beeede3b..12dfc2f5a98 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -103,6 +103,8 @@ static const alter_table_operations INNOBASE_ALTER_REBUILD static const alter_table_operations INNOBASE_ALTER_DATA = INNOBASE_ONLINE_CREATE | INNOBASE_ALTER_REBUILD; +#define ALTER_INDEX_IGNORABILITY 0 + /** Operations for altering a table that InnoDB does not care about */ static const alter_table_operations INNOBASE_INPLACE_IGNORE = ALTER_COLUMN_DEFAULT @@ -11439,8 +11441,10 @@ ha_innobase::commit_inplace_alter_table( During recovery, the purge of InnoDB transaction history will not start until innodb_ddl_recovery_done(). */ +#if 0 ha_alter_info->inplace_alter_table_committed = purge_sys.resume_SYS; purge_sys.stop_SYS(); +#endif trx->commit(deleted); /* At this point, the changes to the persistent storage have diff --git a/storage/innobase/include/univ.i b/storage/innobase/include/univ.i index c5f62f6cf57..23eee89f857 100644 --- a/storage/innobase/include/univ.i +++ b/storage/innobase/include/univ.i @@ -32,6 +32,7 @@ Created 1/20/1994 Heikki Tuuri ****************************************************************************/ #pragma once +#define my_test_if_thinly_provisioned(f) 0 /* aux macros to convert M into "123" (string) if M is defined like #define M 123 */ diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc index bd998094f42..d9bc72bee28 100644 --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -2562,7 +2562,7 @@ row_ins_index_entry_big_rec( return(error); } -#ifdef HAVE_REPLICATION /* Working around MDEV-24622 */ +#if 0 extern "C" int thd_is_slave(const MYSQL_THD thd); #else # define thd_is_slave(thd) 0 @@ -2732,7 +2732,7 @@ row_ins_clust_index_entry_low( goto skip_bulk_insert; } -#ifdef WITH_WSREP +#if 0 if (trx->is_wsrep()) { if (!wsrep_thd_is_local_transaction(trx->mysql_thd)) diff --git a/storage/innobase/trx/trx0trx.cc b/storage/innobase/trx/trx0trx.cc index ed1187e179b..ec5d14cc60a 100644 --- a/storage/innobase/trx/trx0trx.cc +++ b/storage/innobase/trx/trx0trx.cc @@ -1111,9 +1111,8 @@ trx_finalize_for_fts( trx->fts_trx = NULL; } -extern "C" MYSQL_THD thd_increment_pending_ops(MYSQL_THD); -extern "C" void thd_decrement_pending_ops(MYSQL_THD); - +static MYSQL_THD thd_increment_pending_ops(MYSQL_THD) {return nullptr;} +static void thd_decrement_pending_ops(MYSQL_THD) {} #include "../log/log0sync.h" EOF As you can see from the diff, this may break some DDL recovery as well as something related to replication and Galera. For convenience, I made this hybrid available in one commit that I did not test outside this test case. With this hybrid, I got better than the 10.5 performance for the test: 9111ms, 9345ms, 9220ms, 9557ms, 9809ms. A 10.6 485a1b1f116f0c5e73fce3a97ffdac84c861b3c2 with the same build options (CMAKE_BUILD_TYPE=RelWithDebInfo PLUGIN_PERFSCHEMA=NO) is consistently slower: 10950ms, 13908ms, 13529ms, 13734ms. Earlier today, it was 13 to 14 seconds; the 10.9s here is a clear outlier. I think that this proves that the source of the regression resides outside InnoDB. When I analyzed this with perf record , for both 10.5 and 10.6 I saw similar InnoDB functions being invoked during the ANALYZE statement. The top one is buf_LRU_remove_block() . I think that this is normal for the larger-than-buffer-pool test scenario. I think that you will have to reduce the difference between this hybrid and 10.5 or 10.6 until the regression appears or disappears.

Sergei Petrunia added a comment - 2023-04-20 10:35 - edited My results, using the commits Marko has mentioned: commit 821e3d52c0e7405aea7054e138604ae1a4e8c921 (HEAD -> 10.56-MDEV-30986, origin/10.56-MDEV-30986) commit 485a1b1f116f0c5e73fce3a97ffdac84c861b3c2 (HEAD) psergey@blackbox:~/dev-git2/10.56-MDEV-30986/mysql-test$ ./mysql-test-run --mem --repeat=10 main/pt_large.test Logging: ./mysql-test-run --mem --repeat=10 main/pt_large.test main.pt_large 'innodb' [ pass ] 6449 main.pt_large 'innodb' [ 2 pass ] 6563 main.pt_large 'innodb' [ 3 pass ] 6413 main.pt_large 'innodb' [ 4 pass ] 6472 main.pt_large 'innodb' [ 5 pass ] 6466 main.pt_large 'innodb' [ 6 pass ] 6447 main.pt_large 'innodb' [ 7 pass ] 6504 main.pt_large 'innodb' [ 8 pass ] 6384 main.pt_large 'innodb' [ 9 pass ] 6346 main.pt_large 'innodb' [ 10 pass ] 6492 psergey@blackbox:~/dev-git2/10.6-MDEV-30986/mysql-test$ ./mysql-test-run --mem --repeat=10 main/pt_large.test Logging: ./mysql-test-run --mem --repeat=10 main/pt_large.test main.pt_large 'innodb' [ pass ] 6765 main.pt_large 'innodb' [ 2 pass ] 6487 main.pt_large 'innodb' [ 3 pass ] 6299 main.pt_large 'innodb' [ 4 pass ] 6621 main.pt_large 'innodb' [ 5 pass ] 6227 main.pt_large 'innodb' [ 6 pass ] 6875 main.pt_large 'innodb' [ 7 pass ] 6424 main.pt_large 'innodb' [ 8 pass ] 6542 main.pt_large 'innodb' [ 9 pass ] 6489 main.pt_large 'innodb' [ 10 pass ] 6213 Plotting these: I can't reproduce the apparent slowdown...

Valerii Kravchuk added a comment - 2023-04-20 14:09 Just wanted to highlight the "I/O bound" nature of this regression. My tests had shown larger difference on slow HDD vs faster SSD, while your runs with datadir in memory may mask some part of that.

Sergei Petrunia added a comment - 2023-04-20 16:24 - edited For the record, a re-run on the Ram disk with build cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DPLUGIN_PERFSCHEMA=NO . Run 1: Run2: Both runs on the same chart: The only conclusion I can make from these results is that 10.6 can achieve a faster performance, albeit it has some "unlucky" runs.

Marko Mäkelä added a comment - 2023-04-21 05:28 One more point that is worth noting that my 10.6 builds were using io_uring ( MDEV-24883 ), while 10.5 only supports the older libaio ( io_setup() , io_submit() , io_getevents() ). On /dev/shm , libaio would not work and InnoDB would degrade to ‘simulated AIO’. psergei , was your build cmake -DWITH_URING=YES or with liburing-dev available in the system? Can you include also 10.5 in your tests?

Sergei Petrunia added a comment - 2023-04-21 08:07 marko , no I don't have liburing. Should I have it or not?

Sergei Petrunia added a comment - 2023-04-21 12:54 Ok on the rr machine in Sophia, cmake . -DCMAKE_BUILD_TYPE=RelWithDebInfo -DPLUGIN_PERFSCHEMA=NO and ./mtr --repeat=10 pt_large all in the home directory, I get a 17.5% slowdown of 10.6:

Sergei Petrunia added a comment - 2023-04-21 16:58 - edited And could the cause of that be that 10.56- MDEV-30986 branch compiles into a binary that has innodb_use_native_aio=0 , even if compiling 10.6 with the same compile flags produces a binary that has innodb_use_native_aio=1 ? Specifying -DWITH_LIBAIO=1 doesn't seem to help, I still get the binary that doesn't use AIO. Even on the "rr" machine. I do not see a speed regression when using MyISAM.

Sergei Petrunia added a comment - 2023-04-21 19:07 Ok Please find attached: mdev-30986-tests.tgz mdev-30896-its-innodb-fault.ods This is a collection of tests and their results. all done on the "rr in the VPN" machine. It shows that: pt_large: There is a clear 66% slowdown. pt2, pt2-nodistinct - It remains there if I remove partitioning (Question: could support team have figured this out or you guys needed optimizer dev for this? ) other storage engines do not have any slowdowns larger than 1% : pt3-myisam, pt2-rocksdb. As for InnoDB, removing the text column reduces the slowdown to 10% (pt4, pt5, pt6). I'm not sure if this due to text column presense of due to dataset becoming smaller.

Marko Mäkelä added a comment - 2023-04-24 08:29 The test main.pt_large does not look particularly large to me. Its execution time varies a bit. On 10.6, the fastest I got was 9.0 seconds and the slowest 12.4 seconds when running on RAM disk. On 10.5, the times were between 9.6 and 11.0 seconds on RAM disk. It looks like using --repeat is not a good idea, because subsequent invocations would always be slower, likely due to purge lag. If I run the test on fast NVMe storage, the first run will complete in 14.4 seconds on 10.5 and 15.6 seconds on 10.6. During the test execution, I observed at most 100 MB/s reads or writes; the maximum throughput is more than 20 times that. Disabling asynchronous I/O with ./mtr --mysqld=--skip-innodb-use-native-aio main.pt_large does have some impact on the execution time: 15.4 seconds on 10.5 and 17.8 seconds on 10.6. I think that we need a test case were the interesting part (such as the ANALYZE statement) shows a much clearer difference. Which storage did you use on the rr machine? The Samsung MZ7KH480 SSD ( sda ) or the Hitachi HUS726T4TAL HDD ( sdb )?

Marko Mäkelä added a comment - 2023-04-24 08:53 The rr host seems to be from the Ubuntu 20.04 era, running a Linux 5.4 kernel that is just new enough to support io_uring . There is no liburing package available. But, there was no libaio-dev package installed either. So, I assume that the comparison that was conducted by psergei was apples-to-apples, comparing some ‘simulated AIO’ builds. I think that asynchronous I/O is a must-have. One more difference between 10.5 and 10.6 is that MDEV-24854 enabled innodb_flush_method=O_DIRECT , that is, disabled the file system cache, by default. I wonder if that could explain this. On my local system, I ran the following: ./mtr --mysqld=--innodb-flush-method=fsync main.pt_large ./mtr --mysqld=--innodb-flush-method=O_DIRECT main.pt_large The execution times (1 sample each) were as follows: version fsync O_DIRECT 10.5 14.8s 15.0s 10.6 14.4s 15.7s Yes, there could be some regression for innodb_flush_method=O_DIRECT . I will investigate that deeper on the rr host.

Marko Mäkelä added a comment - 2023-04-24 10:49 I tried the following variation of the test: source include/have_innodb.inc; source include/have_partition.inc;   SET unique_checks=0, foreign_key_checks=0; set use_stat_tables=NEVER; SET profiling_history_size=100, profiling = 1;   create table tp (id int , c1 date , c2 int , c3 text, primary key (id, c1)) ENGINE=InnoDB STATS_PERSISTENT=0 partition by range (to_days(c1)) (partition p1 values less than (734874), partition p2 values less than (735240), partition prest values less than (1000000)); SET profiling=0;   BEGIN ; delimiter ||; 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 || delimiter ;|| COMMIT ; source ../suite/innodb/include/wait_all_purged.inc;   SET unique_checks=1, foreign_key_checks=1, profiling=1; --disable_result_log analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; analyze format=json select distinct (c1) from tp partition (prest) as t1; --enable_result_log SHOW PROFILES; SET profiling=0;   DROP TABLE tp; On both 10.5 and 10.6, I am seeing similar time being consumed during the ANALYZE statement according to SHOW PROFILES . Both are with innodb_flush_method=O_DIRECT to get an apples-to-apples comparison: version CREATE minimum average maximum total time 10.5 fsync 0.003s 0.336s 0.369s 0.500s 25.731s 10.6 fsync 0.002s 0.305s 0.332s 0.470s 22.489s 10.5 O_DIRECT 0.003s 0.822s 0.846s 0.871s 32.318s 10.6 O_DIRECT 0.002s 0.805s 0.820s 0.847s 28.704s I am observing an improvement all over the board, no regression at all. The trick to stabilize the numbers might have been to wait for the purge of history, or to prevent the creation of INSERT history, by enabling bulk insert ( MDEV-515 ) in the first place. I was expecting the CREATE TABLE to take longer on 10.6 because of the durable DDL recovery log file write, but apparently MDEV-24626 compensated for that. All across the board, I am observing a slight performance improvement on my local system. I will run the same test on the 4096-byte block size HDD of the rr host and report the results.

Marko Mäkelä added a comment - 2023-04-24 11:17 On the Hitachi HUS726T4TAL hard disk of the rr host, the INSERT would take more than 10 minutes when using O_DIRECT . For innodb_flush_method=fsync , I got the following results: version CREATE minimum average maximum total time 10.5 fsync 0.053s 0.195s 0.203s 0.254s 72.125s 10.6 fsync 0.020s 0.190s 0.194s 0.226s 37.760s I will revise the test so that after the INSERT , it will restart the server with O_DIRECT , to see how slow ANALYZE gets.