Slow Query Log در وردپرس چطور کوئری‌های کند را پیدا می‌کند؟

Slow Query Log در وردپرس یعنی فعال‌سازی و تحلیل لاگ کوئری‌های کند در MySQL برای شناسایی دقیق کوئری‌هایی که زمان پاسخ سایت را افزایش می‌دهند و علت واقعی کندی پایگاه داده را از سطح حدس به سطح داده منتقل می‌کنند.

این لاگ در سطح موتور پایگاه داده کار می‌کند و مستقل از افزونه‌ها، قالب و لایه وب‌سرور، همه کوئری‌های کند را ثبت می‌کند.

سه پارامتر کلیدی این لاگ، آستانه زمان، نحوه فعال‌سازی و دسته‌بندی خروجی هستند.

تحلیل درست این لاگ، کوئری‌های پرتکرار، کوئری‌های بدون ایندکس و کوئری‌های پرحجم را از یکدیگر جدا می‌کند.

هر کوئری که از این لاگ حذف یا بهینه شود، مستقیماً روی TTFB (Time To First Byte) و ظرفیت کلی سرور اثر می‌گذارد.

بار اولی که یک لاگ کوئری کند را روی سایت وردپرسی فعال کردم، انتظار داشتم مقصر اصلی را در افزونه سنگین یا قالب حجیم پیدا کنم. اما خروجی لاگ چیز دیگری نشان می‌داد: بیشترین بار از یک کوئری ساده در افزونه‌ای می‌آمد که هر بار صفحه را باز می‌کرد. همین ساده بودن، آن را از دید همه پنهان کرده بود.

Slow Query Log چیست و در وردپرس چه جایگاهی دارد

Slow Query Log یک مکانیزم بومی در MySQL است که همه کوئری‌هایی را که بیش از یک آستانه زمانی مشخص اجرا می‌شوند، در یک فایل ثبت می‌کند. این مکانیزم در سطح موتور پایگاه داده کار می‌کند و مستقل از هر لایه بالاتر است.

Slow query log (Database log) بخشی از ابزارهای تشخیصی استاندارد MySQL است که در نسخه‌های مختلف این موتور پایگاه داده پشتیبانی می‌شود. برای فعال‌سازی آن نیازی به تغییر کد یا نصب افزونه نیست؛ فقط باید پارامترهای مناسب در فایل تنظیمات موتور پایگاه داده تنظیم شوند.

در وردپرس، جایگاه این ابزار متفاوت از ابزارهای دیگر است. افزونه‌های پروفایل PHP یا ابزارهای مانیتورینگ سرور، زمان اجرای کل درخواست را اندازه می‌گیرند. اما Slow Query Log دقیقاً می‌گوید کدام کوئری چقدر طول کشیده و چه الگویی داشته. این تفاوت، ابزار را از سطح تحلیل عمومی به سطح تحلیل دقیق منتقل می‌کند.

مسئله اصلی در سایت‌های وردپرسی این است که حجم کوئری‌ها بالاست. یک صفحه معمولی می‌تواند ده‌ها کوئری تولید کند و در صفحات پیچیده‌تر، این تعداد به چند صد کوئری برسد. بدون ابزار دقیقی مانند Slow Query Log، یافتن کوئری کند در این حجم، شبیه جستجوی سوزن در انبار کاه است.

ابزارهای دیگر بهینه‌سازی، مکمل این لاگ هستند. اگر بهینه‌سازی ایندکس در وردپرس بخش تصمیم را پوشش می‌دهد، Slow Query Log بخش تشخیص را. بدون تشخیص درست، تصمیم‌های بهینه‌سازی به حدس تبدیل می‌شوند.

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

مکانیزم ثبت کوئری کند در MySQL

موتور MySQL در سطح داخلی، هر کوئری را با یک زمان‌سنج پردازش می‌کند. اگر این زمان از آستانه تعیین‌شده (پارامتر long_query_time) فراتر رود، کوئری در فایل لاگ ثبت می‌شود.

نکته مهم این است که زمان‌سنجی در چه مرحله‌ای انجام می‌شود. MySQL زمان را از لحظه دریافت کوئری تا لحظه ارسال پاسخ اندازه می‌گیرد. این اندازه‌گیری شامل زمان قفل‌گذاری، زمان اجرای کوئری و زمان ارسال نتایج است.

سه پارامتر موتور پایگاه داده این رفتار را کنترل می‌کنند. پارامتر اول، long_query_time است که آستانه زمانی را بر حسب ثانیه تعیین می‌کند. پارامتر دوم، min_examined_row_limit است که حداقل تعداد ردیف‌های بررسی‌شده برای ثبت کوئری را تعیین می‌کند. پارامتر سوم، log_queries_not_using_indexes است که کوئری‌های بدون ایندکس را نیز ثبت می‌کند.

در نسخه‌های مدرن MySQL، این لاگ می‌تواند به جدول mysql.slow_log نیز نوشته شود، نه فقط به فایل. این ویژگی، تحلیل کوئری‌ها را با SQL ساده‌تر می‌کند، اما بار اضافه‌ای روی موتور پایگاه داده تحمیل می‌کند.

