mita2 database life

主にMySQLに関するメモです

MySQL max_connections は雑に設定しておけば良い

MySQL 誕生25周年 らしいです。めでたい! 25年、1つのソフトウェアが継続しているってすごい!

max_connections について

データベースを使っている開発者から「最大までどれぐらいコネクション数を増やせるのか」という質問を良くもらいます。 最大コネクション数(max_connections) の設定値を超えてしまい、too many connections エラーが出る。 max_connections を見直すとして、「じゃあどこまで大きくしていいのか?」と不安になるのはわかる。

以下の話は、コネクションプールを使っている前提のお話。

単にコネクション数が増えるだけでは、負荷は増加しない

単にコネクション数が増えるだけでは、DBサーバの負荷はあまり変化しない。 特にMySQLはスレッドモデルで実装されており、(プロセスモデルのデータベースと比較して)大量にコネクションを張った時のリソース消費が少ない。 SQLを実行していないアイドル(Sleep)状態のコネクションは、大量に存在していても、あまりDBサーバのCPUやIOリソースの使用量には関係しない。

# Command が Sleep になっているコネクションは何も実行していない
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  4 | event_scheduler | localhost | NULL | Daemon  |  521 | Waiting on empty queue | NULL             |
| 13 | root            | localhost | NULL | Sleep   |    8 |                        | NULL             |
| 14 | root            | localhost | NULL | Query   |    0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)

apache + PHP のような、HTTPサーバのワーカスレッド(プロセス)数とDBへのコネクション数が連動してしまうような環境で、アイドルコネクションが多く発生しやすい。

このグラフはコネクション数を約1万コネクションまで増やした時の、CPU使用率を表している。 コネクション数は SHOW PROCESSLIST で出力された数をカウントしている。

このように、いくらアイドルのコネクションを増やしたとしても、CPU使用率にほとんど影響しない。

f:id:mita2db:20200531163640p:plain

なお、メモリは多少、必要となる。実験では1万コネクションで2Gぐらい(1コネクションあたり200KByteぐらい) 消費された*1SQLを流していないので実際のワークロードではもう少し消費されるかもしれない)。

f:id:mita2db:20200531163841p:plain

以下のパラメータで指定した値が、コネクションの利用するメモリ量と大きく関係している。 以下のパラメータの値が大きいほど1つのコネクション(スレッド)が必要とするメモリが大きくなる。

  • sort_buffer_size
  • myisam_sort_buffer_size
  • read_buffer_size
  • join_buffer_size
  • read_rnd_buffer_size

大事なのはワークロード

DBサーバの負荷を左右するのは、コネクション数ではなく、アクティブなコネクションの数それぞれのコネクション(スレッド)で実行されるSQL。 アクティブなコネクションは、実際にSQLが流れているコネクションを指す。「重いSQL」 が 「同時にたくさん」流れるとDBサーバの負荷大きくなる。

アクティブなコネクション数がどれぐらいあるかは、Threads_running の値で確認できる。現在のコネクション数は Threads_connected で確認できる。

mysql> SHOW GLOBAL STATUS LIKE 'Threads_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 2     |
| Threads_created   | 2     |
| Threads_running   | 2     |
+-------------------+-------+
4 rows in set (0.00 sec)

コネクション制限で過負荷を回避するのは困難

DBサーバの能力を超えた大量のリクエストは、データベースのレスポンスタイムを大きく低下させる。 レスポンスタイムの悪化がエンドユーザの再実行やアプリケーションからのリトライを誘発し、さらに負荷が増え、最終的には、一切、レスポンスを返せないハングアップしたような状態になってしまう。

データベースへリクエストの流入量を制限できれば、一部のリクエストは犠牲(エラー)にしつつも、サービスの提供を継続することができる。 リアルの店舗等で混雑時に入場制限をするのと同じだ。

しかし、実際には 過負荷を回避するような値に max_connections をあらかじめ設定しておくのは難しい。 DBの負荷は「SQLの内容」や「アクティブなコネクションの割合」「実行計画」といったコネクション数以外の変数の影響が大きく、コネクション数だけではコントロールできない。

キャップをかける試み自体は否定しないが、変化の激しい、Webサービスでコネクション数の見積もりに工数をかけるのは無駄が大きいように思う*2

なお、いざ過負荷に陥ったときに、max_connections を絞り、過負荷を当座で回避することは出来る。

過負荷を回避するための Thread Pool

過負荷を回避するために役立つ Thread Pool という機能がある。 Thread Pool ではアクティブなスレッド数をコントロールし、過負荷を回避し、スループットを最大に保つことができる。 MySQL では Enterprise Edition(有償) に入っている。 Percona Server (無償) にも同様の機能がある。

(しかし、自分は実運用では使ったことがないので、どこまで、うまく動いてくれるか、わからない。。。)

dev.mysql.com

www.percona.com

どこまで max_connections を増やせますか?と聞かれたら

ワークロードに変化があるのかどうをヒアリングする。現状の負荷と照らし合わせて、心配があるなら、負荷検証したり、もしもの時に備えた準備をする。 max_connections はあまり考えず、雑に大きな値(1000とか2000とか)を設定している。

参考書籍

*1:MySQL 8.0.19で全くデフォルトから設定をへ変更していない環境で試している

*2:アクセスパターンが決まっている業務系のアプリなら、アリかもしれない