تنظیم و عیب‌یابی MySQL و MariaDB روی سرورهای پربازدید

ساعت دو بامداد است. سایت به‌جای اینکه بالا بیاید چند ثانیه مکث می‌کند و بعد خطای Too many connections می‌دهد. یا کوئری‌ای که دیروز در صدم ثانیه جواب می‌داد، حالا چند ثانیه طول می‌کشد و بقیه پشتش صف بسته‌اند. این نوشته از همان علامت شروع می‌کند و بعد می‌رسد به فایل کانفیگ. اگر مستقیم بروید سراغ my.cnf و مقداری را که در یک وبلاگ دیدید کپی کنید، در بهترین حالت چیزی عوض نمی‌شود و در بدترین حالت سرور را با OOM خاموش می‌کنید.

قاعده‌ی کار ساده است: اول اندازه بگیر، بعد دست به تنظیمات بزن. سه ابزار این کار را می‌کنند و هر سه داخل خود دیتابیس‌اند.

اول: لاگ کوئری کند را روشن کن

قبل از هر تنظیمی باید بدانید کدام کوئری‌ها کند هستند. لاگ کوئری کند دقیقاً همین کار را می‌کند: هر دستوری که بیش از long_query_time ثانیه طول بکشد را ثبت می‌کند. طبق مستندات MySQL مقدار پیش‌فرض long_query_time برابر ۱۰ ثانیه است و خود لاگ به‌صورت پیش‌فرض خاموش است. ده ثانیه برای یک سرور پربازدید خیلی بالاست؛ روی یک ثانیه بیاوریدش.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SHOW VARIABLES LIKE 'slow_query_log_file';

خط سوم کوئری‌هایی را هم که اصلاً از ایندکس استفاده نمی‌کنند ثبت می‌کند؛ این گزینه هم به‌صورت پیش‌فرض خاموش است. خروجی خط چهارم مسیر فایل لاگ را می‌دهد، چیزی مثل /var/log/mysql/mysql-slow.log. این تنظیم‌ها با ری‌استارت پاک می‌شوند، پس وقتی عیب‌یابی تمام شد همان‌ها را در [mysqld] دائمی کنید.

چند دقیقه ترافیک واقعی را بگذارید جمع شود، بعد لاگ را خلاصه کنید:

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

گزینه‌ی -s t بر اساس مجموع زمان مرتب می‌کند و -t 10 ده مورد اول را نشان می‌دهد. خروجی به ازای هر الگوی کوئری تعداد اجرا، میانگین زمان و تعداد سطرهای بررسی‌شده را می‌دهد. کوئری‌ای که ۵۰۰ بار اجرا شده و هر بار یک ثانیه طول کشیده، خیلی مهم‌تر از کوئری‌ای است که یک بار سه ثانیه طول کشید. از بالای این لیست شروع کنید.

دوم: با EXPLAIN بفهم چرا کند است

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

EXPLAIN SELECT * FROM orders
  WHERE customer_id = 42
  ORDER BY created_at DESC;

به دو ستون نگاه کنید: type و Extra. اگر type برابر ALL باشد یعنی اسکن کامل جدول. مستندات MySQL همین را می‌گوید: این حالت «معمولاً بد است و با افزودن ایندکس می‌توان از آن پرهیز کرد.» اگر در Extra عبارت Using filesort ببینید، یعنی MySQL نمی‌تواند از ایندکس برای مرتب‌سازی استفاده کند و باید یک گذر اضافه بزند تا سطرها را مرتب کند. Using temporary هم یعنی برای حل کوئری یک جدول موقت ساخته می‌شود، که روی داده‌ی بزرگ گران تمام می‌شود.

حالا یک ایندکس ترکیبی که هم شرط و هم ترتیب را پوشش دهد اضافه کنید:

ALTER TABLE orders
  ADD INDEX idx_customer_created (customer_id, created_at);

همان EXPLAIN را دوباره بگیرید. حالا type باید چیزی مثل ref شود، ستون rows از چند میلیون به چند ده بیفتد و Using filesort از Extra حذف شود. اگر در کوئری به‌جای SELECT * فقط ستون‌هایی را بخواهید که داخل همین ایندکس‌اند، ممکن است Extra: Using index ببینید. طبق مستندات MySQL این یعنی داده مستقیم از خود درخت ایندکس خوانده می‌شود، بدون seek اضافه برای خواندن سطر اصلی؛ به این الگو ایندکس پوشا (covering index) می‌گویند و از همه سریع‌تر است.

