Amazon EC2，mysql 中止开始，因为 InnoDB: mmap (x 字节)失败; errno 12

小开

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

小开

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.

小开

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.

小开

最佳答案

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:

小开

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

小开

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

小开

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