AWSのMySQLがよく落ちるのを解決 「Failed to start MariaDB」「InnoDB: Cannot allocate memory for the buffer pool」

朝4時45分に警報。このサイトを格納しているAWSのMariaDBが落ちたらしい。サイトに接続すると、WordPressの無愛想な「データベース接続エラー」が表示されている。

まずはMariaDBの状態を確認してみる。

[dacelo@space ~]$ sudo service mysqld status
Redirecting to /bin/systemctl status mysqld.service
mariadb.service – MariaDB 10.1.33 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: failed (Result: exit-code) since 土 2018-06-23 04:17:45 JST; 6h ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 4109 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 11314 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
Process: 11227 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 11225 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 11314 (code=exited, status=1/FAILURE)
Status: “MariaDB server is down”6月 23 04:17:44 fuji systemd[1]: mariadb.service holdoff time over, scheduling restart.
6月 23 04:17:44 fuji systemd[1]: Starting MariaDB 10.1.33 database server…
6月 23 04:17:45 fuji mysqld[11314]: 2018-06-23 4:17:45 140417893861632 [Note] /usr/sbin/mysqld (mysqld 10.1.33-Ma…14 …
6月 23 04:17:45 fuji systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
6月 23 04:17:45 fuji systemd[1]: Failed to start MariaDB 10.1.33 database server.
6月 23 04:17:45 fuji systemd[1]: Unit mariadb.service entered failed state.
6月 23 04:17:45 fuji systemd[1]: mariadb.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

落ちて、リスタートに失敗したようだ。原因はなんだろう。

MySQLのログを見てみる

[dacelo@space ~]$ sudo cat /var/log/mysql/mysqld.log
2018-06-23 4:17:45 140417893861632 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2018-06-23 4:17:45 140417893861632 [Note] InnoDB: The InnoDB memory heap is disabled
2018-06-23 4:17:45 140417893861632 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-06-23 4:17:45 140417893861632 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2018-06-23 4:17:45 140417893861632 [Note] InnoDB: Compressed tables use zlib 1.2.7
2018-06-23 4:17:45 140417893861632 [Note] InnoDB: Using Linux native AIO
2018-06-23 4:17:45 140417893861632 [Note] InnoDB: Using SSE crc32 instructions
2018-06-23 4:17:45 140417893861632 [Note] InnoDB: Initializing buffer pool, size = 384.0M
InnoDB: mmap(421724160 bytes) failed; errno 12
2018-06-23 4:17:45 140417893861632 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2018-06-23 4:17:45 140417893861632 [ERROR] Plugin 'InnoDB' init function returned error.
2018-06-23 4:17:45 140417893861632 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2018-06-23 4:17:45 140417893861632 [Note] Plugin 'FEEDBACK' is disabled.
2018-06-23 4:17:45 140417893861632 [ERROR] Unknown/unsupported storage engine: InnoDB
2018-06-23 4:17:45 140417893861632 [ERROR] Aborting

最初に気になるのは
「The InnoDB memory heap is disabled」
というメッセージ。
でもこれはInnoDBが実装しているメモリーマネージャではなく、OSのメモリ割り当てを使うようになっていることをNoteとしてコメントしているものなので、特に気にする必要はない。
その後[ERROR]として真っ先に出てくるのは「InnoDB: Cannot allocate memory for the buffer pool」。ああ、メモリ不足ですな。

メモリの状態を確認

[dacelo@space ~]$ free
total used free shared buff/cache available
Mem: 1013892 110336 681004 41864 222552 703460
Swap: 0 0 0
[dacelo@space ~]$ curl http://169.254.169.254/latest/meta-data/instance-type
t2.micro

t2.microだとメモリーはもともと1GBしかないので、Swapがないと厳しいようだ。

Swapの設定

[dacelo@space ~]$ df -h
ファイルシス サイズ 使用 残り 使用% マウント位置
/dev/xvda1 30G 4.1G 26G 14% /
devtmpfs 473M 0 473M 0% /dev
tmpfs 496M 0 496M 0% /dev/shm
tmpfs 496M 38M 458M 8% /run
tmpfs 496M 0 496M 0% /sys/fs/cgroup
tmpfs 100M 0 100M 0% /run/user/1003

とりあえずメモリと同量のSwapを用意しよう。

[dacelo@space ~]$ sudo dd if=/dev/zero of=/swapfile bs=1M count=1024
1024+0 レコード入力
1024+0 レコード出力
1073741824 バイト (1.1 GB) コピーされました、 12.0896 秒、 88.8 MB/秒
[dacelo@space ~]$ sudo mkswap /swapfile
スワップ空間バージョン1を設定します、サイズ = 1048572 KiB
ラベルはありません, UUID=81d454d7-31db-4e93-bdc6-7e82ac2d0551
[dacelo@space ~]$ sudo swapon /swapfile
swapon: /swapfile: 安全でない権限 0644 を持ちます。 0600 がお勧めです。

あ、そうですか。

広告

[dacelo@space ~]$ sudo chmod 600 /swapfile
[dacelo@space ~]$ free -m
total used free shared buff/cache available
Mem: 990 108 76 40 804 678
Swap: 1023 0 1023
[dacelo@space ~]$ free
total used free shared buff/cache available
Mem: 1013892 111412 78436 41868 824044 694376
Swap: 1048572 0 1048572

Swap設定完了。これでもうそうそう落ちることはないだろう。

[dacelo@space ~]$ sudo service mysqld start
Redirecting to /bin/systemctl start mysqld.service
[dacelo@space ~]$ sudo systemctl status mysqld.service
● mariadb.service - MariaDB 10.1.33 database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since 土 2018-06-23 11:05:54 JST; 11s ago
Docs: man:mysqld(8)
https://mariadb.com/kb/en/library/systemd/
Process: 17574 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 17458 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 17456 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 17545 (mysqld)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─17545 /usr/sbin/mysqld6月 23 11:05:52 fuji systemd[1]: Starting MariaDB 10.1.33 database server...
6月 23 11:05:53 fuji mysqld[17545]: 2018-06-23 11:05:53 140671148730624 [Note] /usr/sbin/mysqld (mysqld 10.1.33-Ma...45 ...
6月 23 11:05:54 fuji systemd[1]: Started MariaDB 10.1.33 database server.
Hint: Some lines were ellipsized, use -l to show in full.

立ち上げて完了。以上。

About: dacelo


1 thought on “AWSのMySQLがよく落ちるのを解決 「Failed to start MariaDB」「InnoDB: Cannot allocate memory for the buffer pool」”

  1. Wonderful goods from you, man. I have understand your stuff previous to and you are just too magnificent.
    I really like what you have acquired here, really like what you are saying and the way in which you say it.

    You make it enjoyable and you still take care of to keep it wise.

    I can’t wait to read far more from you. This is
    actually a tremendous website.

Leave a Reply

Your email address will not be published. Required fields are marked *