Investigation of MySQL Exporter‑Induced Deadlocks in Percona 5.7.23
This article analyses why a custom Prometheus exporter for Percona MySQL 5.7.23 caused new‑connection failures and query timeouts, reveals a mutex deadlock involving LOCK_global_system_variables, LOCK_log and LOCK_status, and shows that upgrading to MySQL 5.7.25‑28 resolves the issue.
Problem Background
A Percona MySQL 5.7.23‑23 semi‑synchronous master‑slave cluster uses a self‑built Prometheus exporter (similar to mysqld_exporter ) that opens an HTTP port, runs simple SHOW statements to collect MySQL status, and publishes the data.
The exporter, although theoretically lightweight, caused abnormal connection states and impacted business operations.
Investigation Steps
Checked connection availability via TCP and socket – new connections could not be established.
Observed query timeouts, no errors in mysql-error.log or system logs, and no abnormal resource usage.
Collected stack traces using pstack $(pgrep -xn mysqld) .
Collected Stack Traces
Thread 18 (Thread 0x7fad69737700 (LWP 3588)):
#0 __lll_lock_wait ...
#1 _L_lock_812 ...
#2 pthread_mutex_lock ...
#3 PolyLock_lock_log::rdlock() ...
#4 sys_var::value_ptr ...
#5 get_one_variable_ext ...
#6 get_one_variable ...
#7 show_status_array ...
#8 fill_variables ...
#9 do_fill_table ...
#10 get_schema_tables_result ...
#11 JOIN::prepare_result ...
#12 JOIN::exec ...
#13 handle_query ...
#14 execute_sqlcom_select ...
#15 mysql_execute_command ...
#16 mysql_parse ...
#17 dispatch_command ...
#18 do_command ...
#19 handle_connection ...
#20 pfs_spawn_thread ...
#21 start_thread ...
#22 clone ...Similar stack traces were collected for the SLAVE SQL THREAD, SHOW GLOBAL STATUS, and connection initialization threads.
Key Findings
SHOW GLOBAL VARIABLES holds LOCK_global_system_variables and waits for LOCK_log .
SLAVE SQL THREAD holds LOCK_log and waits for LOCK_status .
SHOW GLOBAL STATUS holds LOCK_status and waits for LOCK_global_system_variables .
New connection initialization also needs LOCK_global_system_variables , which is blocked, causing the connection hang.
These dependencies form a classic mutex deadlock cycle.
Root Cause
In MySQL 5.7.23 the variables binlog_transaction_dependency_tracking and binlog_transaction_dependency_history_size are read under LOCK_log . The exporter’s frequent SHOW queries acquire these locks, creating contention that escalates into the deadlock observed.
Resolution
Upgrading the cluster to MySQL 5.7.25‑28 changes the lock used for reading those variables to a lower‑impact lock, eliminating the deadlock. After the upgrade the exporter works without connection failures.
References
MySQL bug 92108
Documentation for binlog_transaction_dependency_tracking and binlog_transaction_dependency_history_size in the MySQL 5.7 manual.
Aikesheng Open Source Community
The Aikesheng Open Source Community provides stable, enterprise‑grade MySQL open‑source tools and services, releases a premium open‑source component each year (1024), and continuously operates and maintains them.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.