เปิด Slow Query Log เพื่อหา Query ที่ทำให้ฐานข้อมูลช้า

เมื่อเว็บไซต์ช้าลงและ CPU ของเซิร์ฟเวอร์ถูกใช้โดย mysqld หรือ mariadbd เป็นส่วนใหญ่ สาเหตุมักมาจาก Query ไม่กี่ตัวที่ทำงานช้า เช่น ค้นหาในตารางใหญ่โดยไม่มี Index หรือดึงข้อมูลเกินจำเป็น การเดาเอาเองว่า Query ไหนเป็นตัวปัญหามักเสียเวลา Slow Query Log ช่วยให้ฐานข้อมูลจดบันทึกทุก Query ที่ใช้เวลานานเกินกำหนดไว้ในไฟล์ เพื่อให้เห็นตัวการจริงพร้อมตัวเลข

บทความนี้แสดงวิธีเปิด Slow Query Log ทั้งแบบชั่วคราวและแบบถาวรบน MySQL 8 และ MariaDB 10.x วิธีอ่านผลด้วย mysqldumpslow และ pt-query-digest และการใช้ EXPLAIN ตรวจ Query ที่พบ

สิ่งที่ต้องเตรียม

• สิทธิ์ root บนเซิร์ฟเวอร์ และสิทธิ์ผู้ดูแลฐานข้อมูล (บน Ubuntu เข้าได้ด้วย sudo mysql)
• MySQL 8.0/8.4 หรือ MariaDB 10.x/11.x บน Ubuntu 22.04/24.04 หรือ AlmaLinux/Rocky Linux 8-9
• พื้นที่ดิสก์ว่างพอสมควร เพราะไฟล์ Log อาจโตเร็วบนเว็บที่มีผู้ใช้มาก

ขั้นตอนที่ 1: ดูค่าปัจจุบัน

sudo mysql

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

slow_query_log        OFF
slow_query_log_file   /var/lib/mysql/hostname-slow.log
long_query_time       10.000000

ค่าเริ่มต้นคือปิดอยู่ และนับว่าช้าเมื่อเกิน 10 วินาที ซึ่งนานเกินไปสำหรับเว็บไซต์ ปกติเราจะลดลงเหลือ 1 วินาทีหรือต่ำกว่า

ขั้นตอนที่ 2: เปิดแบบชั่วคราว (ไม่ต้องรีสตาร์ต)

เหมาะกับการตรวจปัญหาเฉพาะหน้า ค่าจะหายเมื่อรีสตาร์ตบริการ

SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = 'ON';

ใช้โฟลเดอร์ข้อมูล /var/lib/mysql เพราะผู้ใช้ mysql เขียนได้แน่นอนในทุก Distro ค่า long_query_time ที่ตั้งด้วย SET GLOBAL มีผลกับการเชื่อมต่อที่เกิดขึ้นใหม่เท่านั้น การเชื่อมต่อที่เปิดค้างอยู่ (เช่น Connection Pool ของแอป) ยังใช้ค่าเดิมจนกว่าจะเชื่อมต่อใหม่

ขั้นตอนที่ 3: เปิดแบบถาวรในไฟล์คอนฟิก

ตำแหน่งไฟล์คอนฟิกต่างกันตามระบบ

เพิ่มบรรทัดต่อไปนี้ใต้ Section [mysqld] (ตัวอย่างสำหรับ Ubuntu)

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

ไฟล์คอนฟิกของ Ubuntu มักมีบางบรรทัดเหล่านี้อยู่แล้วแต่ถูกใส่ # ไว้ ถ้าพบ ให้ลบ # หน้าบรรทัดและแก้ค่าแทนการเพิ่มใหม่ ห้ามประกาศตัวแปรเดียวกันซ้ำหลายที่ เพราะค่าที่อ่านทีหลังจะชนะ ทำให้สับสน

