ตั้งค่า MySQL 8.4 สำหรับ production มีอะไรต้องตั้งบ้าง

ลง MySQL บน Ubuntu ด้วย apt install mysql-server เสร็จ แอป Laravel ก็ต่อได้เลยโดยไม่ต้องแก้ config สักบรรทัด เครื่องใหม่ดิสก์ยังเหลือเยอะ ทุกอย่างดูปกติ แต่ค่าเริ่มต้นของ MySQL ตั้งมาให้ใช้ได้กับทุกเครื่อง ไม่ได้ตั้งมาให้พอดีกับเครื่องเรา บางค่ากินดิสก์ไปเงียบ ๆ ทุกวัน บางค่าทำให้ MySQL ใช้แรมแค่นิดเดียวทั้งที่เครื่องยังเหลืออีกเยอะ

ค่าเริ่มต้นของ MySQL 8.4 ที่ควรตัดสินใจเอง#

ค่าในตารางนี้มาจากคู่มือของ MySQL 8.4 ดูค่าที่เครื่องเราใช้อยู่จริงได้ด้วย SELECT @@ชื่อตัวแปร; เช่น SELECT @@log_bin, @@binlog_expire_logs_seconds;

บน Ubuntu ไฟล์ config อยู่ใน /etc/mysql/mysql.conf.d/ เราไม่ต้องแก้ mysqld.cnf ของแพ็กเกจ สร้างไฟล์ของเราเองในโฟลเดอร์เดียวกัน เช่น zz-production.cnf แล้วใส่ค่าทั้งหมดในโพสต์นี้ไว้ที่เดียว ตอนอัปเกรดแพ็กเกจจะได้ไม่ชนกัน restart MySQL แล้วเช็กด้วย SELECT @@… อีกรอบว่าค่าเข้าจริง

ตัดสินใจเรื่อง binlog ก่อนค่าอื่น#

binlog (binary log) คือไฟล์ที่ MySQL จดทุกการแก้ข้อมูลไว้เรียงตามเวลา ใช้สองงาน คือส่งให้ replica เล่นตาม กับกู้ข้อมูลย้อนเวลา (point-in-time recovery) รายละเอียดอยู่ในโพสต์ ลบไฟล์ binlog ใน MySQL อย่างปลอดภัย

ขนาดของ binlog ไม่ได้ขึ้นกับขนาดฐานข้อมูล แต่ขึ้นกับว่าแอปเขียนข้อมูลบ่อยแค่ไหน format ที่ MySQL ใช้เป็นค่าเริ่มต้นคือ ROW ซึ่งจดค่าก่อนแก้และหลังแก้ของทุกแถว ฐานข้อมูล 4 GB ที่มีงานอัปเดตแถวเดิมซ้ำทุกนาที สร้าง binlog ได้เป็นร้อย GB ใน 30 วัน

ก่อนตั้งค่า ตอบสองข้อนี้ให้ได้

1. มี replica ที่อ่าน binlog จากเครื่องนี้อยู่ไหม
2. ถ้าข้อมูลพังตอนบ่าย ต้องกู้ให้ถึงนาทีก่อนพัง หรือกู้ถึง backup เมื่อคืนก็พอ

ไม่มี replica และกู้ถึง backup ล่าสุดก็พอ ปิด binlog ไปเลย คู่มือรับได้ทั้ง disable_log_bin และ skip-log-bin ผลเหมือนกัน

[mysqld]
disable_log_bin

restart แล้ว SELECT @@log_bin; ต้องได้ 0 ส่วนไฟล์ binlog ที่ค้างอยู่ ลบตามขั้นในโพสต์ binlog ได้เลย

มี replica หรือต้องกู้ข้อมูลย้อนเวลา เก็บ binlog ไว้ แต่ลดเวลาเก็บลงและบีบอัดไฟล์

[mysqld]
# เก็บ 3 วัน (3 × 24 × 60 × 60)
binlog_expire_logs_seconds = 259200
binlog_transaction_compression = ON

