July 29, 2014 by Peter Zaitsev
MySQLのダウンタイム発生の、よくある原因の一つは、コネクション数が不足することだ。こんなエラーを見たことはないだろうか？
ERROR 1040 (00000): Too many connections
MySQLをある程度長く触っている人なら、間違いなく見たことがあるだろう。成功したトランザクションと失敗したトランザクションが混じって一時的に見えるエラーが発生したり、しっかり監視していない場合に限って、いくつかのプロセスが正常に実行されず色々なおかしな現象を引き起こしたり、なかなかに厄介なエラーだ。
コネクション数が不足する原因はいくつかあるが、Webアプリケーションサーバ構成において最もよくある原因と言えば、設定ミスやコネクションのクローズ漏れ、エラーにより多すぎるコネクションを張ってしまったなどの原因で、意図せずに大量のコネクションが張られてしまうことだ。
単純に、max_connectionsを非常に大きな数値に設定して、MySQLが「絶対に」コネクション数不足にならないようにするという手段を採る人もいる。しかしこれは、リソース使用の問題を引き起こしてしまう。設定したコネクション数に本当に達してしまった場合、たくさんのメモリを使ってしまうので、MySQLのサーバがスワップしてしまったり、OOM killerにプロセスを殺されてしまったり、リソースの競合によりパフォーマンスが非常に悪化したりすることになる。
ここで、ベターな方法を提案しよう。スクリプトやアカウントごとにユーザアカウントを使い分け、それぞれにリソース制限をかけるのだ。具体的には、max_user_connectionsを設定すればいい。
mysql> GRANT USAGE ON *.* TO 'batchjob1'@'localhost'
-> WITH MAX_USER_CONNECTIONS 10;
この方法(MySQL 5.0以降で使用できる)は、複数の利点がある。
- セキュリティ 本当に必要な権限だけをそれぞれのユーザアカウントに与えることで、開発時のエラーや侵入者に対してシステムを安全に保てる。
- コネクション不足防止 アプリケーションやスクリプトにバグや設定ミスがあった場合、コネクション不足は起きてしまうが、影響を受けるのはシステムの一部だけになり、他のアプリケーション全てはデータベースを通常通り使うことができる。
- 過負荷防止 コネクション数により、どのくらいのクエリを並列実行できるかを制限できる。並列数を上げ過ぎると、別のダウンタイムを発生させる原因になるので、これを制限することにより、アプリケーションによって意図しない重いクエリが同時に実行されるのを防ぐこともできる。
それぞれのアカウントに対してmax_user_connectionsを設定するのに加えて、my.cnf内でグローバルに「max_user_connections=20」を設定しておこう。これは大ざっぱな私の意見なので、あなたのアプリケーションやスクリプトに応じて設定する必要があるだろう。max_user_connectionsが最も役に立つのは、同じような役割の多くのユーザでシステムを共有している、マルチテナントの環境だろう。
