خدمات IT هزاره سوم

مشاوره، طراحی، توسعه شبکه، سایت، سئوی خود را به ما بسپارید.

WebDev📅 ۲۷ آبان ۱۴۰۵⏱ 10 دقیقه مطالعه💬 0 دیدگاه
راهنما
۲۷ آبان ۱۴۰۵سرور و میزبانی9 دقیقه مطالعه

نکات کلیدی

  • slow_query_log: فعال کنید و آستانه ی زمان اجرا را پایین تر از مقدار پیش فرض تنظیم کنید تا کوئری های کند شناسایی شوند.
  • نکته: برای فعال سازی در MariaDB 10.11، دستور زیر را اجرا کنید: “`sql SET GLOBAL slow_query_log = ‘ON’; SET GLOBAL long_query_time = 10; “` سپس با بررسی فایل لاگ، کوئری های طولانی را شناسایی و بهینه سازی کنید.
  • کش کوئری: در MySQL 8.0 حذف شده و در MariaDB با `query_cache_size` فعال می شود؛ این تنظیم را بی صدا بررسی کنید.

مقدمه

در سرورهای دارای حافظه محدود، هر پروسه‌ی مصرف‌کننده‌ی حافظه می‌تواند باعث تولید خطای Out of memory شود. این خطا معمولا توسط کرنل ایجاد می‌شود تا حافظه آزاد کند و در نتیجه پروسه‌ی دیتابیس (mariadbd یا mysqld) قطع می‌شود. برای جلوگیری از این اتفاق، ابتدا باید سهم حافظه‌ی دیتابیس را نسبت به سایر سرویس‌ها محاسبه کرده و تنظیمات را متناسب با آن انجام داد.


نکات کلیدی تنظیمات حافظه

  • innodb_buffer_pool_size: مقدار آن نباید بیش از درصد مشخصی از کل رم سرور باشد. برای سرورهای دیتابیس اختصاصی معمولاً 80٪ پیشنهاد می‌شود؛ ولی در سرورهای مشترک، بهتر است کمتر باشد.
  • max_connections: این مقدار را نزدیک به بیشترین تعداد اتصال واقعی تنظیم کنید تا ریسورس‌های اضافی مصرف نشوند.
  • sort_buffer_size و join_buffer_size: به حالت پیش‌فرض برگردانید؛ افزایش آن‌ها حافظه را مصرف می‌کند.
  • query_cache_size و query_cache_type: در MariaDB، مقدار صفر و خاموش بودن cache توصیه می‌شود؛ در MySQL نیز مشابه است.
  • slow_query_log: فعال کنید و آستانه‌ی زمان اجرا را پایین‌تر از مقدار پیش‌فرض تنظیم کنید تا کوئری‌های کند شناسایی شوند.

محاسبه سهم حافظه دیتابیس

حافظه باقی‌مانده پس از اختصاص به Nginx، PHP‑FPM و سایر سرویس‌ها، برای تنظیمات MySQL/MariaDB در نظر گرفته می‌شود. قبل از اعمال تغییرات، فایل تنظیمات اصلی را باز کنید:

  • MariaDB (Ubuntu 24.04): `/etc/mysql/mariadb.conf.d/50-server.cnf`
  • MySQL: `/etc/mysql/mysql.conf.d/mysqld.cnf`

هر خط اشتباه در این فایل‌ها ممکن است باعث مصرف بیش از حد حافظه شود.


نکات عملی برای سرورهای وردپرس

  • اگر سرور فقط برای وردپرس استفاده می‌شود، گزینه‌ی innodb_dedicated_server در MySQL 8.4 می‌تواند بافر پول را به صورت خودکار تنظیم کند. برای رم 1 تا 4 گیگ، مقدار پیش‌فرض نصف رم است.
  • MariaDB این گزینه را ندارد؛ در آزمایش‌های انجام شده بر روی 10.11.14، پیدا نشد.

جمع‌بندی

بهینه‌سازی حافظه در MySQL و MariaDB برای سرورهای رم محدود، نیازمند محاسبه دقیق سهم حافظه، تنظیم درست پارامترهای buffer و محدودیت اتصال است. با رعایت نکات بالا، خطر OOM کاهش یافته و پایگاه‌داده به صورت پایدار عمل می‌کند.


