بهینه‌سازی MySQL و MariaDB برای سرور با رم کم

خط Out of memory: Killed process … (mariadbd) در journalctl -k یعنی کرنل برای آزاد کردن حافظه، پروسه دیتابیس را کشته است. بهینه‌سازی MySQL روی سرور کم‌رم از همین خط شروع می‌شود: سهم دیتابیس را از رمی حساب کنید که بعد از Nginx و PHP-FPM می‌ماند و سقف اتصال‌ها را به اتصال‌های واقعی نزدیک کنید.

• ✅ اندازه innodb_buffer_pool_size: کمتر از درصد سرور فقط‌دیتابیس
• ✅ سقف max_connections: نزدیک بیشترین اتصال واقعی
• ✅ بافرهای هر نشست (sort_buffer_size و join_buffer_size): برگشت به پیش‌فرض
• ✅ کش کوئری در MariaDB: query_cache_size صفر و query_cache_type خاموش
• ✅ لاگ کوئری کند: روشن، با آستانه پایین‌تر از پیش‌فرض

فایل تنظیمات فعلی را کنار این صفحه باز کنید. اگر سرور را با راهنمای نصب وردپرس روی سرور مجازی ساخته‌اید، فایل اصلی MariaDB روی Ubuntu 24.04 /etc/mysql/mariadb.conf.d/50-server.cnf است و روی MySQL، /etc/mysql/mysql.conf.d/mysqld.cnf. هر اشتباه پایین، خطی از همین فایل است.

کپی کردن innodb_buffer_pool_size از سرورهای بزرگ

اشتباه اول در بهینه‌سازی MySQL روی سرور کم‌رم، برداشتن درصدی است که برای سرور فقط‌دیتابیس نوشته شده. InnoDB کل بافر پول را هنگام راه‌اندازی رزرو می‌کند و در ساعت شلوغ، سهم PHP-FPM به OOM killer می‌رسد.

عدد ۸۰ درصد برای کدام سرور نوشته شده است

مرجع InnoDB در MySQL 8.4 عدد ۸۰ درصد را برای سرور اختصاصی دیتابیس آورده و صفحه How MySQL Uses Memory بازه ۵۰ تا ۷۵ درصد را رایج می‌داند؛ InnoDB حدود ۱۰ درصد بیشتر هم برمی‌دارد. راهنمای حافظه MariaDB زیر ۴ گیگ رم درصد کمتری پیشنهاد می‌کند.

این اشتباه گاهی از خود اوبونتو شروع می‌شود: 50-server.cnf خط #innodb_buffer_pool_size = 8G را کامنت‌شده دارد. # را بردارید و روی سرور ۲ گیگی، چهار برابر کل رم را خواسته‌اید.

گزینه innodb_dedicated_server روی سرور وردپرس

در MySQL 8.4 این گزینه بافر پول را خودکار می‌چیند: با ۱ تا ۴ گیگ رم، نصف آن. اما مستند همین گزینه وقتی MySQL منابع را با برنامه‌های دیگر شریک است استفاده از آن را توصیه نمی‌کند، یعنی همین سرور وردپرس. MariaDB این متغیر را ندارد؛ در آزمایش ما روی 10.11.14 هم پیدا نشد.

لاگ ردو؛ یک کار با دو اسم در دو موتور

در MySQL 8.4 متغیر innodb_log_file_size منسوخ است و innodb_redo_log_capacity با پیش‌فرض ۱۰۰ مگابایت جایش نشسته. در MariaDB 10.11 برعکس، innodb_log_file_size با ۹۶ مگابایت متغیر اصلی است و سرور آزمایشی با innodb_redo_log_capacity = 1G بالا نیامد:

[ERROR] mariadbd: unknown variable 'innodb_redo_log_capacity=1G'
[ERROR] Aborting

سقف max_connections را پایین بیاورید

خود max_connections حافظه رزرو نمی‌کند، اما سقف مصرف را بالا می‌برد؛ پس قدم دوم بهینه‌سازی MySQL و MariaDB روی سرور کوچک، پایین آوردن همین سقف است. پیش‌فرض هر دو موتور ۱۵۱ است و عدد ۱۰۰۰ یعنی اجازه بافر جدا برای صدها نشست.

