Configure MariaDB 11.6 performance monitoring with Prometheus and Grafana dashboards

Install MariaDB 11.6

Start by installing MariaDB 11.6 from the official repository to ensure you get the latest performance features.

sudo apt update
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
sudo apt install -y mariadb-server mariadb-client

sudo dnf update -y
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash
sudo dnf install -y mariadb-server mariadb-client

Secure MariaDB installation

Start MariaDB and run the security script to remove default accounts and set a root password.

sudo systemctl enable --now mariadb
sudo mysql_secure_installation

Create monitoring user

Create a dedicated MySQL user for Prometheus monitoring with minimal required privileges.

sudo mysql -u root -p

CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'StrongMonitoringPass123!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON . TO 'exporter'@'localhost';
GRANT SELECT ON performance_schema.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Configure MariaDB for monitoring

Enable performance monitoring features in MariaDB configuration.

[mysqld]
Performance Schema
performance_schema = ON
performance_schema_consumer_events_statements_current = ON
performance_schema_consumer_events_statements_history = ON
performance_schema_consumer_events_statements_history_long = ON

Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

General monitoring
general_log = 0
log_error = /var/log/mysql/error.log
innodb_monitor_enable = all

sudo systemctl restart mariadb

Install mysqld_exporter

Download and install the official Prometheus MySQL exporter for MariaDB metrics collection.

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.1.linux-amd64.tar.gz
sudo cp mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter /usr/local/bin/
sudo chmod +x /usr/local/bin/mysqld_exporter

Create mysqld_exporter configuration

Configure the exporter with database connection credentials and security settings.

sudo useradd --no-create-home --shell /bin/false mysqld_exporter

[client]
user=exporter
password=StrongMonitoringPass123!
host=localhost
port=3306

sudo mkdir -p /etc/mysqld_exporter
sudo chown mysqld_exporter:mysqld_exporter /etc/mysqld_exporter
sudo chmod 600 /etc/mysqld_exporter/.my.cnf

Create mysqld_exporter systemd service

Set up mysqld_exporter as a systemd service for automatic startup and process management.

[Unit]
Description=MySQL Exporter
Wants=network-online.target
After=network-online.target
After=mariadb.service

[Service]
User=mysqld_exporter
Group=mysqld_exporter
Type=simple
ExecStart=/usr/local/bin/mysqld_exporter \
  --config.my-cnf=/etc/mysqld_exporter/.my.cnf \
  --collect.global_status \
  --collect.info_schema.innodb_metrics \
  --collect.auto_increment.columns \
  --collect.info_schema.processlist \
  --collect.binlog_size \
  --collect.info_schema.tablestats \
  --collect.global_variables \
  --collect.info_schema.query_response_time \
  --collect.info_schema.userstats \
  --collect.info_schema.clientstats \
  --collect.info_schema.tables \
  --collect.perf_schema.tablelocks \
  --collect.perf_schema.file_events \
  --collect.perf_schema.eventswaits \
  --collect.perf_schema.indexiowaits \
  --collect.perf_schema.tableiowaits \
  --collect.slave_status \
  --web.listen-address=0.0.0.0:9104

SyslogIdentifier=mysqld_exporter
Restart=always

[Install]
WantedBy=multi-user.target

Start and enable mysqld_exporter

Enable the mysqld_exporter service to start on boot and verify it's collecting metrics.

sudo systemctl daemon-reload
sudo systemctl enable --now mysqld_exporter
sudo systemctl status mysqld_exporter

Install and configure Prometheus

Install Prometheus to collect and store MariaDB metrics from mysqld_exporter.

sudo apt install -y prometheus

sudo dnf install -y prometheus2

Configure Prometheus for MariaDB monitoring

Add mysqld_exporter as a scrape target in Prometheus configuration.

global:
  scrape_interval: 15s
  evaluation_interval: 15s

scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'mysqld'
    static_configs:
      - targets: ['localhost:9104']
    scrape_interval: 10s
    metrics_path: /metrics
    params:
      collect[]: ['mysql.global_status', 'mysql.global_variables']

  - job_name: 'node'
    static_configs:
      - targets: ['localhost:9100']
    scrape_interval: 15s

sudo systemctl restart prometheus
sudo systemctl enable prometheus

Install and configure Grafana

