Review: MySQL Exporter

Exporter reviews, Technology / 글쓴이

In our new series, we review best-fit exporters for monitoring metrics that are used by NexClipper. Learn all about specific exporters, their most important metrics as well as recommended alert rules. We also discuss the related Grafana dashboard and Helm Chart for each specific exporter that we introduce. To kick off the series we will be introducing the MySQL exporter – read on to find out more.

About MySQL

Since databases are such a critical resource that downtime can cause significant financial and reputation losses, monitoring is a must. The MySQL exporter is required to monitor and expose MySQL metrics. The MySQL exporter queries MySQL, scraps the data, and exposes the metrics to a Kubernetes service endpoint that can further be scrapped by Prometheus to ingest the time series data. For monitoring MySQL, an external Prometheus exporter can be used, which is maintained by the Prometheus Community. 

On deployment, this exporter scraps OPlog, replica set, server status, sharding, and storage engine metrics. It handles all metrics exposed by MySQL monitoring commands. It loops over all the fields exposed in diagnostic commands and tries to get data from them. The MySQL exporter helps users get crucial and continuous information about the database which is difficult to get from MySQL directly.

How do you set up an exporter for Prometheus?

With the latest version of Prometheus (2.33 as of February 2022), these are the ways to set up a Prometheus exporter: 

Method 1 – Native

Supported by Prometheus since the beginning
To set up an exporter in native way a Prometheus config needs to be updated to add the target.
A sample configuration: 

# scrape_config job
  - job_name: mysql-staging
    scrape_interval: 45s
    scrape_timeout:  30s
    metrics_path: "/metrics"
    static_configs:
    - targets:
      - <Mysql exporter endpoint>
Method 2 – Service Discovery

This method is applicable for Kubernetes deployment only
With this, a default scrap config can be added to the prometheus.yaml file and an annotation can be added to the exporter service. With this, Prometheus will automatically start scrapping the data from the services with the mentioned path.

Prometheus.yaml

     - job_name: kubernetes-services   
        scrape_interval: 15s
        scrape_timeout: 10s
        kubernetes_sd_configs:
        - role: service
        relabel_configs:
        # Example relabel to scrape only endpoints that have
        # prometheus.io/scrape: "true" annotation.
        - source_labels: [__meta_kubernetes_service_annotation_prometheus_io_scrape]
          action: keep
          regex: true
        #  prometheus.io/path: "/scrape/path" annotation.
        - source_labels: [__meta_kubernetes_service_annotation_prometheus_io_path]
          action: replace
          target_label: __metrics_path__
          regex: (.+)
        #  prometheus.io/port: "80" annotation.
        - source_labels: [__address__, __meta_kubernetes_service_annotation_prometheus_io_port]
          action: replace
          target_label: __address__
          regex: (.+)(?::\d+);(\d+)
          replacement: $1:$2

Exporter service:

 annotations:
    prometheus.io/path: /metrics
    prometheus.io/scrape: "true"
Method 3 – Prometheus Operator

Setting up a service monitor
The Prometheus operator supports an automated way of scraping data from the exporters by setting up a service monitor Kubernetes object. A sample service monitor for MySQL can be found here.
These are the necessary steps:

Step 1

Add/update Prometheus operator’s selectors. By default, the Prometheus operator comes with empty selectors which will select every service monitor available in the cluster for scrapping the data.

To check your Prometheus configuration:

Kubectl get prometheus -n <namespace> -o yaml

A sample output will look like this.

ruleNamespaceSelector: {}
    ruleSelector:
      matchLabels:
        app: kube-prometheus-stack
        release: kps
    scrapeInterval: 1m
    scrapeTimeout: 10s
    securityContext:
      fsGroup: 2000
      runAsGroup: 2000
      runAsNonRoot: true
      runAsUser: 1000
    serviceAccountName: kps-kube-prometheus-stack-prometheus
    serviceMonitorNamespaceSelector: {}
    serviceMonitorSelector:
      matchLabels:
        release: kps

Here you can see that this Prometheus configuration is selecting all the service monitors with the label release = kps

So with this, if you are modifying the default Prometheus operator configuration for service monitor scrapping, make sure you use the right labels in your service monitor as well.

Step 2

Add a service monitor and make sure it has a matching label and namespace for the Prometheus service monitor selectors (serviceMonitorNamespaceSelector & serviceMonitorSelector).

To enable service monitor run:

helm install <RELEASE_NAME> prometheus-community/prometheus-mysql-exporter --set serviceMonitor.enabled=true

Sample configuration:

apiVersion: monitoring.coreos.com/v1
kind: ServiceMonitor
metadata:
  annotations:
    meta.helm.sh/release-name: mysql-exporter
    meta.helm.sh/release-namespace: monitor
  generation: 1
  labels:
    app: prometheus-mysql-exporter
    app.kubernetes.io/managed-by: Helm
    heritage: Helm
    release: kps
  name: mysql-exporter-prometheus-mysql-exporter
  namespace: monitor