برای تالار عروسی‌ای با ۱۰۰ صندلی، ۵۰۰ کارت دعوت چاپ شده است. هر مهمانی که برسد صندلی می‌خواهد، شام بخورد یا نخورد. هر اتصال هم استک خودش را می‌گیرد و برای مرتب‌سازی یا join، بشقاب جدا؛ تالار با چاپ کارت بزرگ نمی‌شود.

عدد درست را از Max_used_connections بخوانید

بعد از چند روز ترافیک عادی، با sudo mariadb (یا sudo mysql) وارد شوید و بیشترین اتصال همزمان از آخرین راه‌اندازی را بخوانید:

SHOW GLOBAL STATUS LIKE 'Max_used_connections';

سقف را کمی بالاتر از این عدد بگذارید. اگر عدد خیلی بیشتر از pm.max_children در PHP-FPM بود، اتصال‌ها از جای دیگری هم می‌آیند.

دو برگه پیش‌فرض جدا برای دو موتور

پیش‌فرض‌ها از مرجع متغیرهای سرور MySQL 8.4 و مستندات MariaDB آمده‌اند؛ ستون MariaDB را روی سرور آزمایشی هم با SELECT @@ خواندیم. مخزن Ubuntu 24.04 خودش MySQL 8.0 می‌دهد و این مقادیر از 8.0.30 به بعد همین‌اند.

متغیر	MySQL 8.4	MariaDB 10.11
بافر پول (innodb_buffer_pool_size)	۱۲۸ مگابایت	۱۲۸ مگابایت
سقف اتصال (max_connections)	۱۵۱	۱۵۱
بافر مرتب‌سازی (sort_buffer_size)	۲۵۶ کیلوبایت	۲ مگابایت (هشت برابر)
لاگ ردو	متغیر innodb_redo_log_capacity، ۱۰۰ مگابایت	متغیر innodb_log_file_size، ۹۶ مگابایت
ابزار Performance Schema	روشن	خاموش
کش کوئری	از نسخه 8.0 حذف شده	خاموش، با بافر ۱ مگابایت
لاگ کوئری کند	خاموش، آستانه ۱۰ ثانیه	خاموش، آستانه ۱۰ ثانیه

کوئری کند را حدس نزنید؛ Slow Query Log را روشن کنید

لاگ کوئری کند در MySQL 8.4 و MariaDB 10.11 پیش‌فرض خاموش است و آستانه‌اش ۱۰ ثانیه. بدون این لاگ، بهینه‌سازی MySQL و MariaDB فقط بازی با بافرهاست.

روشن کردن لاگ در MariaDB 10.11

در MariaDB 10.11 نام‌های slow_query_log و long_query_time مستعار log_slow_query و log_slow_query_time شده‌اند. اول در ترمینال پوشه لاگ را به کاربر mysql بدهید:

sudo mkdir -p /var/log/mysql && sudo chown mysql:mysql /var/log/mysql

بعد این سه خط را زیر بخش [mysqld] در 50-server.cnf بگذارید:

log_slow_query      = ON
log_slow_query_file = /var/log/mysql/mariadb-slow.log
log_slow_query_time = 1

روشن کردن لاگ در MySQL 8.4

slow_query_log      = ON
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time     = 1

این سه خط زیر [mysqld] در mysqld.cnf می‌روند.

خلاصه گرفتن به جای خواندن خط‌به‌خط

ابزار mysqldumpslow و نسخه MariaDB آن، mariadb-dumpslow، کوئری‌های مشابه را یکی می‌کنند؛ -s t بر اساس زمان مرتب می‌کند و -t تعداد را محدود:

sudo mariadb-dumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log
# MySQL:
sudo mysqldumpslow -s t -t 10 /var/lib/mysql/mysql-slow.log

کوئری بالای فهرست را با EXPLAIN ببینید؛ ستون key خالی و rows بزرگ یعنی ایندکس کم است.

به گزارش MySQLTuner و کش کوئری چقدر می‌شود اعتماد کرد؟

اعتبار این گزارش به عمر سرور از آخرین ری‌استارت بستگی دارد: README پروژه MySQLTuner دست‌کم ۲۴ ساعت uptime می‌خواهد و اجرا روی سرور تازه ری‌استارت‌شده را بی‌فایده می‌داند. این خروجی برای بهینه‌سازی MySQL و MariaDB فقط نظر دوم است؛ هر پیشنهادش را با مستند متغیر بسنجید.