Install Grafana for creating MariaDB performance dashboards and alerting.

wget -q -O - https://packages.grafana.com/gpg.key | sudo apt-key add -
echo "deb https://packages.grafana.com/oss/deb stable main" | sudo tee /etc/apt/sources.list.d/grafana.list
sudo apt update
sudo apt install -y grafana

sudo dnf install -y https://dl.grafana.com/oss/release/grafana-10.2.2-1.x86_64.rpm

Configure Grafana data source

Start Grafana and configure Prometheus as a data source for MariaDB metrics.

sudo systemctl enable --now grafana-server

Access Grafana at http://your-server:3000 with username admin and password admin. Add Prometheus data source:

• URL: http://localhost:9090
• Access: Server (default)
• HTTP Method: GET

Import MariaDB dashboard

Create a comprehensive MariaDB monitoring dashboard with key performance metrics.

{
  "dashboard": {
    "id": null,
    "title": "MariaDB Performance Monitor",
    "tags": ["mariadb", "mysql", "database"],
    "timezone": "browser",
    "panels": [
      {
        "id": 1,
        "title": "MySQL Uptime",
        "type": "stat",
        "targets": [
          {
            "expr": "mysql_global_status_uptime",
            "legendFormat": "Uptime"
          }
        ],
        "gridPos": {"h": 4, "w": 6, "x": 0, "y": 0}
      },
      {
        "id": 2,
        "title": "Connections",
        "type": "graph",
        "targets": [
          {
            "expr": "rate(mysql_global_status_connections[5m])",
            "legendFormat": "Connections/sec"
          },
          {
            "expr": "mysql_global_status_threads_connected",
            "legendFormat": "Connected Threads"
          }
        ],
        "gridPos": {"h": 8, "w": 12, "x": 0, "y": 4}
      },
      {
        "id": 3,
        "title": "Query Rate",
        "type": "graph",
        "targets": [
          {
            "expr": "rate(mysql_global_status_queries[5m])",
            "legendFormat": "Queries/sec"
          },
          {
            "expr": "rate(mysql_global_status_questions[5m])",
            "legendFormat": "Questions/sec"
          }
        ],
        "gridPos": {"h": 8, "w": 12, "x": 12, "y": 4}
      },
      {
        "id": 4,
        "title": "InnoDB Buffer Pool",
        "type": "graph",
        "targets": [
          {
            "expr": "mysql_global_status_innodb_buffer_pool_pages_data * mysql_global_variables_innodb_page_size",
            "legendFormat": "Data Pages"
          },
          {
            "expr": "mysql_global_status_innodb_buffer_pool_pages_free * mysql_global_variables_innodb_page_size",
            "legendFormat": "Free Pages"
          }
        ],
        "gridPos": {"h": 8, "w": 12, "x": 0, "y": 12}
      }
    ],
    "time": {
      "from": "now-1h",
      "to": "now"
    },
    "refresh": "30s"
  }
}

Import this dashboard through Grafana UI: Dashboard → Import → Paste JSON.

Configure alerting rules

Set up Prometheus alerting rules for critical MariaDB metrics and performance thresholds.

groups:
  - name: mariadb
    rules:
      - alert: MariaDBDown
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MariaDB instance is down"
          description: "MariaDB database on {{ $labels.instance }} has been down for more than 1 minute"

      - alert: MariaDBHighConnections
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MariaDB connection usage high"
          description: "MariaDB is using {{ $value | humanizePercentage }} of max connections"

      - alert: MariaDBSlowQueries
        expr: rate(mysql_global_status_slow_queries[5m]) > 0.1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MariaDB slow queries detected"
          description: "MariaDB is experiencing {{ $value }} slow queries per second"

      - alert: MariaDBInnoDBBufferPoolLow
        expr: mysql_global_status_innodb_buffer_pool_pages_free / mysql_global_status_innodb_buffer_pool_pages_total < 0.1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MariaDB InnoDB buffer pool usage high"
          description: "InnoDB buffer pool free pages below 10%: {{ $value | humanizePercentage }}"

      - alert: MariaDBReplicationLag
        expr: mysql_slave_lag_seconds > 30
        for: 2m
        labels:
          severity: critical
        annotations:
          summary: "MariaDB replication lag high"
          description: "Replication lag is {{ $value }} seconds on {{ $labels.instance }}"

      - alert: MariaDBTableLocks
        expr: rate(mysql_global_status_table_locks_waited[5m]) > 0.1
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "MariaDB table lock waits detected"
          description: "Table locks waited: {{ $value }} per second"