SHOW VARIABLES LIKE "slow_query_log%";
SHOW VARIABLES LIKE "long_query_time";
SHOW VARIABLES LIKE "log_queries_not_using_indexes";

این سه دستور، وضعیت فعلی پارامترهای لاگ را نشان می‌دهند. اگر مقدار slow_query_log برابر OFF باشد، لاگ فعال نیست و هیچ کوئری کندی ثبت نمی‌شود.

مسئله مهم دیگر، زمان اجرای موتور پایگاه داده در محیط‌های مختلف است. در سرورهای پربار، زمان اجرای یک کوئری می‌تواند تحت تأثیر عوامل بیرونی مانند رقابت بر سر منابع یا قفل‌های پایگاه داده افزایش یابد. این رفتار، در لاگ به‌صورت کوئری کند ثبت می‌شود، حتی اگر خود کوئری ذاتاً کند نباشد.

تفکیک این دو حالت، بخشی از تحلیل درست لاگ است. کوئری که به دلیل رقابت منابع کند شده، به اصلاح ساختار نیاز ندارد؛ به کاهش بار کلی سرور نیاز دارد. اصول کلی این تحلیل در بهینه‌سازی کوئری‌های MySQL آمده است.

پیکربندی slow_query_log و پارامترهای کلیدی

فعال‌سازی Slow Query Log نیازمند تغییر در فایل تنظیمات MySQL است. در سیستم‌های لینوکسی، این فایل معمولاً در مسیر /etc/mysql/my.cnf یا در دایرکتوری /etc/mysql/mysql.conf.d/ قرار دارد.

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
min_examined_row_limit = 100
log_output = FILE

هر یک از این پارامترها اثر مشخصی دارند. پارامتر slow_query_log فعال‌سازی لاگ را کنترل می‌کند. پارامتر slow_query_log_file مسیر فایل لاگ را تعیین می‌کند. پارامتر long_query_time آستانه زمانی را مشخص می‌کند.

پارامتر log_queries_not_using_indexes یکی از مفیدترین پارامترهاست. این پارامتر، کوئری‌هایی را که از ایندکس استفاده نمی‌کنند نیز ثبت می‌کند، حتی اگر سریع اجرا شوند. این کوئری‌ها در حجم بالا می‌توانند به گلوگاه تبدیل شوند.

پارامتر log_slow_admin_statements دستورات مدیریتی مانند ALTER TABLE یا OPTIMIZE TABLE را نیز در لاگ ثبت می‌کند. این دستورات، در جداول حجیم می‌توانند ساعت‌ها طول بکشند و پیگیری زمان اجرای آن‌ها اهمیت دارد.

پارامتر min_examined_row_limit به‌طور پیش‌فرض صفر است، یعنی همه کوئری‌هایی که از آستانه زمانی عبور کنند ثبت می‌شوند. افزایش این مقدار، نویز لاگ را کاهش می‌دهد. مقدار صد ردیف، نقطه شروع متعادلی است.

مسئله مهم در پیکربندی، انتخاب long_query_time مناسب است. مقدار پیش‌فرض ده ثانیه برای سایت‌های پربازدید بسیار بالاست. مقدار یک ثانیه برای بیشتر پروژه‌ها متعادل است. در سایت‌های حساس به زمان پاسخ، مقدار 0.5 یا حتی 0.2 توصیه می‌شود.

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

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
    daily
    rotate 7
    missingok
    compress
    delaycompress
    notifempty
    create 640 mysql adm
}

این پیکربندی، چرخش روزانه لاگ را با نگهداری هفت نسخه فعال می‌کند. بدون این مکانیزم، فایل لاگ می‌تواند به چند گیگابایت برسد و فضای دیسک سرور را اشغال کند.

ساختار لاگ خام و آنچه باید خواند

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

# Time: 2024-09-15T10:23:45.123456Z
# User@Host: wp_user[wp_user] @ localhost []  Id: 12345
# Query_time: 3.456789  Lock_time: 0.000123  Rows_sent: 20  Rows_examined: 1250000
SET timestamp=1726393425;
SELECT ID, post_title FROM wp_posts
  INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
  WHERE wp_postmeta.meta_key = "price"
    AND wp_postmeta.meta_value > 100
    AND wp_posts.post_type = "product"
  ORDER BY wp_posts.post_date DESC
  LIMIT 20;

این نمونه، همه اجزای یک رکورد لاگ را نشان می‌دهد. خط اول، زمان ثبت کوئری است. خط دوم، اطلاعات کاربر و اتصال را نشان می‌دهد.

خط سوم، اطلاعات کلیدی کوئری را ارائه می‌دهد. مقدار Query_time زمان کل اجرای کوئری است. مقدار Lock_time زمان انتظار برای قفل‌ها را نشان می‌دهد. مقدار Rows_sent تعداد ردیف‌های بازگشتی است. مقدار Rows_examined تعداد ردیف‌هایی است که موتور پایگاه داده بررسی کرده.

