همه مقالات تخصصی

لاگ کوئری‌های کند MySQL: کوئری‌هایی را که سایت را کند می‌کنند پیدا کنید

slow query log را در MySQL یا MariaDB با آستانهٔ معقول روشن کنید، با mysqldumpslow خلاصه کنید، EXPLAIN را بخوانید و امن خاموشش کنید.

لاگ کوئری‌های کند MySQL: کوئری‌هایی را که سایت را کند می‌کنند پیدا کنید

وقتی صفحهٔ وردپرس یا برنامه کند است، علت اغلب چند کوئری پایگاه داده است. slow query log دستورهایی را که از یک آستانه بیشتر طول می‌کشند ثبت می‌کند تا به جای حدس، بدترین‌ها را اصلاح کنید. این راهنما نشان می‌دهد چطور روشنش کنید، حجم فایل را مدیریت کنید، خلاصه‌اش کنید و از EXPLAIN استفاده کنید. تنظیمات از مستندات MySQL و MariaDB آمده‌اند؛ روی سرور شما اجرایشان نکرده‌ام و مسیر فایل‌ها و پیش‌فرض‌ها فرق می‌کنند.

۱. بررسی و فعال‌سازی لاگ

اول ببینید روشن است یا نه. می‌توانید بدون restart در زمان اجرا روشنش کنید که برای بررسی کوتاه مفید است:

-- Check current settings
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- Turn on at runtime (lost after restart)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'OFF';

آستانهٔ یک ثانیه شروع معقولی است. اگر چیزی دیده نشد بعداً کمترش کنید.

۲. پایدار و کم‌حجم نگهش دارید

تنظیمات زمان اجرا بعد از restart می‌روند. برای بررسی طولانی‌تر آن‌ها را در فایل پیکربندی بگذارید و مطمئن شوید پوشهٔ لاگ فضا دارد و چرخانده می‌شود:

# /etc/mysql/conf.d/slow.cnf  (persistent)
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

از تنظیمات افراطی پرهیز کنید. ثبت همهٔ کوئری‌ها I/O اضافه می‌کند و دیسک را پر می‌کند:

SET GLOBAL long_query_time = 0;     -- logs every query: huge file, extra I/O
SET GLOBAL log_queries_not_using_indexes = 'ON';   -- noisy on small tables

۳. به جای خواندن خط‌به‌خط خلاصه کنید

فایل خام طولانی است. mysqldumpslow کوئری‌های مشابه را گروه و بر اساس مجموع زمان مرتب می‌کند و بدترین‌ها را نشان می‌دهد:

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

-- Then inspect one query
EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = '_price';

روی کوئری‌هایی تمرکز کنید که هم کند و هم پرتکرارند؛ گزارش کند یک‌باره کم‌اهمیت‌تر از کوئری‌ای است که هزاران بار اجرا می‌شود.

۴. EXPLAIN را بخوانید

EXPLAIN نشان می‌دهد کوئری از ایندکس استفاده می‌کند یا کل جدول را اسکن می‌کند. اسکن کامل روی جدول بزرگ علت معمول است. اغلب راه‌حل ایندکس یا کوئری کوچک‌تر است، اما تغییرها را اول روی کپی تست کنید و مطابق راهنمای بکاپ بکاپ داشته باشید. برای بهبود کش در سطح سایت راهنمای TTFB را ببینید.

۵. بعد از پایان خاموشش کنید

بعد از بررسی آستانه را به مقدار معقول برگردانید یا لاگ را خاموش کنید و فایل را حذف یا بچرخانید. یادداشت کنید چه چیزی را تغییر دادید و زمان‌بندی‌های قبل و بعد را نگه دارید تا بهبود را ثابت کنید.

۶. مثال توضیحی، نه اندازه‌گیری واقعی

فرض کنید صفحه‌ای از ووکامرس (ساختگی) چند ثانیه طول می‌کشد. لاگ کند یک کوئری روی جدول post meta را نشان می‌دهد که هزاران بار تکرار شده و هرکدام حدود یک ثانیه طول می‌کشد. EXPLAIN اسکن کامل جدول را نشان می‌دهد. بعد از افزودن ایندکس مناسب روی کپی آزمایشی و سپس روی تولید، همان کوئری سریع می‌شود و زمان صفحه محسوس کم می‌شود.

۷. اشتباه‌های رایج

  • گذاشتن long_query_time روی ۰ در سرور پرترافیک.
  • فراموشی اینکه تنظیمات زمان اجرا با restart ریست می‌شوند.
  • خواندن لاگ خام به جای خلاصه.
  • افزودن ایندکس بدون تست.
  • رهاکردن لاگ روشن تا بی‌نهایت بزرگ شود.
  • بهینه‌سازی کوئری نادر پیش از پرتکرار.

۸. پرسش‌های متداول

slow query log در تولید امن است؟ با آستانهٔ معقول و چرخش، بله. تنظیمات افراطی I/O اضافه می‌کنند.

فایل لاگ کجاست؟ slow_query_log_file را ببینید. محل به توزیع و پیکربندی بستگی دارد.

روی MariaDB هم کار می‌کند؟ بله، مفهوم و تنظیمات اصلی یکی است.

اول چه را اصلاح کنم؟ طبق خلاصه، کوئری‌هایی که هم کند و هم پرتکرارند.

۹. چک‌لیست سریع

  1. تنظیمات فعلی را ببینید.
  2. با آستانهٔ یک ثانیه فعال کنید.
  3. در صورت نیاز پایدارش کنید.
  4. در بازهٔ نماینده جمع کنید.
  5. با mysqldumpslow خلاصه کنید.
  6. با EXPLAIN بررسی کنید.
  7. بعد از کار خاموش یا بچرخانید.

اگر برای بررسی عملکرد یا نگهداری سرور به همکاری نیاز دارید، محدودهٔ خدمات مدیریت سرور را ببینید.

راهنماهای بیشتر سرور و وردپرس در آرشیو مقالات جمع شده‌اند.

منابع رسمی

MySQL: The Slow Query Log · MySQL: mysqldumpslow · MySQL: EXPLAIN · MariaDB: Slow Query Log Overview