سوم: قفل‌ها و دِدلاک را با SHOW ENGINE INNODB STATUS ببین

گاهی کوئری‌ها خودشان کند نیستند، فقط منتظر یکدیگرند. وقتی چند تراکنش روی یک سطر قفل می‌افتند، علامت آن کندی یکهویی و تایم‌اوت است، نه مصرف بالای CPU. این دستور وضعیت داخلی InnoDB را می‌دهد:

SHOW ENGINE INNODB STATUS\G

خروجی طولانی است. چند بخش آن به‌کار عیب‌یابی می‌آید. بخش TRANSACTIONS تراکنش‌های باز و انتظارهای قفل را نشان می‌دهد؛ اگر تراکنشی را ببینید که در حالت LOCK WAIT مانده، یکی نشست خودش را باز گذاشته. بخش LATEST DETECTED DEADLOCK جزئیات آخرین دِدلاک را می‌دهد، شامل دو تراکنشی که به هم گیر کرده‌اند و اینکه InnoDB کدام را قربانی کرد. بخش SEMAPHORES اگر پر از thread منتظر باشد، نشانه‌ی فشار روی قفل‌های داخلی است. بخش BUFFER POOL AND MEMORY هم می‌گوید بافر پول چقدر پر است و نرخ hit چقدر است.

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

اتصال‌های پرشده: چرا max_connections را کورکورانه بالا نبرید

خطای Too many connections وسوسه می‌کند که فوراً max_connections را دو برابر کنید. اول ببینید واقعاً چند اتصال فعال است:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

اگر Max_used_connections به سقف خورده ولی Threads_connected فعلی پایین است، معمولاً اپلیکیشن اتصال‌ها را درست نمی‌بندد یا connection pool اشتباه تنظیم شده. بالا بردن سقف فقط مشکل را به تأخیر می‌اندازد و در عوض یک خطر جدی می‌سازد: مصرف رم. چرا؟ به بخش بعد.

پارامترهایی که واقعاً اثر دارند و رابطه‌شان با رم

مصرف حافظه‌ی MySQL دو بخش دارد. بخش سراسری (global) یک بار برای کل سرور گرفته می‌شود. بخش per-connection به ازای هر اتصال جدا گرفته می‌شود. فرمول تخمینی بدترین‌حالت این است:

کل رم ≈ بافرهای سراسری + (بافرهای هر اتصال × max_connections)

مهم‌ترین پارامتر سراسری innodb_buffer_pool_size است. مقدار پیش‌فرضش در هر دو MySQL و MariaDB برابر ۱۲۸ مگابایت است، که برای هر سرور جدی کم است. مستندات MySQL می‌گویند روی سرور اختصاصی دیتابیس «تا ۸۰٪ حافظه‌ی فیزیکی» را می‌توان به بافر پول داد و مستندات MariaDB هم همین ۸۰٪ را توصیه می‌کند. اما این عدد فقط وقتی درست است که سرور فقط دیتابیس باشد. اگر روی همان VPS وب‌سرور و PHP و ردیس هم دارید، ۸۰٪ یعنی OOM.

مستندات MySQL تصریح می‌کنند که بافرهایی مثل read buffer و sort buffer به ازای هر thread کلاینت گرفته می‌شوند. یعنی اگر sort_buffer_size را روی ۲۵۶M بگذارید و max_connections روی ۵۰۰ باشد، در بدترین حالت فقط همین یک پارامتر می‌تواند ۱۲۵ گیگابایت بخواهد. روی یک VPS چهار گیگابایتی این یعنی مرگ.

بافر پول را می‌توان بدون ری‌استارت و آنلاین تغییر داد:

SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2G

اینکه بافر پول کافی است یا نه را از نرخ hit بفهمید:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

نسبت Innodb_buffer_pool_reads (خواندن از دیسک) به Innodb_buffer_pool_read_requests (کل درخواست‌ها) باید خیلی کوچک باشد. اگر reads نسبت به درخواست‌ها بالاست، دیتابیس مدام از دیسک می‌خواند و بافر پول کوچک است. اینجا دقیقاً جایی است که انتخاب سرور اهمیت پیدا می‌کند: پلن‌های هسته‌اختصاصی و دیسک NVMe روی سرور مجازی انگلستان فضای کافی برای یک بافر پول بزرگ و I/O سریع می‌دهند، تا وقتی داده از رم بیرون می‌زند دیسک گلوگاه نشود.

چرا کپی کورکورانه‌ی کانفیگ سرور را با OOM می‌کشد

