Invoking log_free_check() while holding exclusive dictionary latch may block most InnoDB threads for a long time

Recently, a customer experienced a crash of MariaDB Server 10.6.14 due to innodb_fatal_semaphore_wait_threshold being exceeded for dict_sys.latch. The latch was being exclusively held by a thread that was executing dict_stats_fetch_from_ps() in order to fetch persistent statistics while opening a table.

Among the stack traces of some thousands of threads, there was one thread executing fdatasync() inside buf_page_write_complete(). This appears to be the only write that was in progress at the time of the crash (write_slots->m_cache.m_pos == 1). This would seem to indicate that there was some performance problem with the storage subsystem that ultimately caused the innodb_fatal_semaphore_wait_threshold watchdog to kick in.

The invocation of log_free_check() inside the InnoDB internal SQL interpreter in que_run_threads_low() does not make sense for read-only operations, such as loading statistics. While the call cannot cause deadlocks, unnecessarily waiting for something while holding an exclusive latch is not good practice. The purpose of log_free_check() is to prevent an overrun of the circular ib_logfile0, by ensuring that the log checkpoint will be advanced before writes can continue.

The reason why the wait cannot cause deadlocks is that by design, a thread is not allowed to invoke log_free_check() or wait for dict_sys.latch while it is holding a buffer page latch or any other latch (see MDEV-32049) that other page-latch holding threads may be waiting for. The buf_flush_page_cleaner() thread itself never waits for dict_sys.latch. The progress of log checkpoints could be blocked if any thread is holding a write latch on a block that would need to be written to a data page in order to advance the checkpoint.

I checked all code paths where dict_sys.lock(SRW_LOCK_CALL) is being invoked in MariaDB Server 10.6. The following code paths may invoke log_free_check():

• dict_stats_save() (saving persistent statistics; before 10.6, this was the main trigger of the infamous MDEV-15020 deadlocks)
• dict_stats_fetch_from_ps() (loading persistent statistics)
• fts_load_user_stopword() (loading stopwords for FULLTEXT INDEX)
• (fts_parse_sql() is invoking pars_sql() but not executing it)
• innodb_drop_database() (executing DROP DATABASE) is accessing FOREIGN KEY constraints; this is subject to change in MDEV-16417
• many DDL operations are invoking the InnoDB internal SQL parser to update the InnoDB data dictionary tables while holding exclusive dict_sys.latch

A notable subsystem that is clean in this respect is the loading of table definitions from the InnoDB data dictionary tables into the dict_sys cache.

We might try to alleviate this problem by adding a log_free_check() invocation to each of the functions listed above, right before the dict_sys.lock acquisition. But, I do not think that it would bring much on a heavily loaded server; concurrently other threads would be able to write more log while we were waiting, and the log_free_check() invocation inside que_run_threads_low() could end up waiting again, and that time blocking the progress of many other threads.

A sustainable fix would seem to be to refactor all use of the InnoDB internal SQL parser and remove that code, so that we have a better overview where log_free_check() needs to be invoked and dict_sys.latch really needs to be held. For DDL operations this is going to be tricky. For persistent statistics and operations related to FULLTEXT INDEX, this is more doable.

We could also check if the operations on persistent statistics can be split so that only pars_sql() is invoked while holding dict_sys.latch, similar to how fts_parse_sql() appears to work.