MySQL/MariaDB Performance Tuning on Ubuntu VPS

Your MySQL or MariaDB database is slow. Queries that used to take milliseconds now take seconds. Page loads crawl. Your application's response time has tripled since traffic grew. The instinct is to throw more RAM at the problem — but that might not fix anything. The bottleneck could be a missing index, a misconfigured buffer pool, too many idle connections, or disk I/O contention. Tuning a database means finding the actual bottleneck and fixing it, not blindly changing configuration values you found on Stack Overflow.

This guide walks you through systematic MySQL/MariaDB performance tuning on an Ubuntu VPS. We start with diagnosis, move through the critical configuration variables, cover slow query analysis, query optimization, connection management, I/O tuning, and automated tools — all with specific commands, configuration snippets, and decision frameworks.

MassiveGRID Ubuntu VPS includes: Ubuntu 24.04 LTS pre-installed · Proxmox HA cluster with automatic failover · Ceph 3x replicated NVMe storage · Independent CPU/RAM/storage scaling · 12 Tbps DDoS protection · 4 global datacenter locations · 100% uptime SLA · 24/7 human support rated 9.5/10

Deploy a self-managed VPS — from $1.99/mo
Need dedicated resources? — from $19.80/mo
Want fully managed hosting? — we handle everything

Diagnose Before You Tune

The single most common mistake in database tuning is changing configuration without identifying the problem first. Before you touch my.cnf, answer these questions:

1. Is the database actually the bottleneck? Maybe it's the application code, the web server, or network latency.
2. Which queries are slow? The slow query log tells you exactly.
3. Is the server CPU-bound, memory-bound, or I/O-bound? The fix is different for each.
4. Are there locking issues? Write contention and table-level locks cause different symptoms than resource exhaustion.

Start with a quick health check:

# Current server status
mysqladmin -u root -p status

# Extended status variables
mysqladmin -u root -p extended-status | grep -E "Threads_connected|Threads_running|Slow_queries|Innodb_buffer_pool_read_requests|Innodb_buffer_pool_reads|Questions"

# Current process list (what's running right now?)
mysql -u root -p -e "SHOW FULL PROCESSLIST;"

# Server uptime and query rates
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Uptime'; SHOW GLOBAL STATUS LIKE 'Questions'; SHOW GLOBAL STATUS LIKE 'Slow_queries';"

Calculate your query rate:

mysql -u root -p -e "
SELECT
  VARIABLE_VALUE AS total_queries
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Questions';

SELECT
  VARIABLE_VALUE AS uptime_seconds
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Uptime';
"

Divide total queries by uptime seconds to get queries per second (QPS). A typical WordPress site does 10-50 QPS; a busy application might do 500-5000 QPS.

Prerequisites

You need an Ubuntu 24.04 VPS with MySQL 8.x or MariaDB 11.x installed. If you set up your database as part of a LEMP stack, see our LEMP stack guide. For PostgreSQL users, see our PostgreSQL guide instead.

Verify your installation:

# MySQL
mysql --version
# mysql  Ver 8.0.41 for Linux on x86_64

# Or MariaDB
mariadb --version
# mariadb  Ver 15.1 Distrib 11.4.4-MariaDB

# Check current configuration file locations
mysql -u root -p -e "SELECT @@datadir, @@basedir, @@innodb_buffer_pool_size;"

The main configuration file on Ubuntu 24.04 is:

• MySQL: /etc/mysql/mysql.conf.d/mysqld.cnf
• MariaDB: /etc/mysql/mariadb.conf.d/50-server.cnf

Both also read from /etc/mysql/conf.d/ for additional custom configuration. Create a custom tuning file there to keep your changes separate from package defaults:

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

Key MySQL Variables for VPS

There are hundreds of MySQL configuration variables. On a VPS, these are the ones that actually matter:

InnoDB Buffer Pool Sizing

The InnoDB buffer pool is the single most important MySQL performance variable. It caches table data and indexes in memory, reducing disk reads. Getting this right has more impact than all other variables combined.

Independent scaling: On a MassiveGRID Cloud VPS, allocate 50-70% of total RAM to innodb_buffer_pool_size. Add RAM independently — your CPU and storage stay the same.

The 50-70% Rule

On a dedicated database server, allocate 70-80% of RAM to the buffer pool. On a VPS running other services (web server, application), allocate 50-70%.