spec:
  endpoints:
  - interval: 15s
    port: mysql-exporter
  selector:
    matchLabels:
      app: prometheus-mysql-exporter
      release: mysql-exporter

Here you can see we have a matching label on the service monitor release = kps that we are specifying in the Prometheus operator scrapping configuration.

How to set up the MySQL exporter with sidecar exporter

With the Bitnami Helm charts, the MySQL exporter can be deployed as a sidecar container in the same pod.
To enable the side car:

helm install mysql bitnami/mysql --set metrics.enabled=true

More details can be found here.

After enabling, sidecar Prometheus metrics are exported by the built-in container on the “/metrics” endpoint that can be scrapped by Prometheus. Once metrics are enabled, Helm will automatically add the annotation to the mysql svc.
Annotation:

annotations:
    prometheus.io/path: /metrics
    prometheus.io/scrape: "true"

Now Prometheus will automatically start scraping the data if the svc discovery is enabled.
Prometheus configuration for svc discovery: 

 - job_name: "kubernetes-service-endpoints"

    kubernetes_sd_configs:
      - role: endpoints

Metrics

The following ones are handpicked metrics for the MySQL exporter that will provide insights into MySQL.

  1. MySQL is up
    This shows whether the last scrape of metrics from MySQL was able to connect to the server.
    ➡ The key of the exporter metric is “mysql_up”
    ➡ The value of the metric is a boolean –  1 or 0 which symbolizes if MySQL is up or down respectively (1 for yes, 0 for no) 
  1. Too many connections
    The permitted number of connections is controlled by the max_connections system variable. If all available connections are in use by other clients, the new clients trying to connect will encounter “too many connections” errors when attempting to connect to the MySQL server. Therefore, it is important to monitor the number of connected clients.
    ➡ The metric “ mysql_global_status_threads_connected” shows the total active connections on MySQL
    ➡ The number should be calculated based on “mysql_global_variables_max_connections” which is the maximum number of connections configured
  1.  MySQL slow queries
    Slow queries cause/indicate performance issues. Like many databases, MySQL keeps a log for these slow queries. The number of entries in this log can be consulted with the metric key below.
    ➡ The metric key is “mysql_global_status_slow_query”
    ➡ The value provided will be the count of slow queries
  1. Buffer pool cache
    MySQL InnoDB (default storage engine) uses an area of memory called the buffer pool to cache data for tables and indexes. It uses an in-memory cache to optimize the disk read and write operations. Buffer pool metrics and other resource metrics are primarily useful for investigating performance issues. 
    ➡ The metric “mysql_global_status_innodb_buffer_pool_reads” shows the number of logical reads that InnoDB could not satisfy from the buffer pool and had to read directly from the disk
  1. Total MySQL InnoDB log waits
    This metric provides insight into the number of times that the log buffer was too small and a wait was required for it to be flushed before continuing.
    ➡ The metric  “mysql_global_status_innodb_log_waits” indicates InnoDB log writes are stalling

Alerting

After digging into all the valuable metrics, this section explains in detail how we can get critical alerts with the MySQL exporter.

PromQL is a query language for the Prometheus monitoring system. It is designed for building powerful yet simple queries for graphs, alerts, or derived time series (aka recording rules). PromQL is designed from scratch and has zero common grounds with other query languages used in time series databases, such as SQL in TimescaleDB, InfluxQL, or Flux. More details can be found here.

Prometheus comes with a built-in Alert Manager that is responsible for sending alerts (could be email, Slack, or any other supported channel) when any of the trigger conditions is met. Alerting rules allow users to define alerts based on Prometheus query expressions. They are defined based on the available metrics scraped by the exporter. Click here for a good source for community-defined alerts.

A general alert looks as follows:

– alert:(Alert Name)
expr: (Metric exported from exporter) >/</==/<=/=> (Value)
for: (wait for a certain duration between first encountering a new expression output vector element and counting an alert as firing for this element)
labels: (allows specifying a set of additional labels to be attached to the alert)
annotation: (specifies a set of informational labels that can be used to store longer additional information)

