Linux VPS MySQL Performance Monitoring Tutorial: Complete Setup with Metrics, Alerts, and Query Analysis for 2026

Installing and Configuring MySQL Performance Monitoring Tools

MySQL performance monitoring becomes critical when your database serves high-traffic applications. Poor query performance, memory exhaustion, or connection bottlenecks can destroy user experience within minutes.

This tutorial walks through setting up comprehensive monitoring on Ubuntu 24.04 VPS. You'll configure MySQL's built-in performance tools, deploy Prometheus with MySQL Exporter, and create Grafana dashboards.

These tools show exactly what's happening inside your database.

Prerequisites and Environment Setup

You need root access to an Ubuntu 24.04 VPS with MySQL 8.0 installed. The monitoring stack requires approximately 2GB RAM and 10GB disk space for metrics storage.

First, verify your MySQL version and enable the Performance Schema:

mysql --version
sudo mysql -u root -p

Inside the MySQL shell, check Performance Schema status:

SELECT @@performance_schema;
SHOW VARIABLES LIKE 'performance_schema%';

If Performance Schema shows OFF, edit your MySQL configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines under the [mysqld] section:

[mysqld]
performance_schema = ON
performance-schema-instrument='statement/%=ON'
performance-schema-consumer-events-statements-current=ON
performance-schema-consumer-events-statements-history=ON

Restart MySQL to apply changes:

sudo systemctl restart mysql
sudo systemctl status mysql

MySQL Performance Schema Configuration

Performance Schema captures detailed metrics about query execution, table access patterns, and resource usage. Enable key consumers for comprehensive monitoring.

Connect to MySQL and enable statement monitoring:

mysql -u root -p
UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_statements%';

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE 'statement/%';

Enable table I/O and lock monitoring:

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'events_waits%';

UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES' 
WHERE NAME LIKE 'wait/io/table%';

These changes take effect immediately without restarting MySQL. Verify active instruments:

SELECT NAME, ENABLED, TIMED 
FROM performance_schema.setup_instruments 
WHERE NAME LIKE 'statement/%' AND ENABLED = 'YES' 
LIMIT 10;

Installing MySQL Exporter for Prometheus

MySQL Exporter collects metrics from Performance Schema and exposes them for Prometheus scraping. Download the latest release from GitHub:

cd /opt
sudo wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
sudo tar xvf mysqld_exporter-0.15.1.linux-amd64.tar.gz
sudo mv mysqld_exporter-0.15.1.linux-amd64 mysqld_exporter
sudo chown -R root:root mysqld_exporter

Create a dedicated MySQL user for the exporter with minimal privileges:

mysql -u root -p
CREATE USER 'mysqld_exporter'@'localhost' IDENTIFIED BY 'SecureExporterPass123!';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'mysqld_exporter'@'localhost';
FLUSH PRIVILEGES;
EXIT;

Create the exporter configuration file:

sudo mkdir -p /etc/mysqld_exporter
sudo nano /etc/mysqld_exporter/.my.cnf

Add the MySQL connection details:

[client]
user=mysqld_exporter
password=SecureExporterPass123!
host=localhost
port=3306

Set proper permissions on the config file:

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

Configuring MySQL Exporter as a System Service

Create a systemd service file for automatic startup and management:

sudo nano /etc/systemd/system/mysqld_exporter.service

Add the service configuration:

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

[Service]
Type=simple
ExecStart=/opt/mysqld_exporter/mysqld_exporter \
  --config.my-cnf=/etc/mysqld_exporter/.my.cnf \
  --collect.info_schema.processlist \
  --collect.info_schema.innodb_metrics \
  --collect.info_schema.query_response_time \
  --collect.info_schema.userstats \
  --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.binlog_size \
  --collect.perf_schema.replication_group_members
User=root
Group=root
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

Enable and start the service:

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

Test the exporter by checking its metrics endpoint:

curl http://localhost:9104/metrics | grep mysql_up

You should see mysql_up 1 indicating successful connection to MySQL.

Installing and Configuring Prometheus

Prometheus stores time-series metrics and powers alerting. Install it alongside MySQL Exporter:

cd /opt
sudo wget https://github.com/prometheus/prometheus/releases/download/v2.48.0/prometheus-2.48.0.linux-amd64.tar.gz
sudo tar xvf prometheus-2.48.0.linux-amd64.tar.gz
sudo mv prometheus-2.48.0.linux-amd64 prometheus
sudo chown -R root:root prometheus

Create Prometheus configuration to scrape MySQL metrics:

sudo nano /opt/prometheus/prometheus.yml

Add this configuration:

global:
  scrape_interval: 15s
  evaluation_interval: 15s

rule_files:
  - "mysql_alerts.yml"

scrape_configs:
  - job_name: 'prometheus'
    static_configs:
      - targets: ['localhost:9090']
  
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    scrape_interval: 10s
    scrape_timeout: 5s

Create the Prometheus systemd service:

sudo nano /etc/systemd/system/prometheus.service

[Unit]
Description=Prometheus Server
Wants=network-online.target
After=network-online.target

[Service]
Type=simple
ExecStart=/opt/prometheus/prometheus \
  --config.file=/opt/prometheus/prometheus.yml \
  --storage.tsdb.path=/opt/prometheus/data \
  --web.console.templates=/opt/prometheus/consoles \
  --web.console.libraries=/opt/prometheus/console_libraries \
  --web.listen-address=0.0.0.0:9090 \
  --web.enable-lifecycle
