Monitoring MySQL with Prometheus and mysqld_exporter

2020-11-11 · mysql, monitoring, prometheus

"MySQL is up" is not monitoring. I want connections, buffer pool hit rate, slow queries, replication lag, row lock waits and table growth over time, and an alert before users notice. Prometheus with mysqld_exporter is where I ended up: the exporter runs next to each MySQL instance and exposes several hundred metrics over HTTP, Prometheus scrapes them, Grafana draws the graphs.

Below is the exporter on a CentOS 7 or Ubuntu 18.04 host with MySQL 5.7 or 8.0, added to an existing Prometheus 2.x server (which must reach the host on TCP 9104), plus the first alerts.

The monitoring account

The exporter needs little: PROCESS for SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS, REPLICATION CLIENT for SHOW SLAVE STATUS, and SELECT on performance_schema for the optional collectors.

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'exporter-password' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

On 8.0 add IDENTIFIED WITH mysql_native_password if the exporter build you are using is linked against an older Go MySQL driver; the current releases handle caching_sha2_password fine.

Install mysqld_exporter

Download the release binary and create a service user:

cd /tmp
curl -LO https://github.com/prometheus/mysqld_exporter/releases/download/v0.12.1/mysqld_exporter-0.12.1.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.12.1.linux-amd64.tar.gz
sudo cp mysqld_exporter-0.12.1.linux-amd64/mysqld_exporter /usr/local/bin/
sudo useradd --system --shell /sbin/nologin mysqld_exporter

Store the credentials in a config file only the exporter can read, in the same format the mysql client uses:

sudo tee /etc/.mysqld_exporter.cnf >/dev/null <<'EOF'
[client]
user=exporter
password=exporter-password
socket=/var/lib/mysql/mysql.sock
EOF
sudo chown mysqld_exporter:mysqld_exporter /etc/.mysqld_exporter.cnf
sudo chmod 600 /etc/.mysqld_exporter.cnf

On Ubuntu the socket is /var/run/mysqld/mysqld.sock. Using the socket rather than TCP keeps the account at 'exporter'@'localhost'.

The systemd unit

[Unit]
Description=Prometheus MySQL Exporter
After=network.target mysqld.service

[Service]
User=mysqld_exporter
Group=mysqld_exporter
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/.mysqld_exporter.cnf \
  --collect.global_status \
  --collect.global_variables \
  --collect.slave_status \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.processlist \
  --collect.info_schema.tables \
  --collect.info_schema.tables.databases=shop \
  --collect.perf_schema.tablelocks \
  --collect.perf_schema.eventsstatements \
  --collect.auto_increment.columns \
  --web.listen-address=0.0.0.0:9104
Restart=on-failure

[Install]
WantedBy=multi-user.target

Save it as /etc/systemd/system/mysqld_exporter.service, then:

sudo systemctl daemon-reload
sudo systemctl enable --now mysqld_exporter
curl -s http://localhost:9104/metrics | grep -E '^mysql_up|^mysql_global_status_threads_connected'

mysql_up 1 means the exporter connected successfully. Limit --collect.info_schema.tables to specific databases on servers with tens of thousands of tables; otherwise each scrape hammers information_schema. I learned that on a server with far too many tables.

Firewall and scrape job

sudo firewall-cmd --permanent --add-port=9104/tcp --zone=internal && sudo firewall-cmd --reload    # CentOS
sudo ufw allow from 10.0.9.5 to any port 9104 proto tcp                                            # Ubuntu

In prometheus.yml on the Prometheus server:

scrape_configs:
  - job_name: mysql
    scrape_interval: 15s
    static_configs:
      - targets: ['db01.internal:9104', 'db02.internal:9104']
        labels:
          env: prod

Reload Prometheus (systemctl reload prometheus or kill -HUP), then check Status → Targets in the web UI; both endpoints should be UP.

Dashboards and alerts

In Grafana, add Prometheus as a data source and import dashboard ID 7362 ("MySQL Overview" from the Percona collection), which is built for mysqld_exporter metric names. For alerting, a small mysql.rules.yml:

groups:
  - name: mysql
    rules:
      - alert: MySQLDown
        expr: mysql_up == 0
        for: 1m
        labels: {severity: critical}
        annotations: {summary: "MySQL exporter cannot connect on {{ $labels.instance }}"}
      - alert: MySQLReplicationStopped
        expr: mysql_slave_status_slave_sql_running == 0 or mysql_slave_status_slave_io_running == 0
        for: 2m
        labels: {severity: critical}
      - alert: MySQLReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 300
        for: 5m
        labels: {severity: warning}
      - alert: MySQLTooManyConnections
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
        labels: {severity: warning}
      - alert: MySQLSlowQueriesRising
        expr: rate(mysql_global_status_slow_queries[5m]) > 1
        for: 10m
        labels: {severity: warning}

Reference it under rule_files: in prometheus.yml.

Queries worth keeping

Useful queries to paste into the Prometheus expression browser:

rate(mysql_global_status_questions[5m])
mysql_global_status_innodb_buffer_pool_pages_free / mysql_global_status_innodb_buffer_pool_pages_total
rate(mysql_global_status_innodb_row_lock_waits[5m])
mysql_info_schema_table_size{component="data_length", schema="shop"}

Each should return a series per instance. Stop the replica SQL thread for a minute on a test server and confirm MySQLReplicationStopped goes to PENDING then FIRING.

When the exporter misbehaves

mysql_up 0 and the exporter log shows Error pinging mysqld: Error 1045: Access denied for user 'exporter'@'localhost' – Wrong password in the cnf file, or the file is unreadable by the service user; sudo -u mysqld_exporter cat /etc/.mysqld_exporter.cnf.
Error 1226: User 'exporter' has exceeded the 'max_user_connections' resource – Prometheus is scraping faster than the exporter finishes, or two Prometheus servers scrape the same target. Raise MAX_USER_CONNECTIONS or lengthen scrape_interval.
Target shows context deadline exceeded in Prometheus – A collector is too slow, typically info_schema.tables on a large server. Remove it or restrict it with --collect.info_schema.tables.databases.
mysql_slave_status_* metrics are missing on a replica – --collect.slave_status is off, or the account lacks REPLICATION CLIENT.
Error 1146: Table 'performance_schema.events_statements_summary_by_digest' doesn't exist – Performance Schema is disabled on the server; drop the perf_schema.* collectors or enable it.

Next I'd tune the alert thresholds per server; the ones above are just a start.