MySQL Optimization

Slow Query Log

Enable the slow query log to find queries that need optimization.

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;          -- log queries > 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Check current settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Analyze slow query log with mysqldumpslow
-- mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
-- -s t: sort by total time; -t 10: top 10 queries

-- Or use pt-query-digest (Percona Toolkit)
-- pt-query-digest /var/log/mysql/slow.log

ANALYZE TABLE & Statistics

-- Update table statistics (helps optimizer)
ANALYZE TABLE orders, products, users;

-- Check table status
SHOW TABLE STATUS LIKE 'orders'\G

-- InnoDB statistics settings
SET GLOBAL innodb_stats_auto_recalc = ON;
SET GLOBAL innodb_stats_persistent = ON;
SET GLOBAL innodb_stats_persistent_sample_pages = 20;

-- Force statistics update for a table
ALTER TABLE large_table ENGINE=InnoDB;  -- rebuilds + recalculates

-- Check index statistics
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

InnoDB Buffer Pool Tuning

-- Set buffer pool size (typically 70-80% of RAM)
SET GLOBAL innodb_buffer_pool_size = 4294967296;  -- 4 GB

-- Multiple buffer pool instances (for servers > 1 GB pool)
SET GLOBAL innodb_buffer_pool_instances = 4;

-- Check buffer pool hit rate
SELECT
  (1 - (phy_reads / (buf_reads + 1))) * 100 AS hit_rate_pct
FROM (
  SELECT
    SUM(VARIABLE_VALUE) AS buf_reads
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) r,
(
  SELECT SUM(VARIABLE_VALUE) AS phy_reads
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) p;

-- Show buffer pool status
SHOW STATUS LIKE 'Innodb_buffer_pool%';

Partition Pruning

Partition pruning skips irrelevant partitions, dramatically reducing rows scanned.

-- RANGE partition by year
CREATE TABLE events (
  id        INT NOT NULL,
  event_dt  DATE NOT NULL,
  payload   TEXT
) PARTITION BY RANGE (YEAR(event_dt)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

-- Pruning in action: only scans p2024
SELECT * FROM events WHERE event_dt BETWEEN '2024-01-01' AND '2024-12-31';

-- Verify pruning with EXPLAIN PARTITIONS
EXPLAIN SELECT * FROM events WHERE YEAR(event_dt) = 2024;
-- partitions column shows which partitions are accessed

-- Manage partitions
ALTER TABLE events ADD PARTITION (PARTITION p2025 VALUES LESS THAN (2026));
ALTER TABLE events DROP PARTITION p2022;

Connection Pooling & Key Variables

-- Key connection variables
SHOW VARIABLES LIKE 'max_connections';         -- default 151
SET GLOBAL max_connections = 500;

SHOW VARIABLES LIKE 'thread_cache_size';       -- reuse threads
SET GLOBAL thread_cache_size = 50;

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Connection_errors%';

-- ProxySQL connection pool example config (proxysql.cfg)
-- mysql_servers = ( { address="127.0.0.1" port=3306 max_connections=200 } )
-- mysql_users   = ( { username="app" default_hostgroup=0 } )

-- Important InnoDB I/O settings
SET GLOBAL innodb_flush_log_at_trx_commit = 1;  -- safest (fsync per commit)
SET GLOBAL innodb_io_capacity = 2000;            -- for SSDs
SET GLOBAL innodb_read_io_threads = 4;
SET GLOBAL innodb_write_io_threads = 4;