لاگ ردو؛ دو نام، دو موتور

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


کاهش سقف `max_connections`

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

مثال: اگر در یک سالن با ۱۰۰ صندلی، ۵۰۰ کارت دعوت چاپ شود، هر مهمانی به یک صندلی نیاز دارد؛ همین‌طور هر اتصال به سرور نیاز به استک و حافظهٔ جداگانه‌ای دارد.

منابع حافظهٔ هر نشست

منبع توضیح
`thread_stack` استک هر اتصال
`net_buffer_length` بافر شبکهٔ هر نشست
`sort_buffer_size` حافظهٔ مرتب‌سازی
`join_buffer_size` حافظهٔ join بدون ایندکس (ممکن است چند بار مصرف شود)
`tmp_table_size` / `max_heap_table_size` جدول موقت در حافظه
حتما مطالعه نمائید.
سرور اختصاصی هتزنر؛ راهنمای انتخاب سری AX، EX و آکشن 2026

حد بدبینانهٔ مصرف حافظهٔ کل سرور: `(بفرهای سراسری) + max_connections × (thread_stack + net_buffer_length + سایر بافرهای نشست)`


تعیین مقدار مناسب از `max_used_connections`

پس از چند روز ترافیک معمولی، با دسترسی مدیریتی وارد پایگاه داده شوید و مقدار `max_used_connections` را بخوانید؛ سپس مقدار `max_connections` را کمی بالاتر از آن تنظیم کنید. اگر این مقدار بیش از `pm.max_children` در PHP‑FPM باشد، اتصال‌ها از منابع دیگر نیز وارد می‌شوند.


دو تنظیم پیش‌فرض جدا برای دو موتور

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


لاگ کوئری کند؛ چرا باید روشن شود

لاگ کوئری کند در MySQL 8.4 و MariaDB 10.11 به‌صورت پیش‌فرض خاموش است و آستانهٔ آن ۱۰ ثانیه است. بدون فعال‌سازی این لاگ، بهینه‌سازی پایگاه داده صرفاً به‌صورت تنظیمات بافر محدود می‌شود؛ در نتیجه مشکلات واقعی شناسایی نمی‌شوند.

نکته: برای فعال‌سازی در MariaDB 10.11، دستور زیر را اجرا کنید: “`sql SET GLOBAL slow_query_log = ‘ON’; SET GLOBAL long_query_time = 10; “` سپس با بررسی فایل لاگ، کوئری‌های طولانی را شناسایی و بهینه‌سازی کنید.


تنظیمات لاگ کند در MariaDB 10.11

  • در این نسخه، پارامترهای `slow_query_log` و `long_query_time` به صورت مستعار `log_slow_query` و `log_slow_query_time` شناخته می‌شوند.
  • ابتدا در ترمینال، مجوز پوشهٔ لاگ را به کاربر `mysql` بدهید:

“`bash sudo chown -R mysql:mysql /var/log/mysql “`

  • سپس سه خط زیر را در بخش `[mysqld]` فایل `50-server.cnf` قرار دهید:

“`ini log_slow_query=1 log_slow_query_time=2 log_slow_queries=1 “`


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

  • در MySQL 8.4، همان سه خط را در فایل `mysqld.cnf` تحت بخش `[mysqld]` قرار دهید.

خلاصه‌سازی کوئری‌های مشابه

  • ابزار `mysqldumpslow` (یا نسخهٔ MariaDB آن `mariadb-dumpslow`) کوئری‌های مشابه را گروه‌بندی می‌کند.
  • گزینهٔ `-s t` برای مرتب‌سازی بر اساس زمان و `-t` برای محدود کردن تعداد خروجی استفاده می‌شود.
  • با اجرای `EXPLAIN` بر روی کوئری‌های بالای فهرست، مشاهده می‌کنید ستون `key` خالی و `rows` بزرگ، که نشان‌دهندهٔ اندیس ناکافی است.

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

  • اعتبار گزارش MySQLTuner وابسته به زمان اجرای سرور از آخرین ری‌استارت است؛ در پروژه حداقل ۲۴ ساعت uptime لازم است.
  • پارامتر `–nondedicated` برای سرورهایی است که دیتابیس تنها بر روی آن اجرا نمی‌شود. اجرای اسکریپت با `sudo` ضروری است چون در اوبونتو با `unix_socket` دسترسی root بدون رمز است.