• เวลาเก็บ ต้องนานกว่าช่วงที่ replica อาจหลุดไปนานที่สุด และถ้าใช้กู้ข้อมูลย้อนเวลา ต้องนานกว่าระยะห่างระหว่าง backup เต็มสองรอบ เพราะการกู้ต้องเล่น binlog ต่อจาก backup ล่าสุด
• binlog_transaction_compression บีบอัดแต่ละ transaction ด้วย zstd ก่อนเขียนลงไฟล์ ปิดอยู่เป็นค่าเริ่มต้น ลองอัปเดตตาราง 1,000 แถวทั้งตารางบน MySQL 8.4 binlog เหลือราว 1 ใน 10 แต่ข้อมูลจริงจะบีบได้แค่ไหนขึ้นกับข้อมูลของแต่ละระบบ และแลกกับ CPU ที่ใช้เพิ่ม
• binlog_row_image = MINIMAL จดแค่คอลัมน์ที่ใช้หาแถว (เช่น primary key) กับคอลัมน์ที่คำสั่งตั้งค่า แทนทั้งแถว ในการทดลองเดียวกันเล็กลงราวหนึ่งในสาม แต่คู่มือเตือนว่าถ้าตารางบน replica มีโครงสร้างต่างจากต้นทาง ข้อมูลสองฝั่งอาจเพี้ยนไปโดยไม่มี error ตัวนี้จึงเหมาะกับคนที่คุมโครงสร้างตารางทุกฝั่งได้

ให้ buffer pool ได้แรมพอกับข้อมูลที่ใช้บ่อย#

InnoDB เก็บข้อมูลกับ index ที่อ่านบ่อยไว้ใน buffer pool ค่าเริ่มต้น 128 MB เล็กมากสำหรับเครื่องที่มีแรมหลาย GB query ที่ควรตอบจากแรมได้ก็ต้องไปอ่านดิสก์แทน

• เครื่องที่รันแค่ MySQL ตั้ง innodb_dedicated_server = ON แล้ว MySQL จะคำนวณให้เอง เครื่องแรมเกิน 4 GB ได้ buffer pool 75% ของแรม
• เครื่องที่มี nginx, PHP-FPM, Redis และ queue worker อยู่ด้วย อย่าเปิดตัวนี้ คู่มือเองก็ไม่แนะนำบนเครื่องที่ใช้ร่วมกับโปรแกรมอื่น ให้ตั้งเอง ดูขนาดข้อมูลกับ index รวมทุกฐานข้อมูลก่อน (query อยู่ในหัวข้อถัดไป) ถ้ารวมแล้วเล็กกว่าแรมที่เหลือ ตั้งเท่าขนาดข้อมูลบวกเผื่อโตก็พอ ถ้าใหญ่กว่า ก็แบ่งแรมให้ MySQL โดยกันส่วนของ PHP-FPM worker ทุกตัวไว้ก่อน

[mysqld]
# ตัวอย่างเครื่องแรม 8 GB ที่มีแอปอยู่ด้วย และข้อมูลรวมราว 1.5 GB
innodb_buffer_pool_size = 2G

เปิด slow query log ไว้ตลอด#

slow query log จด query ที่ใช้เวลานานกว่า long_query_time ไว้ให้ ค่าเริ่มต้นปิดอยู่และนับที่ 10 วินาที ซึ่งนานจนผู้ใช้กดปิดหน้าไปก่อนแล้ว ลองเริ่มที่ 1 วินาที

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1

เก็บไฟล์ไว้ใน /var/log/mysql/ เพราะ logrotate ของแพ็กเกจ mysql-server หมุนไฟล์ในโฟลเดอร์นี้ให้ เวลาจะอ่าน ใช้ mysqldumpslow ที่มากับ MySQL สรุปว่า query แบบไหนช้าซ้ำ ๆ บ่อยที่สุด

ส่วน general log จดทุก query ที่เข้ามา รวมทุก SELECT ด้วย เปิดดูได้ช่วงสั้น ๆ ตอน debug แล้วปิด ถ้าเปิดค้างไว้บนเครื่องที่มีคนใช้ ไฟล์จะโตเร็วมาก

max_connections ค่าเริ่มต้น 151 ส่วนใหญ่พอ นับคร่าว ๆ จาก pm.max_children ของ PHP-FPM ทุกแอป บวก queue worker กับ scheduler ถ้ารวมไม่ถึงก็ไม่ต้องแก้ เพิ่มเมื่อเห็น error Too many connections จริง เพราะแต่ละ connection ใช้แรมของตัวเองด้วย

