کانفیگ پایه my.cnf برای MySQL روی سرور کوچک

تنظیمات پیش‌فرض MySQL برای سرورهای کوچک بهینه نیست. با چند پارامتر مهم مصرف حافظه و سرعت را بهبود می‌دهید. مقادیر زیر نقطهٔ شروع‌اند؛ آن‌ها را با پایش واقعی تنظیم کنید.

دیتابیس و بکاپمتوسط8 دقیقه

۱مسیر فایل تنظیمات

تنظیمات سفارشی را در یک فایل جدا بنویسید تا با به‌روزرسانی بسته بازنویسی نشود.

bash

# اوبونتو و دبیان
sudo nano /etc/mysql/mysql.conf.d/99-custom.cnf

# RHEL و CentOS
sudo nano /etc/my.cnf.d/99-custom.cnf

۲تنظیمات پیشنهادی برای سرور ۴ گیگابایتی

innodb_buffer_pool_size مهم‌ترین پارامتر است؛ روی سرور اختصاصی دیتابیس حدود ۶۰ تا ۷۰ درصد رم و روی سروری که سایت هم دارد حدود ۳۰ تا ۵۰ درصد رم بگذارید.

ini

[mysqld]
# کاراکتر و collation
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

# حافظه
innodb_buffer_pool_size = 1G
innodb_log_file_size    = 256M
innodb_flush_method     = O_DIRECT

# اتصال‌ها
max_connections    = 150
thread_cache_size  = 16
wait_timeout       = 300

# لاگ کوئری‌های کند
slow_query_log       = 1
slow_query_log_file  = /var/log/mysql/slow.log
long_query_time      = 1

# امنیت: فقط اتصال محلی
bind-address = 127.0.0.1

روی MariaDB ممکن است نام بعضی پارامترها متفاوت باشد؛ داکیومنت نسخهٔ خودتان را ببینید.

۳اعمال تغییرات و تست

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

bash

sudo systemctl restart mysql
sudo systemctl status mysql --no-pager
sudo journalctl -u mysql -n 30 --no-pager

۴بررسی مقدارهای اعمال‌شده

با دستور زیر مطمئن شوید تنظیمات واقعاً اعمال شده‌اند.

bash

mysql -e "SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','max_connections','character_set_server','slow_query_log');"

۵پایش کوئری‌های کند

بعد از چند روز، کند‌ترین کوئری‌ها را ببینید و برای آن‌ها ایندکس بسازید. ابزار mysqldumpslow (همراه MySQL) لاگ را خلاصه می‌کند.

bash

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

۶نسبت موفقیت buffer pool

اگر مقدار Innodb_buffer_pool_reads نسبت به Innodb_buffer_pool_read_requests زیاد است، حافظهٔ buffer pool کم است.

bash

mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

پرسش‌های پرتکرار دربارهٔ کانفیگ پایه my.cnf برای MySQL روی سرور کوچک

innodb_buffer_pool_size را چقدر بگذارم؟

قاعدهٔ رایج: روی سرور فقط-دیتابیس حدود ۶۰ تا ۷۰٪ رم؛ اگر سرور وب هم روی همان ماشین است حدود ۳۰ تا ۵۰٪. مقدار بیش از حد باعث swap و کندی شدید می‌شود.