MariaDB ที่ยังใช้ค่า default — buffer pool 128M บนเครื่อง RAM 55 GB

พอดีผมนั่งไล่ตรวจสุขภาพเซิร์ฟเวอร์ตัวหนึ่งหลังย้ายระบบเสร็จได้ห้าวัน ตั้งใจว่าจะดูแค่ว่ามีอะไรตกหล่นจากการย้ายไหม แล้วไปสะดุดกับบรรทัดเดียวที่ทำให้ต้องหยุดอ่านนาน คือ innodb_buffer_pool_size ยังเป็น 128 MB อยู่เลย ทั้งที่เครื่องมี RAM 55 GB กับดิสก์ SSD และมีฐานข้อมูลรวม 5.81 GB วิ่งอยู่บนนั้น แปลว่าตั้งแต่วันติดตั้งมาไม่เคยมีใครแตะไฟล์ config เลย ผมเลยเก็บตัวเลขทั้งหมดมาทำเป็นแผนปรับ แล้วอยากเอามาเล่าให้ฟังทั้งกระบวนการ

ชื่อฐานข้อมูลและชื่อโดเมนในบทความเป็นของสมมติทั้งหมด (db_shop, db_drupal, db_wp) ส่วนตัวเลข ค่า config และผลที่วัดได้เป็นของจริง

เก็บ baseline ก่อนแตะอะไรทั้งสิ้น

กฎข้อแรกของผมเวลาจะปรับ config อะไรก็ตามคือ ถ้าไม่มีตัวเลข "ก่อน" ก็ไม่มีทางรู้ว่าที่ทำไปดีขึ้นจริงหรือแค่รู้สึกไปเอง ผมเลยดัมป์สถานะทั้งชุดเก็บไว้เป็นไฟล์ก่อน

mysql -u da_admin -p -e "
  SHOW GLOBAL STATUS WHERE Variable_name IN (
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_wait_free',
    'Innodb_log_waits',
    'Created_tmp_disk_tables',
    'Created_tmp_tables',
    'Open_table_definitions',
    'Open_tables',
    'Max_used_connections',
    'Threads_connected',
    'Queries',
    'Uptime'
  );
" > /root/db-baseline-before.txt

mysql -u da_admin -p -e "SHOW GLOBAL VARIABLES;" > /root/db-variables-before.txt

สิ่งที่อ่านออกมาได้จากไฟล์สองอันนั้น

ตัวเลขฝั่งภาระงานก็ไม่ได้เบานะครับ ทั้งเครื่องมี 113 ฐานข้อมูล รับ 77 โดเมนที่ยังใช้งานอยู่ นับการเชื่อมต่อสะสมได้ 448,690 ครั้งใน 5.3 วัน เฉลี่ยราว 78 ครั้งต่อวินาที ส่วน load เฉลี่ย 2.36 บนเครื่อง 16 core คิดเป็น 15% เท่านั้น แปลว่า CPU ไม่ใช่คอขวด

ตัวเลขที่ฟ้องว่าเครื่องกำลังอ่านดิสก์ฟรี ๆ

จุดที่ทำให้ผมมั่นใจว่าต้องแก้ ไม่ใช่ค่า 128 MB ตรง ๆ หรอกครับ แต่เป็นสามตัวนี้ที่อ่านคู่กัน

ห้าสิบล้านครั้งใน 5.3 วัน เฉลี่ยราว 5 ล้านครั้งต่อวัน นั่นคือจำนวนหน้าข้อมูลที่ InnoDB ต้องไปอ่านจากดิสก์เพราะไม่มีอยู่ในหน่วยความจำของตัวเอง

พูดง่าย ๆ คือหน่วยความจำ 46 GB ที่ Linux เอาไปทำ cache ไฟล์ให้อยู่นั้น มันช่วยจริง แต่ช่วยแบบเสียเที่ยว เพราะข้อมูลต้องถูกคัดลอกจาก page cache เข้ามาใน pool อีกทอด แล้ว InnoDB ก็ทิ้งมันไปเพราะไม่มีที่เก็บ วนแบบนี้ห้าล้านครั้งต่อวัน

กับดักแรก path กับชื่อ service บน DirectAdmin + Debian ไม่ตรงกับที่คนส่วนใหญ่บอก