نسبت میان Rows_sent و Rows_examined یکی از مفیدترین شاخص‌های تحلیل است. اگر این نسبت کوچک باشد (یعنی تعداد ردیف‌های بررسی‌شده بسیار بیشتر از ردیف‌های بازگشتی باشد)، نشانه‌ای از نبود ایندکس مناسب یا ایندکس ناکارآمد است.

در نمونه بالا، نسبت برابر ۲۰ به یک میلیون و دویست و پنجاه هزار است؛ یعنی موتور پایگاه داده برای بازگرداندن بیست ردیف، بیش از یک میلیون ردیف را بررسی کرده. این نسبت به‌تنهایی نشان می‌دهد که مشکل در ایندکس‌گذاری جدول wp_postmeta است.

پارامتر Lock_time نیز اطلاعات ارزشمندی ارائه می‌دهد. اگر این مقدار بالا باشد، کوئری منتظر قفل‌های دیگر بوده است. ریشه این انتظار معمولاً در تراکنش‌های طولانی یا در رقابت بر سر منابع است. اصول مرتبط با این موضوع در تراکنش‌ها در MySQL آمده است.

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

mysqldumpslow و pt-query-digest

فایل خام لاگ، برای تحلیل دستی چندان مناسب نیست. ابزارهای تخصصی، خلاصه‌سازی و دسته‌بندی کوئری‌ها را ساده می‌کنند.

ابزار نخست، mysqldumpslow است که به‌طور پیش‌فرض همراه با MySQL نصب می‌شود. این ابزار، کوئری‌های مشابه را دسته‌بندی می‌کند و آمار خلاصه‌ای از هر دسته ارائه می‌دهد.

mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

این دستور، بیست کوئری کند را بر اساس زمان اجرا مرتب‌شده نمایش می‌دهد. پارامتر -s t ترتیب بر اساس زمان را تعیین می‌کند و پارامتر -t 20 تعداد کوئری‌های نمایش‌داده‌شده را مشخص می‌کند.

ابزار دوم، pt-query-digest از مجموعه Percona Toolkit است که تحلیل عمیق‌تری ارائه می‌دهد. این ابزار، الگوهای کوئری را دسته‌بندی می‌کند، آمار دقیقی از هر الگو ارائه می‌دهد و نقاط بهینه‌سازی را پیشنهاد می‌کند.

pt-query-digest /var/log/mysql/slow.log > report.txt

خروجی این ابزار، شامل چند بخش است. بخش اول خلاصه کلی لاگ را نشان می‌دهد. بخش دوم، کوئری‌های پرتکرار را فهرست می‌کند. بخش سوم، جزئیات هر کوئری را ارائه می‌دهد.

در خروجی pt-query-digest، چند عدد کلیدی وجود دارد. مقدار total تعداد کل اجرای یک کوئری است. مقدار avg میانگین زمان اجرا. مقدار pct درصد سهم کوئری از کل زمان کندی. مقدار sum مجموع زمان اجرای همه نمونه‌های همان کوئری.

مقدار sum یکی از مفیدترین شاخص‌هاست. یک کوئری که هر بار دو ثانیه طول می‌کشد اما هزار بار در روز اجرا می‌شود، در مجموع بار بسیار بیشتری از یک کوئری تحمیل می‌کند که یک بار صد ثانیه طول می‌کشد. تحلیل بر اساس sum، به شناسایی کوئری‌هایی منجر می‌شود که بیشترین بار کلی را ایجاد می‌کنند.

pt-query-digest --order-by Query_time:sum /var/log/mysql/slow.log

این دستور، کوئری‌ها را بر اساس مجموع زمان اجرا مرتب می‌کند. این ترتیب، متفاوت از ترتیب بر اساس میانگین زمان است و تصویر دقیق‌تری از بار کلی ارائه می‌دهد.

ابزار سوم، mysqlslap است که برای تست بار استفاده می‌شود. این ابزار، کوئری مشخصی را چند بار اجرا می‌کند و زمان اجرا را اندازه می‌گیرد. برای اعتبارسنجی تغییرات ایندکس، این ابزار مفید است.

معیارهای تشخیص کوئری کند در وردپرس

تعیین اینکه کدام کوئری «کند» است، به زمینه سایت بستگی دارد. یک کوئری دو ثانیه‌ای در یک سایت پرمخاطب، فاجعه است. همان کوئری در سایت با ترافیک کم، ممکن است قابل قبول باشد.

چند معیار عملی برای تشخیص کوئری کند در وردپرس وجود دارد. معیار اول، زمان اجرای بیش از یک ثانیه است. این آستانه، برای بیشتر سایت‌ها نقطه شروع مناسبی است.

معیار دوم، نسبت Rows_examined به Rows_sent بالاتر از هزار است. این نسبت نشان می‌دهد موتور پایگاه داده ردیف‌های زیادی را بررسی کرده تا به نتیجه رسیده. اگرچه این کوئری سریع اجرا می‌شود، در حجم بالاتر به گلوگاه تبدیل خواهد شد.

معیار سوم، فراوانی بالای اجرا است. کوئری که هر بار تنها صد میلی‌ثانیه طول می‌کشد اما در هر صفحه‌نمایش چند بار اجرا می‌شود، در مجموع بار قابل توجهی تحمیل می‌کند.

