MySQL Performance Tuning in 2025: The 10x Faster Queries Playbook

A SaaS client reached out in early 2025 with P99 query latency at 4.2 seconds on a lookup that should have been milliseconds. The table had 12 indexes. The query wasn't using any of them. Within 48 hours of our audit, P99 was at 180ms — no schema changes, no hardware upgrades. Just configuration corrections and one missing composite index.

MySQL performance problems almost always have the same root causes: buffer pool misconfiguration, indexing mistakes, and queries the optimizer can't plan well. This guide covers the techniques we use in production — not the stuff from the official docs you've already read.

Start Here: InnoDB Buffer Pool

We've audited hundreds of MySQL servers. The most common finding: buffer pool set to the 128MB default, even on a 64GB server. This is the single biggest performance lever in MySQL — and the most frequently misconfigured.

The buffer pool is InnoDB's in-memory cache. Every page read from disk is cached here. When your working set fits in the buffer pool, queries read from RAM. When it doesn't, every query hits disk. The difference is 100–1,000× in latency.

-- Check your current buffer pool hit ratio
SELECT
  FORMAT(100 - (Innodb_buffer_pool_reads * 100.0 / Innodb_buffer_pool_read_requests), 2) AS hit_ratio_pct,
  FORMAT_BYTES(@@innodb_buffer_pool_size) AS configured_size
FROM (
  SELECT
    SUM(IF(variable_name = 'Innodb_buffer_pool_reads', variable_value, 0)) AS Innodb_buffer_pool_reads,
    SUM(IF(variable_name = 'Innodb_buffer_pool_read_requests', variable_value, 0)) AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE variable_name IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests')
) stats;

If your hit ratio is below 95%, your buffer pool is too small. Below 90% is an emergency. Target: 99%+.

-- Dedicated MySQL server: set to 70-80% of RAM
-- 64GB server:
SET PERSIST innodb_buffer_pool_size = 48318382080;  -- ~45GB

-- Or use innodb_dedicated_server for automatic tuning (MySQL 8.0+)
-- Add to my.cnf:
[mysqld]
innodb_dedicated_server = 1

One gotcha with innodb_dedicated_server: when it's ON, MySQL ignores any manually set innodb_buffer_pool_size, innodb_redo_log_capacity, and innodb_flush_method values. This catches teams who set it and then wonder why their manual settings aren't applying. Use one or the other, not both.

-- Verify buffer pool configuration is active
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_dedicated_server';

Indexing: More Isn't Better

The client we mentioned at the top had 12 indexes on their hot table. Seven of them were unused. Each unused index costs write performance — every INSERT, UPDATE, and DELETE has to maintain every index. With 7 unnecessary indexes on a write-heavy table, they were paying a significant write amplification tax for zero read benefit.

Find unused indexes

-- Indexes that haven't been used since the last server restart
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema', 'information_schema', 'mysql', 'sys');

Find duplicate indexes

-- Indexes made redundant by another index (wastes space and write overhead)
SELECT * FROM sys.schema_redundant_indexes;

Use invisible indexes before dropping

Never drop an index cold. Make it invisible first, run for a week, confirm nothing broke, then drop.

-- Step 1: Make invisible (query optimizer ignores it, index still maintained)
ALTER TABLE orders ALTER INDEX idx_created_at INVISIBLE;

-- Step 2: Monitor for a week. If performance degrades, make it visible again:
ALTER TABLE orders ALTER INDEX idx_created_at VISIBLE;

-- Step 3: When confirmed safe, drop
ALTER TABLE orders DROP INDEX idx_created_at;

Build composite indexes correctly

The column order in a composite index matters. For a query filtering on status and sorting by created_at:

