fbpx

InnoDB: Cannot allocate memory for the buffer pool [Solved]

InnoDB: Cannot allocate memory for the buffer pool [Solved]

Recently we received a ticket where the client pointed out that his MySQL server was not starting. Upon investigation, we found out that the client used our MySQL Optimization tool to optimize and enhance MySQL performance. Our MySQL optimization tool set value of innodb-buffer-pool-size size depending upon the available ram on the server. The current formula set its value to 35% of the server ram. Which is fine for most cases, but in this case it was not good, because there were some other processes in the client server taking more ram, so there was not enough ram available to allocate for innodb-buffer-pool-size thus MySQL reports  InnoDB: Cannot allocate memory for the buffer pool as error in MySQL log file.

 

Depending upon the MySQL version you might also receive innodb fatal error cannot allocate memory for the buffer pool as an error, which is the same error with a different description.

 

If doing this is too much for you, you can sign up with us and let our experts do this for you. We offer 3 days free trial no credit card required start free with our managed vps now. We also help our customers with MySQL optimizations.


What is InnoDB Buffer Pool (innodb_buffer_pool_size) and why it is important?

 

InnoDB Buffer is the space in memory used by MySQL to hold many of its InnoDB data structures. Such as caches, buffers, indexes, and even row data. And then innodb_buffer_pool_size is the MySQL directive that controls its value. This is one of the most significant directive in MySQL perspective and should be set with care if you want to improve your MySQL performance.

 

In this tutorial we will see how we can calculate and set optimal value for innodb_buffer_pool_size depending on the available memory on your system and then we will discuss on how to solve InnoDB: Cannot allocate memory for the buffer pool error in case you run into it at a later point in time.


70% – 80% of the Available Ram

 

Normally if your server is only dedicated for MySQL it is recommended to set innodb_buffer_pool_size value to 70-80% of the available ram. So for example, if your server has 8GB of ram, you can calculate the value of innodb_buffer_pool_size using this formula

 

8 * 0.80 = 6.4GB

 

But in case if your server has a very large amount of ram such as 256GB, then you can further enhance it to 90% as well. Because if your server is only being used for MySQL, the rest of the ram will go in waste, so you can increase or decrease this value depending upon the available ram or your needs.

 

If doing this is too much for you, you can sign up with us and let our experts do this for you. We offer 3 days free trial no credit card required start free with our managed vps now. We also help our customers with MySQL optimizations.


Optimal value with CyberPanel

 

When you are using CyberPanel then 70-80% ram cannot be allocated for innodb_buffer_pool_size size, because there are many other things running and there must be some room for them to breath. Otherwise, if you set a large value for innodb_buffer_pool_size you will start receiving InnoDB: Cannot allocate memory for the buffer pool or innodb fatal error cannot allocate memory for the buffer pool as an error. Which means there is not enough ram available and MySQL cannot start now. Which is why our optimization tool set it to 35% of the available ram.

 

But sometimes 35% is not good as well. For example, you have lots of websites and they are continuously forking PHP processes and you are also using FTP and DNS server. Then you either need to further go down with the value of innodb_buffer_pool_size.


Fixing the InnoDB: Cannot allocate memory for the buffer pool error

 

Now let see how we can fix this error, let say you have used or MySQL Optimization tool, and now MySQL is not starting. First, make sure that this is the reason your MySQL is not starting. You can open the MySQL log file located at /var/lib/mysql/mysqld.log. This location is set by our optimization tool, if you are not using our tool, you can find MySQL log file depending upon your configuration, and you must see the following lines somewhere in the log file

 

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

 

Pay close attention to the bold lines, we are now sure that MySQL failed at InnoDB: Initializing buffer pool. Now open /etc/my.cnf and find innodb-buffer-pool-size = xxxM. Now set the value of this directive to something lower such as 50MB just for test and restart MySQL using systemctl restart mysql. However, you can play with various values and make sure you get the optimal value for your configuration.

 

If doing this is too much for you, you can sign up with us and let our experts do this for you. We offer 3 days free trial no credit card required start free with our managed vps now. We also help our customers with MySQL optimizations.

 

Tags: , , , , , , ,

Leave a Reply

Your email address will not be published.