MySQL Crash with Fatal error: cannot allocate memory for the buffer pool
We have a dozen websites mostly running WordPress, and one Drupal website on Digital Ocean with a 1GB instance. The server was running flawlessly for 6 months, and then the MySQL starts to crash every few days unexpectly with the following error in the mysql error log.
140416 11:37:24 mysqld_safe Number of processes running now: 0 140416 11:37:24 mysqld_safe mysqld restarted 140416 11:37:24 [Note] Plugin 'FEDERATED' is disabled. 140416 11:37:24 InnoDB: The InnoDB memory heap is disabled 140416 11:37:24 InnoDB: Mutexes and rw_locks use GCC atomic builtins 140416 11:37:24 InnoDB: Compressed tables use zlib 1.2.3 140416 11:37:24 InnoDB: Using Linux native AIO 140416 11:37:24 InnoDB: Initializing buffer pool, size = 128.0M 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
Upon trying to restart the MySQL service, we are getting the following error:
# service mysqld start ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)
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=512M # 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. [mysqld] innodb_buffer_pool_size=64M
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.