معیار چهارم، Lock_time بالاست. اگر زمان انتظار برای قفل‌ها از یک دهم کل زمان اجرا بیشتر باشد، نشانه‌ای از رقابت بر سر منابع است. این معیار در سایت‌های پربازدید به‌سرعت ظاهر می‌شود.

نکته مهم در این معیارها، ترکیب آن‌هاست. یک کوئری که همه معیارها را همزمان نقض می‌کند، اولویت بالاتری برای بهینه‌سازی دارد. ترتیب رسیدگی به کوئری‌ها بر اساس ترکیب این معیارها تعیین می‌شود.

الگوهای رایج کوئری کند در وردپرس

چند الگوی تکرارشونده در کوئری‌های کند وردپرس وجود دارد که شناخت آن‌ها، تشخیص را سریع‌تر می‌کند.

الگوی اول، کوئری‌های وابسته به متادیتا هستند. این کوئری‌ها بر اساس meta_key و meta_value فیلتر می‌کنند و به دلیل ساختار key-value جدول wp_postmeta، به JOIN های متعدد نیاز دارند. این الگو، پرتکرارترین علت کندی در سایت‌های وردپرسی است.

الگوی دوم، کوئری‌های تحلیلی روی داده‌های حجیم هستند. این کوئری‌ها محاسبات تجمعی مانند شمارش، مجموع یا میانگین انجام می‌دهند. افزونه‌های آماری و گزارش‌گیری معمولاً منشأ این کوئری‌ها هستند.

الگوی سوم، کوئری‌های جستجو هستند. جستجوی متنی با استفاده از عملگر LIKE روی ستون‌های متنی طولانی، به‌طور ذاتی کند است. در سایت‌های با محتوای زیاد، این کوئری‌ها به گلوگاه اصلی تبدیل می‌شوند.

الگوی چهارم، کوئری‌های مربوط به گزینه‌ها هستند. جدول wp_options در سایت‌هایی که افزونه‌های بسیاری دارند، می‌تواند به چند هزار ردیف برسد. اگر افزونه‌ای گزینه‌ها را به‌صورت نامناسب ذخیره کرده باشد، بازیابی آن‌ها کند می‌شود.

الگوی پنجم، کوئری‌های مربوط به ترنزینت‌ها هستند. اگر کش شیء پایدار وجود نداشته باشد، ترنزینت‌ها در جدول wp_options ذخیره می‌شوند و حجم این جدول به‌سرعت رشد می‌کند. موضوع پاک‌سازی این داده‌ها در پاک‌سازی اسپم و ترنزینت‌های وردپرس آمده است.

پرتکرارترین کوئری کند وردپرس، آن کوئری نیست که هرگز بهینه نشده؛ آن کوئری است که هیچ‌کس آن را ندیده است.

کوئری‌های postmeta و trap ساختار key-value

جدول wp_postmeta یکی از پرتکرارترین منابع کوئری‌های کند در وردپرس است. ساختار key-value این جدول، ناکارآمدی ذاتی در کوئری‌های چندویژگی ایجاد می‌کند.

الگوی رایج این کوئری‌ها شامل چند JOIN روی همان جدول است. هر JOIN به موتور پایگاه داده می‌گوید یک ویژگی مشخص را بررسی کند. با افزایش تعداد ویژگی‌ها، هزینه اجرای کوئری به‌صورت نمایی رشد می‌کند.

SELECT p.ID
FROM wp_posts p
INNER JOIN wp_postmeta pm1 ON p.ID = pm1.post_id AND pm1.meta_key = "price"    AND pm1.meta_value > 100
INNER JOIN wp_postmeta pm2 ON p.ID = pm2.post_id AND pm2.meta_key = "in_stock" AND pm2.meta_value = "yes"
INNER JOIN wp_postmeta pm3 ON p.ID = pm3.post_id AND pm3.meta_key = "brand"    AND pm3.meta_value = "acme"
WHERE p.post_type = "product"
ORDER BY p.post_date DESC
LIMIT 20;

در لاگ کوئری کند، این الگو معمولاً با Rows_examined بالا و Rows_sent پایین ظاهر می‌شود. نسبت بالا در این دو عدد، نشانه مستقیم مشکل است.

راه‌حل‌ها در سه سطح قابل بررسی هستند. سطح اول، افزودن ایندکس مرکب روی (meta_key, meta_value) است. این ایندکس، زمان اجرای کوئری‌های تک‌ویژگی را کاهش می‌دهد، اما در کوئری‌های چندویژگی محدودیت دارد.

سطح دوم، انتقال ویژگی‌های پرتکرار به جدول اختصاصی است. اگر یک ویژگی مانند price در بیشتر کوئری‌ها ظاهر می‌شود، انتقال آن به یک ستون در جدول اختصاصی، کوئری‌ها را ساده و سریع می‌کند. اصول این تصمیم در جداول اختصاصی در وردپرس آمده است.

سطح سوم، کش کردن نتایج کوئری است. اگر کوئری پرتکرار باشد و داده‌های آن به‌سرعت تغییر نکند، کش کردن نتیجه در حافظه، بار پایگاه داده را به‌طور محسوس کاهش می‌دهد.

