Prometheus MySQL Exporter is a powerful tool for gaining deep visibility into your MySQL database’s performance, but it’s not just about collecting metrics; it’s about understanding the hidden dynamics of database operations.
Let’s see it in action. Imagine you’re running a web application backed by MySQL. You’ve installed the mysqld_exporter and pointed Prometheus at it. Here’s a snippet of what your Prometheus dashboard might show:
# HELP mysql_global_status_threads_connected Number of threads connected
# TYPE mysql_global_status_threads_connected gauge
mysql_global_status_threads_connected 55
# HELP mysql_global_status_questions Total number of statements executed by the server
# TYPE mysql_global_status_questions counter
mysql_global_status_questions 123456789
# HELP mysql_global_status_slow_queries Number of queries that took longer than long_query_time seconds to execute
# TYPE mysql_global_status_slow_queries counter
mysql_global_status_slow_queries 1523
# HELP mysql_slave_status_seconds_behind_master Seconds the replica is behind the primary
# TYPE mysql_slave_status_seconds_behind_master gauge
mysql_slave_status_seconds_behind_master 0
This output, scraped from the exporter, provides raw data points. But how do you translate this into actionable insights?
The core problem mysqld_exporter solves is making MySQL’s internal state queryable by Prometheus. MySQL itself has a vast array of status variables and performance schema tables, but accessing them efficiently and consistently for monitoring is challenging. mysqld_exporter acts as a bridge, translating these complex internal MySQL structures into Prometheus’s simple key-value metric format.
Internally, mysqld_exporter connects to your MySQL instance using a standard client library. It then executes a series of carefully crafted SQL queries against SHOW GLOBAL STATUS, SHOW GLOBAL VARIABLES, SHOW SLAVE STATUS (if applicable), and various performance_schema tables. These queries are designed to be efficient and retrieve the most relevant operational metrics. The exporter then exposes these metrics over an HTTP endpoint (usually port 9104) in Prometheus’s exposition format, which Prometheus then scrapes.
The exact levers you control are primarily through the exporter’s configuration and the Prometheus query language (PromQL). You can:
- Configure which metrics to collect: The exporter can be told to collect specific sets of metrics using a
mysqld_exporter.cnffile. This allows you to tailor the data to your needs and reduce overhead. For example, you can disable metrics that aren’t relevant to your setup. - Filter metrics: You can use label filters in Prometheus to focus on specific MySQL instances or databases. For example,
mysql_global_status_threads_connected{instance="mysql-prod-01"}. - Set up alerts: Using PromQL, you can define alert rules based on metric thresholds. For instance,
ALERT HighReplicationLag IF mysql_slave_status_seconds_behind_master > 300. - Build dashboards: Tools like Grafana can consume Prometheus data to create rich visualizations of your MySQL performance.
The most surprising thing about mysqld_exporter is its ability to expose metrics from performance_schema without requiring extensive manual SQL query writing. It pre-packages common and useful performance_schema queries, such as those for I/O operations, statement latencies, and wait events, translating them into Prometheus metrics like mysql_performance_schema_events_statements_total or mysql_performance_schema_events_waits_summary_by_event_name_total. This significantly simplifies the process of diagnosing complex performance bottlenecks that would otherwise require deep dives into MySQL’s internal tracing mechanisms.
Understanding the mysql_global_status_commands_total metric and its relationship to mysql_global_status_questions_total is crucial for capacity planning. While questions counts every statement sent to the server, commands counts specific internal operations that result from those statements, like select, insert, update, delete, commit, and rollback. The difference between them can reveal overhead or specific types of query processing.
The next concept you’ll likely explore is how to correlate these MySQL metrics with application-level metrics to get a holistic view of your system’s health.