Amazon EC2, mysql aborting start because InnoDB: mmap (x bytes) failed; errno 12

Question

I have set up a micro instance server on EC2 based on what I read here

mysql server fails frequently and for the third time mysql server is gone. The logs only shows

120423 09:13:38 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
120423 09:14:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
120423  9:14:27 [Note] Plugin 'FEDERATED' is disabled.
120423  9:14:27 InnoDB: The InnoDB memory heap is disabled
120423  9:14:27 InnoDB: Mutexes and rw_locks use GCC atomic builtins
120423  9:14:27 InnoDB: Compressed tables use zlib 1.2.3
120423  9:14:27 InnoDB: Using Linux native AIO
120423  9:14:27 InnoDB: Initializing buffer pool, size = 512.0M
InnoDB: mmap(549453824 bytes) failed; errno 12
120423  9:14:27 InnoDB: Completed initialization of buffer pool
120423  9:14:27 InnoDB: Fatal error: cannot allocate memory for the buffer pool
120423  9:14:27 [ERROR] Plugin 'InnoDB' init function returned error.
120423  9:14:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
120423  9:14:27 [ERROR] Unknown/unsupported storage engine: InnoDB
120423  9:14:27 [ERROR] Aborting

What is really failed; errno 12? And how could I give more space/memory or whatever needed to make this fixed.

I fix this each time by rebooting the whole system and deleting all logs and restart the mysql server. But I know something is wrong with my configuration.