Check Current Buffer Pool Efficiency

mysql -u root -p -e "
SELECT
  @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS logical_reads,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS disk_reads;
"

Calculate the hit ratio:

mysql -u root -p -e "
SELECT
  ROUND(
    (1 - (
      (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
      (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    )) * 100, 2
  ) AS buffer_pool_hit_ratio_pct;
"

You want a hit ratio above 99%. Below 95% means too much data is being read from disk — increase the buffer pool size.

Check If Your Data Fits in the Buffer Pool

mysql -u root -p -e "
SELECT
  ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS total_data_mb,
  @@innodb_buffer_pool_size / 1024 / 1024 AS buffer_pool_mb,
  CASE
    WHEN SUM(data_length + index_length) > @@innodb_buffer_pool_size THEN 'DATA EXCEEDS BUFFER POOL'
    ELSE 'Data fits in buffer pool'
  END AS status
FROM information_schema.tables
WHERE engine = 'InnoDB';
"

Ideally, your entire working dataset fits in the buffer pool. If it doesn't, increase the buffer pool or optimize which data is frequently accessed.

Apply Buffer Pool Configuration

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

[mysqld]
# Buffer pool — 50-70% of total RAM
# Example for 4GB VPS running database + web server:
innodb_buffer_pool_size = 2G

# Multiple buffer pool instances (1 per GB, max 64)
innodb_buffer_pool_instances = 2

# Dump and reload buffer pool on restart (warm cache)
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

The innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup options save the buffer pool contents to disk on shutdown and reload them on startup. This means your database is fast immediately after restart, instead of needing to warm up.

sudo systemctl restart mysql

Online Buffer Pool Resizing (MySQL 8+)

MySQL 8 supports resizing the buffer pool without a restart:

-- Resize to 3 GB (takes effect in chunks)
SET GLOBAL innodb_buffer_pool_size = 3221225472;

-- Monitor the resize progress
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

This is temporary — add the change to tuning.cnf to persist it across restarts.

Enabling and Analyzing the Slow Query Log

The slow query log is your most valuable tuning tool. It records every query that exceeds a time threshold.

Enable the Slow Query Log

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

Add under the [mysqld] section:

# Slow query log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_count = 100

• long_query_time = 1 — log queries taking more than 1 second
• log_queries_not_using_indexes — log queries that perform full table scans (regardless of time)
• min_examined_row_count = 100 — only log queries examining at least 100 rows (reduces noise)

sudo systemctl restart mysql

You can also enable it dynamically without a restart:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Analyzing the Slow Query Log

Don't read the raw log — use mysqldumpslow to aggregate results:

# Top 10 slowest queries by total time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 most frequent slow queries
sudo mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# Top 10 by average time per query
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Queries sorted by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log

Example output:

Count: 347  Time=2.45s (850s)  Lock=0.00s (0s)  Rows=1.0 (347), user@localhost
  SELECT * FROM orders WHERE customer_id = N AND status = 'S' ORDER BY created_at DESC LIMIT N

Count: 1205  Time=1.12s (1349s)  Lock=0.00s (1s)  Rows=523.0 (630215), user@localhost
  SELECT * FROM products WHERE category_id = N

This tells you: the SELECT * FROM orders query ran 347 times, averaging 2.45 seconds each. Total time: 850 seconds. The SELECT * FROM products query ran 1,205 times, averaging 1.12 seconds, examining 523 rows on average.

For More Detailed Analysis: pt-query-digest

# Install Percona Toolkit
sudo apt install -y percona-toolkit

# Analyze slow query log
sudo pt-query-digest /var/log/mysql/slow-query.log

# Analyze last 24 hours only
sudo pt-query-digest --since "24h" /var/log/mysql/slow-query.log

# Generate report and save
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow-query-report.txt

pt-query-digest provides fingerprinted query analysis, ranking queries by total execution time and showing EXPLAIN-ready examples.

Query Optimization Basics

Once the slow query log tells you which queries are slow, optimize them.

Using EXPLAIN

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending' ORDER BY created_at DESC LIMIT 10;

+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 98432 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+

Red flags in EXPLAIN output:

Use EXPLAIN ANALYZE in MySQL 8+ for actual execution statistics:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending' ORDER BY created_at DESC LIMIT 10\G

Creating the Right Indexes

For the slow query above, create a composite index covering the WHERE and ORDER BY clauses:

-- Composite index: filter by customer_id + status, then sort by created_at
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

Now verify the improvement:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 AND status = 'pending' ORDER BY created_at DESC LIMIT 10;

+----+-------------+--------+------+-------------------------------------+-------------------------------------+---------+-------------+------+-------------+
| id | select_type | table  | type | possible_keys                       | key                                 | key_len | ref         | rows | Extra       |
+----+-------------+--------+------+-------------------------------------+-------------------------------------+---------+-------------+------+-------------+
|  1 | SIMPLE      | orders | ref  | idx_orders_customer_status_created  | idx_orders_customer_status_created  | 8       | const,const |   15 | Using where |
+----+-------------+--------+------+-------------------------------------+-------------------------------------+---------+-------------+------+-------------+

From 98,432 rows scanned to 15. From ALL (full table scan) to ref (index lookup). The Using filesort is gone because the index already provides the sort order.

Index Guidelines for VPS

• Index columns in WHERE, JOIN, and ORDER BY clauses
• Composite indexes: put equality columns first, range/sort columns last
• Don't over-index: each index consumes RAM (in the buffer pool) and slows writes
• Check unused indexes:

  SELECT * FROM sys.schema_unused_indexes
  WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');

• Check duplicate indexes:

  SELECT * FROM sys.schema_redundant_indexes;

Connection Management

Every MySQL connection consumes memory — roughly 10-20 MB per connection with default buffers. On a VPS with limited RAM, too many connections eat into the buffer pool.

Check Current Connections

mysql -u root -p -e "
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
"

If Max_used_connections is close to max_connections, you're at risk of connection exhaustion. But the fix isn't always increasing max_connections.

max_connections vs Connection Pooling

Increasing max_connections from 151 to 500 means potentially 500 connections each holding 10-20 MB of memory. On a 4 GB VPS, that's 5-10 GB of RAM just for connection buffers — more than your total RAM.

The better solution is connection pooling at the application level:

For most VPS setups, set max_connections to a reasonable number and use application-level pooling:

[mysqld]
# Set based on your application's pool size + some headroom
max_connections = 100

# Cache threads to speed up new connections
thread_cache_size = 32

# Timeout idle connections (seconds)
wait_timeout = 300
interactive_timeout = 300

Identifying Idle Connections

-- Show connections sleeping for more than 60 seconds
SELECT id, user, host, db, command, time, state
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 60
ORDER BY time DESC;

-- Kill a specific idle connection
KILL CONNECTION 12345;

Temporary Table and Sort Buffer Tuning

When MySQL can't perform a sort or GROUP BY in memory, it creates temporary tables on disk — which is dramatically slower. Two variables control this:

# Check how many temp tables go to disk
mysql -u root -p -e "
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
"

If Created_tmp_disk_tables is more than 25% of Created_tmp_tables, increase the temp table limits:

[mysqld]
# Both must be set to the same value
tmp_table_size = 128M
max_heap_table_size = 128M

# Per-session buffers (increase only if needed)
sort_buffer_size = 4M
join_buffer_size = 4M
read_rnd_buffer_size = 2M

Warning: sort_buffer_size and join_buffer_size are allocated per session. With 100 concurrent connections and sort_buffer_size = 4M, that's potentially 400 MB of RAM just for sort buffers. Keep these modest on VPS.

InnoDB Redo Log Tuning

The InnoDB redo log records changes before they're flushed to the data files. A larger log allows more write buffering, which improves write performance.

# Check current redo log configuration
mysql -u root -p -e "
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
"

[mysqld]
# Redo log size (total = innodb_log_file_size * innodb_log_files_in_group)
# Rule: should hold ~1 hour of writes
innodb_log_file_size = 256M

# Durability setting:
# 1 = flush log on every commit (ACID compliant, safest, slowest)
# 2 = flush log once per second (small risk of 1s data loss on OS crash)
# 0 = flush log once per second (risk of data loss on any crash)
innodb_flush_log_at_trx_commit = 1

# I/O flushing method
innodb_flush_method = O_DIRECT

innodb_flush_method = O_DIRECT bypasses the OS file cache for InnoDB data files. This prevents double-caching (data in both InnoDB buffer pool and OS page cache) and is recommended for all VPS setups.

For write-heavy workloads where you can accept minimal data loss risk:

# Trade 1 second of durability for significant write performance
innodb_flush_log_at_trx_commit = 2

Storage I/O Optimization

If your slow query log shows queries waiting on I/O rather than CPU, the bottleneck is storage.

Diagnose I/O Bottlenecks

# Check InnoDB I/O stats
mysql -u root -p -e "
SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';
SHOW GLOBAL STATUS LIKE 'Innodb_data_pending_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_data_pending_fsyncs';
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_pending_writes';
"

# System-level I/O stats
iostat -x 1 5

# Per-process I/O
sudo iotop -oP

If Innodb_data_pending_reads or Innodb_data_pending_fsyncs are consistently above 0, your storage is struggling to keep up.

InnoDB I/O Configuration

[mysqld]
# I/O capacity — set based on your storage type
# HDD: 200-400
# SATA SSD: 2000-5000
# NVMe SSD: 5000-20000
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Read-ahead threshold (pages)
innodb_read_ahead_threshold = 56

# Write threads
innodb_write_io_threads = 4
innodb_read_io_threads = 4

MassiveGRID VPS uses Ceph 3x replicated NVMe storage, so set innodb_io_capacity on the higher end (2000-5000).

I/O contention suspected? If your slow query log shows queries waiting on I/O, dedicated resources provide dedicated I/O bandwidth — no noisy neighbors, predictable storage performance, from $19.80/mo.

Complete Tuning Configuration

Here's a complete tuning configuration for a 4 GB VPS running MySQL/MariaDB alongside a web application:

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

[mysqld]
# === Buffer Pool ===
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# === InnoDB Redo Log ===
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# === I/O ===
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# === Connections ===
max_connections = 100
thread_cache_size = 32
wait_timeout = 300
interactive_timeout = 300

# === Temporary Tables ===
tmp_table_size = 64M
max_heap_table_size = 64M

# === Per-Session Buffers ===
sort_buffer_size = 2M
join_buffer_size = 2M
read_rnd_buffer_size = 1M

# === Table Cache ===
table_open_cache = 2000
table_definition_cache = 1400

# === Slow Query Log ===
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_count = 100

# === Performance Schema ===
performance_schema = ON

# === Binary Log (if replication is enabled) ===
# binlog_expire_logs_seconds = 604800
# sync_binlog = 1

# Validate configuration syntax
sudo mysqld --validate-config 2>&1 || echo "Check for errors above"

# Apply changes
sudo systemctl restart mysql

# Verify buffer pool is loading
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';"

For an 8 GB VPS with a dedicated database workload, adjust:

innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 5
max_connections = 150
tmp_table_size = 128M
max_heap_table_size = 128M
sort_buffer_size = 4M

mysqltuner.pl: Automated Recommendations

MySQLTuner analyzes your running server's status variables and provides specific tuning recommendations:

# Download and run
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
sudo perl mysqltuner.pl --user root --pass YOUR_PASSWORD

Example output sections:

-------- InnoDB Metrics ---------------------
[--] InnoDB Buffer Pool: 2.00G
[--] InnoDB Buffer Pool Instances: 2
[OK] InnoDB Buffer Pool Hit Rate: 99.8%
[OK] InnoDB Buffer Pool Data fill: 78.3%

-------- Performance Metrics -----------------
[!!] Slow queries: 2.3% (1205/52341)
[!!] Temporary tables created on disk: 28.4%
[OK] Thread cache hit rate: 99.2%
[!!] Table cache hit rate: 67.3%

-------- Recommendations ---------------------
General recommendations:
    Increase tmp_table_size / max_heap_table_size
    Increase table_open_cache
Variables to adjust:
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_open_cache (> 2000)

Important: Run MySQLTuner after the server has been running for at least 24-48 hours under production load. Running it on a freshly restarted server gives misleading results because the status counters are reset.

Monitoring with performance_schema

MySQL's Performance Schema provides detailed instrumentation of server execution. It's enabled by default in MySQL 8 and MariaDB 11.

Top Queries by Total Time

SELECT
  DIGEST_TEXT AS query,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_time_sec,
  ROUND(AVG_TIMER_WAIT / 1000000000000, 3) AS avg_time_sec,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_database'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10\G

Tables with Most I/O

SELECT
  OBJECT_SCHEMA AS db,
  OBJECT_NAME AS table_name,
  COUNT_READ AS reads,
  COUNT_WRITE AS writes,
  ROUND(SUM_TIMER_READ / 1000000000000, 3) AS read_time_sec,
  ROUND(SUM_TIMER_WRITE / 1000000000000, 3) AS write_time_sec
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Index Usage Statistics

SELECT
  OBJECT_SCHEMA AS db,
  OBJECT_NAME AS table_name,
  INDEX_NAME AS index_name,
  COUNT_FETCH AS reads_from_index,
  COUNT_INSERT AS inserts,
  COUNT_UPDATE AS updates,
  COUNT_DELETE AS deletes
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
  AND INDEX_NAME IS NOT NULL
ORDER BY COUNT_FETCH DESC
LIMIT 20;

The sys Schema (Simplified Views)

MySQL 8 includes the sys schema, which provides human-readable views on top of performance_schema:

-- Top 10 queries by total latency
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10\G

-- Tables with full table scans
SELECT * FROM sys.schema_tables_with_full_table_scans;

-- Unused indexes (candidates for removal)
SELECT * FROM sys.schema_unused_indexes;

-- Redundant indexes (duplicates)
SELECT * FROM sys.schema_redundant_indexes;

-- User connection statistics
SELECT * FROM sys.user_summary;

-- I/O by table
SELECT * FROM sys.io_global_by_file_by_bytes LIMIT 20;

-- Memory usage by component
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 20;

For comprehensive server monitoring beyond just MySQL, see our VPS monitoring guide and performance optimization guide.

When to Add RAM vs Optimize Queries

This is the most common decision in database tuning. Here's a framework:

The Decision Process

1. Enable the slow query log and let it run for 24-48 hours
2. Analyze with pt-query-digest — are specific queries slow or everything?
3. If specific queries: use EXPLAIN, add indexes, rewrite queries
4. If buffer pool hit ratio is low: add RAM, increase buffer pool
5. If I/O wait is high: consider dedicated resources
6. If all else is fine but still slow: check application-level issues (N+1 queries, missing caching)

Ongoing Maintenance

Rotate Slow Query Log

The slow query log grows indefinitely. Set up log rotation:

sudo nano /etc/logrotate.d/mysql-slow

/var/log/mysql/slow-query.log {
    daily
    rotate 14
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql adm
    postrotate
        /usr/bin/mysqladmin flush-logs
    endscript
}

Regular Index Maintenance

# Analyze tables to update index statistics (helps query optimizer)
mysqlcheck -u root -p --analyze --all-databases

# Optimize tables (reclaim space from deleted rows, rebuild indexes)
mysqlcheck -u root -p --optimize your_database

Weekly Health Check Script

#!/bin/bash
# /opt/scripts/mysql-health-check.sh

echo "=== MySQL Health Check $(date) ==="

mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "
-- Buffer pool hit rate
SELECT
  ROUND((1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    GREATEST((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'), 1)
  )) * 100, 2) AS buffer_pool_hit_pct;

-- Connection usage
SELECT
  @@max_connections AS max_connections,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Max_used_connections') AS peak_connections,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected') AS current_connections;

-- Slow queries
SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Slow_queries') AS slow_queries,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Questions') AS total_queries;

-- Temp tables on disk
SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') AS disk_tmp_tables,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Created_tmp_tables') AS total_tmp_tables;
"

chmod +x /opt/scripts/mysql-health-check.sh

Schedule it weekly using cron or a systemd timer (see our cron jobs guide).

Prefer Managed Database Optimization?

Database tuning is an ongoing process. Traffic patterns change, data grows, queries evolve. What's optimal today might be a bottleneck in six months. If you'd rather have database experts handle the tuning:

Let us optimize your database. MassiveGRID Managed Dedicated Cloud Servers include database performance tuning, query optimization consulting, automated monitoring, and proactive scaling — all handled by our team. You focus on your application.

Summary

Database performance tuning follows a clear process: diagnose first, then fix what's actually broken. Here's the priority order for a VPS:

Start with a MassiveGRID Cloud VPS where you can scale RAM independently to grow your buffer pool. When I/O becomes the bottleneck, upgrade to a Dedicated VPS for guaranteed I/O bandwidth. Or let our team handle everything with a Managed Dedicated Cloud Server — including database tuning, monitoring, and proactive optimization.