Some of the recommended MySQL alerts are:

  1. Alert – MySQL is Down
 - alert: MysqlDown
    expr: mysql_up == 0
    for: 0m
    labels:
      severity: critical
    annotations:
      summary: MySQL down (instance {{ $labels.instance }})
      description: "MySQL instance is down on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
  1. Alert – MySQL has too many connections
  - alert: MysqlTooManyConnections(>80%)
    expr: avg by (instance) (rate(mysql_global_status_threads_connected[1m])) / avg by (instance) (mysql_global_variables_max_connections) * 100 > 80
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: MySQL too many connections (> 80%) (instance {{ $labels.instance }})
      description: "More than 80% of MySQL connections are in use on {{ $labels.instance }}\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
  1.  Alert – MySQL has slow queries
  - alert: MysqlSlowQueries
    expr: increase(mysql_global_status_slow_queries[1m]) > 0
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: MySQL slow queries (instance {{ $labels.instance }})
      description: "MySQL server mysql has some new slow query.\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
  1.   Alert –  Buffer pool cache
  - alert: BufferPoolHits
    expr: ((rate(mysql_global_status_innodb_buffer_pool_reads[5m])) / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m]) *100 > 2

    for: 0m
    labels:
      severity: critical
    annotations:
      summary: High Read requests rate hitting disk (instance {{ $labels.instance }})
      description: "High number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"
  1.  Alert – InnoDB log writes stalling
 - alert: MysqlInnodbLogWaits
    expr: rate(mysql_global_status_innodb_log_waits[15m]) > 10
    for: 0m
    labels:
      severity: warning
    annotations:
      summary: MySQL InnoDB log waits (instance {{ $labels.instance }})
      description: "MySQL innodb log writes stalling\n  VALUE = {{ $value }}\n  LABELS = {{ $labels }}"

Dashboard

Graphs are easier to understand and more user-friendly than a row of numbers. For this purpose, users can plot their time series data in visualized format using Grafana.

Grafana is an open-source dashboarding tool used for visualizing metrics with the help of customizable and illustrative charts and graphs. It connects very well with Prometheus and makes monitoring easy and informative. Dashboards in Grafana are made up of panels, with each panel running a PromQL query to fetch metrics from Prometheus.
Grafana supports community-driven graphs for most of the widely used software, which can be directly imported to the Grafana Community.

NexClipper uses the MySQL database by the nasskach dashboard, which is widely accepted and has a lot of useful panels.

What is a Panel?

Panels are the most basic component of a dashboard and can display information in various ways, such as gauge, text, bar chart, graph, and so on. They provide information in a very interactive way. Users can view every panel separately and check the value of metrics within a specific time range. 
The values on the panel are queried using PromQL, which is Prometheus Query Language. PromQL is a simple query language used to query metrics within Prometheus. It enables users to query data, aggregate and apply arithmetic functions to the metrics, and then further visualize them on panels.

Here are some examples of panels:

1. Database overview, connection, table locks

2. Aborted connection, network, memory

3. Command & handlers

Helm Chart

Helm chart to install MySQL
$ helm repo add bitnami https://charts.bitnami.com/bitnami
$ helm install my-release  bitnami/Mysql
Installing MySQL Exporter

The MySQL exporter can be deployed in Kubernetes using the Helm chart. The Helm chart used for deployment is from the Prometheus community, which can be found here. To deploy this Helm chart, users can either follow the steps in the above link or refer to the ones outlined below:

$ helm repo add add prometheus-community https://prometheus-community.github.io/helm-charts
$ helm repo update
$ helm install [RELEASE_NAME] prometheus-community/prometheus-mysql-exporter

Some of the common parameters that must be changed in the values file include: 

mysql.host: Defines the Mysql URL you want to monitor.
mysql.user: Mysql connection User.
mysql.pass: Mysql password.

Additional parameters can be changed based on individual needs – such as enabling and disabling collectors, parameters, etc. All these parameters can be tuned via the values.yaml file here.

mysql:
  db: ""
  host: "localhost"
  param: ""
  pass: "password"
  port: 3306
  protocol: ""
  user: "exporter"
  # secret with full DATA_SOURCE_NAME env var as stringdata
  existingSecret: ""
  # secret only containing the password
  existingPasswordSecret:
    name: ""
    key: ""

In addition to the native way of setting up Prometheus monitoring, a service monitor can be deployed (if the Prometheus operator is being used) to scrap the data from MySQL, and Prometheus then scraps the data from the service monitor. With this approach, multiple MySQL can be scrapped without altering the Prometheus configuration. Every MySQL exporter comes with its own service monitor.

In the above-mentioned chart, a service monitor can be deployed by turning it on from the values.yaml file here.

serviceMonitor:
  # enabled should be set to true to enable prometheus-operator discovery of this service
  enabled: false
  # interval is the interval at which metrics should be scraped
  # interval: 30s
  # scrapeTimeout is the timeout after which the scrape is ended
  # scrapeTimeout: 10s
  # additionalLabels is the set of additional labels to add to the ServiceMonitor
  # namespace: monitoring
  additionalLabels: {}
  jobLabel: ""
  targetLabels: []
  podTargetLabels: []
  metricRelabelings: []

Another way of scraping metrics while having the service discovery enabled in Prometheus is by updating the annotation section here with the following:

 annotations:
    prometheus.io/path: /metrics
    prometheus.io/scrape: "true"

This concludes our discussion of the MySQL exporter! If you have any questions, you can reach our team via support@nexclipper.io and stay tuned for further exporter reviews and tips coming soon.