Root Cause Analysis of MySQL "Opening tables" Stalls Caused by Poor Disk Write Performance and InnoDB Redo Log Flush

The author reports a MySQL performance issue where many sessions remain in the Opening tables state. Initial suspicion fell on AHI‑related data dictionary locks, but no DROP or TRUNCATE statements were found, prompting deeper investigation.

Using the pt-pmp tool, a snapshot of thread stacks revealed that 1,402 threads were spinning while waiting for a mutex, and 36 threads were blocked on buf_flush_wait_flushed . The remaining three threads were the page cleaner, purge thread, and dict_stats_thread .

Further stack analysis showed the following call chain for the problematic threads:

log_free_check -> log_check_margins -> log_checkpoint_margin -> log_preflush_pool_modified_pages -> buf_flush_wait_flushed -> os_thread_sleep

Source‑code tracing identified that the dict_stats_thread holds the dict_sys->mutex (data dictionary lock) while executing dict_stats_save and dict_stats_exec_sql , and does not release it during the redo‑log checks.

The redo‑log logic checks whether the log buffer has enough free space, whether dirty pages in the InnoDB buffer pool exceed a threshold, and whether a checkpoint is needed. When the dirty‑page LSN lags behind the current LSN, the system triggers a synchronous dirty‑page flush, waking the page‑cleaner thread while the original threads wait in buf_flush_wait_flushed .

Because the dict_stats_thread retains the data dictionary lock during this flush, other threads cannot acquire the lock, causing them to appear stuck in the Opening tables state. The root cause is therefore poor disk write performance, which leads to excessive dirty‑page accumulation and the observed stalls.

The article concludes that improving disk I/O or tuning InnoDB's flush parameters can alleviate the issue, and points readers to further resources on InnoDB redo‑log behavior.