Performance Tuning Methodology

  1. Measure — identify bottlenecks with metrics and slow query log
  2. Hypothesize — index missing? buffer pool too small? lock contention?
  3. Change one thing — isolate impact
  4. Verify — compare before/after with EXPLAIN ANALYZE and benchmarks
  5. Document — record settings and rationale

Never tune randomly — every change should trace to measured evidence.

InnoDB Buffer Pool

The buffer pool caches data and index pages in memory — the single most important setting.

  [mysqld]
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

On a dedicated MySQL server with 8 GB RAM, allocate ~70% (5–6 GB) to the buffer pool. Monitor hit ratio:

  SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Hit ratio = 1 - (reads / read_requests) — target > 99%

Connection Settings 

  [mysqld]
max_connections = 300
wait_timeout = 600
interactive_timeout = 600
thread_cache_size = 50

Do not set max_connections to 10,000 without connection pooling — each connection consumes memory (~256KB–4MB+).

Use ProxySQL, HAProxy, or application pools (HikariCP, SQLAlchemy pool) to multiplex connections.

Redo Log and Flush Behavior 

  innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit Durability Performance
1 Full ACID — flush every commit Safest, slowest
2 Flush to OS cache each commit Common compromise
0 Flush every second Fastest, risk on crash

Use 1 for financial data; 2 may be acceptable for analytics with risk acceptance.

Query Pattern Optimization

Eliminate N+1 

  -- One query instead of N
SELECT u.id, u.name, o.id AS order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);

Keyset Pagination 

  SELECT id, name FROM products
WHERE id > :last_id AND category_id = 5
ORDER BY id ASC LIMIT 50;

Batch Operations 

  INSERT INTO logs (message, level) VALUES
    ('msg1', 'info'), ('msg2', 'warn'), ('msg3', 'error');
-- One round trip vs three

Table and Schema Optimizations 

  -- Archive old data
CREATE TABLE orders_2023 LIKE orders;
INSERT INTO orders_2023 SELECT * FROM orders WHERE created_at < '2024-01-01';
DELETE FROM orders WHERE created_at < '2024-01-01' LIMIT 10000;
-- Repeat DELETE in batches

-- Summary tables for dashboards
CREATE TABLE daily_revenue AS
SELECT DATE(created_at) AS day, SUM(total) AS revenue
FROM orders GROUP BY DATE(created_at);
-- Refresh via scheduled event

Read Replicas

Route analytics and reporting to replicas:

  App → Primary (writes, critical reads)
App → Replica pool (reports, search indexing, batch jobs)

Handle replication lag — do not read-your-writes on replica immediately after write unless using session tracking or semi-sync with lag monitoring.

Caching Layers

Layer Tool TTL
Application Redis/Memcached Seconds to hours
Query result Application cache Per query
MySQL Buffer pool Automatic
CDN Static assets Long

MySQL 8.0 removed the query cache — do not look for query_cache_type.

Monitoring Key Metrics 

  SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;
Metric Indicates
High Created_tmp_disk_tables Queries need better indexes or more tmp_table_size
High Sort_merge_passes Sort buffer too small or missing index for ORDER BY
Sustained high Threads_running Query backlog or insufficient CPU

Best Practices

  1. Baseline performance before any tuning
  2. Index based on slow query log evidence
  3. Right-size hardware — IOPS matter as much as CPU
  4. Use SSD/NVMe for data directory; separate redo log disk on extreme workloads
  5. Schedule ANALYZE TABLE after large data changes

Common Mistakes

Mistake Impact
Increasing max_connections instead of pooling OOM kills
SELECT * everywhere Memory and network waste
Tuning without slow query log Optimizing wrong queries
Ignoring replication lag on read scaling Stale data bugs

Troubleshooting

Sudden slowdown after upgrade:

  SELECT * FROM mysql.sys.version;
-- Check optimizer changes; analyze tables; review release notes
ANALYZE TABLE orders, users, products;

High CPU, low QPS: likely inefficient queries — run pt-query-digest.

Disk I/O saturated: increase buffer pool, add indexes, or upgrade storage.

Production Scenario

An e-commerce platform’s checkout p99 latency spiked during sales. Investigation showed buffer pool hit ratio dropped to 92% after catalog import doubled table size. Increasing innodb_buffer_pool_size from 4G to 8G and adding a covering index on (user_id, status, created_at) restored p99 from 800ms to 45ms.

Performance tuning is iterative — measure continuously, tune deliberately, and validate every change under realistic load.