-- BAD: wrong column order (can't use index for the sort)
CREATE INDEX idx_orders ON orders (created_at, status);

-- GOOD: equality filter column first, range/sort column second
CREATE INDEX idx_orders ON orders (status, created_at);

-- Verify the index is being used
EXPLAIN SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;
-- Look for: key: idx_orders, Extra: (no "Using filesort" — means index handles sort)

Find tables doing full scans

-- Tables that are full-scanned most often
SELECT
  object_schema,
  object_name,
  FORMAT(rows_full_scanned, 0) AS rows_scanned,
  FORMAT_PICO_TIME(latency) AS total_latency
FROM sys.schema_tables_with_full_table_scans
WHERE object_schema NOT IN ('performance_schema', 'information_schema', 'mysql')
ORDER BY rows_full_scanned DESC
LIMIT 20;

EXPLAIN ANALYZE: The Right Way to Debug Queries

EXPLAIN shows the optimizer's plan. EXPLAIN ANALYZE (MySQL 8.0.18+) actually executes the query and shows you what really happened — including actual row counts vs estimated, and actual timings per step.

-- EXPLAIN shows estimates (can be wildly wrong)
EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending' AND o.created_at > '2025-01-01';

-- EXPLAIN ANALYZE shows reality
EXPLAIN ANALYZE SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending' AND o.created_at > '2025-01-01'\G

-- Key things to look for in the output:
-- "rows=X (actual rows=Y)" -- if Y >> X, optimizer is wrong, statistics need updating
-- "Using filesort" -- needs index on the ORDER BY columns
-- "Using temporary" -- GROUP BY/DISTINCT is creating temp tables, often avoidable
-- "Full scan on ... (cost=X rows=Y)" -- missing index

Update table statistics when optimizer is wrong

-- Force statistics recollection on a table
ANALYZE TABLE orders;

-- Or for all tables in a schema
SELECT CONCAT('ANALYZE TABLE ', table_schema, '.', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_database'
  AND table_type = 'BASE TABLE';

Slow Query Log: Your Continuous Performance Monitor

-- Enable slow query log (safe to do on production)
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;        -- log queries over 1 second
SET GLOBAL log_queries_not_using_indexes = 1;  -- log full scans too
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Analyze the slow log with mysqldumpslow
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log  -- top 20 by total time
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log  -- top 20 by count

-- Or use Performance Schema directly (no log file needed)
SELECT
  SUBSTRING(digest_text, 1, 120) AS query,
  count_star AS executions,
  ROUND(avg_timer_wait / 1e9, 3) AS avg_sec,
  ROUND(sum_timer_wait / 1e9, 2) AS total_sec,
  ROUND(sum_rows_examined / count_star) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name = 'your_database'
ORDER BY sum_timer_wait DESC
LIMIT 15;

MySQL Shell: 70% Faster Data Import

If you're still using mysqldump for large datasets, you're leaving significant time on the table. MySQL Shell's parallel dump and load utility is not well-known outside the DBA community, but we use it on every large migration now.

# Traditional approach — single-threaded, slow
mysqldump mydb > backup.sql                    # hours for large DBs
mysql mydb < backup.sql                        # equally slow

# MySQL Shell — parallel, chunk-based, fast
mysqlsh

# Dump using all CPU cores
JS> util.dumpInstance("/opt/dump/", {threads: 16})

# Load using all CPU cores
JS> util.loadDump("/opt/dump/", {threads: 16})

Real numbers from a client migration: 194.7M rows, 64.75 GB dataset. Traditional mysqldump/restore: 9 minutes 20 seconds. MySQL Shell parallel load: 2 minutes 43 seconds. That's a 70% reduction — matters a lot when you're in a maintenance window.

For initial bulk loads where you can trade durability for speed

-- Disable redo logging (ONLY during initial data load, re-enable immediately after)
ALTER INSTANCE DISABLE INNODB REDO_LOG;

-- Optimize for bulk insert
SET GLOBAL innodb_extend_and_initialize = OFF;
SET GLOBAL innodb_max_dirty_pages_pct = 10;
SET GLOBAL innodb_max_dirty_pages_pct_lwm = 10;

-- Run your load...

-- Re-enable redo log immediately
ALTER INSTANCE ENABLE INNODB REDO_LOG;

Warning: disabling redo logging means a crash during the load leaves the database in an unrecoverable state. Only do this if you have the source data and can re-run the load. Never do this on a running production server.

tcmalloc: The 5-Minute Win Most Teams Skip

MySQL uses the system's default memory allocator (glibc malloc on Linux). Under high concurrency, glibc malloc fragments badly — MySQL ends up holding 2–3× more virtual memory than it actually needs, and you see CPU time wasted in memory management.

Switching to tcmalloc takes 5 minutes and typically yields 20–40% memory reduction and 15–25% concurrency improvement on servers with 100+ concurrent connections.

# Install tcmalloc (RHEL/CentOS)
sudo yum install gperftools-libs

# Install on Ubuntu/Debian
sudo apt-get install google-perftools

# Configure MySQL to use tcmalloc via systemd override
sudo systemctl edit mysqld
# Add:
[Service]
Environment="LD_PRELOAD=/usr/lib64/libtcmalloc_minimal.so.4"

sudo systemctl daemon-reload
sudo systemctl restart mysqld

# Verify it's loaded
sudo cat /proc/$(pidof mysqld)/maps | grep tcmalloc

We've deployed this on every dedicated MySQL server we manage. It's one of the highest ROI changes you can make with no application code changes whatsoever.

Storage I/O Configuration

innodb_io_capacity tells InnoDB how fast your storage is, so it can tune its background flush rate. Getting this wrong in either direction causes problems: too low and dirty pages accumulate; too high and you thrash the disk unnecessarily.

-- SSD (SATA or standard NVMe)
SET GLOBAL innodb_io_capacity = 2000;
SET GLOBAL innodb_io_capacity_max = 4000;

-- High-end NVMe (AWS io2, gp3 at max IOPS)
SET GLOBAL innodb_io_capacity = 4000;
SET GLOBAL innodb_io_capacity_max = 8000;

-- Traditional spinning disk (HDD)
SET GLOBAL innodb_io_capacity = 200;
SET GLOBAL innodb_io_capacity_max = 400;

-- Check current dirty page ratio (should be < 75% for healthy operation)
SELECT
  variable_name,
  variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_buffer_pool_pages_data'
);

Redo log sizing

Undersized redo logs cause excessive checkpoint flushing, which hurts write throughput. In MySQL 8.0.30+, use innodb_redo_log_capacity to configure it dynamically:

-- Measure redo log generation rate (run for 60 seconds)
SELECT VARIABLE_VALUE INTO @start FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_redo_log_current_lsn';

SELECT SLEEP(60);

SELECT
  FORMAT_BYTES(ABS(VARIABLE_VALUE - @start)) AS redo_per_minute,
  FORMAT_BYTES(ABS(VARIABLE_VALUE - @start) * 60) AS redo_per_hour
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_redo_log_current_lsn';

-- Set capacity to 2x the per-hour generation rate
SET PERSIST innodb_redo_log_capacity = 2147483648;  -- 2GB example

Connection Management

Each MySQL connection consumes memory — roughly 1–4MB depending on your buffer sizes and query complexity. At 500 connections, that's 500MB–2GB just for connection overhead before a single query runs.

-- Check current connection usage
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';     -- actively executing right now
SHOW STATUS LIKE 'Max_used_connections'; -- peak since last restart

-- If Threads_running >> CPU cores, you have a concurrency problem
-- If Max_used_connections approaches max_connections, you risk connection exhaustion

-- Use ProxySQL or MySQL Router for connection pooling
-- Or configure connection memory limits (MySQL 8.0.28+):
SET GLOBAL connection_memory_limit = 2097152;       -- 2MB per connection
SET GLOBAL global_connection_memory_limit = 536870912;  -- 512MB total
SET GLOBAL global_connection_memory_tracking = 1;

-- Monitor per-connection memory
SELECT FORMAT_BYTES(variable_value) AS total_connection_memory
FROM performance_schema.global_status
WHERE variable_name = 'Global_connection_memory';

MySQL 8.4 LTS: What Changes for Performance Tuning

MySQL 8.0 reaches EOL in April 2026. If you're planning an upgrade to 8.4 LTS, here's what changes for performance:

• Automatic histogram updates: 8.4 can update column histograms automatically, reducing optimizer errors caused by stale statistics
• InnoDB parallel DDL: Index creation uses parallel threads by default — large table ALTER operations are significantly faster
• Removed deprecated variables: Several variables from 8.0 are gone in 8.4 — run mysqlcheck --all-databases and review deprecated variable warnings before upgrading
• Default authentication: caching_sha2_password is the only default — legacy mysql_native_password clients may break

-- Check for deprecated variables before upgrading
SELECT * FROM performance_schema.global_variables
WHERE variable_name IN (
  'query_cache_size',
  'query_cache_type',
  'innodb_log_file_size',  -- replaced by innodb_redo_log_capacity
  'innodb_log_files_in_group'
);

-- These should all be zero or absent before migrating to 8.4

For a full upgrade guide, see our MySQL 8.0 EOL and 8.4 upgrade guide.

Production Monitoring Dashboard

-- One-shot performance snapshot
SELECT
  'Buffer Pool Hit %'                          AS metric,
  CONCAT(FORMAT(100 - (r.v * 100.0 / rr.v), 2), '%') AS value
FROM
  (SELECT variable_value AS v FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') r,
  (SELECT variable_value AS v FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') rr

UNION ALL SELECT 'QPS', FORMAT(q.v / u.v, 0)
FROM
  (SELECT variable_value AS v FROM performance_schema.global_status WHERE variable_name = 'Questions') q,
  (SELECT variable_value AS v FROM performance_schema.global_status WHERE variable_name = 'Uptime') u

UNION ALL SELECT 'Active Connections', variable_value
FROM performance_schema.global_status WHERE variable_name = 'Threads_connected'

UNION ALL SELECT 'Running Threads', variable_value
FROM performance_schema.global_status WHERE variable_name = 'Threads_running'

UNION ALL SELECT 'Slow Queries (total)', variable_value
FROM performance_schema.global_status WHERE variable_name = 'Slow_queries';

Automated alerting thresholds

SELECT
  CASE
    WHEN hit_ratio < 95 THEN CONCAT('CRITICAL: Buffer pool hit ratio ', FORMAT(hit_ratio, 1), '%')
    WHEN hit_ratio < 98 THEN CONCAT('WARNING: Buffer pool hit ratio ', FORMAT(hit_ratio, 1), '%')
    ELSE CONCAT('OK: Buffer pool ', FORMAT(hit_ratio, 1), '%')
  END AS buffer_pool_alert
FROM (
  SELECT 100 - (r.v * 100.0 / rr.v) AS hit_ratio
  FROM
    (SELECT variable_value AS v FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') r,
    (SELECT variable_value AS v FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') rr
) m;

Counterintuitive Tips We've Learned in Production

• Sort columns in EXPLAIN output don't always mean what you think: "Using index" means the query was satisfied entirely from the index (fast). "Using index condition" means ICP (Index Condition Pushdown) applied — still good. "Using filesort" doesn't always mean slow — filesort of 100 rows is faster than a bad index scan of 10M rows.
• AUTO_INCREMENT gaps are fine: Teams sometimes implement complex "fill gaps" logic to avoid gaps in sequential IDs. This introduces locking and complexity for no real benefit. Gaps in AUTO_INCREMENT are normal and harmless.
• VARCHAR(255) vs VARCHAR(100) doesn't matter for performance on disk — InnoDB stores only the actual length. But it matters in memory: temp tables and sort buffers allocate the full declared length. Oversized VARCHAR columns cause memory bloat in query processing.
• SELECT * in production is always wrong: Even if you need all columns, specifying them explicitly allows the optimizer to consider covering indexes and avoids fetching columns you're adding to the table later.

Working with JusDB

MySQL performance problems almost always fall into one of five categories: buffer pool misconfiguration, index sprawl, optimizer statistics staleness, connection pool saturation, or slow queries without proper analysis. We've seen and fixed all of them across hundreds of MySQL deployments on AWS RDS, self-hosted on EC2, and on bare metal.

Our MySQL consulting engagement starts with a 24-hour automated audit of your slow query log, Performance Schema, and InnoDB metrics — producing a prioritized list of exactly what to fix and in what order. Most clients see measurable improvement within the first week.

If you're dealing with specific issues — P99 latency spikes, buffer pool thrashing, connection exhaustion, or an upcoming 8.4 upgrade — reach out and we'll take a look.

Related reading: MySQL Indexes: Complete Guide | MySQL 8.0 EOL Guide | MySQL Architecture Deep Dive