function wpk_get_products_by_filters( $filters ) {
    $cache_key = "wpk_products_" . md5( serialize( $filters ) );
    $cached = wp_cache_get( $cache_key, "wpk_products" );
    if ( false !== $cached ) {
        return $cached;
    }
    global $wpdb;
    $query = $wpdb->prepare( "SELECT ...", $filters );
    $results = $wpdb->get_results( $query );
    wp_cache_set( $cache_key, $results, "wpk_products", 600 );
    return $results;
}

این الگو، نتیجه کوئری را در کش شیء نگه می‌دارد و از اجرای مکرر آن جلوگیری می‌کند. کلید کش از هش فیلترها ساخته می‌شود تا هر ترکیب فیلتر، کلید اختصاصی داشته باشد.

مسئله مهم در این رویکرد، بی‌اعتبارسازی کش است. هر بار که داده‌ای تغییر می‌کند، کلیدهای مرتبط باید پاک شوند. اصول این موضوع در بی‌اعتبارسازی کش در وردپرس به‌تفصیل آمده است.

کوئری‌های ووکامرس و بار تحلیل‌محور

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

سه دسته اصلی کوئری کند در ووکامرس وجود دارد. دسته اول، کوئری‌های محصولات است که بر اساس متادیتا فیلتر می‌کنند. این کوئری‌ها در صفحات دسته‌بندی و جستجوی محصول اجرا می‌شوند.

دسته دوم، کوئری‌های گزارش‌گیری است. این کوئری‌ها محاسبات تجمعی روی جدول سفارش‌ها و مشتریان انجام می‌دهند و معمولاً در پیشخوان مدیریت اجرا می‌شوند.

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

SELECT
    DATE(date_created) AS day,
    COUNT(*) AS orders,
    SUM(total_sales) AS revenue
FROM wp_wc_order_stats
WHERE date_created >= DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND status IN ("wc-completed","wc-processing")
GROUP BY DATE(date_created);

این کوئری نمونه‌ای از گزارش‌گیری است. در حجم بالا، این کوئری می‌تواند چند ثانیه طول بکشد. اگر ایندکس مناسب روی date_created و status وجود نداشته باشد، زمان اجرا چند مرتبه افزایش می‌یابد.

راه‌حل بهینه‌سازی در سه سطح قابل بررسی است. سطح اول، افزودن ایندکس مرکب مناسب روی جدول گزارش‌ها است. سطح دوم، انتقال گزارش‌گیری به یک جریان پس‌زمینه با استفاده از Action Scheduler است که اصول آن در Action Scheduler در وردپرس آمده است. سطح سوم، کش کردن گزارش‌ها برای بازه‌های زمانی مشخص است.

مسئله مهم در فروشگاه‌های ووکامرس، پاک‌سازی دوره‌ای داده‌های قدیمی است. جدول wp_wc_order_stats در فروشگاه‌های با حجم بالا می‌تواند به میلیون‌ها ردیف برسد. آرشیو کردن داده‌های قدیمی، حجم جدول را کنترل می‌کند و زمان اجرای کوئری‌های گزارش‌گیری را کاهش می‌دهد.

بهینه‌سازی کامل فروشگاه در این لایه، ترکیبی از تحلیل Slow Query Log، ایندکس‌گذاری دقیق و مدیریت داده‌های قدیمی است. اصول تفصیلی این موضوع در بهینه‌سازی دیتابیس ووکامرس آمده است.

اصلاح با ایندکس و بازنویسی کوئری

پس از شناسایی کوئری کند، دو رویکرد اصلی برای اصلاح وجود دارد: افزودن ایندکس و بازنویسی کوئری. انتخاب رویکرد مناسب، به تحلیل دقیق علت کندی بستگی دارد.

افزودن ایندکس، ساده‌ترین راه‌حل است، اما همیشه راه‌حل درست نیست. اگر کوئری به دلیل ساختار ناکارآمد کند باشد، افزودن ایندکس تنها بخشی از مشکل را حل می‌کند.

EXPLAIN SELECT ... ;

پیش از هر تغییری، خروجی EXPLAIN باید تحلیل شود. مقادیر ستون type نشان می‌دهد که آیا ایندکس استفاده می‌شود یا موتور پایگاه داده اسکن کامل انجام می‌دهد.

در افزودن ایندکس، رعایت قواعد ترتیب ستون‌ها ضروری است. ایندکس مرکب روی (A, B, C) برای کوئری‌هایی که بر اساس A یا (A, B) یا (A, B, C) فیلتر می‌کنند کارآمد است، اما برای کوئری‌هایی که بر اساس B یا C فیلتر می‌کنند بی‌استفاده است.

در بازنویسی کوئری، چند تکنیک رایج وجود دارد. تکنیک اول، تبدیل زیرکوئری به JOIN است. تکنیک دوم، حذف JOIN های غیرضروری. تکنیک سوم، محدودسازی نتایج با LIMIT پیش از JOIN.

