Amazon EC2, mysql aborting start because InnoDB: mmap (x bytes) failed; errno 12
MysqlAmazon Ec2Mysql Problem Overview
I have a micro instance server on EC2 based this
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
Mysql Solutions
Solution 1 - Mysql
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 0to/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:
Solution 2 - Mysql
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.
Solution 3 - Mysql
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
Solution 4 - Mysql
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.
Solution 5 - Mysql
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
Solution 6 - Mysql
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
Solution 7 - Mysql
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