حل خطای InnoDB: Cannot allocate memory for the buffer pool

[Habili]

بعضی از کاربران با مشکل stop شدن سرور MySQL/MariaDB مواجه میشوند. بعد از بررسی متوجه شدیم که این کاربران از ابزار MySQL Optimization برای بهینه سازی و افزایش کارایی MySQL/MariaDB استفاده کرده اند. این ابزار مقدار innodb-buffer-pool-size را بر اساس مموری در دسترس (آزاد) روی سرور تعریف میکند. بر اساس فرمول موجود مقدار آنرا برابر با 35٪ مموری سرور تعریف میکند. که در اکثر موارد خوب است اما در این کیس خاص این مقدار مناسب نیست زیرا یکسری پراسس های دیگری در محیط کلاینت سرور هستند که ram بیشتری مصرف میکنند. در نتیجه مموری کافی برای اختصاص به innodb-buffer-pool-size وجود ندارد و از این رو مای اسکیول Error InnoDB: Cannot allocate memory for the buffer pool را در لاگ فایل MySQL گزارش میکند.

بسته به ورژن MySQL/MariaDB شما همچنین ممکن است خطای innodb fatal error cannot allocate memory for the buffer pool را دریافت کنید که با Error بالا یکسان است.

و اما InnoDB Buffer Pool innodb_buffer_pool_size چیست و چرا مهم است؟

بافر InnoDB در حقیقت فضایی در مموری است که توسط مای اسکیول برای نگهداری تعداد زیادی از ساختارهای اطلاعات innodb خود استفاده میکند. مانند caches ،buffers ،indexes و حتی row data. و اما innodb_buffer_pool_size بخشی از MySQL است که مقدار پارامتر بالا را تنظیم و کنترل میکند. این بخش در حقیقت یکی از مهمترین و بارزترین بخش های MySQL است و در صورتیکه بخواهید کارایی سرور مای اسکیول خود را بهبود دهید باید آنرا با احتیاط تعریف کنید.

در این مقاله، ما به شما نحوه محاسبات و تعریف مقدار بهینه برای innodb_buffer_pool_size بر اساس حافظه آزاد روی سیستم را آموزش خواهیم داد و درباره نحوه رفع خطای InnoDB: Cannot allocate memory for the buffer pool بحث خواهیم کرد.



70٪ تا 80٪ مموری آزاد

به صورت نرمال اگر سرورتان فقط برای MySQL اختصاص داده شده به شما توصیه میشود که مقدار innodb_buffer_pool_size را برابر با 70 تا 80 درصد رم آزاد تعریف کنید. در نتیجه برای مثال اگر سرورتان 8 گیگ مموری داشته باشد شما میتوانید مقدار innodb_buffer_pool_size را با استفاده از فرمول زیر محاسبه کنید.

8 * 0.80 = 6.4GB

اما در مواردی که سرور شما RAM بسیار بالایی (حدود 256GB) داشته باشد شما میتوانید درصد بالا را تا 90٪ افزایش دهید. زیرا اگر سرور شما تنها برای MySQL مورد استفاده قرار گیرد مابقی مموری هدر خواهد رفت در نتیجه شما میتوانید این مقدار را بر اساس رم آزاد و نیاز خود افزایش یا کاهش دهید.



رفع خطای InnoDB: Cannot allocate memory for the buffer pool

و اما چگونه خطای بالا را رفع کنیم. بگذاریم به این صورت بگوییم که شما از ابزار MySQL Optimization استفاده کرده اید و اکنون مای اسکیول شما start نمیشود. ابتدا مطمئن شوید که علت اجرا نشدن MySQL تان همین دلیل باشد. شما میتوانید لاگ فایل MySQL را که در مسیر /var/lib/mysql/mysqld.log قرار دارد باز کنید که توسط ابزار MySQL Optimization ایجاد شده است. در صورتیکه از این ابزار استفاده نمیکنید شما میتوانید log فایل MySQL را بسته به نوع تنظیمات خود در مسیر /var/log پیدا کنید. شما باید خط هایی شبیه به زیر را مشاهده کنید.

2019-10-22 17:30:15 987654321987654 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2019-10-22 17:30:15 987654321987654 [Note] InnoDB: The InnoDB memory heap is disabled
2019-10-22 17:30:15 987654321987654 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-10-22 17:30:15 987654321987654 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-10-22 17:30:15 987654321987654 [Note] InnoDB: Compressed tables use zlib 1.2.7
2019-10-22 17:30:15 987654321987654 [Note] InnoDB: Using Linux native AIO
2019-10-22 17:30:15 987654321987654 [Note] InnoDB: Using SSE crc32 instructions
[B]2019-10-22 17:30:15 987654321987654 [Note] InnoDB: Initializing buffer pool, size = 358.0M
InnoDB: mmap(393183232 bytes) failed; errno 12[/B]
2019-10-22 17:30:15 987654321987654 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2019-10-22 17:30:15 987654321987654 [ERROR] Plugin ‘InnoDB’ init function returned error.
2019-10-22 17:30:15 987654321987654 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

به خطوطی که در Error بالا به صورت برجسته نشان کرده ایم توجه کنید، اکنون بعد از بررسی log مطمئن شدیم که مشکل InnoDB: Initializing buffer pool را داریم. اکنون فایل /etc/my.cnf را باز کنید و پارامتر innodb-buffer-pool-size = xxxM را پیدا کنید و مقدار این بخش را به مقداری کمتر مانند 50MB (تنها برای تست و بررسی) تغییر دهید و سرویس MySQL را با استفاده از دستور systemctl restart mysql ریست کنید. به هر حال شما میتوانید با مقادیر مختلفی بازی کنید و مطمئن شوید که مقدار بهینه برای تنظیمات خود را پیدا کنید.

# systemctl restart mysql

لطفا توجه داشته باشید که مقدار 256MB در حقیقت یک مقدار پایه است. اگر شما مموری بیشتری در سرور دارید، به طور مثال 8GB دارید شما میتوانید 1GB یا مقدار بیشتری را به این پارامتر اختصاص دهید. شما میتوانید ایم مقدار را در MySQL/MariaDB با استفاده از دستور زیر نیز چک کنید :

MariaDB [(none)]> SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'innodb_buffer_pool_size';
+----------------+
| variable_value |
+----------------+
| 268435456 |
+----------------+
1 row in set (0.00 sec)

برای محاسبه مقدار میتوانید از فرمول زیر نیز استفاده کنید :

268435456/1024/1024 = 256M

همچنین توصیه میشود مطالعه کنید :

آموزش نصب MariaDB 10 در لینوکس (Debian - Ubuntu - RedHat - CentOS)

آموزش نصب MySQL 8 در اوبونتو

آموزش دستورات MySQL (بخش اول)

دستورات Backup / Restore و Import / Export دیتابیس MySQL/MariaDB

ترفند های بهینه سازی و افزایش سرعت دیتابیس MySQL/MariaDB


موفق باشید