Top key metrics for monitoring MySQL

Monitoring MySQL with Prometheus is easy to do thanks to the MySQL Prometheus Exporter. MySQL doesn’t need an introduction – it’s one of the most used relational databases in the world, and it’s also open-source!

Being such a popular database means that the community behind it is also huge. So don’t worry: you won’t be alone.

In this article, you’ll learn how to start monitoring a MySQL Server with Prometheus – from configuring the exporter to what are the top metrics, with alert examples. If you’re running MySQL with RDS, don’t miss our top 5 key metrics for monitoring Amazon RDS.

Setting things up

Before deploying the exporter in the cluster, you have to create the user and password for the exporter in the database. Enter your MySQL database and execute the following queries:

CREATE USER 'exporter'@'%' IDENTIFIED BY 'YOUR-PASSWORD' WITH MAX_USER_CONNECTIONS 3;

GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'%';

Substitute the user name and the password in the SQL sentence for your custom ones.

Once you have the user and password for the exporter in the database, you have to create a mysql-exporter.cnf file with the credentials of the exporter. Also, in this file you will include the url to connect your MySQL database in the field host:

[client]
user = exporter
password = "YOUR-PASSWORD"
host=YOUR-DB-IP

In your Kubernetes cluster, create the secret with the mysql-exporter.cnf file. This file will be mounted in the exporter to authenticate with the database:

kubectl create secret generic mysql-exporter \
  --from-file=.my.cnf=./mysql-exporter.cnf

How to deploy the MySQL Prometheus Exporter

To start monitoring MySQL with Prometheus, you’ll need to deploy the MySQL Exporter in your cluster.

To deploy the exporter, you can use the example files provided below.

apiVersion: apps/v1
kind: Deployment
metadata:
  name: mysql-exporter-exporter
spec:
  selector:
    matchLabels:
      app: mysql-exporter
  replicas: 1
  template:
    metadata:
      labels:
        app: mysql-exporter
      annotations:
        prometheus.io/scrape: "true"
        prometheus.io/port: "9104"
    spec:
      containers:
        - name: mysql-exporter
          image: prom/mysqld-exporter:latest
          args:
            - --config.my-cnf=/tmp/.my.cnf
          ports:
            - containerPort: 9104
          volumeMounts:
            - name: my-cnf
              mountPath: /tmp/.my.cnf
              subPath: .my.cnf
          resources:
            limits:
              memory: "256Mi"
              cpu: "256m"
      volumes:
        - name: my-cnf
          secret:
            defaultMode: 420
            secretName: mysql-exporter
            items:
              - key: .my.cnf
                path: .my.cnf

After applying the Deployment for the exporter, Prometheus will automatically start scraping the MySQL metrics as it already has the standard annotations.

Top 5 metrics to monitor MySQL with Prometheus

#1 Availability

There are two metrics that monitor the availability of your MySQL instance. The first one is mysql_up. If this metric equals zero, the exporter cannot access the database, which can be a symptom of an unhealthy or failed database.

You can create an alert to notify you in case of a database down with the following query:

mysql_up == 0

Also, the metric mysql_global_status_uptime can give you an idea of quick restarts that can pass under the radar of the previous metric. You can create an alert with the following Prometheus query to detect instances that have an uptime of less than half an hour:

mysql_global_status_uptime < 1800

#2 Connections

One of the main sources of errors in databases are connection errors. The metric mysql_global_status_connection_errors_total allows you to detect when the database is generating these errors:

rate(mysql_global_status_connection_errors_total[5m])

One common cause of connection error is the lack of available connections. You can check the percent of available connections used with this Prometheus query:

100 * mysql_global_status_threads_connected / mysql_global_variables_max_connections

If this value is near 100, the database can start refusing new connections. A solution for this can be increasing the number of maximum connections. Before doing that, be sure to check the available number of open files in the system. You can check it with the following Prometheus query:

mysql_global_variables_open_files_limit - mysql_global_variables_innodb_open_files

Want to dig deeper into PromQL? Read our getting started with PromQL guide to learn how Prometheus stores data, and how to use PromQL functions and operators.

#3 Slow queries

Like many databases, MySQL keeps a log for slow queries. The number of entries in this log can be consulted with the metric mysql_global_status_slow_queries. You can create an alert with the following Prometheus query to notify when there are new entries in the slow queries log, which can mean that there is a performance issue:

rate(mysql_global_status_slow_queries[5m])

#4 Cache Hit Rate

MySQL uses in-memory cache to optimize the disk read and write operations. A low hit rate in the cache will impact the performance of the database. This Prometheus query will give you the value of the open tables cache hit rate:

rate(mysql_global_status_table_open_cache_hits[5m]) /
(rate(mysql_global_status_table_open_cache_hits[5m]) + rate(mysql_global_status_table_open_cache_misses[5m]))

Also, you can monitor the buffer pool cache with the following promQL query:

(rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]) -
rate(mysql_global_status_innodb_buffer_pool_reads[5m])) / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]))

You can tune the buffer size for open tables and pool caches in the MySQL configuration, but keep in mind that doing so will affect the memory usage of your instance. Be sure that there is enough memory. You can check the available memory of the container using cAdvisor metrics:

sum by (namespace,pod,container)((container_memory_usage_bytes{container!="POD",container!=""} -  on (namespace,pod,container) avg by (namespace,pod,container)(kube_pod_container_resource_limits{resource="memory"})) \* -1 >0 ) / (1024\*1024\*1024)

If your MySQL instance is in AWS RDS, check our blog post on how to monitor your RDS instance

#5 Query types

You can get statistics of the usage of each kind of SELECT query in your MySQL database, using the metric that gives information on each of them:

Full join: mysql_global_status_select_full_join
Full range join: mysql_global_status_select_full_range_join
Select range check (joins without keys that check for key usage after each row): mysql_global_status_select_range_check
Select scan: mysql_global_status_select_scan All of them are counters, so remember to use the function rate in Prometheus queries, like this:

rate(mysql_global_status_select_full_join[5m])

Similarly, you can also have statistics on the use of sorting queries in MySQL:

Sort rows: mysql_global_status_sort_rows
The number of sorts that were done using ranges: mysql_global_status_sort_range
Sort merge (The number of merge passes that the sort algorithm has had to do): mysql_global_status_sort_merge_passes
The number of sorts that were done by scanning the table: mysql_global_status_sort_scan

Next steps

In this article, you learned how easy it is to start monitoring MySQL with Prometheus. You just need the right exporter and configuration.
You also learned the top Prometheus queries you should have in mind when monitoring MySQL with Prometheus.

If you want to get some inspiration for writing Prometheus queries, you can take a look at these examples for monitoring Kubernetes, or download our PromQL getting started cheatsheet.