Crash with Fatal error: cannot allocate memory for the buffer pool
Having a dozen websites mostly running Wordpress, and one Drupal website on VPS server with a 1GB instance. if the MySQL starts to crash every few days unexpectedly with the following error in the mysql error log.
InnoDB: mmap(137363456 bytes) failed; errno 12
140416 11:37:24 InnoDB: Completed initialization of buffer pool
140416 11:37:24 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140416 11:37:24 [ERROR] Plugin 'InnoDB' init function returned error.
140416 11:37:24 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140416 11:37:24 [ERROR] Unknown/unsupported storage engine: InnoDB
140416 11:37:24 [ERROR] Aborting
The problem is that the server does not have enough memory to allocate for MySQL process. There are a few solutions to this problem.
(1) Increase the physical RAM. Adding 1GB of additional RAM will solve the problem.
(2) Allocate SWAP space. Digital Ocean VPS instance is not configured to use swap space by default. By allocating 512MB of swap space, we were able to solve this problem. To add swap space to your server, please follow the following steps:
## As a root user, perform the following:
# dd if=/dev/zero of=/swap.dat bs=1024 count=512K
# mkswap /swap.dat
# swapon /swap.dat
## Edit the /etc/fstab, and the following entry.
/swap.dat none swap sw 0 0
(3) Reduce the size of MySQL buffer pool size
## Edit /etc/my.cnf, and add the following line under the [mysqld] heading.
Restart mysql and you're good to go. You'll have to continuously monitor MySQL process for a week or two to ensure that the server is running without memory allocation problem.
If you want to undo SWAP if incase extra memory is added to the instance :
First, remove that entry from /etc/fstab. Then:
sudo swapoff /swap.dat
# Wait for all resources paged to that swap file to be moved to other swap files or to RAM.
sudo rm -f /swap.dat
If you remove the file before it is no longer in use, the disk space will remain allocated until swapoff is complete.