MySQL Performance Tuning: Optimization Guide for Production Servers

Poor MySQL performance is rarely about the database engine itself — it is almost always a configuration or query problem. A default MySQL installation ships with conservative settings designed for low-memory development machines, not production servers handling thousands of concurrent queries. Throwing hardware at the problem without tuning your configuration, queries, and indexes is like buying a faster car without changing the flat tires. This guide covers the essential MySQL performance tuning techniques for production Linux servers, from InnoDB buffer pool sizing to index optimization and monitoring.

Prerequisites

Before you start tuning, ensure you have:

• MySQL 8.0 or 8.4 installed on a Linux server (Ubuntu, RHEL, or similar)
• Root or sudo access to edit the MySQL configuration file
• MySQL administrative access (root or equivalent user)
• Baseline metrics: current query volume, response times, and server resource usage
• A staging environment to test configuration changes before production rollout

Important: Never tune a production MySQL server without testing changes in staging first. Some settings require a restart and incorrect values can prevent MySQL from starting.

Step 1: Assess Current Performance

Before changing anything, establish a baseline. You need to know what is slow and why.

Check Global Status

-- Key performance counters
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Uptime';

Calculate Buffer Pool Hit Ratio

-- Should be > 99% for a well-tuned server
SELECT
  (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 AS buffer_pool_hit_ratio;

Check Current Configuration

-- Critical variables to review
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'innodb_io_capacity%';
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';

Step 2: Configure InnoDB Buffer Pool

The InnoDB buffer pool is the single most important setting. It caches data and index pages in memory, reducing disk I/O dramatically.

Edit the Configuration File

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

Core InnoDB Settings

[mysqld]
# === InnoDB Buffer Pool ===
# Set to 70-80% of total RAM on a dedicated MySQL server
# Example: 32 GB server → 24 GB buffer pool
innodb_buffer_pool_size = 24G

# Split buffer pool into multiple instances (1 per GB, max 64)
innodb_buffer_pool_instances = 24

# Dump and reload the buffer pool on restart for faster warmup
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

# === InnoDB Redo Log ===
# Larger log files improve write performance but increase recovery time
# MySQL 8.0.30+: use innodb_redo_log_capacity instead
innodb_redo_log_capacity = 4G

# === InnoDB Flush Settings ===
# 1 = full ACID compliance (safest, default)
# 2 = flush to OS cache each commit, sync to disk once per second (faster)
innodb_flush_log_at_trx_commit = 1

# Use O_DIRECT to avoid double-buffering with OS page cache
innodb_flush_method = O_DIRECT

# === I/O Capacity ===
# SSD: 2000-10000, HDD: 200-400
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

Apply and Verify

# Restart MySQL to apply changes
sudo systemctl restart mysql

# Verify settings
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Note: Starting with MySQL 8.0, you can resize innodb_buffer_pool_size dynamically without a restart:

SET GLOBAL innodb_buffer_pool_size = 25769803776;  -- 24 GB

Step 3: Enable and Analyze the Slow Query Log

The slow query log is your most valuable diagnostic tool. It tells you exactly which queries are consuming the most time.

Enable Slow Query Logging

[mysqld]
# Enable slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

# Log queries taking longer than 1 second
long_query_time = 1

# Log queries not using indexes
log_queries_not_using_indexes = 1

# Throttle "not using index" warnings (prevent log flooding)
log_throttle_queries_not_using_indexes = 60

Or enable dynamically without restart:

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

Analyze with pt-query-digest

# Install Percona Toolkit
sudo apt install percona-toolkit

# Analyze the slow query log
pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow-query-report.txt

# View top 10 worst queries
pt-query-digest /var/log/mysql/mysql-slow.log --limit=10

The output ranks queries by total execution time, showing you exactly where to focus optimization efforts. A single query running 10,000 times at 0.5 seconds each is worse than one query running once at 30 seconds.

Query Performance via Performance Schema

-- Top 10 queries by total execution time
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_time_sec,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_time_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Step 4: Optimize Queries and Indexes

Even with perfect server configuration, poorly written queries and missing indexes will destroy performance.

Use EXPLAIN ANALYZE

-- Analyze query execution plan
EXPLAIN ANALYZE
SELECT o.order_id, o.order_date, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'pending'
  AND o.order_date >= '2026-01-01'
ORDER BY o.order_date DESC
LIMIT 50;

Look for these red flags in the output:

• Full table scan (type: ALL) — missing index
• Using filesort — add an index covering the ORDER BY column
• Using temporary — query may need restructuring
• Large rows_examined vs rows_sent ratio — index not selective enough

Create Effective Composite Indexes

-- Bad: separate single-column indexes
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_date ON orders(order_date);

-- Good: composite index matching the WHERE + ORDER BY
CREATE INDEX idx_status_date ON orders(status, order_date DESC);

-- Better: covering index (includes selected columns, avoids table lookup)
CREATE INDEX idx_status_date_covering
  ON orders(status, order_date DESC, order_id, customer_id);

Index Design Rules

The order of columns in a composite index matters. Follow these guidelines:

1. Equality columns first: columns in WHERE col = value
2. Range columns next: columns in WHERE col > value or BETWEEN
3. ORDER BY columns last: match the sort direction (ASC/DESC)

-- Query pattern:
-- WHERE status = 'active' AND region = 'US' AND created_at > '2026-01-01'
-- ORDER BY created_at DESC

-- Optimal index:
CREATE INDEX idx_optimized ON orders(status, region, created_at DESC);

Find and Remove Unused Indexes

-- Indexes that have never been used since last restart
SELECT
  s.TABLE_SCHEMA,
  s.TABLE_NAME,
  s.INDEX_NAME,
  s.COLUMN_NAME,
  t.TABLE_ROWS
FROM information_schema.STATISTICS s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage u
  ON s.TABLE_SCHEMA = u.OBJECT_SCHEMA
  AND s.TABLE_NAME = u.OBJECT_NAME
  AND s.INDEX_NAME = u.INDEX_NAME
JOIN information_schema.TABLES t
  ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
  AND s.TABLE_NAME = t.TABLE_NAME
WHERE u.INDEX_NAME IS NULL
  AND s.INDEX_NAME != 'PRIMARY'
  AND s.TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY t.TABLE_ROWS DESC;

Warning: Only drop unused indexes after verifying across a full business cycle (weekly/monthly reports may use indexes that appear unused during daily operations).

Step 5: Connection and Thread Configuration

Configure Connection Limits

[mysqld]
# Maximum concurrent connections (tune based on actual needs)
max_connections = 300

# Cache threads for reuse (reduces thread creation overhead)
thread_cache_size = 50

# Connection timeout for idle connections (seconds)
wait_timeout = 600
interactive_timeout = 600

# Per-connection memory buffers (keep these small — multiplied by max_connections)
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M

Calculate Total Memory Usage

Before setting these values, estimate your total memory consumption:

# Quick formula:
# Total = innodb_buffer_pool_size
#       + (max_connections × per_connection_buffers)
#       + OS_overhead (2-4 GB)
#
# Per-connection ≈ sort_buffer + join_buffer + read_buffer + read_rnd_buffer
# Example: 4M + 4M + 2M + 2M = 12M per connection
# 300 connections × 12M = 3.6 GB
# Total: 24 GB + 3.6 GB + 4 GB = 31.6 GB (fits in 32 GB)

Monitor Connection Usage

-- Check current connection usage
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Threads_cached';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

-- Check for connections waiting
SHOW PROCESSLIST;

-- Connection usage ratio (should be < 80%)
SELECT
  @@max_connections AS max_allowed,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Max_used_connections') AS peak_used,
  ROUND(
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Max_used_connections') / @@max_connections * 100, 1
  ) AS usage_pct;

Step 6: Additional Performance Settings

Binary Logging and Replication

[mysqld]
# Binary log for point-in-time recovery and replication
log_bin = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800  # 7 days
binlog_format = ROW
sync_binlog = 1  # Full durability (set to 0 for better perf with slight risk)

Table and File Settings

[mysqld]
# Table cache (increase for many-table databases)
table_open_cache = 4000
table_open_cache_instances = 16
table_definition_cache = 2000

# Temp tables larger than this go to disk
tmp_table_size = 64M
max_heap_table_size = 64M

# Open file limit
open_files_limit = 65535

Linux OS-Level Tuning

MySQL performance depends heavily on the operating system configuration.

# Check and set vm.swappiness (reduce swapping)
echo "vm.swappiness = 1" | sudo tee -a /etc/sysctl.conf

# Set I/O scheduler for SSDs
echo "none" | sudo tee /sys/block/sda/queue/scheduler

# Increase open file limits for mysql user
# Add to /etc/security/limits.conf:
# mysql soft nofile 65535
# mysql hard nofile 65535

# Apply sysctl changes
sudo sysctl -p

Step 7: Set Up Monitoring

Key Metrics to Watch

-- Create a quick health check query
SELECT
  'Buffer Pool Hit Ratio' AS metric,
  CONCAT(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 value
UNION ALL
SELECT
  'Queries Per Second',
  ROUND((SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Queries') /
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Uptime'), 1)
UNION ALL
SELECT
  'Slow Queries',
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Slow_queries')
UNION ALL
SELECT
  'Threads Running',
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Threads_running')
UNION ALL
SELECT
  'Open Tables',
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status
   WHERE VARIABLE_NAME = 'Open_tables');

Automated Monitoring Script

#!/bin/bash
# mysql-health-check.sh — Run via cron every 5 minutes

MYSQL_USER="monitor"
MYSQL_PASS_FILE="/root/.mysql_monitor_pass"
LOG="/var/log/mysql-health.log"

timestamp=$(date '+%Y-%m-%d %H:%M:%S')

# Buffer pool hit ratio
hit_ratio=$(mysql --defaults-extra-file="$MYSQL_PASS_FILE" -u "$MYSQL_USER" -N -e "
  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);")

# Threads running
threads=$(mysql --defaults-extra-file="$MYSQL_PASS_FILE" -u "$MYSQL_USER" -N -e "
  SHOW GLOBAL STATUS LIKE 'Threads_running';" | awk '{print $2}')

# Slow queries since last check
slow=$(mysql --defaults-extra-file="$MYSQL_PASS_FILE" -u "$MYSQL_USER" -N -e "
  SHOW GLOBAL STATUS LIKE 'Slow_queries';" | awk '{print $2}')

echo "$timestamp | HitRatio: ${hit_ratio}% | Threads: $threads | SlowTotal: $slow" >> "$LOG"

# Alert if buffer pool hit ratio drops below 99%
if (( $(echo "$hit_ratio < 99" | bc -l) )); then
  echo "$timestamp WARNING: Buffer pool hit ratio is ${hit_ratio}%" >> "$LOG"
fi

Troubleshooting

MySQL Won’t Start After Configuration Changes

# Check error log
sudo tail -50 /var/log/mysql/error.log

# Common cause: innodb_buffer_pool_size exceeds available RAM
# Fix: reduce the value in my.cnf, then restart
sudo systemctl start mysql

Queries Suddenly Slow After an Update

Table statistics may be stale after bulk operations:

-- Rebuild table statistics
ANALYZE TABLE orders;

-- For all tables in a database
mysqlcheck -u root -p --analyze mydatabase

High CPU from a Single Query

-- Find the offending query
SELECT * FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC
LIMIT 10;

-- Kill a runaway query if necessary (use the ID from above)
KILL QUERY 12345;

InnoDB Buffer Pool Too Small

If your hit ratio is below 99%, the buffer pool cannot fit your working dataset:

-- Check how much data InnoDB is managing
SELECT
  ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.TABLES
WHERE ENGINE = 'InnoDB';

If total data size exceeds your buffer pool, either increase the buffer pool (add RAM) or optimize your schema to reduce data size.

Performance Tuning Checklist

Run through this checklist after configuration changes:

-- 1. Buffer pool hit ratio > 99%?
-- 2. Max_used_connections < 80% of max_connections?
-- 3. Threads_running < CPU core count during normal load?
-- 4. Slow_queries not growing rapidly?
-- 5. No full table scans on large tables? (check slow log)
-- 6. innodb_buffer_pool_size + per-conn buffers < total RAM?
-- 7. Disk I/O wait < 10%? (check with iostat)
-- 8. No deadlocks? Check:
SHOW ENGINE INNODB STATUS\G

Summary

MySQL performance tuning is an iterative process: measure, identify bottlenecks, apply targeted changes, and measure again. The highest-impact changes for most production servers are correctly sizing the InnoDB buffer pool to 70-80% of available RAM, enabling and regularly analyzing the slow query log, creating composite indexes that match your actual query patterns, and configuring connection limits to prevent memory exhaustion. Avoid cargo-cult tuning — every setting you change should be justified by a specific metric. Set up monitoring from day one so you can detect regressions and validate that your changes actually improved performance. Tools like Percona Monitoring and Management (PMM) or Grafana with the MySQL exporter provide dashboards that make ongoing optimization straightforward.

Related Articles

• Linux Server Initial Setup and Security Hardening
• Database Backup Strategies for Production Environments