ตรงนี้คือจุดที่ผมอยากเตือนคนที่จะทำตามคู่มือทั่วไปในอินเทอร์เน็ตมากที่สุด เพราะบทความส่วนใหญ่จะบอกให้ไปเขียนไฟล์ที่ /etc/mysql/mariadb.conf.d/ แล้ว restart mariadb.service ซึ่งบนเครื่อง DirectAdmin + Debian ที่ผมเจอผิดทั้งสองอย่าง

# ดูว่าไฟล์ my.cnf สั่งอ่าน config เพิ่มจากที่ไหน
grep -r includedir /etc/mysql/my.cnf
# !includedir /etc/mysql/conf.d/        <- ที่นี่ ไม่ใช่ mariadb.conf.d

ls -la /etc/mysql/conf.d/
# directadmin-infile.cnf
# directadmin-max-allowed-packet.cnf
# directadmin-native-password.cnf
# directadmin-socket.cnf
# 99-row-format.cnf                     <- ของเดิมที่มีคนวางไว้แล้ว อย่าไปแตะ

# ชื่อ unit จริงบนเครื่องนี้
systemctl list-units --type=service | grep -iE 'maria|mysql'
# mysqld.service    loaded active running    MariaDB database server

อีกเรื่องคือไฟล์ที่ขึ้นต้นด้วย directadmin- ในไดเรกทอรีนั้นเป็นของแผงควบคุม มันจะถูกเขียนทับได้ทุกเมื่อ ผมเลยตั้งชื่อไฟล์ของตัวเองว่า 99-tuning.cnf เหตุผลคือเลข 99 ทำให้ถูกอ่านทีหลังสุด ค่าที่เราตั้งเลยชนะค่าที่มาก่อน และชื่อไม่ซ้ำกับรูปแบบที่แผงควบคุมใช้ เลยไม่โดนเขียนทับตอนอัปเดต

เฟส 1 ปรับสดก่อน ยังไม่ต้อง restart

ค่าส่วนใหญ่ใน MariaDB รุ่นหลัง ๆ ปรับได้ทันทีขณะรัน รวมถึง buffer pool ด้วย ผมเลยทำเฟสนี้ก่อนเพื่อดูผลจริงว่าตัวเลขขยับไหม โดยยังไม่ต้องเสี่ยงกับการ restart

-- 1) ขยาย buffer pool แบบ online (ใช้เวลาราว 30 วินาที)
SET GLOBAL innodb_buffer_pool_size = 8589934592;   -- 8G

-- ดูความคืบหน้าการขยาย อย่าเพิ่งไปทำอย่างอื่นจนกว่าจะ completed
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

-- 2) โปรไฟล์ I/O สำหรับ SSD (ของเดิมเป็นค่าสำหรับจานหมุน)
SET GLOBAL innodb_io_capacity     = 4000;
SET GLOBAL innodb_io_capacity_max = 10000;

-- 3) ขยาย cache ของ table handle และ table definition
SET GLOBAL table_definition_cache = 4000;
SET GLOBAL table_open_cache       = 4000;

-- 4) ตารางชั่วคราวในหน่วยความจำ ลดการตกลงดิสก์เวลา GROUP BY / ORDER BY ใหญ่
SET GLOBAL tmp_table_size      = 67108864;   -- 64M
SET GLOBAL max_heap_table_size = 67108864;

-- 5) เปิด slow log เพื่อเก็บข้อมูลไว้ใช้รอบหน้า
SET GLOBAL slow_query_log              = ON;
SET GLOBAL long_query_time             = 1;
SET GLOBAL log_queries_not_using_indexes = ON;

-- 6) คืน RAM จาก key buffer ของ MyISAM ให้ pool เพราะข้อมูลส่วนใหญ่เป็น InnoDB แล้ว
SET GLOBAL key_buffer_size = 33554432;       -- 32M

เหตุผลที่ผมกล้าขยับ innodb_io_capacity จาก 200 ไป 4000 คือค่าเดิมเป็นค่าที่สมมติว่าดิสก์เป็นจานหมุน เช็คของจริงได้ง่ายมาก

lsblk -d -o NAME,ROTA,SIZE,MODEL
# NAME ROTA  SIZE
# sda     0  183G     <- ROTA=0 คือ SSD

ค่านี้ไม่ใช่การ "เร่ง" ให้เขียนเยอะขึ้น แต่เป็นการบอก InnoDB ว่างบประมาณ I/O เบื้องหลังที่มันใช้ได้มีเท่าไหร่ ถ้าตั้งต่ำเกินจริง มันจะกั๊กงานเขียน flush ไว้จนไปกองกันตอน checkpoint แล้วเกิดอาการสะดุดเป็นช่วง ๆ แทน