รายการที่ควรเช็กทุกเดือน#

ขนาดของแต่ละฐานข้อมูลและแต่ละตาราง#

information_schema.tables บอกขนาดข้อมูล (data_length) ขนาด index (index_length) และพื้นที่ว่างที่ InnoDB จองไว้ในไฟล์ (data_free) ของทุกตาราง ตัวเลขนี้ MySQL เก็บ cache ไว้ 24 ชั่วโมง (information_schema_stats_expiry) อยากได้ค่าล่าสุดให้ตั้งเป็น 0 ก่อน

SET SESSION information_schema_stats_expiry = 0;

SELECT table_schema,
       ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS size_gb,
       ROUND(SUM(data_free) / 1024 / 1024 / 1024, 2) AS free_gb
FROM information_schema.tables
GROUP BY table_schema
ORDER BY size_gb DESC;

อยากรู้ว่าตารางไหนใหญ่สุด เปลี่ยนเป็นเรียงทีละตาราง

SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024) AS size_mb,
       ROUND(data_free / 1024 / 1024) AS free_mb
FROM information_schema.tables
ORDER BY data_length + index_length DESC
LIMIT 20;

แล้วเทียบผลรวมกับขนาดโฟลเดอร์ /var/lib/mysql ที่ดูด้วย du (ดูขนาดโฟลเดอร์บน Linux) ถ้าโฟลเดอร์ใหญ่กว่าผลรวมมาก ส่วนที่เกินมักเป็น binlog

สถานะของ binlog#

SELECT @@log_bin, @@binlog_expire_logs_seconds; บอกว่าเปิดอยู่ไหมและเก็บกี่วัน ถ้าเปิด SHOW BINARY LOGS; บอกขนาดของทุกไฟล์ ถ้า binlog โตเร็วกว่าที่ควรเป็น มีวิธีหาว่าตารางไหนเขียนเยอะอยู่ในโพสต์ binlog

พื้นที่ว่างในตารางหลังลบข้อมูลเยอะ ๆ#

ลบแถวออกไปครึ่งตาราง ไฟล์ .ibd ของตารางไม่ได้เล็กลง InnoDB เก็บพื้นที่นั้นไว้ใช้ต่อ และแสดงเป็น data_free ถ้าอยากคืนพื้นที่ให้ดิสก์ ใช้ OPTIMIZE TABLE ซึ่งบน InnoDB คือสร้างตารางใหม่ทั้งตาราง

• ระหว่างนั้นอ่านและเขียนตารางได้ตามปกติ MySQL ล็อกแค่ช่วงสั้น ๆ ตอนเริ่มกับตอนจบ
• ต้องมีดิสก์ว่างราวขนาดของตาราง เพราะ MySQL สร้างไฟล์ใหม่ก่อนทิ้งไฟล์เก่า
• ทำเฉพาะตารางที่เพิ่งลบข้อมูลไปเยอะ ไม่ต้องสั่งกับทุกตารางทุกเดือน

ตาราง log ที่ไม่มีใครลบ#

ตารางที่โตเร็วที่สุดมักไม่ใช่ข้อมูลธุรกิจ แต่เป็นตารางที่จดเหตุการณ์ไว้เรื่อย ๆ อย่าง failed_jobs, log ของ API, ตาราง event ของระบบติดตาม error เช่น Watchtower หรือ Telescope ถ้าเปิดไว้บน production

• failed_jobs ตั้ง queue:prune-failed ใน scheduler
• ตาราง log ของเราเอง ใส่ trait Prunable ใน model แล้วตั้ง model:prune ให้รันทุกวัน (เอกสาร Laravel) คำสั่งนี้ลบทีละก้อนให้อยู่แล้ว
• ถ้าต้องลบเองด้วย SQL ทีละมาก ๆ ใส่ LIMIT 5000 แล้ววนจนลบไม่เหลือ ดีกว่า DELETE ก้อนเดียวที่ล็อกตารางนานและทำให้ transaction ใหญ่

ไฟล์ backup กับ log ของระบบ#

ไฟล์ mysqldump ใหญ่ตามตารางที่ใหญ่ที่สุด ถ้าเก็บไว้ 14 ชุด ตาราง log ที่โตขึ้น 2 GB ก็กินดิสก์เพิ่มเกือบ 30 GB ทุกครั้งที่ขนาด dump กระโดด ให้ดูขนาดไฟล์ dump ล่าสุดเทียบกับเดือนก่อนด้วย

