[SOLVED] 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)

Solution:

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.

Tags: 

Comments

Thank you.
I did have this problem exactly on DigitalOcean Droplet and solved it with the help of this article.

By Alex (not verified)

# dd if=/dev/zero of=/swap.dat bs=1024 count=512M

should be 512k, thanks

By scott lu (not verified)

Great article - saved me loads of time.

Cheers dude!

By TomVoxsen (not verified)

Wow, 512g swap!!!! Must be a crazy WordPress install.

By sudopeople (not verified)

I think this is greate and easy article but i am done something wrong
dd if=/dev/zero of=/swap.dat bs=1024 count=512M , i just used this comment it take long time so i cancel the running process and it shows me :
^C10054281+0 records in
10054281+0 records out
10295583744 bytes (10 GB) copied, 154.276 s, 66.7 MB/s
where can i find the copied files and how can i remove that, thanks.

By dev (not verified)

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.