เฟส 2 เขียนลงไฟล์ แล้ว restart หนึ่งครั้ง

ผมรอดูผลอยู่ประมาณสี่ชั่วโมง เห็นว่าอัตราการอ่านดิสก์ลดลงชัดเจนและไม่มีอะไรผิดปกติ ก็เขียนค่าทั้งชุดลงไฟล์ให้อยู่ถาวร

# /etc/mysql/conf.d/99-tuning.cnf
[mysqld]

# === Memory ===
innodb_buffer_pool_size      = 8G
innodb_buffer_pool_instances = 8
innodb_log_file_size         = 512M
innodb_log_buffer_size       = 32M
key_buffer_size              = 32M

# === I/O profile สำหรับ SSD ===
innodb_flush_method          = O_DIRECT
innodb_io_capacity           = 4000
innodb_io_capacity_max       = 10000
innodb_read_io_threads       = 8
innodb_write_io_threads      = 8
innodb_flush_neighbors       = 0
innodb_flush_log_at_trx_commit = 1

# === Concurrency ===
max_connections   = 200
thread_cache_size = 32

# === Caches ===
table_definition_cache = 4000
table_open_cache       = 4000
tmp_table_size         = 64M
max_heap_table_size    = 64M

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

สองบรรทัดที่ผมอยากอธิบายเพิ่ม เพราะมีคนถามบ่อย

innodb_flush_neighbors = 0 มีไว้ปิดพฤติกรรมที่ InnoDB พยายามเขียนหน้าที่อยู่ติดกันไปพร้อมกันเพื่อประหยัดการขยับหัวอ่าน ซึ่งเป็นเรื่องของจานหมุนล้วน ๆ บน SSD มันไม่มีหัวอ่านให้ขยับ การรวมงานแบบนั้นเลยกลายเป็นเขียนเกินความจำเป็นเฉย ๆ

innodb_flush_log_at_trx_commit = 1 ผมคงค่าเดิมไว้ ตัวเลือก 2 จะทำให้เขียนเร็วขึ้นราวสามเท่า แลกกับความเสี่ยงเสียข้อมูลหนึ่งวินาทีสุดท้ายถ้าไฟดับ บนเครื่องที่มีข้อมูลของคนอื่นฝากอยู่ ผมมองว่าไม่คุ้มที่จะแลก คำตอบสุดท้ายคือ 1 เท่านั้น ถ้าเป็นเครื่องส่วนตัวที่ข้อมูลหายแล้วสร้างใหม่ได้ก็ตามสะดวกครับ

ขั้นตอน restart ผมทำแบบนี้ทุกครั้ง

1. สำรองสถานะเดิมทั้งชุดไว้ก่อน ทั้ง SHOW GLOBAL VARIABLES, SHOW GLOBAL STATUS และไฟล์ my.cnf เดิม เก็บไว้ในโฟลเดอร์ที่มีวันที่กำกับ
2. ตรวจไวยากรณ์ของ config ก่อนเสมอ ด้วยคำสั่งที่ให้ตัว daemon อ่านไฟล์เองแล้วรายงานว่าอ่านได้ไหม
3. เลือกเวลาช่วงตีสองถึงตีสี่ ทราฟฟิกต่ำที่สุดของวัน
4. restart แล้วจับเวลา จากนั้นยิงเช็คเว็บทุกตัวว่ากลับมาแล้วจริง ไม่ใช่แค่ดูว่า service ขึ้น

# 2) ตรวจไฟล์ก่อน ถ้าไวยากรณ์ผิดจะได้รู้ตอนนี้ ไม่ใช่ตอน service ไม่ยอมขึ้น
mysqld --help --verbose > /dev/null && echo "[OK] config อ่านได้"

# 4) restart แล้วจับเวลาจริง
time systemctl restart mysqld

# ตรวจว่าค่าเข้าจริงไหม อย่าเชื่อว่าเขียนไฟล์แล้วคือเสร็จ
mysql -u da_admin -p -e "
  SHOW VARIABLES WHERE Variable_name IN (
    'innodb_buffer_pool_size','innodb_io_capacity',
    'innodb_log_file_size','slow_query_log');"

