🐬

典型的な対策はしているのに MySQL server has gone away が頻発し、よくよく調べたらメモリ不足が原因だった件

2023/12/22に公開

MySQL

PHP

tech

MySQL Advent Calendar 2023 の22日目の記事です。

タイトルで全部言ってますが

とあるPHP + MySQLのWebシステムにて、ある日を境に突然 MySQL server has gone away が頻発するようになりました。

このシステムは古き良きVPS上で動いている（同一サーバー上でPHPとMySQLをホストしている）のですが、たまたま最近とある事情でこのVPSにOSを再インストールしてプロビジョニングし直したばかりだったため、何か設定ミスが原因に違いないと思い込んでしまったのですが、ググって見つけられる典型的な対策をやってみても一向に状況が改善せず、よくよく調べたら普通にマシンのメモリ不足が原因だった ことが分かりました。

（初歩的すぎて）ググってもあまり見つけられない情報な気がするので、せっかくなのでアドベントカレンダーに寄稿しておこうと思った次第です。

典型的な対策をしてみた

すでに人類が5000兆回は検索しているであろう mysql server has gone away でググってみたところ、以下の記事が上位に出てきました。ありがとうございます🤲

典型的な原因は以下の3つぐらいがあるとのこと。

サーバーがタイムアウトして接続が切れた
どこかで誤ってcloseを発行している
クエリの最大サイズの設定を超えるクエリが発行されている

1. サーバーがタイムアウトして接続が切れた？

これが原因である場合、wait_timeout の設定を大きくしておくと安心だそうです。

が、デフォルトでも28800（8時間）と十分大きいので、誤ってこれを小さく設定しているとかでない限りは問題にはならなそうです。

が、まあ一応念のために86400に上げてみました。

[mysqld]
wait_timeout = 86400

設定が正しく反映されているかどうかは以下のSQLで確認できます。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.01 sec)

2. どこかで誤ってcloseを発行している？

アプリケーションのコードは一切触っていないのにある日を境に頻発し始めたので、この可能性はなさそうでした。

3. クエリの最大サイズの設定を超えるクエリが発行されている？

これが原因である場合、max_allowed_packet の設定を見直してみるとよいそうです。

デフォルトは1Mらしいので、例えば16Mなどに増やしてみます。

[mysqld]
max_allowed_packet = 16MB

設定が正しく反映されているかどうかは以下のSQLで確認できます。

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

なんの成果も！得られませんでした！

上記の対策をして少し様子を見てみたところ、残念ながらなんの成果も得られませんでした。

で、ここからが本題で、諦めずにさらにググっていたところ、たまたま以下のStackOverflowが目に留まりました。

MySQL server has gone away とメモリ使用量の関係について言及されていて、「あ、その観点完全に抜けてたわ・・」と気づくきっかけになりました。

上記StackOverflowの回答からリンクされている以下の記事に、メモリ容量に応じたMySQLの細かい設定の例があり参考になります。

https://www.drupal.org/docs/7/managing-site-performance/optimizing-mysql

確かに言われてみれば、今回問題になっているWebシステムは、DBのレコード数がまあまあ多く、PHPアプリケーションのメモリ使用量も割と大きくなってしまっているレガシーシステムであり、それにもかかわらずVPSのインスタンスサイズは小さめで、かなりメモリがカツカツの状態で動いているものでした。めっちゃそれが原因っぽいやん・・

Docker環境で再現確認

メモリが足りなくなると MySQL server has gone away になるのか、手元のDocker環境で確認してみました。

幸い、もともとDocker Composeで開発環境を作っていたので、確認作業はとても簡単でした。

以下のような compose.yaml がもともとあったのですが、

services:
  database:
    image: mysql:${MYSQL_VERSION:-8.0.35}
    environment:
      TZ: Asia/Tokyo
      MYSQL_RANDOM_ROOT_PASSWORD: yes
      MYSQL_DATABASE: ${MYSQL_DATABASE:-app}
      MYSQL_USER: ${MYSQL_USER:-app}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD:-password}
    volumes:
      - db-data:/var/lib/mysql:rw
      - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
    ports:
      - ${HOST_IP:-127.0.0.1}:3306:3306

volumes:
  db-data:

これに以下のような4行を書き足して、コンテナに与えるメモリ容量を制限します。

services:
  database:
    image: mysql:${MYSQL_VERSION:-8.0.35}
    environment:
      TZ: Asia/Tokyo
      MYSQL_RANDOM_ROOT_PASSWORD: yes
      MYSQL_DATABASE: ${MYSQL_DATABASE:-app}
      MYSQL_USER: ${MYSQL_USER:-app}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD:-password}
    volumes:
      - db-data:/var/lib/mysql:rw
      - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
    ports:
      - ${HOST_IP:-127.0.0.1}:3306:3306
+     deploy:
+       resources:
+         limits:
+           memory: 100M # 例えば

volumes:
  db-data:

この状態でローカルでPHPアプリケーションを動かしてみたところ、MySQLサーバーのメモリ容量を一定以下にすると MySQL server has gone away が100%再現し、逆に閾値より少し大きい値にすると100%再現しなくなることが確認できました。ビンゴです。

最終的な原因と行った対策

本番環境で何が起こっていたかというと、一定以上の同時接続が発生するなどしてPHPプロセスのメモリ消費量が大きくなってシステムの空きメモリが少なくなっている状態で、大きな結果を返すようなMySQLにクエリが投げられると、MySQLが必要なメモリを確保できず、上記の再現実験と同じ状況になる、ということだったのだと思われます。

というわけで、お客さんにお願いしてVPSのインスタンスサイズを少し大きくしてもらい、システムのメモリ容量を増やしました。

数日が経過しましたが、今のところ MySQL server has gone away のエラー通知は飛んできていません。どうやら一応の解決を見たようです。ホッ。

おわりに

「1つの小さいVPSインスタンスにアプリケーションサーバーとMySQLサーバーを両方乗せているシステムで、アプリケーションもMySQLも割とメモリを食ってしまっている」という境遇の方にだけ役立つかもしれない情報をお送りしました。

いつか「ググっても wait_timeout と max_allowed_packet の情報しか出てこないよーそれはやってるんだけど直らないんだよー」と途方に暮れた誰かがこの記事に辿り着いて問題が解決することがあれば幸甚です。

GitHubで編集を提案