Server Configuration Tuning

Learning Focus

Use this lesson to understand MySQL server configuration tuning — adjusting internal settings to maximize performance for your specific workload and hardware.

Concept Overview

Why Tune MySQL Configuration?

MySQL's default configuration is designed to work on modest hardware with minimal resource usage. Defaults are rarely optimal for production. Tuning the configuration can dramatically improve:

• Query response time — more data cached in memory = fewer disk reads
• Throughput — more concurrent connections handled efficiently
• Stability — proper buffer sizes prevent out-of-memory errors and crashes

The Tuning Philosophy

Measure first, tune second. Never change a setting without understanding what it does and measuring the impact.

Key Areas to Tune

Area	Primary Settings	Impact
InnoDB Buffer Pool	innodb_buffer_pool_size	Biggest single performance factor
Connection Handling	max_connections, thread_cache_size	Handles concurrent users
Query Execution	tmp_table_size, sort_buffer_size	Complex query performance
Logging	log_bin, sync_binlog	Replication and recovery
I/O	innodb_io_capacity, innodb_flush_log_at_trx_commit	Disk write performance

---

Basic Syntax & Rules

Viewing Current Settings

-- View a specific variable
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- View all InnoDB variables
SHOW VARIABLES LIKE 'innodb%';

-- View status counters
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Threads%';

Setting Variables

-- Set for current session only
SET SESSION sort_buffer_size = 4 * 1024 * 1024;  -- 4MB

-- Set globally (affects new connections)
SET GLOBAL max_connections = 200;

-- Some changes require restart (persistent in my.cnf)

Configuration File (my.cnf)

[mysqld]
# InnoDB Buffer Pool (most important setting)
innodb_buffer_pool_size = 4G

# Connections
max_connections = 200
thread_cache_size = 16

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

# I/O
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Logging
slow_query_log = ON
long_query_time = 1

---

Step-by-Step Examples

Example 1: Tuning the InnoDB Buffer Pool

The buffer pool is MySQL's most important performance setting. It caches table data and indexes in memory, avoiding disk reads.

Step 1: Check current size and hit rate

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Default: 128M (far too small for production)

-- Check buffer pool hit rate
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Calculate hit rate:

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_rate_pct;

buffer_pool_hit_rate_pct
98.50

Target: 99%+ hit rate. Below 95% means the buffer pool is too small.

Step 2: Set optimal size

Rule of thumb: Set innodb_buffer_pool_size to 50–75% of total server RAM (on a dedicated database server).

# Server with 8GB RAM:
[mysqld]
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4    # divide into 4 instances for concurrency

Step 3: Apply and verify

-- Dynamic resize (MySQL 8.0+, no restart needed)
SET GLOBAL innodb_buffer_pool_size = 5368709120;  -- 5GB in bytes

-- Verify
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

---

Example 2: Tuning Connection Settings

Step 1: Check connection usage

SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_cached';
SHOW VARIABLES LIKE 'max_connections';

Variable	Value
Max_used_connections	85
max_connections	151 (default)

Step 2: Set appropriate limits

[mysqld]
# Set to peak usage + 20% buffer
max_connections = 150

# Cache threads to avoid thread creation overhead
thread_cache_size = 16

# Timeout idle connections
wait_timeout = 300
interactive_timeout = 300

Don't blindly increase max_connections! Each connection uses ~10MB of memory. 1000 connections = 10GB just for connection overhead.

---

Example 3: Tuning Temporary Table Settings

When MySQL creates temporary tables for sorting or grouping, they start in memory. If they exceed the size limit, they spill to disk (much slower).

-- Check how many temp tables went to disk
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

Variable	Value
Created_tmp_tables	50000
Created_tmp_disk_tables	5000

If more than 10% of temp tables go to disk, increase the limits:

[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M   # Both must be set — MySQL uses the smaller value

---

Example 4: InnoDB Flush Settings (Durability vs. Speed)

[mysqld]
# Full ACID durability (default, safest)
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

# Better performance, slight durability risk (1-second data loss window)
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

Value	Behavior	When to Use
= 1	Flush to disk on every commit	Financial systems, any data where loss is unacceptable
= 2	Flush to OS cache on commit, disk sync every second	Most applications — good performance with minimal risk
= 0	Flush every second regardless of commits	Batch imports, dev environments

---

Practical Use Cases

1. Dedicated Database Server (8GB RAM)

[mysqld]
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
max_connections = 200
thread_cache_size = 16
tmp_table_size = 64M
max_heap_table_size = 64M
innodb_io_capacity = 2000
innodb_flush_log_at_trx_commit = 2
slow_query_log = ON
long_query_time = 1

2. Shared Server (2GB RAM)

[mysqld]
innodb_buffer_pool_size = 512M
max_connections = 50
thread_cache_size = 8
tmp_table_size = 32M
max_heap_table_size = 32M

3. Development Environment

[mysqld]
innodb_buffer_pool_size = 256M
max_connections = 20
innodb_flush_log_at_trx_commit = 2
slow_query_log = ON
long_query_time = 0.5

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Leaving buffer pool at 128M default	Most queries hit disk	Set to 50-75% of RAM
Setting max_connections = 10000	Server runs out of memory	Set based on actual peak + 20%
Not setting max_heap_table_size with tmp_table_size	MySQL uses the smaller of the two	Always set both to the same value
Changing settings without measuring before/after	No way to verify improvement	Record metrics before and after changes
Tuning too many variables at once	Can't identify which change helped	Change one variable at a time and measure
Ignoring slow query log	Performance problems go unnoticed	Always enable in production

---

Best Practices

• Start with the buffer pool — it has the biggest impact; tune it first
• Measure before and after — use SHOW GLOBAL STATUS to verify improvements
• Change one setting at a time — isolate the impact of each change
• Use my.cnf for persistent changes — don't rely on SET GLOBAL alone
• Monitor continuously — set up dashboards for key metrics
• Don't over-allocate memory — leave room for the OS and other processes
• Enable slow query log in production — catch regressions early
• Review quarterly — as data and traffic grow, settings need adjustment

---

Hands-On Practice

Exercise 1: Buffer Pool Analysis (Easy)

Calculate your buffer pool hit rate. Is it above 99%? If not, what would be the ideal buffer pool size?

Exercise 2: Connection Analysis (Medium)

1. Check your current max_connections and Max_used_connections
2. Is the setting appropriate?
3. Check Threads_cached — is thread caching working?
4. Adjust settings and verify

Exercise 3: Full Server Audit (Advanced)

Write a comprehensive diagnostic query that reports:

1. Buffer pool size and hit rate
2. Max connections vs peak usage
3. Temp tables disk ratio
4. Current InnoDB flush setting
5. Slow query log status

Generate a "tuning recommendations" report based on the findings.

---

Connection to Other Concepts

Related Concept	How It Connects
Query Profiling	Profiling reveals what's slow; config tuning provides the resources
Indexes	Even the best configuration can't fix a missing index
Transactions	innodb_flush_log_at_trx_commit directly affects transaction durability
Backup and Recovery	log_bin and sync_binlog affect backup capabilities
Event Scheduler	Maintenance events run better with properly tuned resources

What to Learn Next

• 19. Backup and Recovery — protect your tuned database

---

Visual Learning Diagram

flowchart TD
    A["Query arrives"] --> B{"Data in\nbuffer pool?"}
    B -->|Yes| C["⚡ Memory read\n(microseconds)"]
    B -->|No| D["💾 Disk read\n(milliseconds)"]
    D --> E["Load into buffer pool"]
    E --> C

    F["Buffer Pool Size\n(innodb_buffer_pool_size)"]
    F -->|Larger| G["More data fits in memory\n→ Higher hit rate\n→ Faster queries"]
    F -->|Smaller| H["Frequent disk reads\n→ Lower hit rate\n→ Slower queries"]

    classDef fast fill:#28a745,stroke:#fff,color:#fff
    classDef slow fill:#dc3545,stroke:#fff,color:#fff
    class C,G fast
    class D,H slow

Quick Reference

-- Key diagnostic queries
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

-- Key settings to tune (my.cnf)
-- innodb_buffer_pool_size = 50-75% of RAM
-- max_connections = peak + 20%
-- tmp_table_size = max_heap_table_size = 64M
-- thread_cache_size = 16
-- slow_query_log = ON
-- long_query_time = 1

-- Dynamic changes (no restart)
SET GLOBAL innodb_buffer_pool_size = 4294967296;  -- 4GB
SET GLOBAL max_connections = 200;
SET GLOBAL slow_query_log = ON;

What's Next

• 19. Backup and Recovery