سایت روی هاست لینوکس کند شده، TTFB از ۲۰۰ میلی‌ثانیه رسیده به ۳ ثانیه، و شما هنوز نمی‌دانید کدام کوئری این کار را میکند. اگر فقط به CPU و RAM نگاه کنید، هیچ‌وقت جواب نمی‌گیرید. ابزار درست، slow query log است؛ همان چیزی که MySQL خودش می‌نویسد و اکثر مدیرها هرگز روشنش نمی‌کنند.
اول ببینید slow query log اصلاً روشن است یا نه
قبل از هر تغییری در my.cnf، از داخل خود MySQL بپرسید:
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';اگر slow_query_log روی OFF بود، یعنی تا امروز هیچ داده‌ای نداشته‌اید و باید از صفر شروع کنید. مقدار long_query_time هم معمولاً پیش‌فرض ۱۰ ثانیه است که برای یک سایت وردپرسی عملاً بیفایده است؛ هیچ کوئری‌ای ۱۰ ثانیه طول نمی‌کشد، ولی هزار کوئری ۳۰۰ میلی‌ثانیه‌ای سایت را زمین می‌زند.
فعال‌سازی بدون ری‌استارت
برای اینکه همین حالا شروع کنید و سرویس را قطع نکنید:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_output = 'TABLE';مقدار ۰.۵ ثانیه نقطه شروع خوبی است. اگر ترافیک بالاست و لاگ دارد منفجر میشود، ببریدش روی ۱. اگر سایت کوچک است و دنبال جزئیات هستید، ۰.۲ هم منطقی است. با log_output = 'TABLE' خروجی در جدول mysql.slow_log مینشیند و با SQL قابل کوئری گرفتن است. اگر FILE بگذارید، در فایل لاگ می‌نویسد که برای تحلیل حجیم بهتر است ولی خواندنش دستی‌تر است.
این تنظیمات با ری‌استارت MySQL پاک می‌شوند. برای دائمی‌کردن، در بخش [mysqld] فایل کانفیگ بنویسید:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0آن خط آخر مهم است. log_queries_not_using_indexes را خیلی‌ها روی ۱ می‌گذارند چون به نظرشان یعنی «هر کوئری بدون ایندکس را بگیر». در عمل روی سایتهای وردپرسی، جداول کوچک مثل wp_options با چند صد ردیف را هم لاگ میکند و فایل لاگ در چند ساعت به گیگابایت می‌رسد. من پیش‌فرض می‌گذارمش ۰ و فقط وقتی روشنش می‌کنم که دنبال یک الگوی خاص باشم.
خواندن خروجی: کدام خط مهم است
هر ورودی لاگ سه چیز دارد که به کارتان می‌آید: زمان اجرا، تعداد ردیف‌هایی که MySQL بررسی کرده، و خود کوئری. نمونه واقعی:
# Time: 2024-05-11T09:14:22.318471Z
# User@Host: wpuser[wpuser] @ localhost []
# Query_time: 4.812301 Lock_time: 0.000142 Rows_sent: 12 Rows_examined: 184320
SET timestamp=1715418862;
SELECT * FROM wp_postmeta WHERE meta_key = '_thumbnail_id' ORDER BY meta_value LIMIT 12;عدد Rows_examined: 184320 در برابر Rows_sent: 12 تمام ماجرا را می‌گوید. MySQL صد و هشتاد هزار ردیف را خوانده تا دوازده تا بدهد. این نسبت، نه زمان اجرا، معیار اصلی است. کوئری‌ای با Query_time نیم‌ثانیه ولی نسبت ۱۵۰۰۰ به ۱، زیر بار همزمانی خیلی سریع‌تر از کوئری دو ثانیه‌ای با نسبت ۲ به ۱ سایت را می‌خواباند.
اگر خروجی در جدول است، این کوئری مرتبسازی را بزنید:
SELECT sql_text, query_time, rows_examined, rows_sent
FROM mysql.slow_log
ORDER BY rows_examined DESC
LIMIT 20;مرتبسازی بر اساس rows_examined و نه query_time، معمولاً سریع‌تر به ریشه می‌رساند.
EXPLAIN: قبل از اضافه کردن ایندکس، نگاه کنید
کوئری مشکوک را بردارید و جلویش EXPLAIN بگذارید:
EXPLAIN SELECT * FROM wp_postmeta
WHERE meta_key = '_thumbnail_id'
ORDER BY meta_value LIMIT 12;در خروجی، ستون type و key را ببینید. اگر type روی ALL است، اسکن کامل جدول دارید. اگر key خالی است، هیچ ایندکسی استفاده نشده. ستون rows تخمین می‌زند چند ردیف خوانده میشود و اگر با Rows_examined واقعی هم‌خوانی نداشت، آمار جدول کهنه است و ANALYZE TABLE wp_postmeta; لازم دارید.
ایندکس درست، نه هر ایندکسی
برای کوئری بالا، ایندکس تک‌ستونی روی meta_key کافی نیست چون ORDER BY meta_value هم داریم. ایندکس ترکیبی جواب می‌دهد:
ALTER TABLE wp_postmeta ADD INDEX idx_key_value (meta_key, meta_value(20));ترتیب ستون‌ها در ایندکس ترکیبی مهم است. اگر برعکس بنویسید، MySQL نمی‌تواند از آن برای مرتبسازی استفاده کند و دوباره filesort می‌گیرید. آن (20) هم پیشوند است؛ روی ستون longtext بدون پیشوند نمی‌توانید ایندکس بسازید و خطای ERROR 1170 (42000): BLOB/TEXT column used in key specification without a key length می‌گیرید.
اینجا اشتباه می‌کنند: ایندکس نداشتن همیشه علت نیست
رایج‌ترین اشتباهی که می‌بینم این است: مدیر سایت لاگ را باز میکند، یک کوئری با Rows_examined بالا می‌بیند، فوراً ایندکس اضافه میکند، و سایت هنوز کند است. علامتش هم این است که EXPLAIN بعد از ایندکس key را پر نشان می‌دهد ولی زمان اجرا تغییری نکرده.
سه علت واقعی که پشت این حالت است:
- قفل‌شدن روی جدول. اگر
Lock_timeدر لاگ بالاست، مشکل رقابت روی قفل است نه سرعت خواندن. یک تراکنش طولانی، بقیه را پشت صف نگه داشته. - کوئری N+1. هزار کوئری ۵ میلی‌ثانیه‌ای که هرکدام زیر آستانه
long_query_timeهستند، هیچ‌وقت در لاگ نمی‌آیند ولی مجموعشان سایت را می‌خواباند. اینجا بایدlong_query_timeرا موقتاً روی ۰ بگذارید یا ازperformance_schemaاستفاده کنید. - انتقال داده از دیسک. کوئری‌ای که
SELECT *می‌زند و ستون‌های سنگین برمی‌گرداند، حتی با ایندکس درست، IO دیسک را اشباع میکند. اگر روی هاست لینوکس با دیسک SATA هستید وiostatنشان می‌دهد%utilبالای ۹۰ است، مسئله ایندکس نیست.
نکته دوم را جدی بگیرید. در یک نصب وردپرس با ۱۵ پلاگین، دیدن ۸۰۰ کوئری در یک رندر صفحه کاملاً عادی است و هیچ‌کدام در slow query log نمی‌آیند. اگر لاگ خالی است ولی سایت کند است، مشکل جای دیگری است؛ wp-cron وردپرس را چک کنید، چون اجرای همزمان کرون با درخواست کاربر یکی از شایع‌ترین دلایل پیک‌های ناگهانی دیتابیس است.
هزینه‌ای که باید بپذیرید
روشن کردن slow query log رایگان نیست. هر کوئری باید زمان‌سنجی شود و در I/O نوشته شود. روی بار سنگین، با log_output = 'FILE' و دیسک کند، خود لاگ می‌تواند ۵ تا ۱۰ درصد throughput را بخورد. به همین دلیل من هیچ‌وقت آن را به‌صورت دائمی روی سرور پرترافیک روشن نمی‌گذارم. الگو این است: روشن کنید، ۲۴ ساعت داده جمع کنید، تحلیل کنید، خاموش کنید. اگر لازم شد هفته بعد دوباره.
برای تحلیل، pt-query-digest از جعبه‌ابزار Percona خیلی بهتر از چشم انسان کار میکند:
pt-query-digest /var/log/mysql/slow.log > /tmp/report.txtخروجی‌اش کوئری‌ها را بر اساس مجموع زمان مصرفی مرتب میکند، نه بر اساس تک‌تک اجراها. این دقیقاً همان چیزی است که می‌خواهید.
پرسش‌های پرتکرار
چرا slow query log خالی است ولی سایت کند است؟
چون احتمالاً long_query_time خیلی بالاست یا مشکل از کوئری نیست. مقدار را روی ۰.۲ بگذارید و یک ساعت صبر کنید. اگر باز هم خالی بود، مسئله در لایه دیگری است: قفل جدول، کوئریهای N+1، یا اشباع I/O دیسک.
آیا اضافه کردن ایندکس همیشه کوئری را سریع میکند؟
نه. ایندکس روی ستونهایی که کاردینالیتی پایین دارند (مثل ستون وضعیت با سه مقدار) تقریباً بیفایده است و فقط هزینه نوشتن را بالا میبرد. همچنین هر ایندکس اضافه، سرعت INSERT و UPDATE را کم میکند. ایندکس را با EXPLAIN قبل و بعد بسنجید، نه با حدس.
تفاوت log_output بین TABLE و FILE چیست؟
در حالت TABLE خروجی در جدول mysql.slow_log مینشیند و با SQL قابل فیلتر و مرتبسازی است، ولی روی بار سنگین خودش رقابت ایجاد میکند. در حالت FILE نوشتن سبکتر است و برای تحلیل با ابزارهایی مثل pt-query-digest مناسبتر. برای عیبیابی کوتاهمدت TABLE راحتتر است، برای سرور پرترافیک FILE.
چطور بفهمم کوئری کند از خود وردپرس است یا از یک پلاگین؟
به نام جدولها در کوئری نگاه کنید. جداول wp_options و wp_postmeta معمولاً یعنی پلاگین یا قالب. برای اطمینان، پلاگینها را یکییکی غیرفعال کنید و بعد از هر تغییر لاگ را پاک کنید تا ببینید کدام کوئری حذف میشود. اگر دنبال ابزار آماده برای تست سرعت و بررسی وضعیت سرور هستید، ابزارهای رایگان سرورنت نقطه شروع خوبی است.
اگر بعد از این مراحل مشخص شد گلوگاه از خود سرور است و نه کوئری، وقتش رسیده به هاست لینوکس با منابع مناسبتر فکر کنید. برای سایتهای وردپرسی هم هاست وردپرس با تنظیمات بهینهشده MySQL معمولاً دردسر کمتری دارد.
همین حالا این دو خط را بزنید: SET GLOBAL slow_query_log = 'ON'; و SET GLOBAL long_query_time = 0.5;. فردا صبح لاگ را باز کنید و ستون Rows_examined را نگاه کنید، نه Query_time را.
دیدگاه‌ها ۰
هنوز دیدگاهی ثبت نشده؛ اولین نفر باشید!