حالت --nondedicated در همان README برای سروری است که دیتابیس در آن تنها نیست. با sudo اجرا کنید، چون root در MariaDB اوبونتو با unix_socket بدون رمز وارد می‌شود:

wget https://mysqltuner.pl/ -O mysqltuner.pl
sudo perl mysqltuner.pl --nondedicated

کش کوئری؛ در MySQL حذف شده، در MariaDB خاموش

یادداشت‌های MySQL 8.0 از حذف کش کوئری می‌گویند و متغیر ناشناخته در my.cnf سرور را با خطا متوقف می‌کند؛ پس خطوط query_cache در MySQL 8.4 جایی ندارند.

در MariaDB 10.11 کش هست و خاموش. مرجع متغیرهای MariaDB هشدار می‌دهد اگر سرور با query_cache_size غیرصفر و غیرپیش‌فرض بالا بیاید، query_cache_type خودکار ON می‌شود، حتی با OFF در فایل. سرور آزمایشی با 64M همین را نشان داد. هر دو را صفر بگذارید و کش را در لایه وردپرس بسازید (کش صفحه یا object cache).

همان فایل، پیش و پس از اصلاح

کپی از سرور بزرگ (نمونه رایج روی سرور ۲ گیگی با MariaDB 10.11)

[mysqld]
innodb_buffer_pool_size  = 8G
max_connections          = 1000
sort_buffer_size         = 8M
join_buffer_size         = 8M
query_cache_type         = 0
query_cache_size         = 64M
innodb_redo_log_capacity = 1G

نقطه شروع برای سرور کم‌رم، فقط MariaDB 10.11 (پشتوانه هر عدد یک قاعده مستند است)

[mysqld]
# 60-70% of available RAM
innodb_buffer_pool_size = 512M
# example: a bit above Max_used_connections
max_connections         = 60
# sort/join buffers: defaults
query_cache_type        = 0
query_cache_size        = 0
log_slow_query          = ON
log_slow_query_file     = /var/log/mysql/mariadb-slow.log
log_slow_query_time     = 1

راهنمای حافظه MariaDB برای InnoDB حدود ۶۰ تا ۷۰ درصد رم در دسترس را پیشنهاد می‌کند، یعنی رم کل منهای سیستم‌عامل و سرویس‌های دیگر. اگر روی سرور ۲ گیگی، سیستم‌عامل و Nginx و PHP-FPM در ساعت شلوغ حدود ۱٫۲ گیگ بگیرند (با free -m)، حدود ۸۰۰ مگابایت می‌ماند و 512M در همین بازه است. روی MySQL این فایل سرور را بالا نمی‌آورد: پنج خط آخر را با سه خط بخش MySQL عوض کنید.

سرورتان ۱ یا ۲ گیگ رم دارد و فعلاً قصد ارتقا ندارید؟ پیش از ارتقا، این‌ها را روی همان رم امتحان کنید.

جمع‌بندی؛ بهینه‌سازی MySQL روی سرور کم‌رم یعنی تقسیم درست رم درصدهای سرور اختصاصی روی سرور وردپرس جواب نمی‌دهد. بافر پول را از رم در دسترس حساب کنید. سقف max_connections را از Max_used_connections دربیاورید و بافرهای هر نشست را پیش‌فرض نگه دارید، چون در تعداد اتصال‌ها ضرب می‌شوند. لاگ کند را روشن کنید و MySQLTuner را بعد از ۲۴ ساعت uptime بخوانید. خطوط دو موتور را هم قاطی نکنید، چون MySQL با متغیر ناشناخته بالا نمی‌آید: کش کوئری در MySQL 8.0 حذف شد و در MariaDB با یک query_cache_size کپی‌شده بی‌صدا روشن می‌شود. اگر باز هم داده پرکاربرد در رم جا نشد، نوبت ارتقای منابع است.

کدام خط را اول عوض کردید و بعدش mysqld یا mariadbd چند روز بدون پیام OOM دوام آورد؟ اگر هنوز OOM می‌گیرید، رم سرور و خط innodb_buffer_pool_size را هم بگذارید؛ معمولاً مقصر همان است.

• مرجع MySQL 8.4: How MySQL Uses Memory
• مرجع MySQL 8.4: innodb_dedicated_server
• مرجع MySQL 8.4: Server System Variables
• مستندات MariaDB: Server System Variables
• مخزن MySQLTuner-perl در GitHub