นอกฐานข้อมูล log ของ systemd (journald) ก็โตได้ถึง 10% ของดิสก์ สูงสุด 4 GB ดูขนาดด้วย journalctl --disk-usage ถ้าอยากจำกัดให้เล็กลง สร้างไฟล์นี้แล้ว systemctl restart systemd-journald

[Journal]
SystemMaxUse=500M

กรณีที่อาจเจอบนเครื่องจริง#

binlog 183 GB บนฐานข้อมูลที่มีข้อมูลจริงแค่ 4 GB#

เซิร์ฟเวอร์ Ubuntu เครื่องหนึ่งที่เราดูแลมี Laravel 4 แอปใช้ MySQL 8.4 ตัวเดียวกัน ดิสก์ขึ้นไปถึง 264 GB จาก 390 GB ทั้งที่ตอนเปิดเครื่องใช้แค่ 3% ใน /var/lib/mysql มี binlog ไฟล์ละ 100 MB ราว 1,890 ไฟล์ รวม 183 GB ส่วนข้อมูลจริงทุกฐานข้อมูลรวมกันราว 4 GB ไม่มีใครตั้งค่า binlog เลย เครื่องจึงใช้ค่าเริ่มต้นที่เปิดไว้และเก็บ 30 วัน

binlog ขึ้นไฟล์ใหม่วันละราว 70 ไฟล์ เฉพาะช่วง 07:00 ถึง 20:00 ซึ่งตรงกับเวลาทำงานของระบบคลังสินค้าที่ sync ข้อมูลจาก ERP ทุกนาที พอนับ row event ในไฟล์เดียว (ราว 11 นาที) มีการ UPDATE ราว 211,000 แถวบนตาราง order lines ที่มีอยู่แค่ราว 37,000 แถว และคอลัมน์เดียวที่เปลี่ยนคือ updated_at ต้นเหตุฝั่งโค้ดกับวิธีแก้อยู่ในโพสต์ งาน sync ใน Laravel ที่เขียนเฉพาะแถวที่เปลี่ยน

เครื่องนี้ไม่มี replica และไม่ได้ใช้ binlog กู้ข้อมูลย้อนเวลา เราจึง PURGE BINARY LOGS ทิ้ง ดิสก์ลดจาก 265 GB เหลือ 82 GB ทันที แล้วปิด binlog ด้วย disable_log_bin

ตาราง error ที่ลากไฟล์ backup ให้โตตาม#

ช่วงเดียวกัน แอปหนึ่งเขียนไฟล์ในโฟลเดอร์ storage ไม่ได้เพราะสิทธิ์ของโฟลเดอร์ เกิด error ราว 27,000 ครั้งต่อวันติดกันสองวัน ทุกครั้งส่งไปที่ Watchtower ซึ่งเก็บ event ไว้ใน MySQL เครื่องเดียวกัน ตาราง event โตเป็น 3 GB แล้วไฟล์ mysqldump ทุกคืนก็โตจากราว 400 MB เป็น 2.8 GB ต่อชุด เก็บ 14 ชุดรวมเป็น 24 GB

แก้สิทธิ์โฟลเดอร์ให้ error หยุดก่อน แล้วลบ event ที่ท่วมทีละ 5,000 แถว จากนั้น OPTIMIZE TABLE ไฟล์ของตารางเล็กลงจาก 3.1 GB เหลือ 445 MB ใช้เวลาราว 4 นาที dump ชุดใหม่ได้ 454 MB เราเปิดดูว่าครบทุกตารางก่อน แล้วค่อยลบ dump ชุดที่บวมทิ้ง

รวมกับจำกัด journald และล้าง cache ของ apt ดิสก์ทั้งเครื่องลดจาก 264 GB เหลือ 54 GB ทั้งหมดนี้เกิดจากค่าที่ไม่มีใครตัดสินใจ ถ้าเซิร์ฟเวอร์ที่ดูแลอยู่ยังไม่เคยมีใครเปิดดู ลองเริ่มจากสองหัวข้อแรกในรายการเช็กประจำเดือนได้ครับ