Also my `my.cnf' is like below :

[mysqld]
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
# max_allowed_packet=500M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


innodb_buffer_pool_size         = 512M


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I have the same problem on my EC2 micro instance. Have tried setting innodb_buffer_pool_size=128M and will see how it goes. — swxxii, Jun 24 '12 at 03:42
You may need to add swap space if you are using a micro instance : http://www.prowebdev.us/2012/05/amazon-ec2-linux-micro-swap-space.html — pmoubed, Jun 26 '12 at 17:32
On EC2 micro Instances there is NO swap space by default and it is needed to be set up manually. Otherwise you may see lots of MySQL crashes due to lack of memory. — pmoubed, Jul 18 '12 at 17:38

score 163 · Accepted Answer · edited Jul 06 '20 at 16:28

163

I met the same problem when I tried to run a wordpress on my micro instance without RDS.

Adding a Swap page solved the problem for me.

You can follow steps below to setup the swap space.

If it still doesn't work for you, consider using the RDS service.

===============================================

I copied the content of the blog for the record. Credit goes to the blog author pmoubed:

Amazon EC2 Micro Instance Swap Space - Linux

I have a Amazon EC2 Linux Micro instance. Since Micro instances have only 613MB of memory, MySQL crashed every now and then. After a long search about MySQL, Micro Instance and Memory Managment I found out there is no default SWAP space for Micro instance. So if you want to avoid the crash you may need to setup a swap space for your micro instance. Actually performance wise is better to enable swap.

Steps below show how to make a swap space for your Micro instance. I assume you have AWS Account with a Micro instance running.

Run dd if=/dev/zero of=/swapfile bs=1M count=1024
Run mkswap /swapfile
Run swapon /swapfile
Add this line /swapfile swap swap defaults 0 0 to /etc/fstab

Step 4 is needed if you would like to automatically enable swap file after each reboot.

Some useful command related to SWAP space:

$ swapon -s   
$ free -k

$ swapoff -a
$ swapon  -a

References:

edited Jul 06 '20 at 16:28

pmoubed

3,570
10
33
54

answered Dec 22 '12 at 03:26

Bohr

1,862
1
13
8

8

FYI, this worked for me on a Digital Ocean droplet (512 MB). Not that this should surprise anyone, but in case anyone who is unsure, it will probably work on any server with the same issues. – jfacemyer Jun 09 '13 at 02:49
Thank you for this life saver! Was also running a micro instance with Ubuntu Server. – ECC-Dan Jul 01 '13 at 17:59
4

For Digital Ocean users, I followed this tutorial and it worked like a charm: https://www.digitalocean.com/community/articles/how-to-add-swap-on-ubuntu-12-04 – Chris Ray May 14 '14 at 12:56
Thanks a lot. Have been pulling my hair out for the past 24 hours, played with all kinds of buffer/cache/query sizes .. You are a life saver ! – pranshus Jul 25 '14 at 08:52
Another good source of info here: https://support.rackspace.com/how-to/create-a-linux-swap-file/ – Hal50000 Feb 07 '16 at 06:24
Check this answer http://stackoverflow.com/a/28645507/4458531 "If you are on t2 instances (t2.micro, t2.medium, t2.small), there is no ephemeral or instance storage available to you. So you need to just create your swap in EBS which depending on your use case may or maynot be a good idea. Otherwise create your swap on the ephemeral storage to avoid paying EBS costs." – NineCattoRules Feb 07 '16 at 14:06
@ChrisRay nice share, it's a great/detailed resource for AWS users as well o/ – keepthepeach Mar 15 '16 at 06:28
More recent Digital Ocean tutorial for Ubuntu 16.04: https://www.digitalocean.com/community/tutorials/how-to-add-swap-space-on-ubuntu-16-04 – SuperShoot Jul 20 '17 at 22:27
Thank you so much! – Thomas Aug 10 '17 at 08:25
just copy `echo "/swapfile swap swap defaults 0 0" >> /etc/fstab` in case you are too lazy to type – ospider Oct 27 '18 at 12:03
I had to `chmod 0600 /swapfile` after mkswap, and then I had to mkswap again, because the first time didn't work for some reason. – naught101 Nov 25 '18 at 22:49
Just gave a shot on one of my lightsail instance though, this scared me a little while reading that it is not recommended to use it on instances using ssd. Does anyone as a success story with a lightsail instane on setting swap? I am refering to the tutorial that @ChrisRay posted. – Mark Odey Jan 24 '19 at 18:56

score 24 · Answer 2 · answered Aug 22 '12 at 16:14

24

I had this problem too on an Amazon EC2 micro instance. I tried decreasing inno_db's memory usage by adding the following to /etc/my.cnf

innodb_buffer_pool_size = 64M

That didn't work, I tried dropping it down to 16M and it still didnt work. Then I realized that the instance had basically zero free memory. So I tried restarting apache

sudo system httpd restart
sudo system mysqld restart

And everything worked fine. Maybe another solution is to configure apache to not eat up so much memory somehow.

answered Aug 22 '12 at 16:14

wfbarksdale

6,962
11
60
87

2

MySQL may still crashes so you may need to add swap space to your micro instance. – pmoubed Aug 22 '12 at 21:12
Thanks, that makes sense. I think I may also try to limit the number of threads apache can spawn. – wfbarksdale Aug 23 '12 at 16:02
Works great. I have this issue too and by restarting httpd solved the issue. – Lionel Chan Sep 18 '12 at 04:05
1

Awesome catch, same boat here. I set my apache to use less ram and also created a 512m swap file, but set the vm.swappiness to 10 so that it would only be used in a pinch. – newz2000 Oct 05 '12 at 02:26
Restarting nginx and php-fpm also released enough memory to allow mysql to start! Thanks! – msEmmaMays May 06 '13 at 17:15
Tried decreasing innodb buffer pool size. That worked but I forgot to restart httpd service. thats why i was getting the error. Great suggestion. – Manish Mudgal Jan 09 '14 at 06:50

score 5 · Answer 3 · edited May 23 '17 at 12:25

It looks like you are requesting 128M of memory for the innodb_buffer_pool_size in the my.cfg file you show in the post, but MySQL thinks you are asking for 512M of memory:

Initializing buffer pool, size = 512.0M

A few lines down, the error message tells you MySQL will not start because it cannot reserve enough (512M) memory for the InnoDB buffer pool:

Fatal error: cannot allocate memory for the buffer pool

That begs three questions:

How much memory is on your instance? Should there be enough memory to accommodate the 512M InnoDB is trying to grab for the buffer pool, plus everything else MySQL allocates, plus your application(s), plus the operating system?
Why is InnoDB trying to take more than you think it should?
Why is MySQL restarting anyhow?

You can answer 1.

As to 2., there are a few different places MySQL option files can be located. Subsequently found files override options specified in previously found files. See

http://dev.mysql.com/doc/refman/5.5/en/option-files.html

Issue 3. could be due to an out of memory condition that occurs sometime after startup. You should see an indication of that further back in the logs if that is the case.

Finally, but somewhat unrelated, are you using EBS backed instances? That's generally highly recommended for database servers (actually, for any instance barring special circumstances). For more on that see

https://stackoverflow.com/a/3630707/141172

score 2 · Answer 4 · answered Nov 30 '12 at 01:09

For me, this exactly problem was rectified by adding a swap volume to my EC2 instance. My services were simply consuming all the memory on the box, and would crash. Not something I was used to, being a RedHat/CentOS admin for years - Anaconda does a LOT of work that the free Ubuntu EC2 instance does not.

I simply created a 2Gb volume through the web console, attached it to my instance, and did "mkswap /dev/[whatever]", edited /etc/fstab, and the crashing stopped.

These instances do NOT install like a media-based OS install that most of us are used to - it's stripped bare with no packages, no proper filesystem, and things like AppArmor, which cause all kinds of problems if you aren't aware of it and/or don't know how to configure it.

score 1 · Answer 5 · answered Sep 08 '16 at 08:51

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

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

Also please check your Disk Space. Make sure you have sufficient space.

df-h

score 1 · Answer 6 · answered Jun 18 '19 at 19:36

EASY ANSWER:

* * * * * systemctl is-active --quiet mysqld || systemctl restart mysqld

DETAILED ANSWER:

This is an important question especially for people who are using a very small VPS, say 1GB of RAM or less. If MySQL is dropping out, it may be a problem with your server configuration (Apache | nginx) or MySQL configuration. DOS attacks may cause an increased spike in system resource usages (see image). The end result is that MySQL process gets shutdown by the Kernel. For a long-term solution should look at optimizing your Apache or MySQL configurations.

There are several other discussions Stack Overflow those topics as well as the MySQL manual and Percona Blog:

MySQL Manual - How MySQL Uses Memory:

https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

Percona - Best Practices for Configuring Optimal MySQL Memory Usage:

https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/

How to Optimize MySQL Performance Using MySQLTuner:

https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/

Apache Memory Usage Configuration:

https://serverfault.com/questions/254436/apache-memory-usage-optimization

Apache Manual on Performance Tuning:

https://httpd.apache.org/docs/2.4/misc/perf-tuning.html

Tuning Apache Server:

https://www.linode.com/docs/web-servers/apache-tips-and-tricks/tuning-your-apache-server/

However, with respect to your original question, yes, you can script a temporary solution that checks if MySQL service is loaded and active and will restart MySQL if it is not loaded and active.

You did not mention what operating system you are using. That would help to give you a specific command. I will give you an example for CentOS linux.
Look at the following output of the command systemctl status mysql. You can see at the top that the service is loaded and active.

[root@centos-mysql-demo ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2019-06-18 18:28:18 UTC; 924ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3350 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3273 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3353 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3353 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jun 18 18:28:11 centos-mysql-demo systemd[1]: Starting MySQL Server...
Jun 18 18:28:18 centos-mysql-demo systemd[1]: Started MySQL Server.

If the service is not loaded, then a command such as:

systemctl status mysqld || systemctl restart mysqld

will do the trick of re-starting the process. You could cron that:

* * * * * systemctl status mysqld || systemctl restart mysqld

However, in the case that mysql is loaded, but the service is not active, your cron will do nothing. So, you should use a more detailed command such as:

* * * * * systemctl is-active --quiet mysqld || systemctl restart mysqld

In this case, if the service is loaded but inactive such as the state that a DOS attack can leave your mysql service, the command will also restart mysql. Using the --quiet flag just specifies the command only to return a status code, not output anything to the screen. If you ommit the --quiet flag you will see a status output of either active or inactive.

You may also create some swap space to add more available RAM resources to your server such as:

sudo dd if=/dev/zero of=/swapfile count=2096 bs=1MiB
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
swapon --show
swapon --summary
free -h

score 0 · Answer 7 · edited Oct 19 '15 at 05:42

0

Use anyone of the following solution:

Increase the physical RAM. Adding 1GB of additional RAM will solve the problem.
Allocate SWAP space by using the config changes below:

config

dd if=/dev/zero of=/extraswap bs=1024 count=512M
mkswap  /extraswap 
swapon  /extraswap 
## Edit the /etc/fstab, and the following entry.
/extraswap      none    swap    sw      0       0

edited Oct 19 '15 at 05:42

amdixon

3,703
8
23
33

answered Oct 19 '15 at 05:18

cloud_geek

325
2
12

Amazon EC2, mysql aborting start because InnoDB: mmap (x bytes) failed; errno 12

7 Answers7

Linked

Related