จูน MySQL/MariaDB บน VPS เพิ่มประสิทธิภาพ Database

MySQL และ MariaDB เป็น Database Engine ยอดนิยมที่ขับเคลื่อนเว็บไซต์และแอปพลิเคชันนับล้านทั่วโลก แต่การติดตั้งด้วยค่า default ไม่ได้หมายความว่าจะทำงานได้เร็วที่สุด โดยเฉพาะบน VPS ที่มี RAM และ CPU จำกัด การจูนค่าตัวแปรใน my.cnf ให้เหมาะสมกับทรัพยากรที่มีและลักษณะ workload ของระบบ สามารถเพิ่มความเร็ว query ได้ตั้งแต่ 2 เท่าไปจนถึงหลายสิบเท่า บทความนี้รวบรวมเทคนิคการจูน MySQL/MariaDB บน VPS อย่างละเอียด พร้อมคำสั่งและค่าที่แนะนำจริงจากประสบการณ์ใช้งาน

ก่อนเริ่มจูน สิ่งสำคัญที่สุดคือการเข้าใจว่า "ไม่มีค่า one-size-fits-all" การจูนที่เหมาะสมต้องอาศัยข้อมูล เช่น ขนาด RAM บน VPS จำนวน connection พร้อมกัน และประเภท workload ว่าเน้น read หรือ write ค่าที่แนะนำในบทความนี้เป็นจุดเริ่มต้นที่ดีสำหรับ VPS ขนาดกลาง (RAM 2–8GB) ที่รัน LAMP Stack หรือ WordPress

ทำความเข้าใจไฟล์ my.cnf ก่อนจูน

ไฟล์ตั้งค่าหลักของ MySQL/MariaDB คือ /etc/mysql/my.cnf หรือ /etc/my.cnf (แล้วแต่ distro) บน Ubuntu/Debian อาจอยู่ใน /etc/mysql/mysql.conf.d/mysqld.cnf ตรวจสอบตำแหน่งที่ระบบใช้จริงด้วย:

mysql --verbose --help | grep "my.cnf"
# หรือตรวจสอบ
mysqld --verbose --help 2>/dev/null | grep -A 1 "Default options"

ก่อนแก้ไขควร backup ไฟล์เดิมเสมอ:

sudo cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup.$(date +%Y%m%d)

โครงสร้างไฟล์ my.cnf แบ่งเป็น section ต่างๆ section ที่สำคัญสำหรับ server คือ [mysqld] ซึ่งตั้งค่าพฤติกรรมของ daemon ส่วน [mysql] และ [client] ตั้งค่าของ command-line client การแก้ไขผิด section จะไม่ส่งผล ดังนั้นต้องระวังให้ดี

ตรวจสอบสถานะ MySQL ก่อนจูน

การจูนที่ดีต้องเริ่มจากการเก็บ baseline ก่อน เพื่อเปรียบเทียบผลลัพธ์หลังจูน คำสั่งต่อไปนี้ให้ข้อมูลที่จำเป็น:

# ดูค่าตัวแปรปัจจุบัน
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'innodb%';"
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'key_buffer%';"

# ดู Status ปัจจุบัน
mysql -u root -p -e "SHOW GLOBAL STATUS;"

# ดู Process ที่กำลังรัน
mysql -u root -p -e "SHOW PROCESSLIST;"

# ดูข้อมูล engine
mysql -u root -p -e "SHOW ENGINE INNODB STATUS\G"

นอกจากนั้นควรดูการใช้ทรัพยากรระดับ OS ด้วย:

# ดู RAM ที่ใช้ทั้งหมด
free -h

# ดู process ที่ใช้ RAM มาก
ps aux --sort=-%mem | head -10