کش کوئری

  • در MySQL 8.0، کش کوئری حذف شده و خطوط مربوط به `query_cache` بی‌فایده‌اند.
  • در MariaDB 10.11، کش کوئری وجود دارد ولی خاموش است. اگر `query_cache_size` غیر صفر و غیر پیش‌فرض باشد، `query_cache_type` به صورت خودکار ON می‌شود، حتی اگر در فایل OFF باشد. برای خاموش کردن، هر دو را صفر بگذارید و کش را در لایهٔ وردپرس (کش صفحه یا object cache) ایجاد کنید.

پیش و پس از اصلاح فایل پیکربندی

  • نمونهٔ فایل پیکربندی برای سرور ۲ گیگابایتی با MariaDB 10.11 را بررسی کنید.
  • راهنمای حافظه MariaDB برای InnoDB پیشنهاد می‌کند ۶۰–۷۰٪ از رم در دسترس باشد، یعنی رم کل منهای سیستم عامل و سرویس‌های دیگر.
  • اگر سرور ۲ گیگ باشد و سیستم عامل، Nginx و PHP‑FPM در ساعت شلوغ حدود ۱٫۲ گیگ بگیرند، حدود ۸۰۰ مگابایت برای MariaDB باقی می‌ماند؛ بنابراین ۵۱۲M کافی است.

هنگام محدودیت رم

  • اگر دادهٔ پرکاربرد در رم جا نمی‌شود، تنظیمات اضافی ممکن است کمک نکند. منابع سرور مجازی را در پنل ارتقا دهید و قبل از آن، رم را با راهنمای انتخاب رم سرور مجازی بررسی کنید.

پیش و پس از ری‌استارت

  1. `sudo cp /etc/mysql/mariadb.conf.d/50-server.cnf ~/50-server.cnf.bak`
  2. `sudo systemctl restart mariadb`
  3. `sudo journalctl -u mariadb -n 30`
  • در صورت عدم راه‌اندازی سرویس، خطاهای مربوط به نام متغیر مشکل‌دار نمایش داده می‌شود.
حتما مطالعه نمائید.
معرفی ESXi: هایپروایزر قدرتمند برای مجازی سازی سرور

نگه داشتن وردپرس و MariaDB بر روی سرور ۱ گیگ رم

  • ممکن است امکان‌پذیر باشد؛ پیش‌فرض MySQL 8.4 برای ۵۱۲M رم طراحی شده، در حالی که MariaDB پیش‌فرض بافر پول ۱۲۸M است. بافر را نگه دارید و `max_connections` را بر اساس `Max_used_connections` تنظیم کنید.

تغییر اندازه بافر پول بدون ری‌استارت

  • در MySQL 8.4، می‌توانید با `SET GLOBAL innodb_buffer_pool_size` و عددی که ضربهٔ `innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances` است، تغییر دهید.
  • در MariaDB 10.11، کوچک کردن ممکن است، اما بزرگ کردن تا حد `innodb_buffer_pool_size_max` مجاز است؛ در نسخه‌های 10.11.12 تا 10.11.16 مقدار اولیه همان است؛ در 10.11.14 بر روی اوبونتو با هشدار 1292 رد شد. از 10.11.17 سقف ۸ ترابایت است. در غیر این صورت، مقدار را در فایل بنویسید و ری‌استارت کنید.

چرا MySQLTuner پس از ری‌استارت پیشنهادهای عجیب می‌دهد؟

  • چون آمار هنوز جمع نشده است؛ پروژه حداقل ۲۴ ساعت uptime می‌خواهد.

خاموش کردن Performance Schema برای صرفه‌جویی در رم

  • در MySQL 8.4، Performance Schema روشن است و حافظه را تا ری‌استارت آزاد نمی‌کند؛ خاموش کردن آن رم را آزاد می‌کند، اما مانیتورینگ را خاموش می‌کند.
  • در MariaDB 10.11، از ابتدا خاموش است.

