MySQL Performance Tuning — คู่มือจูน MySQL ให้เร็วสุดขีด 2026

ทำไม MySQL ถึงช้า — หาสาเหตุก่อนแก้

ผมเจอปัญหา MySQL ช้ามาแทบทุกรูปแบบตลอด 28 ปีที่ดูแล database servers และ 90% ของปัญหาเกิดจาก 3 สาเหตุหลักคือ InnoDB Buffer Pool เล็กเกินไป, queries ที่ไม่มี index รองรับ, และ connections ที่ไม่ได้จัดการก่อนจะจูนอะไรต้องรู้ก่อนว่าปัญหาอยู่ตรงไหน

ตรวจสอบ Status เบื้องต้น

# ดู process ที่กำลังรัน
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

# ดูสถานะ InnoDB
SHOW ENGINE INNODB STATUS\G

# ดู global status
SHOW GLOBAL STATUS LIKE 'Threads_%';
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';

# ดู variables ปัจจุบัน
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL VARIABLES LIKE 'query_cache_%';

ใช้ MySQLTuner วิเคราะห์อัตโนมัติ

# ดาวน์โหลด MySQLTuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl

# รัน
perl mysqltuner.pl --host 127.0.0.1 --user root --pass 'YourPassword'

# ตัวอย่างผลลัพธ์ที่สำคัญ:
# [!!] InnoDB buffer pool / data size: 128.0M / 2.5G (ปัญหา!)
# [!!] Slow queries: 15% (ปัญหา!)
# [OK] Highest connection usage: 45% (ดีแล้ว)

InnoDB Buffer Pool — หัวใจของ Performance

InnoDB Buffer Pool เป็นพื้นที่ memory ที่ MySQL ใช้ cache data และ index pages ถ้า Buffer Pool ใหญ่พอจะ cache ข้อมูลที่ใช้บ่อยทั้งหมดได้ MySQL จะอ่านจาก RAM แทน disk ซึ่งเร็วกว่าหลายร้อยเท่านี่คือ setting ที่สำคัญที่สุดของ MySQL

ตั้งค่า Buffer Pool

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Buffer Pool — ตั้ง 70-80% ของ RAM ที่มี
# Server RAM 16GB → ตั้ง 12GB
# Server RAM 32GB → ตั้ง 24GB
# Server RAM 64GB → ตั้ง 48GB
innodb_buffer_pool_size = 12G

# แบ่ง buffer pool เป็นหลาย instances (1 instance per GB)
innodb_buffer_pool_instances = 12

# เปิด buffer pool dump/load เมื่อ restart
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 75

ตรวจสอบ Buffer Pool Hit Rate

# ดู hit rate ต้องได้ 99%+
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

# คำนวณ hit rate
# Hit Rate = (read_requests - reads) / read_requests * 100
# ถ้าต่ำกว่า 99% แสดงว่า buffer pool เล็กไป

# ดู buffer pool usage
SELECT
 FORMAT(@@innodb_buffer_pool_size / 1073741824, 2) AS 'Buffer Pool Size (GB)',
 FORMAT(
 (SELECT VARIABLE_VALUE FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data') *
 @@innodb_page_size / 1073741824, 2
 ) AS 'Data Pages (GB)',
 FORMAT(
 (SELECT VARIABLE_VALUE FROM performance_schema.global_status
 WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free') *
 @@innodb_page_size / 1073741824, 2
 ) AS 'Free Pages (GB)';

เปิด Slow Query Log

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_limit = 100

# Apply โดยไม่ต้อง restart
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

วิเคราะห์ Slow Queries

# ใช้ mysqldumpslow (built-in tool)
mysqldumpslow -t 20 -s c /var/log/mysql/slow-query.log
# -t 20: top 20 queries
# -s c: sort by count

# ใช้ pt-query-digest (Percona Toolkit — แนะนำ)
apt install percona-toolkit
pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow-query-report.txt

# ตัวอย่างผลลัพธ์ pt-query-digest:
# Rank Query ID Response time Calls R/Call
# ==== ============================= ============= ===== ======
# 1 0x3A2E56FB81E94A7A 456.2340 42.1% 12450 0.0366
# SELECT orders WHERE user_id = ?
# 2 0x7B4F92AC3D108E55 234.1200 21.6% 3200 0.0731
# SELECT products JOIN categories WHERE ...

EXPLAIN — เครื่องมือวิเคราะห์ Query

# วิเคราะห์ query ด้วย EXPLAIN
EXPLAIN SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2026-01-01'
 AND o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 100;

# สิ่งที่ต้องดู:
# type: ALL (แย่), index (พอได้), ref (ดี), eq_ref (ดีมาก), const (ดีที่สุด)
# rows: จำนวน rows ที่ MySQL ต้องตรวจ ยิ่งน้อยยิ่งดี
# Extra: "Using filesort" (ไม่ดี), "Using temporary" (ไม่ดี), "Using index" (ดี)

# EXPLAIN ANALYZE (MySQL 8.0+) — ดู actual execution
EXPLAIN ANALYZE SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2026-01-01'
 AND o.status = 'completed';

สร้าง Index ที่เหมาะสม

# Composite index สำหรับ query ข้างบน
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);

# Covering index — MySQL อ่านจาก index อย่างเดียว ไม่ต้องอ่าน table
CREATE INDEX idx_orders_covering
ON orders (status, created_at, user_id, total_amount);

# ดู index ที่มี
SHOW INDEX FROM orders;

# ดู index usage statistics (MySQL 8.0+)
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'production';

# ดู duplicate indexes
SELECT * FROM sys.schema_redundant_indexes
WHERE object_schema = 'production';

# ลบ index ที่ไม่ได้ใช้ (ลด write overhead)
DROP INDEX idx_old_unused ON orders;

Query Optimization Tips

# 1. หลีกเลี่ยง SELECT *
-- แย่
SELECT * FROM orders WHERE user_id = 123;
-- ดี
SELECT id, total_amount, created_at FROM orders WHERE user_id = 123;

# 2. ใช้ LIMIT เสมอ
SELECT id, name FROM products WHERE category_id = 5 LIMIT 100;

# 3. หลีกเลี่ยง function บน WHERE column
-- แย่ (ใช้ index ไม่ได้)
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- ดี
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

# 4. ใช้ EXISTS แทน IN สำหรับ subquery ใหญ่
-- แย่
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
-- ดี
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000);