Update Prometheus configuration for alerting

Include the MariaDB alerting rules in Prometheus configuration.

global:
  scrape_interval: 15s
  evaluation_interval: 15s

rule_files:
  - "/etc/prometheus/mariadb-alerts.yml"

scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']

  - job_name: 'mysqld'
    static_configs:
      - targets: ['localhost:9104']
    scrape_interval: 10s
    metrics_path: /metrics

  - job_name: 'node'
    static_configs:
      - targets: ['localhost:9100']

sudo systemctl reload prometheus

Install node_exporter for system metrics

Add system-level monitoring to correlate database performance with server resources.

wget https://github.com/prometheus/node_exporter/releases/download/v1.7.0/node_exporter-1.7.0.linux-amd64.tar.gz
tar xzf node_exporter-1.7.0.linux-amd64.tar.gz
sudo cp node_exporter-1.7.0.linux-amd64/node_exporter /usr/local/bin/
sudo useradd --no-create-home --shell /bin/false node_exporter

Create node_exporter service

Set up node_exporter as a systemd service for system metrics collection.

[Unit]
Description=Node Exporter
Wants=network-online.target
After=network-online.target

[Service]
User=node_exporter
Group=node_exporter
Type=simple
ExecStart=/usr/local/bin/node_exporter --web.listen-address=:9100
SyslogIdentifier=node_exporter
Restart=always

[Install]
WantedBy=multi-user.target

sudo systemctl daemon-reload
sudo systemctl enable --now node_exporter

Enable query response time monitoring

Configure MariaDB to collect detailed query performance statistics.

sudo mysql -u root -p

INSTALL SONAME 'query_response_time';
SET GLOBAL query_response_time_stats = ON;
SET GLOBAL query_response_time_range_base = 10;

Configure custom mysqld_exporter collectors

Enable additional performance schema collectors for detailed monitoring.

[client]
user=exporter
password=StrongMonitoringPass123!
host=localhost
port=3306

[mysqld_exporter]
collect.global_status=true
collect.global_variables=true
collect.slave_status=true
collect.info_schema.processlist=true
collect.info_schema.tables=true
collect.info_schema.innodb_tablespaces=true
collect.info_schema.innodb_metrics=true
collect.perf_schema.tablelocks=true
collect.perf_schema.eventswaits=true
collect.perf_schema.file_events=true

Restrict monitoring user access

Limit the monitoring user to specific hosts and minimal privileges.

-- Drop existing user if needed
DROP USER 'exporter'@'localhost';

-- Create user with host restriction
CREATE USER 'exporter'@'127.0.0.1' IDENTIFIED BY 'StrongMonitoringPass123!';
GRANT PROCESS, REPLICATION CLIENT ON . TO 'exporter'@'127.0.0.1';
GRANT SELECT ON performance_schema.* TO 'exporter'@'127.0.0.1';
FLUSH PRIVILEGES;

Secure configuration files

Protect credential files from unauthorized access.

sudo chmod 600 /etc/mysqld_exporter/.my.cnf
sudo chown mysqld_exporter:mysqld_exporter /etc/mysqld_exporter/.my.cnf

Verify permissions
ls -la /etc/mysqld_exporter/

Configure efficient scraping

Adjust scrape intervals based on your monitoring needs and database load.

scrape_configs:
  - job_name: 'mysqld'
    static_configs:
      - targets: ['localhost:9104']
    scrape_interval: 30s
    scrape_timeout: 10s
    metrics_path: /metrics
    params:
      collect[]:
        - mysql.global_status
        - mysql.global_variables
        - mysql.slave_status
        - mysql.info_schema.processlist

Optimize Prometheus retention

Configure appropriate data retention for MariaDB metrics storage.

ARGS="--config.file=/etc/prometheus/prometheus.yml --storage.tsdb.path=/var/lib/prometheus/ --storage.tsdb.retention.time=30d --storage.tsdb.retention.size=10GB --web.console.libraries=/etc/prometheus/console_libraries --web.console.templates=/etc/prometheus/consoles --web.enable-lifecycle"

sudo systemctl restart prometheus