روشن کردن کش کوئری در MariaDB و تأثیر آن بر سرعت سایت

  • برای SELECTهای تکراری ممکن است مفید باشد، اما هر نوشتن روی جدول کش آن را باطل می‌کند و باعث کاهش قفل مشترک می‌شود. راهنمای حافظه MariaDB توصیه می‌کند کش را خاموش نگه دارید.

محل دقیق فایل my.cnf در اوبونتو

  • فایل `my.cnf` در مسیر `/etc/mysql/my.cnf` قرار دارد، اما تنظیمات نهایی در فایل‌های زیر در دایرکتوری `/etc/mysql/mariadb.conf.d/` یا `/etc/mysql/mysql.conf.d/` تعریف می‌شوند.

فایل my.cnf و دستورات مرتبط

  • `/etc/mysql/my.cnf` به‌عنوان یک لینک به تنظیمات MariaDB یا MySQL عمل می‌کند.
  • اجرای `my_print_defaults –mariadbd` گزینه‌هایی را نمایش می‌دهد که MariaDB در زمان راه‌اندازی می‌خواند.

مشکل «Too many connections» و max_connections

  1. ابتدا سعی کنید اتصالات باقی‌مانده را پیدا کنید؛ ممکن است یک اتصال بسته نشده باشد.
  2. MariaDB یک اتصال اضافی برای کاربر SUPER یا CONNECTION ADMIN نگه می‌دارد تا امکان ورود فراهم شود.
  3. اگر تعداد اتصالات بیش از حد مجاز باشد، باید مقدار `max_connections` را افزایش دهید، اما این کار بدون تحلیل دقیق ممکن است باعث مصرف غیرضروری حافظه شود.

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

  • تقسیم حافظه: درصدهای حافظه را به‌صورت مناسب بین سرور اختصاصی و سرویس وردپرس تخصیص دهید؛ استفاده از حافظه برای بافر پول (pool buffer) را حتماً محاسبه کنید.
  • محدودیت max_connections: از مقدار `max_used_connections` برای تعیین حد بالای `max_connections` استفاده کنید تا از بیش‌خروجی جلوگیری شود.
  • حافظه نشست (session buffers): پیش‌فرض‌های بافرهای هر نشست را نگه دارید؛ زیرا در تعداد اتصالات ضرب می‌شوند.
  • رویت‌های لاگ: لاگ کند (slow query log) را فعال کنید و پس از ۲۴ ساعت uptime، گزارش MySQLTuner را بررسی کنید.
  • موتورهای دوگانه: از ترکیب دو موتور (مثلاً InnoDB و MyISAM) پرهیز کنید، زیرا متغیرهای ناشناخته ممکن است باعث افزایش مصرف حافظه شوند.
  • کش کوئری: در MySQL 8.0 حذف شده و در MariaDB با `query_cache_size` فعال می‌شود؛ این تنظیم را بی‌صدا بررسی کنید.
  • ارتقای منابع: اگر حافظه کافی در رم قرار نگرفت، زمان آن رسیده است که منابع سخت‌افزاری را افزایش دهید.

گام بعدی: راه‌اندازی وردپرس

  • پس از تثبیت MariaDB، می‌توانید Nginx و PHP‑FPM را نصب و تنظیم کنید.
  • وردپرس را بر روی همان سرور نصب کنید و تنظیمات اولیه را اعمال کنید.
  • در صورت بروز 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

پرسش‌های متداول

چرا خطای *Out of memory* در `journalctl -k` رخ می دهد؟

کرنل برای آزاد کردن حافظه، پروسه ی دیتابیس را متوقف می کند؛ معمولاً به دلیل مصرف بیش از حد حافظه توسط آن.

چه مقدار باید `innodb_buffer_pool_size` برای سرورهای 2 گیگ رم تنظیم شود؟

معمولاً 30-40٪ از رم، یعنی حدود 600-800 مگابایت، برای جلوگیری از مصرف زیاد حافظه.

آیا `query_cache` در MariaDB مفید است؟

در اکثر موارد بهتر است آن را خاموش کنید؛ چون مصرف حافظه را افزایش می دهد و در محیط های رم محدود مزیت چندانی ندارد.

امتیازدهی

دیدگاه خود را بنویسید