# ดูขนาด database
du -sh /var/lib/mysql/*

ค่าสำคัญที่ต้องจูน — InnoDB Buffer Pool

ค่าที่ส่งผลมากที่สุดต่อประสิทธิภาพ InnoDB คือ innodb_buffer_pool_size ซึ่งกำหนดขนาดหน่วยความจำที่ MySQL ใช้แคช data และ index ของ InnoDB ค่า default ต่ำมาก (128MB) ซึ่งไม่เพียงพอสำหรับฐานข้อมูลขนาดกลางถึงใหญ่

หลักการตั้งค่า innodb_buffer_pool_size:

• ถ้า MySQL รันบน VPS โดยไม่มี service อื่น → ตั้งที่ 70–80% ของ RAM ทั้งหมด
• ถ้ารัน LAMP Stack (Apache + PHP + MySQL) → ตั้งที่ 50–60% ของ RAM
• ถ้า RAM ≤ 1GB → ตั้งที่ 256M–512M เพื่อเหลือ RAM ให้ OS และ service อื่น

ตัวอย่างการตั้งค่าสำหรับ VPS RAM 4GB ที่รัน LAMP:

[mysqld]
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2   # แบ่งเป็น 2 instance (แนะนำ 1 instance ต่อ 1GB)
innodb_log_file_size = 256M        # ยิ่งใหญ่ยิ่ง write performance ดี แต่ recovery นานขึ้น
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2  # 1=ปลอดภัยสูงสุด, 2=เร็วขึ้น (อาจเสีย 1 วินาทีสุดท้าย)
innodb_flush_method = O_DIRECT     # ลด double-buffering กับ OS page cache

ค่า Connection และ Thread — รองรับผู้ใช้พร้อมกันได้มากขึ้น

ค่า default ของ max_connections ใน MySQL คือ 151 ซึ่งเพียงพอสำหรับเว็บขนาดเล็ก แต่ถ้าเว็บไซต์มี traffic สูง หรือรัน WordPress หลายไซต์ อาจพบ error "Too many connections" ได้ อย่างไรก็ตาม การเพิ่ม max_connections ไม่ใช่แค่ตัวเลข เพราะแต่ละ connection ใช้ RAM ประมาณ 1–2MB ดังนั้นต้องคำนวณให้พอดีกับ RAM ที่มี

[mysqld]
max_connections = 200              # สำหรับ VPS RAM 4GB
thread_cache_size = 50             # cache thread เพื่อลด overhead การสร้าง thread ใหม่
thread_stack = 192K
back_log = 128                     # queue รอ connection เมื่อ thread เต็ม

# Per-connection memory (ใช้ทุก connection)
sort_buffer_size = 2M              # ใช้เมื่อ sort ข้อมูล
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

คำนวณ memory สูงสุดที่ MySQL อาจใช้แบบคร่าวๆ:

# Total max memory ≈ innodb_buffer_pool_size + (max_connections × per_connection_memory)
# ตัวอย่าง: 2048M + (200 × (2M + 2M + 1M + 2M)) = 2048M + 1400M = ~3.4GB
# ต้องไม่เกิน RAM ทั้งหมดลบ ~1GB สำหรับ OS

เปิดและวิเคราะห์ Slow Query Log

Slow Query Log เป็นเครื่องมือสำคัญในการหา query ที่เป็นปัญหา ก่อนจูน query ต้องรู้ว่า query ไหนช้า จึงจะแก้ได้ตรงจุด

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1               # บันทึก query ที่ใช้เวลา > 1 วินาที
log_queries_not_using_indexes = 1 # บันทึก query ที่ไม่ใช้ index ด้วย
min_examined_row_limit = 100      # ไม่ log query ที่อ่านแถวน้อยกว่า 100 แถว

หลังจาก restart MySQL และรอให้มีข้อมูลสักพัก ใช้เครื่องมือ mysqldumpslow วิเคราะห์:

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

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

# ดู query ที่ lock นานที่สุด
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

เมื่อพบ query ที่ช้า ให้ใช้ EXPLAIN เพื่อดูว่า MySQL วาง execution plan อย่างไร:

EXPLAIN SELECT * FROM orders
  WHERE user_id = 123
  AND status = 'pending'
  ORDER BY created_at DESC;

-- ถ้า type=ALL หรือ rows สูงมาก แสดงว่าขาด index
-- เพิ่ม index:
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status, created_at);

ตารางเปรียบเทียบค่าตัวแปรสำคัญ

ตารางต่อไปนี้สรุปค่าแนะนำสำหรับ VPS ขนาดต่างๆ เพื่อใช้เป็นจุดเริ่มต้น ก่อนปรับให้เหมาะกับ workload จริงของคุณ:

การใช้ Query Cache และ Performance Schema

Query Cache เป็นฟีเจอร์ที่แคช result ของ SELECT query ไว้ใน RAM เพื่อให้ query เดิมที่รันซ้ำตอบสนองได้ทันที อย่างไรก็ตาม Query Cache มีข้อเสียที่สำคัญคือ MySQL ต้อง invalidate cache ทุกครั้งที่ตาราง update หรือ insert แม้จะเป็นแถวอื่นก็ตาม ทำให้บน site ที่มีการ write สูงมาก Query Cache อาจลดประสิทธิภาพแทนที่จะเพิ่ม

ใน MySQL 8.0 ได้ถอด Query Cache ออกไปแล้ว แต่ MariaDB ยังมีอยู่ ถ้าใช้ MariaDB และ workload เน้น read:

[mysqld]
# สำหรับ workload ที่ read >> write
query_cache_type = 1              # 0=off, 1=on สำหรับ query ที่ไม่มี SQL_NO_CACHE
query_cache_size = 64M            # ไม่ควรเกิน 128M (fragmentation ปัญหา)
query_cache_limit = 2M            # result สูงสุดที่จะ cache

สำหรับ MySQL 8.0+ หรือ workload write-heavy ให้ปิด query cache และใช้ Performance Schema แทนในการ monitor:

[mysqld]
query_cache_type = 0
query_cache_size = 0

# Performance Schema (ใช้ monitor ประสิทธิภาพ)
performance_schema = ON
performance_schema_events_statements_history_size = 100

จูน Table Cache และ Temporary Tables

เมื่อ MySQL เปิด table ทุกครั้ง จะใช้ file descriptor และ RAM สำหรับ table definition cache ถ้า table_open_cache ต่ำเกินไป MySQL จะต้องปิดและเปิด file ซ้ำๆ ซึ่งช้า โดยเฉพาะ WordPress multisite หรือ database ที่มีตารางมาก

[mysqld]
table_open_cache = 800            # ปรับตาม Opened_tables ใน SHOW STATUS
table_definition_cache = 1400    # cache table schema definition

# Temporary tables — ใช้เมื่อ GROUP BY, ORDER BY ไม่มี index
tmp_table_size = 64M              # ขนาด temp table ใน RAM ก่อนเขียน disk
max_heap_table_size = 64M         # ต้องเท่ากับ tmp_table_size

# Temp directory ให้อยู่ใน tmpfs ถ้าเป็นไปได้
# tmpdir = /tmp

ตรวจสอบว่าต้องเพิ่ม tmp_table_size หรือไม่:

mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"
# ถ้า Created_tmp_disk_tables / Created_tmp_tables > 5% ควรเพิ่ม tmp_table_size

การสร้าง Index ที่ถูกต้อง — หัวใจของ Query Optimization

การจูน my.cnf ช่วยได้มาก แต่ไม่มี buffer pool ขนาดไหนที่จะแก้ query ที่ขาด index ได้ การสร้าง index ที่ถูกต้องมักให้ผลลัพธ์มากกว่าการเพิ่ม RAM ในระยะยาว

ตรวจหา Table ที่ควรมี Index

-- ดู table ที่ถูก full scan มากที่สุด
SELECT * FROM sys.schema_tables_with_full_table_scans
ORDER BY rows_full_scanned DESC LIMIT 10;

-- ดู index ที่ไม่ได้ใช้งานเลย (MariaDB/MySQL 5.6+)
SELECT * FROM sys.schema_unused_indexes;

-- ดู index ที่ซ้ำซ้อน
SELECT * FROM sys.schema_redundant_indexes;

หลักการสร้าง Composite Index

-- สำหรับ query: WHERE user_id=? AND status=? ORDER BY created_at DESC
-- Index ที่ถูกต้อง: ใส่ column ที่ filter ก่อน ตามด้วย sort column
ALTER TABLE orders ADD INDEX idx_user_status_date (user_id, status, created_at);

-- ตรวจว่า index ถูกใช้หรือไม่
EXPLAIN SELECT * FROM orders
  WHERE user_id=1 AND status='pending'
  ORDER BY created_at DESC;

ข้อควรระวัง: index ทุกตัวเพิ่ม overhead ของ INSERT/UPDATE/DELETE เพราะ MySQL ต้อง update index ทุกครั้ง ดังนั้น index ควรสร้างเฉพาะตัวที่ใช้จริงเท่านั้น

ตัวอย่าง my.cnf ฉบับสมบูรณ์สำหรับ VPS RAM 4GB

ตัวอย่างนี้เหมาะสำหรับ VPS ที่รัน WordPress หรือ PHP application ทั่วไปบน RAM 4GB โดยมี Apache/Nginx และ PHP-FPM ทำงานพร้อมกัน:

[mysqld]
# === Basic Settings ===
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
bind-address = 127.0.0.1

# === Character Set ===
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# === InnoDB Settings ===
default_storage_engine = InnoDB
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 2

# === Connection Settings ===
max_connections = 200
max_allowed_packet = 64M
thread_cache_size = 50
thread_stack = 192K

# === Cache Settings ===
table_open_cache = 800
table_definition_cache = 1400
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
join_buffer_size = 2M

# === Logging ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# === Binary Log (ถ้าไม่ได้ทำ replication ปิดได้เพื่อประหยัด disk) ===
# skip-log-bin

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

ตรวจสอบและวัดผลหลังจูน

หลังจาก restart MySQL ด้วยค่าใหม่ ควรรอให้ระบบ warm up สักพัก (อย่างน้อย 1–2 ชั่วโมง) แล้วตรวจสอบผลลัพธ์:

# ตรวจ Buffer Pool Hit Rate (ควรสูงกว่า 99%)
mysql -u root -p -e "
SELECT
  (Innodb_buffer_pool_read_requests /
   (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)) * 100
   AS buffer_pool_hit_rate
FROM (
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests'
) a,
(
  SELECT
    VARIABLE_VALUE AS Innodb_buffer_pool_reads
  FROM performance_schema.global_status
  WHERE VARIABLE_NAME='Innodb_buffer_pool_reads'
) b;"

# ตรวจ Thread Cache Hit Rate (ควรสูงกว่า 90%)
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Threads_created';"
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Connections';"
# hit_rate = 1 - (Threads_created / Connections)

เครื่องมือที่ช่วยวิเคราะห์และแนะนำค่าอัตโนมัติ:

# MySQLTuner — script วิเคราะห์ config และแนะนำการจูน
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass yourpassword

# Percona Toolkit — pt-query-digest วิเคราะห์ slow log
sudo apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log | head -100

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

ค่า innodb_buffer_pool_size ควรตั้งเท่าไรบน VPS RAM 2GB

สำหรับ VPS ที่ใช้ InnoDB เป็นหลัก แนะนำตั้ง innodb_buffer_pool_size ที่ 50–70% ของ RAM ทั้งหมด บน VPS RAM 2GB ควรตั้งไว้ที่ 1G (1024M) หากรันเฉพาะ MySQL ผู้เดียว แต่ถ้ามี Web Server และ PHP อยู่ด้วย ให้ลดเหลือ 512M–768M เพื่อเหลือ RAM ให้ส่วนอื่นทำงาน การตั้งค่าเกิน RAM จริงจะทำให้ระบบ swap ซึ่งช้ากว่า RAM หลายเท่า

Slow Query Log คืออะไร และควรเปิดไว้ตลอดเวลาหรือไม่

Slow Query Log คือ log ที่ MySQL/MariaDB บันทึก query ที่ใช้เวลานานกว่าค่า long_query_time ที่กำหนด (ค่า default คือ 10 วินาที แต่แนะนำให้ตั้ง 1–2 วินาทีเพื่อจับ query ที่ช้ากว่าปกติ) การเปิดไว้ตลอดเวลาบน production server มี overhead เล็กน้อย แต่ยอมรับได้ ควรเปิดไว้เพื่อวิเคราะห์ปัญหาและปิดได้เมื่อระบบเสถียรดีแล้ว

MyISAM กับ InnoDB ต่างกันอย่างไร ควรใช้แบบไหน

InnoDB รองรับ Transactions, Foreign Keys, Row-level Locking ทำให้เหมาะกับงาน OLTP (write-heavy เช่น e-commerce, CMS) และ crash-safe กว่ามาก ส่วน MyISAM เร็วกว่าสำหรับงาน read-only (full-text search บางกรณี) แต่ไม่รองรับ Transactions และ lock ทั้งตารางเมื่อ write ทำให้ concurrent users สูงๆ ช้า ปัจจุบันแนะนำใช้ InnoDB เป็นค่าเริ่มต้นสำหรับทุกตาราง ยกเว้นมีเหตุผลเฉพาะเจาะจง

วิธีดูว่า MySQL ใช้ memory มากแค่ไหน และ tuning ได้ผลหรือเปล่า

ใช้คำสั่ง SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%' เพื่อดูสัดส่วนการใช้งาน buffer pool ถ้า Innodb_buffer_pool_pages_free เป็น 0 เกือบตลอดแสดงว่า buffer pool เต็มและควรเพิ่ม ดู Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads เพื่อคำนวณ hit rate (ควรสูงกว่า 99%) นอกจากนี้ใช้ SHOW PROCESSLIST ดู query ที่รันอยู่ และ mysqladmin status เพื่อดู Queries per second รวม