InnoDB : mmap (x 바이트) 실패로 인해 Amazon EC2, mysql 중단 시작

InnoDB : mmap (x 바이트) 실패로 인해 Amazon EC2, mysql 중단 시작 오류 번호 12

---

여기에서 읽은 내용을 기반으로 EC2에 마이크로 인스턴스 서버를 설정했습니다.

mysql 서버가 자주 실패하고 세 번째로 mysql 서버가 사라졌습니다. 로그는

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

정말로 무엇입니까 failed; errno 12? 그리고 어떻게하면 더 많은 공간 / 메모리 또는이 문제를 해결하는 데 필요한 모든 것을 제공 할 수 있습니다.

매번 전체 시스템을 재부팅하고 모든 로그를 삭제하고 mysql 서버를 다시 시작하여이 문제를 해결합니다. 하지만 내 구성에 문제가 있다는 것을 알고 있습니다.

또한 내`my.cnf '는 다음과 같습니다.

[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

---

RDS없이 마이크로 인스턴스에서 워드 프레스를 실행하려고 할 때 동일한 문제가 발생했습니다.

스왑 페이지를 추가하면 문제가 해결되었습니다.

다음을 따라 스왑 페이지를 설정할 수 있습니다.

http://www.prowebdev.us/2012/05/amazon-ec2-linux-micro-swap-space.html

그래도 작동하지 않으면 RDS 서비스 사용을 고려하십시오.

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

블로그 링크가 때때로 실패합니다. 기록을 위해 아래 내용을 복사했습니다. 크레딧은 블로그 작성자 Pedram Moubed 에게 갑니다 .

Amazon EC2 마이크로 인스턴스 스왑 공간-Linux

Amazon EC2 Linux Micro 인스턴스가 있습니다. 마이크로 인스턴스의 메모리는 613MB에 불과하기 때문에 MySQL은 때때로 충돌했습니다. MySQL, 마이크로 인스턴스 및 메모리 관리에 대한 긴 검색 결과 마이크로 인스턴스에 대한 기본 SWAP 공간이 없음을 발견했습니다. 따라서 충돌을 방지하려면 마이크로 인스턴스에 대한 스왑 공간을 설정해야 할 수 있습니다. 실제로 성능면에서 스왑을 활성화하는 것이 좋습니다.

아래 단계는 마이크로 인스턴스의 스왑 공간을 만드는 방법을 보여줍니다. 마이크로 인스턴스가 실행중인 AWS 계정이 있다고 가정합니다.

1. 운영 dd if=/dev/zero of=/swapfile bs=1M count=1024
2. 운영 mkswap /swapfile
3. 운영 swapon /swapfile
4. 이 줄을 추가 /swapfile swap swap defaults 0 0에/etc/fstab

재부팅 할 때마다 스왑 파일을 자동으로 활성화하려면 4 단계가 필요합니다.

SWAP 공간과 관련된 몇 가지 유용한 명령 :

$ swapon -s   
$ free -k

$ swapoff -a
$ swapon  -a

참조 :

1. http://www.thegeekstuff.com/2010/08/how-to-add-swap-space/
2. http://cloudstory.in/2012/02/getting-the-best-out-of-amazon-ec2-micro-instances/
3. http://cloudstory.in/2012/02/adding-swap-space-to-amazon-ec2-linux-micro-instance-to-increase-the-performance/
4. http://aws.amazon.com/ec2/instance-types/

---

Amazon EC2 마이크로 인스턴스에서도이 문제가 발생했습니다. 다음을 추가하여 inno_db의 메모리 사용량을 줄이려고했습니다./etc/my.cnf

innodb_buffer_pool_size = 64M

작동하지 않았고, 16M으로 떨어 뜨리려고했지만 여전히 작동하지 않았습니다. 그런 다음 인스턴스에 기본적으로 여유 메모리가 없음을 깨달았습니다. 그래서 아파치를 다시 시작해 보았습니다.

sudo 시스템 httpd 다시 시작
sudo 시스템 mysqld 재시작

그리고 모든 것이 잘 작동했습니다. 어쩌면 또 다른 해결책은 어떻게 든 너무 많은 메모리를 먹지 않도록 아파치를 구성하는 것입니다.

---

게시물에 표시된 my.cfg 파일의 innodb_buffer_pool_size에 대해 128M 의 메모리를 요청하는 것처럼 보이지만 MySQL은 512M 의 메모리를 요청한다고 생각합니다 .

버퍼 풀 초기화 중, 크기 = 512.0M

몇 줄 아래로 내려 가면 InnoDB 버퍼 풀에 대해 충분한 (512M) 메모리를 예약 할 수 없기 때문에 MySQL이 시작되지 않는다는 오류 메시지가 표시됩니다.

치명적 오류 : 버퍼 풀에 메모리를 할당 할 수 없습니다.

세 가지 질문이 있습니다.

1. 인스턴스에 얼마나 많은 메모리가 있습니까? 512M InnoDB가 버퍼 풀을 확보하려고 시도하는 메모리와 MySQL이 할당하는 다른 모든 것, 애플리케이션 및 운영 체제를 수용하기에 충분한 메모리가 있어야합니까?
2. Why is InnoDB trying to take more than you think it should?
3. 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

---

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.

---

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

---

Use anyone of the following solution:

1. Increase the physical RAM. Adding 1GB of additional RAM will solve the problem.

2. 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

---

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

참고URL : https://stackoverflow.com/questions/10284532/amazon-ec2-mysql-aborting-start-because-innodb-mmap-x-bytes-failed-errno-12