Analyzing InnoDB Semaphore Crashes and a DBA Diagnostic Tool

When InnoDB detects a semaphore wait longer than 600 seconds, the background thread srv_error_monitor_thread will panic after ten consecutive checks, preventing the server from hanging indefinitely.

The log shows a MySQL 5.5.40 instance where thread 139998697924352 holds an X‑lock on a data‑dictionary latch for over 900 seconds, while another thread waits at dict0dict.c:305 and btr0sea.c:1134.

These locks occur in the functions dict_table_stats_lock (triggered by innodb_stats_on_metadata) and btr_search_drop_page_hash_index (used by the Adaptive Hash Index during buffer‑pool eviction). The global AHI lock btr_search_latch becomes a hotspot, especially when the buffer pool is full.

The article provides a concise summary: enabling metadata statistics updates and insufficient buffer‑pool space cause the data‑dictionary lock to block many operations, while AHI lock contention during page eviction leads to the final semaphore timeout and crash.

To help DBAs diagnose such issues, the author created a small command‑line program mysqldba. Example usage: mysqldba doctor -f /Users/hongbin/workbench/mysqld_safe.log The tool extracts semaphore crash events, counts service starts, identifies which threads waited on which RW‑latches, and correlates writer threads with their transaction information. It can also monitor live logs for the keyword “a writer (thread id …) has reserved it in mode” and capture transaction details on‑the‑fly.

Additional commands include: mysqldba -uxxx -pxxx doctor -w which watches the MySQL error log in real time and records relevant transaction data.

For more information and to access the source code, visit the GitHub repository: https://github.com/kevinbin/mysqldba .