کانفیگ‌هایی که در اینترنت پیدا می‌کنید معمولاً برای یک سرور اختصاصی ۶۴ گیگابایتی نوشته شده‌اند. وقتی همان را روی VPS چهار گیگابایتی می‌چسبانید، جمع بافر پول بزرگ و بافرهای per-connection و max_connections بالا از کل رم سرور عبور می‌کند. لینوکس وقتی رم و swap تمام شود OOM killer را فعال می‌کند و فرایندی را که بیشترین حافظه را گرفته می‌کشد. آن فرایند تقریباً همیشه خود mysqld است، چون بزرگ‌ترین مصرف‌کننده‌ی رم روی سرور است. نتیجه: دیتابیس بی‌هوا kill می‌شود و اپلیکیشن می‌خوابد.

اگر دیتابیس ناگهان قطع شد، اول همین را چک کنید:

dmesg -T | grep -i -E 'oom|killed process'
journalctl -k | grep -i oom

اگر خطی مثل Out of memory: Killed process 1234 (mysqld) دیدید، مشکل کانفیگ است نه خرابی دیتابیس. قبل از دائمی‌کردن هر my.cnf، این جمع را روی کاغذ بزنید: بافر پول + (بافرهای هر اتصال × max_connections) باید زیر رم فیزیکی بماند و برای سیستم‌عامل و بقیه‌ی سرویس‌ها هم جا بگذارید. مستندات MySQL هم تأکید دارند که باید به‌قدری حافظه برای بقیه‌ی پروسه‌ها بماند که سرور بدون paging زیاد کار کند.

ایندکس‌گذاری: چیزی که بیشتر از هر تنظیمی جواب می‌دهد

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

چند قاعده‌ی عملی:

• ستون‌هایی که در WHERE، JOIN و ORDER BY می‌آیند نامزد ایندکس‌اند.
• در ایندکس ترکیبی ترتیب ستون‌ها مهم است. ستونی که با تساوی فیلتر می‌شود جلوتر، ستون بازه یا مرتب‌سازی عقب‌تر. در مثال بالا (customer_id, created_at) جواب داد چون customer_id با تساوی و created_at با ترتیب استفاده شد.
• ایندکس روی ستونی با گزینش کم (مثل فیلد بله/خیر) تقریباً بی‌فایده است.
• ایندکس اضافه مجانی نیست. هر INSERT و UPDATE باید ایندکس‌ها را هم به‌روز کند، پس ایندکس بی‌استفاده را حذف کنید.

هر ایندکسی که زدید، با EXPLAIN تأیید کنید که واقعاً استفاده می‌شود. گاهی به‌خاطر نوع داده‌ی ناسازگار یا تابع روی ستون، MySQL ایندکس را نادیده می‌گیرد و شما یک ایندکس دارید که فقط نوشتن را کند کرده.

کجا این را اجرا کنیم

یک دیتابیس پربازدید دو چیز می‌خواهد: رم کافی برای اینکه داده‌ی داغ در بافر پول جا شود، و I/O سریع برای وقتی که داده از رم بیرون می‌زند. برای بیشتر سایت‌ها و اپلیکیشن‌ها یک پلن هسته‌اختصاصی با رم مناسب و دیسک NVMe روی سرور مجازی انگلستان کافی است و اجازه می‌دهد بافر پول را راحت تنظیم کنید بدون ترس از OOM. اگر دیتاست از رم VPS بزرگ‌تر شده، نوشتن‌ها سنگین‌اند و همسایه‌ها روی پلن اشتراکی اذیت‌تان می‌کنند، وقتش است بروید سراغ سرور اختصاصی که کل رم و دیسک در اختیار خود دیتابیس است. قبل از ارتقا یک بار مطمئن شوید کندی از ایندکس نیست؛ خیلی‌ها سخت‌افزار بزرگ‌تر خریدند و مشکلشان با یک ایندکس حل می‌شد.

منابع

• MySQL 8.0 Reference Manual: The Slow Query Log (unknown)
• MySQL 8.0 Reference Manual: EXPLAIN Output Format (unknown)
• MySQL 8.0 Reference Manual: The InnoDB Buffer Pool (unknown)
• MySQL 8.0 Reference Manual: How MySQL Uses Memory (unknown)
• MySQL 8.0 Reference Manual: InnoDB Standard Monitor and Lock Monitor Output (unknown)
• MariaDB Documentation: InnoDB System Variables (unknown)
• Understanding the Linux Virtual Memory Manager: Out Of Memory Management (unknown)

پرسش‌های پرتکرار