Couldn't start mysql server. It only worked after reboot.

February 18, 2015 977 views

Hi, here's what happened Yesterday around 9am (London time):

150217 09:04:29 mysqldsafe Starting mysqld daemon with databases from /var/lib/mysql
150217 9:04:29 [Warning] Using unique option prefix key
buffer instead of keybuffersize is deprecated and will be removed in a future release. Please use the full name instead.
150217 9:04:29 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
150217 9:04:29 [Note] Plugin 'FEDERATED' is disabled.
150217 9:04:29 InnoDB: The InnoDB memory heap is disabled
150217 9:04:29 InnoDB: Mutexes and rw_locks use GCC atomic builtins
150217 9:04:29 InnoDB: Compressed tables use zlib 1.2.8
150217 9:04:29 InnoDB: Using Linux native AIO
150217 9:04:29 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
150217 9:04:29 InnoDB: Completed initialization of buffer pool
150217 9:04:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150217 9:04:29 [ERROR] Plugin 'InnoDB' init function returned error.
150217 9:04:29 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150217 9:04:29 [ERROR] Unknown/unsupported storage engine: InnoDB
150217 9:04:29 [ERROR] Aborting

150217 9:04:29 [Note] /usr/sbin/mysqld: Shutdown complete

150217 09:04:29 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Does anyone know why this could happen and what would be the fix for it (other than reboot)?

Thanks,
Lukasz

2 Answers

The key line in that log output is this:

150217 9:04:29 InnoDB: Fatal error: cannot allocate memory for the buffer pool

Your droplet did not have the available RAM for the service. Without sufficient memory available MySQL may fail to start or crash when it exhausts the physical memory. There are a few things you can do to address this issue. Please note that rebooting when this occurs is not a workable solution as frequent database crashes will likely result in your database ending up corrupted and possibly unrecoverable.

1.) You can upgrade to a larger droplet plan in order to provide more memory for the process.

2.) You can enable a swap file on your droplet. This will be used when memory is exhausted and can prevent these crashes. This tutorial will guide you in setting this up.

3.) You can work to optimize your scripts, MySQL configuration and other services to lessen the amount of memory in use.

I would recommend doing all three of these things. Just doing #2 immediately should stop the crashes while you investigate the other two options.

by Justin Ellingwood
Swap space can be used as an "overflow" area for your system when you run out of RAM. The operating system can store data that would normally be kept in RAM on the hard drive in a specially formatted file. In this guide, we'll demonstrate how to create and use one of these files in Ubuntu 14.04.
Have another answer? Share your knowledge.