-- Slow version
SELECT * FROM wp_posts
WHERE ID IN (SELECT post_id FROM wp_postmeta WHERE meta_key = "featured")
  AND post_status = "publish";

-- Faster version
SELECT p.* FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = "featured"
  AND p.post_status = "publish";

در این نمونه، تبدیل زیرکوئری به JOIN معمولاً زمان اجرا را کاهش می‌دهد. اما این قاعده مطلق نیست و در برخی نسخه‌های MySQL، بهینه‌ساز داخلی زیرکوئری‌ها را به‌طور خودکار به JOIN تبدیل می‌کند.

نکته مهم در بهینه‌سازی، اندازه‌گیری پس از تغییر است. هر تغییری باید با اندازه‌گیری مجدد زمان اجرای کوئری همراه باشد. تفاوت میان یک تغییر مؤثر و یک تغییر خوش‌شانس، در اندازه‌گیری مشخص می‌شود.

هماهنگی با لایه کش و کاهش بار پایگاه داده

Slow Query Log نشان می‌دهد کدام کوئری‌ها کند هستند. لایه کش، بخش بزرگی از این کندی را با حذف اجرای مکرر کوئری‌ها برطرف می‌کند.

سه نوع کش در این زمینه اهمیت دارند. کش شیء (Object Cache) نتایج کوئری‌های تکراری را در حافظه نگه می‌دارد. کش صفحه (Page Cache) کل خروجی HTML را ذخیره می‌کند و از رسیدن درخواست به پایگاه داده جلوگیری می‌کند. کش لایه وب‌سرور (FastCGI Cache یا Varnish) نیز در لایه پایین‌تر همین کار را انجام می‌دهد.

ترکیب این سه لایه می‌تواند بار پایگاه داده را به‌طور محسوس کاهش دهد. اگر کوئری‌های کند در لاگ کاهش یابند، نشانه مستقیمی است که لایه کش به‌درستی کار می‌کند.

در پیکربندی کش شیء پایدار، استفاده از Redis یا Memcached توصیه می‌شود. این ابزارها نتایج کوئری‌ها را در حافظه نگه می‌دارند و دسترسی به آن‌ها بسیار سریع‌تر از پایگاه داده است.

define( "WP_REDIS_HOST", "127.0.0.1" );
define( "WP_REDIS_PORT", 6379 );
define( "WP_CACHE_KEY_SALT", "wpk_" );
define( "WP_REDIS_DATABASE", 0 );

این پیکربندی، اتصال وردپرس به Redis را فعال می‌کند. برای استفاده کامل، افزونه Redis Object Cache نیز باید نصب و فعال شود.

مسئله مهم در هماهنگی کش و پایگاه داده، بی‌اعتبارسازی درست است. اگر کش شیء داده‌های قدیمی را نگه دارد، کوئری‌های کند در لاگ کاهش می‌یابند اما ناسازگاری داده ظاهر می‌شود. این تعادل در بی‌اعتبارسازی کش در وردپرس به‌تفصیل آمده است.

در لایه کش صفحه، افزونه‌های تخصصی مانند WP Rocket یا W3 Total Cache عملکرد مناسبی دارند. انتخاب و پیکربندی این افزونه‌ها موضوعی است که در بهترین افزونه‌های کش وردپرس آمده است.

خودکارسازی پایش و تحلیل لاگ

فعال‌سازی لاگ، تنها بخش اول کار است. برای بهره‌برداری مستمر، تحلیل لاگ باید خودکار شود. بدون خودکارسازی، تحلیل لاگ به یک کار پروژه‌ای تبدیل می‌شود که دیر یا زود فراموش می‌شود.

سه سطح خودکارسازی وجود دارد. سطح اول، چرخش و پاک‌سازی لاگ است که با Logrotate انجام می‌شود. سطح دوم، اجرای دوره‌ای pt-query-digest و ارسال گزارش است. سطح سوم، هشدار خودکار در زمان ظهور کوئری کند جدید.

#!/usr/bin/env bash
# /usr/local/bin/analyze-slow-log.sh
REPORT=/var/log/mysql/slow-report-$(date +%Y%m%d).txt
pt-query-digest --order-by Query_time:sum /var/log/mysql/slow.log > $REPORT
grep -A 5 "Profile" $REPORT | head -50 | mail -s "Slow Query Report" admin@example.com

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

سطح سوم خودکارسازی، هشدار در زمان ظهور کوئری کند جدید است. این سطح، پیچیده‌تر است اما ارزش بالایی دارد. الگوی استاندارد، مقایسه لاگ روز جاری با لاگ روزهای پیشین و هشدار در صورت ظهور الگوی جدید است.

# Schedule via crontab
# 0 6 * * * /usr/local/bin/analyze-slow-log.sh

این زمان‌بندی، تحلیل را در ساعت شش صبح اجرا می‌کند. انتخاب زمان، بر اساس الگوی ترافیک سایت تعیین می‌شود. هدف، اجرای تحلیل در بازه‌ای است که بار سرور کم است.

نکته مهم در خودکارسازی، جلوگیری از انباشت گزارش‌هاست. اگر گزارش‌ها ذخیره شوند و کسی آن‌ها را بررسی نکند، هدف خودکارسازی محقق نمی‌شود. طراحی مکانیزم‌های خلاصه‌سازی و هشدار، بخش ضروری این کار است.

