Boost MySQL Performance: Deep Parameter Tuning to Eliminate Slowness

1. Memory Parameter Optimization

1. Buffer Pool

Purpose : The InnoDB buffer pool caches table and index pages to minimise disk I/O.

-- View current buffer‑pool variables
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- Recommended sizing (physical RAM 50‑80%)
SET GLOBAL innodb_buffer_pool_size = <em>RAM_IN_BYTES</em>;

-- Key related variables
-- innodb_buffer_pool_size          – total pool size (recommended 70‑80% of RAM)
-- innodb_buffer_pool_instances    – one instance per GB of pool size
-- innodb_buffer_pool_chunk_size   – default 128 MB
-- innodb_buffer_pool_dump_at_shutdown – dump hot pages on shutdown
-- innodb_buffer_pool_load_at_startup – load hot pages on startup

Configuration example for an 8 GB server :

SET GLOBAL innodb_buffer_pool_size = 6 * 1024 * 1024 * 1024;  -- 6 GB
SET GLOBAL innodb_buffer_pool_instances = 6;               -- 6 instances

2. Log Buffer

Purpose : Buffers redo‑log records for batch writes to disk.

-- View log‑buffer variables
SHOW VARIABLES LIKE 'innodb_log_buffer%';

-- Important variables
-- innodb_log_buffer_size   – default 16 MB
-- innodb_log_files_in_group – default 2
-- innodb_log_file_size     – recommended 1‑2 GB per file

Configuration guidance :

High‑concurrency write workloads: 64‑256 MB

Typical workloads: 16‑32 MB

Adjust innodb_flush_log_at_trx_commit to change the flush policy.

2. I/O Parameter Configuration

1. Flush Policy Optimization

-- Transaction‑log flush policy
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
-- Values
-- 0 : flush once per second (best performance, lowest durability)
-- 1 : flush on every commit (default, highest durability)
-- 2 : write to OS cache, flush to disk each second

-- Data‑file flush method
SHOW VARIABLES LIKE 'innodb_flush_method';
-- Recommended: O_DIRECT on Linux, unbuffered on Windows

2. Read/Write I/O Settings

-- Read‑ahead threshold (default 56 pages)
SHOW VARIABLES LIKE 'innodb_read_ahead_threshold';

-- Random read‑ahead (default OFF)
SHOW VARIABLES LIKE 'innodb_random_read_ahead';

-- I/O thread counts (default 4 each)
SHOW VARIABLES LIKE 'innodb_write_io_threads';
SHOW VARIABLES LIKE 'innodb_read_io_threads';
-- Recommendation: increase proportionally to CPU cores or SSD capability

3. Connection Management and Thread Pool

1. Connection Settings

-- View connection‑related variables
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'thread_cache_size';

-- Key variables
-- max_connections          – recommended 500‑3000, adjust to available RAM
-- thread_cache_size        – recommended 10 % of max_connections
-- wait_timeout             – recommended 300‑600 s
-- interactive_timeout      – recommended 1800 s

-- Example configuration
SET GLOBAL max_connections = 1000;
SET GLOBAL thread_cache_size = 100;
SET GLOBAL wait_timeout = 300;
SET GLOBAL interactive_timeout = 1800;

2. Thread Pool

-- MySQL Enterprise thread‑pool variables
SHOW VARIABLES LIKE 'thread_pool%';

-- Percona Server thread‑pool configuration
SET GLOBAL thread_handling = 'pool-of-threads';
SET GLOBAL thread_pool_size = <em>CPU_CORE_COUNT</em>;   -- number of CPU cores
SET GLOBAL thread_pool_max_threads = 1000;

4. System Variable Best‑Practice Guide

1. Query Cache (removed in MySQL 8.0)

-- MySQL 5.7 and earlier
SHOW VARIABLES LIKE 'query_cache%';
-- Recommended: disable
SET GLOBAL query_cache_type = OFF;
SET GLOBAL query_cache_size = 0;

2. Sort and Temporary Table Optimization

-- Sort buffer (recommended 2‑4 MB)
SHOW VARIABLES LIKE 'sort_buffer_size';

-- Join buffer (recommended 256 KB‑1 MB)
SHOW VARIABLES LIKE 'join_buffer_size';

-- Temporary table size (recommended 64‑256 MB)
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';   -- keep equal to tmp_table_size

-- Internal temporary‑table storage engine (recommended MEMORY)
SHOW VARIABLES LIKE 'internal_tmp_mem_storage_engine';

3. Table and Index Optimization

-- Open files limit (recommended 65535)
SHOW VARIABLES LIKE 'open_files_limit';

-- InnoDB file‑per‑table and file format
SET GLOBAL innodb_file_per_table = ON;   -- each table gets its own tablespace
SET GLOBAL innodb_file_format = Barracuda; -- enables compression

-- Adaptive hash index (enable for high read concurrency)
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';

4. Transaction Isolation and Locking

-- Transaction isolation level
SHOW VARIABLES LIKE 'transaction_isolation';
-- Recommended: REPEATABLE‑READ (default) or READ‑COMMITTED

-- Lock wait timeout (default 50 s)
SET GLOBAL innodb_lock_wait_timeout = 50;

-- Deadlock detection (default ON)
SET GLOBAL innodb_deadlock_detect = ON;

5. Monitoring and Tuning Practice

1. Key Performance Metric Monitoring

-- Buffer‑pool and thread status
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

-- Calculate buffer‑pool hit rate
SELECT (1 - (Variable_value / (
    SELECT Variable_value FROM information_schema.global_status
    WHERE Variable_name = 'Innodb_buffer_pool_read_requests'))
) * 100 AS hit_rate
FROM information_schema.global_status
WHERE Variable_name = 'Innodb_buffer_pool_reads';

2. my.cnf / my.ini Example

[mysqld]
# Memory
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_log_buffer_size = 32M
innodb_log_file_size = 1G

# I/O
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Connections
max_connections = 1000
thread_cache_size = 100
wait_timeout = 300

# Query optimisation
sort_buffer_size = 4M
join_buffer_size = 1M
tmp_table_size = 256M
max_heap_table_size = 256M

# InnoDB
innodb_file_per_table = ON
innodb_lock_wait_timeout = 30

Other Tuning Considerations

Adjust one variable at a time and observe the effect.

Record baseline metrics before any change.

Match tuning to workload type: OLTP – focus on connections and transactions; OLAP – focus on sort and temporary‑table settings.

Never exceed the physical limits of the server hardware.

Parameter defaults and availability may differ across MySQL versions.

Common Diagnostic Commands

-- InnoDB engine status
SHOW ENGINE INNODB STATUS\G

-- List all variables
SHOW VARIABLES;

-- Server status
SHOW STATUS;

-- Active processes
SHOW PROCESSLIST;

-- Lock information
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

Performance tuning is an iterative process that should be validated in a staging environment before applying changes to production.