# ยิงเช็คเว็บทุกโดเมนที่หนักสุด
for d in shop.example blog.example forum.example; do
  printf '%s -> ' "$d"
  curl -s -o /dev/null -w "%{http_code}\n" "https://$d/"
done

ผลจริงของรอบนี้คือดาวน์ไทม์ 6.5 วินาที แล้วเว็บทั้ง 9 ตัวที่ผมยิงเช็คตอบ 200 ครบทุกตัว แผนบนกระดาษผมประเมินไว้ที่ 3 ถึง 5 วินาที ของจริงเลยเกินไปนิดหน่อยเพราะต้องสร้าง redo log ไฟล์ใหม่ตามขนาดที่เปลี่ยน ตรงนี้ผู้ใช้ที่กำลังกดหน้าเว็บพอดีจะเห็นข้อความ database connection error หนึ่งครั้งแล้วรีเฟรชก็หาย ถ้าใครรับไม่ได้แม้แต่วินาทีเดียว ต้องไปทางอื่นคือทำ replica แล้วสลับ ซึ่งเป็นคนละเรื่องกันเลยครับ

ค่าก่อนและหลังทั้งชุด

ตรรกะเบื้องหลังตัวเลขไม่ได้ซับซ้อนเลยครับ ฐานข้อมูลรวมทั้งเครื่อง 5.81 GB ผมตั้ง pool ไว้ 8 GB คือใหญ่กว่าข้อมูลทั้งหมด แปลว่าข้อมูลร้อนมีสิทธิ์อยู่ในหน่วยความจำได้ครบตลอดเวลา ส่วนที่ไม่ตั้ง 16 GB ทั้งที่ RAM เหลือ เพราะต้องกันที่ให้ web server, mail และงานอื่นบนเครื่องเดียวกันตอน peak ด้วย คิดกรณีแย่ที่สุดออกมาแล้วประมาณนี้

# กรณีแย่สุดที่ MariaDB จะกินได้
#   buffer pool 8G                        = 8,192 MB
#   key_buffer 32M                        =    32 MB
#   200 connection x ~2.9 MB/thread       =   580 MB
#   log buffer 32M + table caches ~50M    =    82 MB
#   ------------------------------------------------
#   รวม                                   = 8,886 MB  (~8.7 GB)
# เหลือให้ Linux + DA + web + mail + buff/cache อีกราว 46 GB

table_definition_cache จาก 400 เป็น 4000 ก็มีเหตุผลตรงไปตรงมา บนเครื่องนี้มี 113 ฐานข้อมูล ตารางรวมกันเกินห้าพันตาราง ค่า 400 แปลว่ามันต้องปิดแล้วเปิดนิยามตารางใหม่ตลอดเวลา เป็นงานที่ไม่ได้สร้างประโยชน์อะไรเลย

rollback ต้องเตรียมไว้ก่อนเสมอ

ผมไม่เคยแตะ config ฐานข้อมูลโดยไม่เขียนวิธีถอยไว้ก่อน เพราะเวลาที่จะได้ใช้มันคือตอนตีสามที่หัวไม่แล่นแล้ว

# เตรียมก่อนลงมือ — เก็บของเดิมไว้ให้ครบ
mkdir -p /root/rollback-$(date +%F)/db-tune
cp /etc/mysql/my.cnf /root/rollback-$(date +%F)/db-tune/
mysql -u da_admin -p -e "SHOW GLOBAL VARIABLES;" \
  > /root/rollback-$(date +%F)/db-tune/variables-before.txt

# ถอยจริง — เอาไฟล์ออกแล้ว restart ก็กลับไปค่าเดิมทั้งหมด
mv /etc/mysql/conf.d/99-tuning.cnf /root/99-tuning.cnf.disabled
systemctl restart mysqld

เหตุผลที่วิธีถอยมันง่ายขนาดนี้ เพราะผมเขียนของใหม่เป็นไฟล์แยกไฟล์เดียว ไม่ไปแก้ my.cnf เดิม ไม่ไปแก้ไฟล์ของแผงควบคุม เอาออกไฟล์เดียวจบ ส่วนตัวผมถือเป็นกฎเลยว่าอย่าแก้ไฟล์ที่ระบบอื่นเป็นเจ้าของ ให้เพิ่มไฟล์ของเราต่างหากเสมอ

เฟส 3 MyISAM อีกราวห้าร้อยตารางที่ยังรอแปลง

ตอนไล่ตรวจ ผมเจอว่ายังมีตาราง MyISAM ค้างอยู่ในระบบราว 500 ตาราง ส่วนใหญ่มาจากเว็บรุ่นเก่าที่ย้ายตามกันมา