# 5. Pagination ด้วย cursor แทน OFFSET
-- แย่ (OFFSET 100000 ช้ามาก)
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- ดี (ใช้ cursor)
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

ตั้งค่า Connections

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Max connections — อย่าตั้งสูงเกินไป
# แต่ละ connection ใช้ RAM ~1-10MB
max_connections = 500
max_connect_errors = 100000

# Thread cache — reuse threads แทนสร้างใหม่
thread_cache_size = 50

# Wait timeout — ปิด idle connections
wait_timeout = 300
interactive_timeout = 300

# Thread pool (MySQL Enterprise / Percona)
# thread_handling = pool-of-threads
# thread_pool_size = 16

ตรวจสอบ Connection Usage

# ดู connection usage
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

# ถ้า Max_used_connections ใกล้ max_connections = ปัญหา
# ต้องใช้ connection pooling (ProxySQL, PgBouncer สำหรับ PostgreSQL)

ProxySQL สำหรับ Connection Pooling

# ติดตั้ง ProxySQL
apt install proxysql

# ตั้งค่า backend servers
mysql -u admin -padmin -h 127.0.0.1 -P 6032
INSERT INTO mysql_servers(hostgroup_id, hostname, port)
VALUES (10, '127.0.0.1', 3306);

# ตั้งค่า connection pooling
UPDATE mysql_servers SET max_connections=200 WHERE hostname='127.0.0.1';

# ตั้งค่า query routing
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10), -- writes to master
 (2, 1, '^SELECT', 20); -- reads to replica

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Replication สำหรับ Read Scaling

ถ้า application มี read queries มากกว่า write queries (ซึ่งเป็นปกติของ web applications) การใช้ MySQL Replication กระจาย reads ไป replica servers จะช่วยลดภาระ master server ได้มาก

ตั้งค่า Source-Replica Replication

# บน Source (Master) — /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
gtid_mode = ON
enforce_gtid_consistency = ON
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

# สร้าง replication user
CREATE USER 'replicator'@'10.10.10.%' IDENTIFIED BY 'StrongReplicaPass123!';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.10.10.%';

# บน Replica — /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 2
relay_log = /var/log/mysql/relay-bin
read_only = ON
super_read_only = ON
gtid_mode = ON
enforce_gtid_consistency = ON

# เริ่ม replication
CHANGE REPLICATION SOURCE TO
 SOURCE_HOST='10.10.10.11',
 SOURCE_USER='replicator',
 SOURCE_PASSWORD='StrongReplicaPass123!',
 SOURCE_AUTO_POSITION=1;
START REPLICA;

# ตรวจสอบ
SHOW REPLICA STATUS\G
# Replica_IO_Running: Yes
# Replica_SQL_Running: Yes
# Seconds_Behind_Source: 0

Physical Backup ด้วย Percona XtraBackup

# ติดตั้ง
apt install percona-xtrabackup-80