ตรวจว่าโฟลเดอร์ Log เป็นของผู้ใช้ mysql (บน AlmaLinux/Rocky ที่ใช้ MariaDB ให้เปลี่ยน path เป็น /var/log/mariadb)

ls -ld /var/log/mysql
sudo install -d -o mysql -g mysql -m 750 /var/log/mysql

รีสตาร์ตบริการ (ชื่อบริการ mysql บน Ubuntu+MySQL, mariadb สำหรับ MariaDB, mysqld บน AlmaLinux/Rocky+MySQL)

sudo systemctl restart mysql

ตัวเลือก log_queries_not_using_indexes บันทึกทุก Query ที่ไม่ใช้ Index แม้จะเร็ว มีประโยชน์ตอนพัฒนา แต่บนเครื่องจริงมักสร้าง Log จำนวนมาก จึงตั้งเป็น 0 ไว้ก่อน และเปิดชั่วคราวด้วย SET GLOBAL เมื่อต้องการ

หมายเหตุ: บน AlmaLinux/Rocky ที่เปิด SELinux ไฟล์ Log ควรอยู่ในโฟลเดอร์มาตรฐานข้างต้น ถ้าวางไว้ที่อื่น ฐานข้อมูลจะเขียนไม่ได้และบริการอาจเริ่มไม่ขึ้น ใน MariaDB 10.11 ขึ้นไป ชื่อตัวแปรใหม่คือ log_slow_query, log_slow_query_file และ log_slow_query_time แต่ชื่อเดิมในบทความนี้ยังใช้ได้

ขั้นตอนที่ 4: ทดสอบว่ามีการบันทึกจริง

สร้าง Query ที่ช้าเกิน 1 วินาทีโดยตั้งใจ

sudo mysql -e "SELECT SLEEP(2);"
sudo tail -n 20 /var/log/mysql/mysql-slow.log

จะเห็นรายการลักษณะนี้

# Time: 2026-09-19T03:14:07.123456Z
# User@Host: root[root] @ localhost []  Id:    42
# Query_time: 2.000214  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1789787647;
SELECT SLEEP(2);

ค่าที่สำคัญคือ Query_time (เวลาที่ใช้), Lock_time (เวลารอ Lock) และ Rows_examined เทียบกับ Rows_sent ถ้าตรวจหลายแสนแถวเพื่อส่งกลับไม่กี่แถว เป็นสัญญาณชัดว่าขาด Index

ขั้นตอนที่ 5: สรุปผลด้วย mysqldumpslow

หลังเก็บ Log ไประยะหนึ่ง (เช่น หนึ่งวันทำการ) ใช้ mysqldumpslow รวม Query ที่หน้าตาเหมือนกันเข้าด้วยกัน โดยแทนค่าตัวเลขและข้อความด้วย N และ 'S'

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

• -s t เรียงตามเวลารวม (ใช้ -s c เพื่อเรียงตามจำนวนครั้ง หรือ -s at ตามเวลาเฉลี่ย)
• -t 10 แสดง 10 อันดับแรก

Count: 1843  Time=1.92s (3538s)  Lock=0.00s (0s)  Rows=12.0 (22116), wpuser[wpuser]@localhost
  SELECT * FROM wp_postmeta WHERE meta_value = 'S'

ตัวอย่างนี้บอกว่า Query เดียวถูกเรียก 1,843 ครั้ง รวมเวลาเกือบหนึ่งชั่วโมง ควรแก้ตัวนี้ก่อน บน MariaDB คำสั่งอาจชื่อ mariadb-dumpslow (มีชื่อ mysqldumpslow ให้ใช้ด้วยในแพ็กเกจส่วนใหญ่)

ถ้าต้องการรายงานละเอียดกว่า ใช้ pt-query-digest จาก Percona Toolkit (Ubuntu ติดตั้งด้วย sudo apt install percona-toolkit ส่วน AlmaLinux/Rocky ติดตั้งจาก Repository ของ Percona)

