تنظیمات کلیدی سرور: buffer pool، max_connections و redo log

چند تنظیم که بیشترِ اثر را دارند

MySQL صدها متغیر دارد، اما روی یک سرور معمولی سه چهار تنظیم بیشترین اثر را می‌گذارند. پیش‌فرض‌ها برای ماشینی کوچک انتخاب شده‌اند؛ مثلاً buffer pool پیش‌فرض فقط ۱۲۸ مگابایت است، حتی اگر سرور شما ۳۲ گیگابایت حافظه داشته باشد.

نمونه‌ی my.cnf برای سرور اختصاصی ۱۶ گیگابایتی

[mysqld]
innodb_buffer_pool_size        = 11G
innodb_redo_log_capacity       = 2G     # از 8.0.30
innodb_flush_log_at_trx_commit = 1
max_connections                = 300
wait_timeout                   = 600
table_open_cache               = 4000
slow_query_log                 = ON
long_query_time                = 1

اندازه‌گیری، نه حدس

-- نسبت خواندن از دیسک به کل خواندن‌ها؛ باید زیر یک درصد باشد
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

-- بیشترین اتصال هم‌زمانی که تا امروز رخ داده
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Threads_running';

-- حجم redo نوشته‌شده؛ دو بار با فاصله‌ی یک ساعت بگیرید و تفاضل را ببینید
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

-- چه چیزی حافظه را گرفته؟
SELECT * FROM sys.memory_global_by_current_bytes LIMIT 10;

-- تغییر بدون ری‌استارت
SET PERSIST innodb_buffer_pool_size = 12884901888;
SET PERSIST innodb_redo_log_capacity = 2147483648;

اگر سرور فقط برای MySQL است، innodb_dedicated_server = ON اندازه‌ی buffer pool و redo log را بر اساس RAM خودکار تعیین می‌کند. روی VPS اشتراکی که جنگو و Nginx و Redis هم روی آن‌اند، این گزینه را روشن نکنید و حافظه را دستی تقسیم کنید.

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

• بافرهای sort_buffer_size و join_buffer_size برای هر اتصال (و گاهی هر عملیات) جدا گرفته می‌شوند؛ بالا بردن سراسری‌شان با ۳۰۰ اتصال، سرور را به دست OOM killer می‌سپارد. برای یک گزارش سنگین فقط در همان session افزایش دهید.
• innodb_flush_log_at_trx_commit = 2 با کرش خود mysqld داده از دست نمی‌دهد؛ فقط با کرش سیستم‌عامل یا قطع برق تا حدود یک ثانیه تراکنش از بین می‌رود.
• عدد مهم Threads_running است، نه Threads_connected؛ صدها اتصال بی‌کار ارزان‌اند، اما بیش از چند برابر تعداد هسته کوئری فعال یعنی صف و کندی.
• buffer pool هنگام خاموشی فهرست صفحه‌های داغ را ذخیره و در راه‌اندازی بعدی بارگذاری می‌کند (innodb_buffer_pool_dump_at_shutdown)؛ برای همین بعد از ری‌استارت چند دقیقه طول می‌کشد تا سرعت عادی برگردد.
• در داکر، اگر برای کانتینر محدودیت حافظه گذاشته‌اید، buffer pool را بر اساس همان محدودیت تنظیم کنید؛ وگرنه کانتینر بی‌صدا kill و ری‌استارت می‌شود.