همه مقالات تخصصی

اندازهٔ InnoDB Buffer Pool: نرخ miss را بخوانید و اندازهٔ امن برای MySQL انتخاب کنید

اندازهٔ InnoDB buffer pool را تعیین کنید: تنظیم فعلی را ببینید، نرخ miss خواندن از دیسک را بسنجید، با حجم داده و RAM مقایسه کنید، امن تغییر دهید و بعد از گرم‌شدن دوباره اندازه بگیرید.

اندازهٔ InnoDB Buffer Pool: نرخ miss را بخوانید و اندازهٔ امن برای MySQL انتخاب کنید

InnoDB buffer pool حافظه‌ای است که MySQL یا MariaDB برای نگه‌داشتن صفحه‌های جدول و ایندکس به کار می‌برد. اگر کوچک باشد کوئری‌ها بیشتر از دیسک می‌خوانند؛ اگر بزرگ باشد سیستم‌عامل برای PHP، وب‌سرور و خود پایگاه داده حافظه کم می‌آورد. اندازهٔ درست از اندازه‌گیری می‌آید نه عدد کپی‌شده. این راهنما نشان می‌دهد نرخ miss را چطور ببینید، با حجم داده و RAM مقایسه کنید و تنظیم را امن تغییر دهید. دستورها از مستندات MySQL و MariaDB آمده‌اند؛ روی سرور شما اجرایشان نکرده‌ام.

۱. تنظیم و شمارنده‌های فعلی را بخوانید

با دیدن اندازهٔ تنظیم‌شده و شمارنده‌های buffer pool شروع کنید:

SELECT @@innodb_buffer_pool_size/1024/1024 AS buffer_pool_mb;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_%';

شمارنده‌ها از آخرین ری‌استارت جمع می‌شوند، پس بعد از مدتی که سرور مشغول بوده بخوانید.

۲. نرخ miss را بسنجید

خواندن‌هایی که به دیسک رفتند را با همهٔ درخواست‌های خواندن مقایسه کنید:

-- Miss rate: disk reads divided by all read requests (a rough guide)
SELECT
  s1.VARIABLE_VALUE AS disk_reads,
  s2.VARIABLE_VALUE AS read_requests,
  ROUND(100 * s1.VARIABLE_VALUE / NULLIF(s2.VARIABLE_VALUE,0), 3) AS miss_percent
FROM performance_schema.global_status s1, performance_schema.global_status s2
WHERE s1.VARIABLE_NAME='Innodb_buffer_pool_reads' AND s2.VARIABLE_NAME='Innodb_buffer_pool_read_requests';

درصد خیلی کم معمولاً یعنی pool برای مجموعهٔ کاری شما کافی است. مقدار بالا یعنی حافظهٔ بیشتر یا کوئری‌های بهتر؛ راهنمای کوئری کند را ببینید.

۳. با حجم داده و RAM مقایسه کنید

ببینید سرور واقعاً چقدر حافظه دارد و داده InnoDB چقدر است:

free -m
SELECT ROUND(SUM(data_length+index_length)/1024/1024) AS innodb_data_mb
FROM information_schema.tables WHERE engine='InnoDB';
-- Rule of thumb: dedicated DB server 50-70% of RAM;
-- a server shared with PHP/web: much less, leave room for PHP-FPM and the OS

روی سروری که PHP و وب‌سرور هم دارد برای آن‌ها جا بگذارید؛ راهنمای تنظیم Apache، راهنمای PHP-FPM و راهنمای حافظه و OOM را ببینید.

۴. امن تغییر دهید

فایل پیکربندی را ویرایش کنید و اگر نسخه‌تان اجازه می‌دهد آنلاین تغییر اندازه دهید. بکاپ نگه دارید و در زمان آرام تغییر دهید:

# /etc/mysql/conf.d/buffer-pool.cnf (example, size to your own server)
[mysqld]
innodb_buffer_pool_size = 1G

-- Or change online (MySQL 5.7+/MariaDB 10.2+), then also put it in the file
SET GLOBAL innodb_buffer_pool_size = 1073741824;
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

بعدش حافظهٔ آزاد و swap را ببینید و اگر سرویس بالا نیامد لاگ خطا را بخوانید؛ راهنمای journalctl را ببینید.

۵. اشتباه‌هایی که باید پرهیز کرد

این عادت‌ها دردسر می‌سازند:

Setting 80-90% of RAM on a shared web+DB server   # swap or OOM kill
Changing it without looking at the miss rate      # no evidence it helps
Ignoring slow queries and adding RAM instead       # a missing index stays missing
Judging right after a restart                      # the pool is still cold

بعد از گرم‌شدن pool، معمولاً بعد از ساعت‌ها ترافیک عادی، دوباره اندازه بگیرید.

۶. مثال توضیحی، نه اندازه‌گیری واقعی

فرض کنید VPS ۴ گیگابایتی (ساختگی) با PHP و MySQL دارید که buffer pool ۱۲۸ مگابایتی و داده InnoDB حدود ۶۰۰ مگابایت دارد. نرخ miss در اوج بالاست. بعد از بالابردن pool به ۱ گیگابایت با نگه‌داشتن حافظه برای PHP-FPM، نرخ miss کم می‌شود و بررسی حافظه مصرف swap نشان نمی‌دهد. این اعداد فقط توضیح مثال‌اند.

۷. اشتباه‌های رایج

  • مصرف بیش‌ازحد RAM روی سرور مشترک.
  • تغییر بدون اندازه‌گیری.
  • قضاوت درست بعد از ری‌استارت.
  • افزودن RAM به‌جای اصلاح کوئری‌های کند.
  • فراموشی ماندگارکردن تغییر آنلاین.
  • نادیده‌گرفتن swap بعد از تغییر.

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

اندازهٔ خوب buffer pool چقدر است؟ به‌اندازهٔ داده داغ شما ولی آنقدر کوچک که برای بقیهٔ سرور حافظه بماند.

بدون ری‌استارت تغییر اندازه می‌شود؟ در نسخه‌های جدید اغلب بله، ولی فایل پیکربندی را هم به‌روز کنید.

نرخ miss تنها معیار است؟ نه، کوئری‌های کند و حافظه را هم ببینید.

برای سایت کوچک مهم است؟ کمتر، ولی پیش‌فرض برای پایگاه داده در حال رشد ممکن است کوچک باشد.

۹. چک‌لیست سریع

  1. اندازهٔ فعلی را بخوانید.
  2. نرخ miss را بسنجید.
  3. RAM و حجم داده را ببینید.
  4. برای PHP و سیستم‌عامل جا بگذارید.
  5. در زمان آرام تغییر دهید.
  6. در پیکربندی ماندگار کنید.
  7. بعد از گرم‌شدن دوباره اندازه بگیرید.

اگر برای بررسی عملکرد یا نگهداری سرور به همکاری نیاز دارید، محدودهٔ خدمات مدیریت سرور را ببینید.

راهنماهای بیشتر سرور و وردپرس در آرشیو مقالات جمع شده‌اند.

منابع رسمی

MySQL: InnoDB Buffer Pool · MySQL: Configuring Buffer Pool Size · MySQL: Server Status Variables · MariaDB: InnoDB Buffer Pool