جدول تصمیم‌گیری تحلیل و اقدام

اشتباهات رایج

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

دومین اشتباه، انتخاب آستانه بسیار بالا یا بسیار پایین است. آستانه بالا باعث می‌شود کوئری‌های متوسط پنهان بمانند. آستانه پایین باعث تولید حجم زیاد لاگ می‌شود. مقدار یک ثانیه نقطه شروع متعادلی است که با شناخت سایت تنظیم می‌شود.

سومین اشتباه، نادیده گرفتن log_queries_not_using_indexes است. کوئری‌هایی که از ایندکس استفاده نمی‌کنند، در حجم بالا می‌توانند به گلوگاه تبدیل شوند، حتی اگر هر بار سریع اجرا شوند.

چهارمین اشتباه، تحلیل دستی فایل خام است. فایل خام برای تحلیل تخصصی مناسب نیست. ابزارهایی مانند pt-query-digest دسته‌بندی و خلاصه‌سازی را انجام می‌دهند و تحلیل را سریع‌تر می‌کنند.

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

ششمین اشتباه، بی‌توجهی به Lock_time است. کوئری‌ای که به دلیل انتظار برای قفل کند شده، به اصلاح ساختار نیاز ندارد. ریشه این کندی در تراکنش‌های طولانی یا در رقابت منابع است. اصول مرتبط در تراکنش‌ها در MySQL آمده است.

هفتمین اشتباه، نبود چرخش لاگ است. اگر فایل لاگ چرخش نداشته باشد، می‌تواند به چند گیگابایت برسد و فضای دیسک را اشغال کند. پیکربندی Logrotate یک اقدام پایه است.

هشتمین اشتباه، عدم اندازه‌گیری پس از تغییر است. هر تغییری در ایندکس یا کوئری باید با اندازه‌گیری مجدد همراه باشد. بدون اندازه‌گیری، تفاوت میان یک تغییر مؤثر و یک تغییر خوش‌شانس قابل تشخیص نیست.

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

دهمین اشتباه، بی‌توجهی به تفاوت میان کوئری کند ذاتی و کوئری کند ناشی از رقابت منابع است. این دو نوع کندی، دو مسیر اصلاح متفاوت دارند. تفکیک این دو، بخش ضروری تحلیل است.

یازدهمین اشتباه، نادیده گرفتن جدول‌های حجیم است. جدول‌هایی مانند wp_postmeta و wp_options در حجم بالا به گلوگاه تبدیل می‌شوند. بهینه‌سازی این جدول‌ها بخشی از مسیر کلی است که در بهینه‌سازی پیشرفته دیتابیس وردپرس آمده است.

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

سیزدهمین اشتباه، نادیده گرفتن هماهنگی با لایه وب‌سرور است. اگرچه Slow Query Log در سطح موتور پایگاه داده کار می‌کند، تحلیل بار کلی سایت نیازمند داده‌های لایه‌های دیگر است. ترکیب داده‌های پایگاه داده با داده‌های سرور، تصویر کامل‌تری ارائه می‌دهد که اصول آن در مانیتورینگ عملکرد سرور آمده است.

پرسش‌های پرتکرار درباره Slow Query Log در وردپرس

Slow Query Log چیست و چه تفاوتی با لاگ خطا دارد؟

Slow Query Log فقط کوئری‌هایی را ثبت می‌کند که از آستانه زمانی مشخص عبور کرده باشند. لاگ خطا، خطاهای موتور پایگاه داده را ثبت می‌کند. این دو لاگ مکمل یکدیگرند و برای تحلیل کامل، هر دو باید فعال باشند.

چگونه Slow Query Log را در MySQL فعال کنم؟

با تنظیم پارامترهای slow_query_log = 1، slow_query_log_file و long_query_time در فایل تنظیمات MySQL. پس از تغییر، سرویس MySQL باید راه‌اندازی مجدد شود. یا می‌توان بدون راه‌اندازی مجدد، با دستور SET GLOBAL فعال‌سازی موقت انجام داد.

مقدار مناسب long_query_time چقدر است؟

مقدار پیش‌فرض ده ثانیه برای سایت‌های وردپرسی بسیار بالاست. مقدار یک ثانیه نقطه شروع متعادلی است. در سایت‌های حساس به زمان پاسخ، مقدار 0.5 یا حتی 0.2 توصیه می‌شود. آستانه پایین‌تر، حجم لاگ را افزایش می‌دهد.

تفاوت pt-query-digest و mysqldumpslow چیست؟

mysqldumpslow ابزار ساده‌ای است که همراه با MySQL نصب می‌شود و خلاصه‌سازی اولیه انجام می‌دهد. pt-query-digest ابزار پیشرفته‌تری است که تحلیل عمیق‌تری ارائه می‌دهد و نقاط بهینه‌سازی را پیشنهاد می‌کند.

چگونه بفهمم کوئری کند در کدام افزونه است؟