sudo pt-query-digest /var/log/mysql/mysql-slow.log | less

ขั้นตอนที่ 6: ตรวจ Query ด้วย EXPLAIN

นำ Query ที่พบไปใส่ค่าจริงแล้วเติม EXPLAIN ข้างหน้า

EXPLAIN SELECT * FROM wp_postmeta WHERE meta_value = 'blue';

ดูคอลัมน์ต่อไปนี้

ทางแก้มักเป็นการเพิ่ม Index ให้คอลัมน์ที่ใช้ใน WHERE, JOIN หรือ ORDER BY การเพิ่ม Index บนตารางใหญ่ใช้เวลาและทรัพยากร ควรสำรองข้อมูลและทำในช่วงที่มีผู้ใช้น้อย ดู สำรองและกู้คืนฐานข้อมูล MySQL/MariaDB ด้วย mysqldump สำหรับเว็บ WordPress ที่ Query ช้ามาจากปลั๊กอิน การปิดหรือเปลี่ยนปลั๊กอินมักได้ผลเร็วและปลอดภัยกว่าการแก้โครงสร้างตารางเอง

ตรวจสอบผลลัพธ์

SHOW GLOBAL VARIABLES LIKE 'slow_query_log%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

slow_query_log ต้องเป็น ON และค่า Slow_queries จะเพิ่มขึ้นตามจำนวน Query ช้าที่เกิดขึ้นตั้งแต่เริ่มบริการ หลังแก้ Query หรือเพิ่ม Index แล้ว ใช้ mysqldumpslow กับ Log ช่วงใหม่เปรียบเทียบ Query ตัวเดิมควรหายไปจากอันดับต้น ๆ

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

เปิดแล้วแต่ไม่มีไฟล์ Log เกิดขึ้น

ผู้ใช้ mysql ไม่มีสิทธิ์เขียนโฟลเดอร์ หรือ SELinux ไม่อนุญาต ดู Error Log ของฐานข้อมูลด้วย sudo journalctl -u mysql -n 50 (หรือชื่อบริการของคุณ) และตรวจสิทธิ์โฟลเดอร์ตามขั้นตอนที่ 3

ไฟล์ Log โตจนดิสก์เต็ม

ตั้ง long_query_time ต่ำเกินไป หรือเปิด log_queries_not_using_indexes ทิ้งไว้ บน Ubuntu แพ็กเกจฐานข้อมูลมีไฟล์ logrotate สำหรับ /var/log/mysql/*.log ให้แล้ว ตรวจใน /etc/logrotate.d/ ถ้าไม่มี ให้ตั้งเองตาม ตั้งค่า logrotate ไม่ให้ไฟล์ Log เต็มดิสก์ เมื่อหาสาเหตุเสร็จแล้วสามารถปิดด้วย SET GLOBAL slow_query_log = 'OFF';

มี Query ช้า แต่ไม่ปรากฏใน Log

การเชื่อมต่อของแอปเปิดค้างไว้ตั้งแต่ก่อนเปลี่ยน long_query_time ให้รีสตาร์ต PHP-FPM หรือแอป เพื่อให้เชื่อมต่อใหม่ อีกกรณีคือความช้าไม่ได้อยู่ที่ฐานข้อมูล ตรวจ CPU, RAM และดิสก์ตาม ตรวจสอบ CPU, RAM และ Load Average ของ Linux Server

Query ส่วนใหญ่ช้าทุกตัวเท่า ๆ กัน

อาการนี้มักไม่ใช่ปัญหา Query รายตัว แต่เป็นหน่วยความจำของฐานข้อมูลไม่พอ ดู ปรับแต่ง MySQL/MariaDB เบื้องต้นด้วย innodb_buffer_pool_size

หากต้องการความช่วยเหลือวิเคราะห์ปัญหาฐานข้อมูลช้า ติดต่อทีมงาน THAI DATA CLOUD ได้ที่ https://thaidata.cloud/contact/