Marko Mäkelä added a comment - 2024-10-15 06:04 As far as I can tell, this is a performance bug, which was recently pointed out by maxk . During the CREATE OR REPLACE TABLE of a partitioned table, each partition is being treated as a separate InnoDB table, in its own separate transactions. This should be roughly equivalent to executing the operation on a non-partitioned table for a number of times that corresponds to the number of partitions. In full_back_trace.log , the following thread is holding an exclusive dict_sys.latch : Thread 6 (Thread 0x14bf900f6700 (LWP 1595731)): #0 unlink_chunk (p=p@entry=0x14bf47480790, av=0x14bf50000020) at malloc.c:1462 #1 0x000014bf924e6a7c in malloc_consolidate (av=av@entry=0x14bf50000020) at malloc.c:4494 #2 0x000014bf924e8c83 in _int_malloc (av=av@entry=0x14bf50000020, bytes=bytes@entry=1816) at malloc.c:3699 #3 0x000014bf924eb299 in __GI___libc_malloc (bytes=bytes@entry=1816) at malloc.c:3066 #4 0x000055a9eea53245 in ut_allocator<unsigned char, true>::allocate (throw_on_error=false, set_to_zero=false, autoevent_idx=35, n_elements=<optimized out>, this=<optimized out>) at /test/11.6_dbg/storage/innobase/include/ut0new.h:336 #5 mem_heap_create_block_func (heap=heap@entry=0x14bf47c393c8, n=<optimized out>, file_name=file_name@entry=0x14bf47c393c8 "0mem.cc", line=145, type=0) at /test/11.6_dbg/storage/innobase/mem/mem0mem.cc:277 #6 0x000055a9eea53959 in mem_heap_add_block (heap=heap@entry=0x14bf47c393c8, n=n@entry=128) at /test/11.6_dbg/storage/innobase/mem/mem0mem.cc:378 #7 0x000055a9eecb62a4 in mem_heap_alloc (n=128, heap=0x14bf47c393c8) at /test/11.6_dbg/storage/innobase/include/mem0mem.inl:193 #8 dict_table_t::create (name=..., space=space@entry=0x0, n_cols=n_cols@entry=1, n_v_cols=n_v_cols@entry=0, flags=flags@entry=33, flags2=flags2@entry=80) at /test/11.6_dbg/storage/innobase/dict/dict0mem.cc:173 #9 0x000055a9eeca7cb2 in dict_load_table_low (mtr=mtr@entry=0x14bf900ef7d0, uncommitted=uncommitted@entry=false, rec=rec@entry=0x14bf74ed82bc "test/t#P#p0", table=table@entry=0x14bf900ef3c8) at /test/11.6_dbg/include/span.h:66 #10 0x000055a9eecac5a3 in dict_load_table_one (name=..., ignore_err=ignore_err@entry=DICT_ERR_IGNORE_DROP, fk_tables=std::deque with 0 elements) at /test/11.6_dbg/storage/innobase/dict/dict0load.cc:2382 #11 0x000055a9eecaf05f in dict_sys_t::load_table (this=0x55a9efa07d40 <dict_sys>, name=..., ignore=ignore@entry=DICT_ERR_IGNORE_DROP) at /test/11.6_dbg/storage/innobase/dict/dict0load.cc:2563 #12 0x000055a9ee9a7895 in ha_innobase::delete_table (this=<optimized out>, name=0x14bf900f0550 "./test/t#P#p0") at /test/11.6_dbg/storage/innobase/handler/ha_innodb.cc:13426 … #24 0x000055a9ee2bab2b in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x14bf500008e8, packet=packet@entry=0x14bf50026249 "CREATE OR REPLACE TABLE t (i INT)", packet_length=packet_length@entry=33, blocking=blocking@entry=true) at /test/11.6_dbg/sql/sql_class.h:1640 11.6 ba7088d462c326c9df7de97a46fe69419cd7e116 ha_innodb.cc 13425 dict_sys.lock(SRW_LOCK_CALL); 13426 table= dict_sys.load_table(n, DICT_ERR_IGNORE_DROP); Because MDEV-17805 has not been fixed, ha_innobase::delete_table() is forced to look up the table by name, instead of operating on an already opened table handle. In this case, the first partition of the table does not exist in the dict_sys cache and therefore needs to be loaded. Loading a table definition into the cache must involve acquiring an exclusive dict_sys.latch at some point. Currently, the exclusive latch is acquired before some I/O may be executed, to load multiple index pages of data dictionary tables into the buffer pool so that the table definition can be loaded. In the stack trace above, the thread happens to be allocating some memory, which should be fast, but this should have been preceded with some buffer pool access. We can reduce the exclusive latch hold time by first inserting a table ‘stub’ into the dict_sys cache and marking it as incomplete, which in turn would force any thread that wishes to access the table, wait for the definition to become fully available. Special care would be needed in the scenarios that the table definition turns out to be corrupted. In this way, only those threads that need to access this particular table or partition definition would have to wait for the I/O. InnoDB always used something similar for buffer pool pages. We’d first allocate and read-fix a buffer page descriptor and acquire an exclusive latch on it, then release the buf_pool.mutex . While the page is being read or created, other threads may concurrently access any other page in the buffer pool, because we are not hogging the buf_pool.mutex nor a latch on the buf_pool.page_hash shard that maps a page identifier to the block descriptor.

Marko Mäkelä added a comment - 2025-01-08 11:55 In MDEV-35424 there is an example where dict_load_foreigns() is blocked on a pread() deep inside btr_pcur_open_on_user_rec() . We really must not load anything into the buffer pool while holding an exclusive dict_sys.latch . We should also avoid doing that when holding a shared dict_sys.latch .