MySQLでToo many connectionsが起きた場合の対処方法
表記の件について。ちなみにほとんどはブログ主の推測です。
目次
キーワード
そもそもToo many connectionsとは？
- 「古い接続を切断しない限り、これ以上新規の接続ができません」とMySQLが言っている
- ここでの「接続」の定義は「認証開始後～quit」と思われる
- Too many connectionsが認証処理の後に出力されたため
- quitは推測
関連パラメータ
- 接続から切断まで、全ての処理が正常に進む前提なら、以下が関係しそう
- どれだけの頻度で新規接続が行われるか
- どれだけの時間接続されているか
- 接続後に行われる処理はどれだけの計算量か
- 処理を行う計算機はどれだけの性能を持っているか
- 処理が正常に終了しない場合は以下の2パターンの処理が起こりそう
- 異常を検知して強制的に切断する
- ずっと接続が残り続ける
- MySQLには上記を考慮した振る舞いを期待したくなる
- そのためのMySQLの設定値は以下の2つ
- max_connections
- wait_timeout
で、結局どうすればいいのよ
以下を疑いましょう。
- すべての処理は正常に行われているが、負荷や接続頻度の上昇によって同時接続数が増えてしまっている？
- 特定のクエリが非常に遅かったりして、ほかの処理を阻害している？
まずは現状把握から。
- 腐っている接続が無いか？
mysql> show processlist; +------+--------+-------------+-------+-------------+--------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+--------+-------------+-------+-------------+--------+-------+------------------+ | 66 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 186 | | NULL | | 67 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 186 | | NULL | | 2450 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 428 | | NULL | | 2451 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 428 | | NULL | | 4725 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 46 | | NULL | | 6194 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6213 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 2 | | NULL | | 6215 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6216 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6217 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 405 | | NULL | | 6218 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6219 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6221 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6222 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6225 | xxxxxx | xxxxx:xxxxx | NULL | Query | 0 | NULL | show processlist | | 6226 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6237 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | | 6238 | xxxxxx | xxxxx:xxxxx | xxxxx | Sleep | 7 | | NULL | +------+--------+-------------+-------+-------------+--------+-------+------------------+ 18 rows in set (0.00 sec) mysql>
Timeが異常に長いものとかあれば、そのIdを殺すと滞留が解消するかもしれません。 自分が遭遇した状況では、Commandに「Sending Data」というIdが存在し、 それ以外は「Lock」という状態のものが多く存在している状況で、 Sending DataのIdをkillしたら復活したことがありました。
mysql> kill 6031; Query OK, 0 rows affected (0.00 sec) mysql>
こんな感じ。
上記は処理が正常終了していないケースでしたが、全部正常だと、最大同時接続数の設定が そもそも足りていなかったり、タイムアウト時間が長すぎるのが原因かもしれません。
以下で現状の設定を把握します。
mysql> show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 100 | +-----------------+-------+ 1 row in set (0.00 sec) mysql>
mysql> show global variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 28800 | +---------------+-------+ 1 row in set (0.00 sec) mysql>
同時接続数は計算量と性能を見ながら決めることになるのかな。 wait_timeoutは秒単位で、デフォルトだと28800(8時間)とのこと。 タイムアウトがアイドルタイムアウトなのか接続開始から接続終了までの時間なのかが不明。 だけど8時間は長すぎる、と判断できる機会は多そう。今自分が面倒を見ているシステムでもそう。
設定変更（の前の注意事項）
計算機の設定を変更するときは以下に注意する必要がある。
- 現状の動作を変更する
- 今すぐすべてに変更が反映されるのか？（例えば「既に接続されている接続のwait_timeoutも変更されるのか？」という疑問）
- すべてではなく、これから発生するイベントに対してか？（例えば「これから接続される接続のwait_timeoutだけが変更されるのか？」という疑問）
- 次回起動時の動作を変更する
- 再起動後、どのような動作となるか？
前者は動作中のプロセスに対してなんらか命令することで動作を変更させる、という設計のシステムが多い。 後者は起動時に読み込まれる設定ファイルを変更する、という設計のシステムが多い。
で、ネット上のいろんな情報を見ていると、どちらかしか紹介していない例が多い。 現状の動作変更だけやって、再起動した際の動きを考慮していないものとか。 もしくは設定ファイルを変更して軽々しくOSとかプロセスを再起動していたりとか。 構築中とかで止めることが許されるシステムならいいんですが、止められない場合はどちらも丁寧にやらなければいけないですね。
設定変更
結論は以下。
現状の動作を変更する
当然ですが、設定値はシステムの特性を考えて適切に決めてくださいね。
- まずはwait_timeoutから
mysql> set global wait_timeout = 3600; Query OK, 0 rows affected (0.00 sec) mysql>
- 次にmax_connections
mysql> set global max_connections = 200; Query OK, 0 rows affected (0.00 sec) mysql>
変更したらちゃんと変更されているか確認しましょう。
mysql> show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 200 | +-----------------+-------+ 1 row in set (0.00 sec) mysql>
mysql> show global variables like 'wait_timeout'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | wait_timeout | 3600 | +---------------+-------+ 1 row in set (0.00 sec) mysql>
次回起動時の動作を変更する
自分の環境の場合は/etc/my.cnfを変更することになるみたいだった。
[mysqld] (snip) wait_timeout=3600 max_connections=200
止めてもいいシステムならservice mysqld restartとかでmysqlを再起動して正常に反映されるかどうか確認するといいと思います。 ＃わかりやすいはCentOS使っています。 ちなみにservice mysqld reloadでもいけるかもしれませんが、それで設定値が全部動作に反映するかどうかは謎です。 止めてはいけないシステムならきっと同じバージョンとかの開発・検証環境が存在するはず。そこでテストしましょう。 止めてはいけないシステムで開発・検証環境が無いなら、それはシステムの運用保守体制が残念すぎる。 仮想サーバとか簡単に調達できる昨今、性能を落とせばそのような環境は簡単に手に入る。 それが無いということは…。推して知るべし。
参考文献
今回のブログは以下を参考にしながら記載しました。