User=root
Group=root
Restart=always
RestartSec=5

[Install]
WantedBy=multi-user.target

Start Prometheus:

sudo systemctl daemon-reload
sudo systemctl enable prometheus
sudo systemctl start prometheus

Verify Prometheus is collecting MySQL metrics by visiting http://your-vps-ip:9090 and checking the targets page.

Setting Up MySQL Performance Alerts

Create alerting rules for common MySQL performance issues. These rules trigger when specific thresholds are exceeded:

sudo nano /opt/prometheus/mysql_alerts.yml

Add these critical performance alerts:

groups:
- name: mysql-performance
  rules:
  - alert: MySQLHighConnections
    expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL connection usage high ({{ $value | humanizePercentage }})"
      description: "MySQL is using {{ $value | humanizePercentage }} of available connections"

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

  - alert: MySQLHighInnoDBBufferPoolWait
    expr: rate(mysql_global_status_innodb_buffer_pool_wait_free[5m]) > 0
    for: 1m
    labels:
      severity: critical
    annotations:
      summary: "MySQL InnoDB buffer pool exhausted"
      description: "MySQL InnoDB buffer pool has no free pages available"

  - alert: MySQLHighTableLockWait
    expr: rate(mysql_global_status_table_locks_waited[5m]) > 1
    for: 3m
    labels:
      severity: warning
    annotations:
      summary: "MySQL table lock contention ({{ $value }} locks/sec)"
      description: "MySQL is experiencing {{ $value }} table lock waits per second"

Restart Prometheus to load the new rules:

sudo systemctl restart prometheus

Installing Grafana for Visualization

Grafana provides rich dashboards for MySQL metrics visualization. Add the official repository:

sudo apt update
sudo apt install -y software-properties-common
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 -a /etc/apt/sources.list.d/grafana.list

Install and start Grafana:

sudo apt update
sudo apt install grafana
sudo systemctl enable grafana-server
sudo systemctl start grafana-server

Access Grafana at http://your-vps-ip:3000 using admin/admin credentials. Change the default password immediately.

Add Prometheus as a data source:

Navigate to Configuration > Data Sources
Click "Add data source" and select Prometheus
Set URL to http://localhost:9090
Click "Save & Test"

Creating MySQL Performance Dashboards

Import the official MySQL dashboard from Grafana's community repository. Use dashboard ID 7362 for comprehensive MySQL monitoring.

Key panels to monitor include:

Query Per Second (QPS) and Transaction Per Second (TPS)
Connection utilization and active threads
InnoDB buffer pool efficiency and dirty page ratio
Slow query count and average query execution time
Table lock wait time and deadlock frequency
Binary log space usage and replication lag

Create custom panels for application-specific metrics. Add a panel showing top 10 slowest statements:

Query: topk(10, mysql_perf_schema_events_statements_total{quantile="0.95"})

Analyzing Slow Query Performance

Enable MySQL's slow query log for detailed analysis of problematic queries:

mysql -u root -p
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Make these changes permanent by editing MySQL configuration:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add these lines:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Analyze slow queries using Performance Schema:

SELECT 
    DIGEST_TEXT,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT/1000000000 as avg_exec_time_sec,
    SUM_TIMER_WAIT/1000000000 as total_exec_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

This query identifies the most time-consuming statements in your database.

Ready to deploy professional MySQL monitoring on your production environment? HostMyCode VPS hosting provides the reliable infrastructure you need for database performance monitoring. Our managed VPS hosting includes monitoring tools and expert support to keep your MySQL databases running smoothly.

Troubleshooting Common Monitoring Issues

If MySQL Exporter fails to connect, check user permissions:

mysql -u mysqld_exporter -p
SHOW GRANTS FOR 'mysqld_exporter'@'localhost';

For missing Performance Schema data, verify instrument configuration:

SELECT NAME, ENABLED 
FROM performance_schema.setup_instruments 
WHERE NAME LIKE 'statement/%' AND ENABLED = 'NO';

High memory usage in Performance Schema can be controlled by adjusting table sizes:

SHOW VARIABLES LIKE 'performance_schema_max%';

If Grafana dashboards show no data, verify Prometheus is successfully scraping metrics:

curl http://localhost:9104/metrics | grep -c "mysql_"

Frequently Asked Questions

How much overhead does MySQL performance monitoring add?

Performance Schema typically adds 2-5% CPU overhead and 100-200MB RAM usage. The impact scales with query volume and enabled instruments.

What's the recommended retention period for MySQL metrics?

Store high-resolution metrics (10-15 second intervals) for 7 days, then aggregate to 5-minute intervals for 90 days. This balances storage costs with troubleshooting needs.

Can I monitor MySQL replication with these tools?

Yes, MySQL Exporter collects replication metrics including slave lag, SQL thread status, and relay log position. Enable with --collect.slave_status flag.

How do I monitor multiple MySQL instances on one VPS?

Run separate MySQL Exporter instances on different ports, each with its own configuration file pointing to different MySQL sockets or ports.

What alerts should I set up for production MySQL servers?

Monitor connection utilization (>80%), slow query rate (>0.1/sec), replication lag (>30 seconds), and InnoDB buffer pool hit ratio (<95%). Adjust thresholds based on your application requirements.