با تحلیل کوئری در لاگ و پیگیری نام جدول. اگر کوئری روی wp_postmeta باشد، به احتمال زیاد از یک افزونه با ویژگی‌های متادیتا می‌آید. با فعال‌سازی و غیرفعال‌سازی افزونه‌ها و مقایسه لاگ، می‌توان افزونه مقصر را شناسایی کرد.

آیا Slow Query Log روی عملکرد سایت اثر دارد؟

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

چگونه لاگ را تجزیه و تحلیل کنم؟

با استفاده از ابزارهایی مانند pt-query-digest که گزارش تحلیل را در چند ثانیه تولید می‌کند. ترتیب کوئری‌ها بر اساس مجموع زمان اجرا، تصویر دقیق‌تری از بار کلی ارائه می‌دهد.

آیا Slow Query Log روی سرعت سایت اثر دارد؟

خود لاگ اثر مستقیم روی سرعت ندارد. اما تحلیل آن، به شناسایی کوئری‌هایی منجر می‌شود که سرعت سایت را کاهش می‌دهند. بهینه‌سازی این کوئری‌ها مستقیماً روی TTFB اثر می‌گذارد که اصول آن در کاهش زمان TTFB آمده است.

چند وقت یک‌بار باید لاگ را بررسی کنم؟

در سایت‌های پربازدید، بررسی روزانه یا هفتگی توصیه می‌شود. در سایت‌های کوچک، بررسی ماهانه کافی است. بررسی در زمان‌های بحرانی مانند پس از به‌روزرسانی افزونه‌ها یا تغییرات بزرگ، ضروری است.

آیا می‌توان تحلیل لاگ را خودکار کرد؟

بله، با ترکیب ابزارهایی مانند pt-query-digest با اسکریپت‌های تحلیل و ارسال ایمیل. این خودکارسازی، از فراموشی این کار جلوگیری می‌کند و پایش مستمر را ممکن می‌سازد.

آیا Slow Query Log در محیط توسعه هم مفید است؟

بله، در محیط توسعه حتی مفیدتر است. کوئری‌های کندی که در توسعه شناسایی و اصلاح می‌شوند، در محیط تولید ظاهر نمی‌شوند. شناسایی این کوئری‌ها پیش از انتشار، هزینه اصلاح را به‌طور محسوس کاهش می‌دهد.

آیا بهینه‌سازی کوئری کند جایگزین ایندکس‌گذاری است؟

خیر. بهینه‌سازی کوئری و ایندکس‌گذاری دو رویکرد مکمل هستند. یک کوئری بهینه‌نشده حتی با بهترین ایندکس هم کند می‌ماند. بهترین نتیجه با ترکیب این دو رویکرد به‌دست می‌آید.

آیا Slow Query Log برای سایت‌های کوچک هم توصیه می‌شود؟

بله، به‌ویژه اگر سایت با کندی گاه‌به‌گاه روبه‌رو است. فعال‌سازی این لاگ با آستانه مناسب، هزینه‌ای ندارد و در زمان عیب‌یابی می‌تواند تفاوت بزرگی ایجاد کند.

چگونه از انباشت لاگ جلوگیری کنم؟

با پیکربندی Logrotate برای چرخش و فشرده‌سازی لاگ‌های قدیمی. نگهداری هفت نسخه روزانه، نقطه شروع متعادلی است. در سایت‌های حساس، نگهداری سی نسخه توصیه می‌شود.

آیا می‌توان لاگ را در جدول پایگاه داده ذخیره کرد؟

بله، با تنظیم log_output = TABLE به‌جای FILE. این رویکرد، تحلیل با SQL ساده را ممکن می‌کند، اما بار اضافه‌ای روی موتور پایگاه داده تحمیل می‌کند. برای سایت‌های پربازدید، روش فایل توصیه می‌شود.

آیا بهینه‌سازی همه کوئری‌های کند ضروری است؟

خیر. اولویت‌بندی بر اساس مجموع بار تحمیلی، رویکرد منطقی‌تری است. کوئری‌هایی که بیشترین بار کلی را تحمیل می‌کنند، اولویت بالاتری برای بهینه‌سازی دارند.

یک نکته برای ادامه مسیر

Slow Query Log یکی از آن ابزارهایی است که پیش از استفاده، پیچیده به‌نظر می‌رسد، اما پس از اولین تحلیل، جایگاه آن در ذهن تثبیت می‌شود. این لاگ، شکاف میان مشاهده کندی و یافتن ریشه آن را پر می‌کند و هر تصمیم بهینه‌سازی را از سطح حدس به سطح داده منتقل می‌سازد.

اگر روی پروژه خودتان این مسیر را طی کرده‌اید، خوشحال می‌شوم بدانم کدام بخش بیشترین زمان را گرفت: پیکربندی اولیه لاگ، تحلیل خروجی ابزارهای خلاصه‌ساز، یا جدا کردن کوئری کند ذاتی از کوئری کند ناشی از رقابت منابع. تجربه‌تان را در دیدگاه‌ها بنویسید؛ به‌ویژه اگر راه‌حل متفاوتی پیدا کرده‌اید که می‌تواند برای خواننده بعدی مفید باشد.