ทำไมต้องแปลง เหตุผลหลักไม่ใช่ความเร็วนะครับ แต่เป็นสองข้อนี้ หนึ่ง MyISAM ล็อกทั้งตารางเวลาเขียน ใครเขียนอยู่คนอื่นรอหมด พอเว็บมีคนใช้พร้อมกันหลายคนจะเห็นเป็นอาการค้างทั้งเว็บ สอง MyISAM ไม่มีระบบกู้คืนหลังเครื่องดับ ถ้าไฟดับกลางคันมีสิทธิ์ได้ตารางเสียมาแทน ส่วนเรื่องที่ InnoDB ใช้ buffer pool ที่เราเพิ่งขยายไป 8 GB ในขณะที่ MyISAM ใช้ได้แค่ key buffer 32 MB นั่นเป็นของแถม

วิธีที่ผมใช้คือให้ฐานข้อมูลเขียนคำสั่ง ALTER ให้เราเอง ไม่ต้องนั่งพิมพ์เอง

-- ดูรายการก่อน เรียงจากตารางเล็กไปใหญ่ เพื่อไล่ทำจากตัวที่เสี่ยงน้อยที่สุด
SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name, '` ENGINE=InnoDB;') AS stmt,
       ROUND((data_length + index_length)/1024/1024, 1) AS mb
FROM information_schema.tables
WHERE engine = 'MyISAM'
  AND table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY (data_length + index_length) ASC;

# พอดูรายการแล้วพอใจ ค่อยสั่งทำจริงทีละฐานข้อมูล เริ่มจากตัวเล็กสุด
mysql -u da_admin -p -Nse "
  SELECT CONCAT('ALTER TABLE \`db_wp\`.\`', table_name, '\` ENGINE=InnoDB;')
  FROM information_schema.tables
  WHERE engine='MyISAM' AND table_schema='db_wp';
" | mysql -u da_admin -p

เฟสนี้ผมยังทำไม่ครบนะครับ ตั้งใจไว้ว่าจะไล่ทีละเว็บโดยแจ้งเจ้าของเว็บก่อนทุกครั้ง เพราะบางเว็บใช้ปลั๊กอินเก่ามากที่ยังเขียนคำสั่งเฉพาะของ MyISAM อยู่ ต้องทดสอบก่อนไม่งั้นเจ็บฟรี

ผลข้างเคียงที่ได้มาโดยไม่ตั้งใจ

เปิด slow log ไปแล้ว 3 วินาที ไฟล์โต 171 KB ทันที ตอนแรกผมนึกว่าตั้ง long_query_time ผิด แต่ไปเปิดดูของจริงแล้วคือมี query ที่ช้าเกินหนึ่งวินาทีจริง ๆ เยอะมาก ส่วนใหญ่มาจากปลั๊กอินและธีมของเว็บลูกค้าที่เขียน query โดยไม่มี index รองรับ

สรุปสั้น ๆ ตามที่ผมเจอมา เครื่องนี้มีฮาร์ดแวร์เหลือเฟือตั้งแต่วันแรก แต่ซอฟต์แวร์ถูกตั้งไว้ให้ใช้แค่เศษเสี้ยวเดียวเพราะไม่มีใครเปิดดู การขยับ buffer pool จาก 128 MB เป็น 8 GB กับปรับโปรไฟล์ I/O ให้ตรงกับดิสก์จริง ใช้เวลาทั้งหมดไม่ถึงครึ่งชั่วโมงและมีดาวน์ไทม์ 6.5 วินาที ไม่ต้องซื้ออะไรเพิ่มสักบาท

ใครจะเอาไปใช้ อย่าลอกตัวเลขผมไปทั้งชุดนะครับ ต้องเอา RAM จริงกับขนาดฐานข้อมูลจริงของเครื่องตัวเองมาคิดก่อน หลักที่ใช้ได้กับทุกเครื่องคือ pool ควรใหญ่พอที่จะเก็บข้อมูลส่วนที่ถูกเรียกใช้บ่อยได้ครบ และต้องเหลือที่ให้ระบบอื่นบนเครื่องเดียวกันหายใจด้วย ตรงไหนผมอธิบายตกหล่นหรือพิมพ์ผิด เดี๋ยวกลับมาแก้เพิ่มให้ภายหลังครับ </content>