# Full backup
xtrabackup --backup --target-dir=/backup/full/$(date +%Y%m%d) \
 --user=backup --password='BackupPass123!'

# Incremental backup
xtrabackup --backup --target-dir=/backup/incr/$(date +%Y%m%d) \
 --incremental-basedir=/backup/full/20260228 \
 --user=backup --password='BackupPass123!'

# Prepare backup (ก่อน restore)
xtrabackup --prepare --target-dir=/backup/full/20260228

# Restore
systemctl stop mysql
rm -rf /var/lib/mysql/*
xtrabackup --copy-back --target-dir=/backup/full/20260228
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

Logical Backup ด้วย mysqldump

# Backup ทุก databases
mysqldump --all-databases --single-transaction --routines \
 --triggers --events --quick \
 --user=backup --password='BackupPass123!' \
 | gzip > /backup/all-databases-$(date +%Y%m%d).sql.gz

# Backup เฉพาะ database
mysqldump --single-transaction --routines --triggers \
 production_db | gzip > /backup/production-$(date +%Y%m%d).sql.gz

# Automated backup script
#!/bin/bash
# /usr/local/bin/mysql-backup.sh
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M)
RETENTION_DAYS=30

mkdir -p "$BACKUP_DIR"

mysqldump --all-databases --single-transaction \
 --routines --triggers --events \
 | gzip > "/all-.sql.gz"

find "$BACKUP_DIR" -name "*.sql.gz" -mtime + -delete

echo "Backup completed: all-.sql.gz ($(du -h /all-.sql.gz | cut -f1))"

# Crontab: 0 2 * * * /usr/local/bin/mysql-backup.sh

MySQL กับ MariaDB ต่างกันยังไงใช้ตัวไหนดี?

MariaDB เป็น fork ของ MySQL โดย original MySQL creator (Monty Widenius) ทั้งคู่ compatible กันในระดับ SQL สำหรับ workload ทั่วไป MySQL 8.x มี features ใหม่เช่น Window Functions, CTEs, JSON improvements ที่ดีมาก MariaDB มี storage engines เพิ่มเติมเช่น Aria, ColumnStore ผมใช้ MySQL 8.x สำหรับ production ใหม่และ MariaDB สำหรับ legacy systems ที่ migrate มา

innodb_buffer_pool_size ตั้งเท่าไรดี?

กฎทั่วไปคือ 70-80% ของ RAM สำหรับ dedicated database server ถ้า server รัน application อื่นด้วย (web server, cache) ให้ลดลงเช่น 50-60% วิธีตรวจสอบคือดู data size จริงถ้า data ทั้งหมดมี 5 GB ก็ตั้ง buffer pool 8 GB พอไม่ต้องตั้ง 24 GB เสียเปล่าแต่ถ้า data มี 30 GB และ RAM มี 32 GB ตั้ง 24 GB เลยเพราะ MySQL จะ cache ได้แค่ 80% ของ data ที่ใช้บ่อยส่วนที่เหลือจะอ่านจาก disk

MySQL ควรรันใน Docker ไหม?

สำหรับ development ใช่เลยสะดวกมากแต่สำหรับ production ผมไม่แนะนำเพราะ database ต้องการ direct disk access ที่เร็วที่สุด container layer เพิ่ม overhead แม้จะน้อยและ data persistence ใน Docker ซับซ้อนกว่าจำเป็นผมรัน MySQL บน bare metal หรือ VM เสมอสำหรับ production ใช้ Docker Compose สำหรับ dev/staging เท่านั้น

ใช้ Query Cache ดีไหม?

MySQL 8.0 ลบ Query Cache ออกแล้วเพราะมันเป็น bottleneck สำหรับ high-concurrency workloads แทนที่จะใช้ Query Cache ให้ใช้ application-level caching เช่น Redis หรือ Memcached แทนดีกว่าทั้งเรื่อง performance และ flexibility

สรุป

MySQL Performance Tuning ไม่ได้ยากอย่างที่คิดเริ่มจาก 3 สิ่งนี้ก่อนตั้ง InnoDB Buffer Pool ให้เหมาะสม, เปิด Slow Query Log แล้ววิเคราะห์ queries ที่ช้า, สร้าง index ที่เหมาะสมแค่ 3 อย่างนี้ก็แก้ปัญหาได้ 90% แล้ว

สำหรับ monitoring ผมใช้ Grafana + Prometheus + mysqld_exporter เพื่อดู metrics real-time ทำให้จับปัญหาได้เร็วก่อนที่จะกระทบ users อย่าลืม backup ทุกวันทดสอบ restore สม่ำเสมอเพราะ backup ที่ restore ไม่ได้ก็เหมือนไม่มี backup