ปรับแต่ง MySQL/MariaDB บน VPS ให้ทำงานเร็วขึ้น

MySQL และ MariaDB เป็นฐานข้อมูลที่ใช้งานบน VPS มากที่สุด แต่ค่าเริ่มต้น (default) ที่มาพร้อมการติดตั้งมักถูกออกแบบมาสำหรับเครื่องที่มี RAM น้อย ไม่เหมาะกับ VPS ในยุคปัจจุบัน บทความนี้จะพาคุณปรับค่าทีละขั้นตอนเพื่อให้ได้ประสิทธิภาพสูงสุด

ทำไม MySQL ถึงช้า

ก่อนปรับแต่ง เราต้องเข้าใจก่อนว่าปัญหาหลักของ MySQL/MariaDB ที่ติดตั้งใหม่คืออะไร:

ปรับ InnoDB Buffer Pool

InnoDB buffer pool คือส่วนที่สำคัญที่สุดในการ tune MySQL — มันทำหน้าที่ cache ข้อมูลและ index จาก disk ไว้ใน RAM ยิ่งใหญ่ยิ่งดี (ภายในขอบเขตที่ RAM อนุญาต)

ตรวจสอบการใช้งานปัจจุบัน:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW STATUS LIKE 'Innodb_buffer_pool_reads';"

ตั้งค่า my.cnf ที่สำคัญ

ไฟล์ config หลักของ MySQL/MariaDB อยู่ที่ /etc/mysql/my.cnf หรือ /etc/my.cnf ขึ้นอยู่กับ distro

[mysqld]
# Buffer Pool — ปรับตาม RAM ที่มี
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2

# Log File — ใหญ่ขึ้น = checkpoint น้อยลง
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M

# I/O Settings
innodb_flush_log_at_trx_commit = 2    # ลด I/O (ยอมเสี่ยง ข้อมูล 1 วินาที)
innodb_flush_method = O_DIRECT        # ลด double buffering กับ OS

# Connections
max_connections = 150                  # ลดจาก default 151-200
thread_cache_size = 16

# Query Cache (MariaDB เท่านั้น — MySQL 8 ถอดแล้ว)
# query_cache_type = 1
# query_cache_size = 64M
# query_cache_limit = 2M

# Tmp Tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Slow Query Log (เปิดไว้เพื่อ tune)
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Binary Log — ปิดถ้าไม่ได้ใช้ replication
# skip-log-bin

หลังแก้ไขแล้ว restart MySQL:

sudo systemctl restart mysql
# หรือสำหรับ MariaDB
sudo systemctl restart mariadb

เปิด Slow Query Log

Slow query log คือเครื่องมือที่ทรงพลังมากสำหรับการหา query ที่เป็นต้นเหตุความช้า ควรเปิดไว้เสมอในช่วง optimize

# ตรวจสอบว่า slow log เปิดอยู่หรือยัง
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

# เปิด real-time โดยไม่ต้อง restart
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

วิเคราะห์ slow log ด้วย mysqldumpslow:

# แสดง 10 query ที่ช้าที่สุด
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# แสดง query ที่รันซ้ำบ่อยสุด
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

ปรับ Index และ Query

Index ที่ดีสามารถเพิ่มความเร็ว query ได้ 100–1000 เท่า ใช้ EXPLAIN เพื่อตรวจสอบว่า query ใช้ index หรือไม่

-- ตรวจสอบ query plan
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'pending';

-- ดู index ที่มีอยู่
SHOW INDEX FROM orders;

-- เพิ่ม composite index
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- ลบ index ที่ไม่ใช้
DROP INDEX old_index_name ON orders;

ตรวจหา index ที่ไม่ถูกใช้งาน

-- MySQL 8 / MariaDB 10.6+
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('performance_schema','mysql','sys','information_schema');

ใช้ MySQLTuner

MySQLTuner เป็น script ที่วิเคราะห์ MySQL/MariaDB และแนะนำค่าที่ควรปรับโดยอัตโนมัติ ควรรันหลังจาก server ทำงานมาแล้วอย่างน้อย 24 ชั่วโมงเพื่อให้ข้อมูลเพียงพอ

# ติดตั้ง
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

# รัน
perl mysqltuner.pl --user root --pass your_password

# หรือรันแบบไม่ต้องใส่ password (อ่านจาก ~/.my.cnf)
perl mysqltuner.pl

ผลลัพธ์จะแสดง:

• [!!] — ปัญหาที่ต้องแก้ไขด่วน
• [OK] — ค่าที่ดีอยู่แล้ว
• Variables to adjust — รายการค่าที่ควรปรับพร้อมตัวเลขแนะนำ

MariaDB vs MySQL

สำหรับ VPS ใหม่แนะนำ MariaDB เพราะมีข้อดีหลายประการ:

การ Monitor ประสิทธิภาพ

หลังจากปรับค่าแล้ว ควร monitor อย่างต่อเนื่องเพื่อดูผล:

# ดู status real-time
mysqladmin -u root -p status

# ดู process list
SHOW PROCESSLIST;

# ดู global status ที่สำคัญ
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Aborted_connects';

คำนวณ Buffer Pool Hit Rate

-- Hit rate ควรสูงกว่า 99%
SELECT
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
  AS buffer_pool_hit_rate
FROM (
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM information_schema.GLOBAL_STATUS
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

Checklist สรุป

คำถามที่พบบ่อย