เปิด 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 ที่พบ

สิ่งที่ต้องเตรียม (Slow Query Log)

• สิทธิ์ 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: ดูค่าปัจจุบัน (Slow Query Log)

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: เปิดแบบชั่วคราว (ไม่ต้องรีสตาร์ต) (Slow Query Log)

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

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: เปิดแบบถาวรในไฟล์คอนฟิก (Slow Query Log)

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

ระบบ	ไฟล์	โฟลเดอร์ Log ที่แนะนำ
Ubuntu + MySQL	/etc/mysql/mysql.conf.d/mysqld.cnf	/var/log/mysql/
Ubuntu + MariaDB	/etc/mysql/mariadb.conf.d/50-server.cnf	/var/log/mysql/
AlmaLinux/Rocky + MariaDB	/etc/my.cnf.d/mariadb-server.cnf	/var/log/mariadb/
AlmaLinux/Rocky + MySQL (AppStream)	/etc/my.cnf.d/mysql-server.cnf	/var/log/mysql/

เพิ่มบรรทัดต่อไปนี้ใต้ 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

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

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'

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

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

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