آموزش بهینه‌سازی MariaDB برای حداکثر کارایی
آیا وب‌سایت یا اپلیکیشن شما کند شده است؟ آیا مصرف منابع CPU و RAM در سرور شما به شکل نگران‌کننده‌ای بالاست؟ در بسیاری از موارد، ریشه این مشکلات نه در کد برنامه، بلکه در قلب تپنده آن یعنی پایگاه داده نهفته است. MariaDB، به عنوان یکی از محبوب‌ترین سیستم‌های مدیریت پایگاه داده، یک موتور قدرتمند است، اما تنظیمات پیش‌فرض آن برای عملکرد بهینه در محیط‌های پربازدید طراحی نشده‌اند.
این مقاله یک راهنمای جامع و کاربردی برای کانفیگ و بهینه‌سازی دیتابیس MariaDB است. ما به شما نشان می‌دهیم که چگونه مشکلات عملکردی را تشخیص دهید و با تنظیم صحیح پارامترهای کلیدی، سرعت و پایداری سرور خود را به شکل چشمگیری افزایش دهید.
قدم اول: شناسایی عامل اصلی کندی با ابزار MySQLTuner
قبل از تغییر هرگونه تنظیمات، باید بدانید که مشکل کجاست. تغییر بدون دانش پارامترها می‌تواند وضعیت را بدتر کند. MySQLTuner یک اسکریپت perl فوق‌العاده است که سرور شما را تحلیل کرده و پیشنهادهای مشخصی برای بهبود عملکرد ارائه می‌دهد.
نصب و اجرای MySQLTuner:
#ابتدا ابزار مورد نیاز را نصب کنید sudo apt update sudo apt install libstatistics-basic-perl libtimedate-perl # دانلود اسکریپت wget http://mysqltuner.pl/ -O mysqltuner.pl chmod +x mysqltuner.pl # اجرای اسکریپت ./mysqltuner.pl
خروجی این اسکریپت، نقاط ضعف کانفیگ شما را در بخش‌هایی مانند امنیت و عملکرد مشخص کرده و مقادیر پیشنهادی برای پارامترها را ارائه می‌دهد. این گزارش، نقشه راه شما برای ادامه این مقاله خواهد بود.
۱. بهینه‌سازی حافظه (Memory Tuning): کلید سرعت
بیشترین تاثیر در عملکرد دیتابیس، از مدیریت صحیح حافظه ناشی می‌شود. هدف این است که داده‌های پراستفاده تا حد امکان در RAM باقی بمانند تا نیاز به دسترسی به هارد دیسک کند، به حداقل برسد.
innodb_buffer_pool_size
این مهم‌ترین پارامتری است که باید تنظیم کنید. Buffer Pool فضایی در RAM است که MariaDB داده‌ها و ایندکس‌های جداول InnoDB را در آن cache می‌کند.
- قانون کلی: در یک سرور که منحصرا برای دیتابیس استفاده می‌شود، این مقدار را بین 70 تا 80 درصد کل RAM سرور تنظیم کنید. اگر سرویس‌های دیگری (مانند وب‌سرور) روی سرور فعال هستند، این مقدار را با احتیاط بیشتری تنظیم کنید.
- نحوه تنظیم (در فایل /etc/mysql/mariadb.conf.d/50-server.cnf):
[mysqld] innodb_buffer_pool_size = 4G # برای سروری با 6GB رم
query_cache_size (نکته مهم)
کش کوئری، نتایج کوئری‌های SELECT را ذخیره می‌کرد. این ویژگی در نسخه‌های جدید منسوخ شده و استفاده از آن توصیه نمی‌شود. زیرا در محیط‌های پربازدید، مدیریت این کش خود به یک نقطع ضعف تبدیل می‌شود.
توصیه: این ویژگی را غیرفعال کنید، مگر اینکه دلیل بسیار مشخصی برای استفاده از آن داشته باشید.
query_cache_type = 0 query_cache_size = 0
۲. بهینه‌سازی ورودی/خروجی (I/O Tuning)
innodb_file_per_table
این پارامتر باعث می‌شود هر جدول InnoDB در یک فایل .ibd مجزا ذخیره شود. این کار مدیریت فضا را بسیار ساده‌تر می‌کند. برای مثال، وقتی یک جدول بزرگ را حذف یا TRUNCATE می‌کنید، فضای آن فورا به سیستم‌عامل بازگردانده می‌شود.
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit
این پارامتر تعیین می‌کند که لاگ تراکنش‌ها با چه دقتی روی دیسک نوشته شود و تعادل بین پایداری داده (ACID) و عملکرد را مشخص می‌کند.
- 1 (مقدار پیش‌فرض و امن‌ترین حالت): لاگ‌ها پس از هر COMMIT روی دیسک نوشته می‌شوند. این حالت حداکثر پایداری را دارد اما کندترین است.
- 2 (حالت بهینه): لاگ‌ها پس از هر COMMIT در کش سیستم‌عامل نوشته شده و هر ثانیه یک‌بار به دیسک منتقل می‌شوند. در صورت کرش کردن سیستم‌عامل (نه دیتابیس)، ممکن است تراکنش‌های یک ثانیه اخیر را از دست بدهید. این گزینه بهترین تعادل بین سرعت و امنیت برای اکثر وب‌سایت‌هاست.
- 0 (سریع‌ترین حالت): نوشتن روی دیسک کاملا به سیستم‌عامل واگذار می‌شود. سرعت بسیار بالاست اما ریسک از دست دادن داده در صورت قطعی برق یا کرش سیستم، بیشتر است.
innodb_flush_log_at_trx_commit = 2
۳. بهینه‌سازی کوئری‌ها (Query Optimization)
حتی با بهترین کانفیگ سرور، یک کوئری بد می‌تواند کل سیستم را مختل کند.
slow_query_log
این ابزار مهم، تمام کوئری‌هایی را که اجرایشان بیشتر از زمان مشخصی طول می‌کشد، در یک فایل لاگ ذخیره می‌کند. این فایل، منبعی برای پیدا کردن کوئری‌های ناکارآمد است.
نحوه فعال‌سازی:
slow_query_log = 1 slow_query_log_file = /var/log/mysql/mariadb-slow.log long_query_time = 1 # لاگ کردن کوئری‌هایی که بیش از 1 ثانیه طول می‌کشند
پس از فعال‌سازی، این فایل لاگ را به طور منظم بررسی کرده و با استفاده از دستور EXPLAIN قبل از کوئری‌های کند، نحوه اجرای آن‌ها را تحلیل و با افزودن ایندکس‌های مناسب، آن‌ها را بهینه کنید.
۴. بهینه‌سازی اتصالات (Connection Tuning)
max_connections
این پارامتر حداکثر تعداد اتصالات همزمان به دیتابیس را مشخص می‌کند. مقدار پیش‌فرض آن (151) برای بسیاری از سایت‌ها کم است. اگر با خطای Too many connections مواجه می‌شوید، باید این مقدار را افزایش دهید.
- نکته: هر اتصال مقداری RAM مصرف می‌کند. این عدد را بی‌رویه بالا نبرید. خروجی MySQLTuner و همچنین دستور SHOW GLOBAL STATUS LIKE ‘Max_used_connections’; به شما کمک می‌کند تا یک مقدار منطقی پیدا کنید.
wait_timeout
بسیاری از اتصالات پس از انجام کار، در حالت Sleep باقی می‌مانند و منابع را اشغال می‌کنند. این پارامتر، حداکثر زمانی (به ثانیه) را که یک اتصال غیرفعال می‌تواند باز بماند، مشخص می‌کند. کاهش این مقدار به آزاد شدن سریع‌تر منابع کمک می‌کند.
مقدار پیشنهادی: بین 30 تا 60 ثانیه برای اکثر اپلیکیشن‌های وب مناسب است.
wait_timeout = 60
- بهینه‌سازی پایگاه داده یک فرآیند مداوم است، نه یک کار یک‌باره.
- همیشه با ابزار شروع کنید: ابتدا با ابزارهایی مانند MySQLTuner نقاط ضعف در عملکرد پایگاه داده را شناسایی کنید.
- تغییرات را یک به یک اعمال کنید: هر بار یک پارامتر را تغییر دهید و عملکرد سیستم را برای مدتی زیر نظر بگیرید.
کوئری‌ها پادشاه هستند: بهترین کانفیگ سرور نمی‌تواند یک کوئری بد را جبران کند. تمرکز اصلی خود را روی بهینه‌سازی کوئری‌ها با استفاده از slow_query_log و EXPLAIN بگذارید. - زیرساخت قدرتمند: تمام این بهینه‌سازی‌ها روی یک زیرساخت قوی و پایدار بهترین نتیجه را می‌دهند. استفاده از سرور مجازی پرسرعت بلوسرور با حافظه‌های NVMe، بستر لازم برای دستیابی به حداکثر کارایی دیتابیس شما را فراهم می‌کند.
فهرست تیترها