Optimize MySQL บน VPS RAM น้อย ให้ทำงานเร็วขึ้นโดยไม่ต้องอัปเกรด

มิถุนายน 2026 · อ่าน 7 นาที

MySQL ที่ติดตั้งด้วยค่า default บน VPS RAM 1–2 GB มักทำงานได้ไม่เต็มประสิทธิภาพ เพราะค่าหลายตัวถูก set ให้รองรับ hardware หลากหลาย การปรับให้ตรงกับ workload ช่วยได้มาก

ตัวแปรที่สำคัญที่สุด: innodb_buffer_pool_size

นี่คือค่าที่ส่งผลมากที่สุด InnoDB ใช้ buffer pool เก็บ data และ index ใน RAM เพื่อลด disk I/O ค่า default มักแค่ 128MB ซึ่งน้อยมาก

กฏทั่วไป: ตั้งเป็น 50–70% ของ RAM ทั้งหมด

# VPS RAM 2 GB → ตั้ง 1G
# VPS RAM 4 GB → ตั้ง 2G–3G
innodb_buffer_pool_size = 1G

ไฟล์ config: /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# Buffer pool: 50-70% ของ RAM
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 2   # 1 ต่อทุก 1G

# Log file — ใหญ่ขึ้นช่วยลด checkpoint overhead
innodb_log_file_size = 256M

# Connection สูงสุด — ลดจาก default 151 ถ้า RAM น้อย
max_connections = 100

# Thread cache — ลด overhead ของการสร้าง thread ใหม่
thread_cache_size = 8

# Table cache
table_open_cache = 400

# Sort และ join buffer — per-connection RAM
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

# Tmp table size
tmp_table_size = 64M
max_heap_table_size = 64M

systemctl restart mysql

เปิด Slow Query Log หา Query ที่ช้า

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1     # log query ที่ใช้เวลามากกว่า 1 วินาที
log_queries_not_using_indexes = 1

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

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

ตรวจสอบสถานะและ Cache Hit Rate

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_reads: disk reads
-- Innodb_buffer_pool_read_requests: total reads
-- Hit rate = (requests - reads) / requests * 100
-- ควรได้ > 99% ถ้าต่ำกว่านี้ buffer pool ยังน้อยเกินไป

MariaDB กับ MySQL ต่างกันตรงไหน

MariaDB เป็น drop-in replacement ของ MySQL ที่หลาย distro เลือกใช้ config เกือบทั้งหมดใช้ร่วมกันได้ MariaDB มี Aria storage engine เพิ่มเติมและบางครั้ง optimize ได้ดีกว่าสำหรับ workload เฉพาะ

ตรวจสอบ RAM จริงที่ MySQL ใช้

ก่อนปรับ buffer pool ควรรู้ว่า MySQL กินอะไรไปบ้าง:

# ดู RAM ที่ MySQL process ใช้
ps aux | grep mysqld | awk '{print $6/1024 " MB"}'

# ดู current values
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'max_connections';

# ดู RAM ที่ theoretically สูงสุดที่ MySQL จะใช้
# (มักเกิน RAM จริง แต่ใช้ทั้งหมดพร้อมกันไม่ได้)
SELECT (@@key_buffer_size + @@query_cache_size + @@tmp_table_size
  + @@innodb_buffer_pool_size + @@innodb_log_buffer_size
  + @@max_connections * (
      @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
      + @@join_buffer_size + @@binlog_cache_size + @@thread_stack
      + @@tmp_table_size
  )) / 1024 / 1024 AS 'Est. Max RAM (MB)';

หา Index ที่หายไปด้วย slow query log

Query ที่ไม่ใช้ index มักช้าที่สุด ดูได้จาก slow log:

# วิเคราะห์ slow query log
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# ดู query execution plan
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
-- ถ้า type = ALL แปลว่า full table scan ควรเพิ่ม INDEX

-- เพิ่ม index
ALTER TABLE users ADD INDEX idx_email (email);

ค่า Config ตามขนาด RAM

ตรวจสอบว่าการ Optimize ได้ผลจริง

หลังปรับ config และ restart แล้ว ยืนยันว่าค่าใหม่ถูกโหลด และวัดผลด้วยตัวเลขจริง ไม่ใช่แค่ "รู้สึกว่าเร็วขึ้น":

-- 1. ยืนยันว่า buffer pool ใหม่ถูกโหลดจริง
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 2. วัด buffer pool hit ratio (ควร > 99%)
SHOW ENGINE INNODB STATUS\G   -- ดูบรรทัด "Buffer pool hit rate"

-- 3. หา query ที่ช้าที่สุด (เปิด slow query log ก่อน)
SHOW VARIABLES LIKE 'slow_query_log%';
-- แล้ววิเคราะห์ด้วย: mysqldumpslow -s t /var/log/mysql/slow.log | head

-- 4. ดู connection ที่ใช้จริงเทียบกับ max
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

แก้ปัญหาที่พบบ่อย

MySQL crash หลังแก้ innodb_buffer_pool_size

เกิดจากตั้งค่าสูงเกิน RAM จริง ให้ดู error log:

tail -100 /var/log/mysql/error.log | grep -i "error\|warn\|oom"

# ถ้าเห็น "Cannot allocate memory" ให้ลดค่าลง
# ดู RAM จริงที่เหลือก่อนตั้งค่า
free -h

MySQL ใช้ RAM มากกว่าที่ตั้งค่าไว้

เกิดจาก per-connection buffer ที่ alloc ตาม max_connections เมื่อมี connection พร้อมกันมาก ลด max_connections ถ้า traffic ไม่ได้สูงจริง หรือลด sort_buffer_size และ join_buffer_size:

SHOW STATUS LIKE 'Max_used_connections';
-- ดูว่าใช้จริงสูงสุดเท่าไร แล้วค่อยตั้ง max_connections ให้เหมาะสม

Slow query log ใหญ่มาก กิน disk

ตั้ง logrotate หรือหมุน log เองด้วย:

mysqladmin flush-logs
# หรือเพิ่มใน /etc/logrotate.d/mysql
/var/log/mysql/slow.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
}

SHOW STATUS แสดง Innodb_buffer_pool_reads สูง

แปลว่า buffer pool ยังน้อยเกินไป data ต้องอ่านจาก disk บ่อย ค่อย ๆ เพิ่ม innodb_buffer_pool_size แล้วดู hit rate หลัง restart 1–2 ชั่วโมง:

SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- hit rate = (read_requests - reads) / read_requests * 100
-- เป้าหมาย: > 99%

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

innodb_buffer_pool_size ควรตั้งเท่าไรสำหรับ VPS RAM 1 GB?

ตั้งที่ 512M (50% ของ RAM) เป็นจุดเริ่มต้นที่ปลอดภัย เหลือ RAM ไว้ให้ OS, PHP-FPM และ process อื่น ๆ ถ้า MySQL เป็น service เดียวบน VPS อาจเพิ่มเป็น 640–700M ได้ แต่อย่าเกิน 70%

MariaDB กับ MySQL ใช้ config เดียวกันได้ไหม?

ได้เกือบทั้งหมด ชื่อ parameter เหมือนกัน path config เหมือนกัน MariaDB มี parameter เพิ่มเติมบางตัวที่ MySQL ไม่มี เช่น aria_pagecache_buffer_size แต่ใช้ค่า default ไปก็ได้ถ้าไม่ได้ใช้ Aria tables

Slow query log กิน disk มากไหม?

ขึ้นอยู่กับจำนวน slow query ตั้ง long_query_time = 2 (2 วินาที) แทน 1 วินาทีเพื่อลด noise ใช้ logrotate หมุน log รายวันเพื่อควบคุม disk usage

ควรเปิด query cache ไหม?

MySQL 8.0 ลบ query cache ออกแล้ว ใน MySQL 5.7 และ MariaDB query cache มีประโยชน์สำหรับ read-heavy workload ที่ข้อมูลไม่เปลี่ยนบ่อย แต่สำหรับ write-heavy เช่น WooCommerce query cache อาจทำให้ช้าลงเพราะต้อง invalidate cache ทุก write

สรุป

การปรับ innodb_buffer_pool_size เพียงอย่างเดียวมักช่วยได้มากที่สุด ทำก่อนเสมอแล้วค่อยวัดผลด้วย slow query log และ cache hit rate ก่อนปรับค่าอื่น อย่าเดาโดยไม่มีข้อมูล