記事の一覧

MySQLのToo many connectionsの原因と対処法

MySQL の Too many connections が出たときに、接続の漏れ・クエリの詰まり・上限不足を SQL で見分け、max_connections とプールを見直す手順を解説。RDS の既定値と PostgreSQL にも触れます。

アプリのログに ERROR 1040 (HY000): Too many connections が出て、画面がエラーになった。この記事はそんなときに何を確かめ、どう直すかをまとめたものです。max_connections を上げれば一度は収まりますが、原因によっては上げても同じことが起きます。原因を見分ける SQL から、上限の決め方、Amazon RDS の既定値、PostgreSQL の「too many clients already」まで順に説明します。

Too many connections とは

MySQL は同時につないでいられる接続の数に上限を持っています。その上限が max_connections というシステム変数です。今つながっている接続の数がこの値に達すると、新しく接続しようとしたクライアントは Too many connections のエラーで断られます。

数えられるのは「接続」であって「実行中のクエリ」ではない点に注意してください。何もしていない (Sleep の) 接続も 1 つと数えます。アプリがクエリを投げていなくても、接続を開いたまま持っていれば枠は埋まります。

MySQL 8.4 の max_connections の既定値は 151 です。実際に効く上限は max_connections に設定した値と「open_files_limit の実効値から 810 を引いた値」の小さい方です。設定ファイルで大きな値を書いても、OS のファイル数の上限が低いと思ったほど増えないことがあります。

似たエラーに ERROR 1203 (42000): User ... already has more than 'max_user_connections' active connections があります。こちらはサーバー全体ではなく、ユーザーごとの上限 (max_user_connections、既定は 0 = 無制限) に当たったときに出ます。メッセージの番号で区別してください。

まず現在の状態を確かめる

エラーが出ている最中は、普通のユーザーではログインできないことがあります。MySQL は実際には max_connections + 1 本の接続を受け付けます。最後の 1 本は CONNECTION_ADMIN 権限 (古い SUPER 権限でも可) を持つアカウントのために空けてあります。アプリ用のユーザーにこの権限を渡していなければ、管理者は上限に達していてもログインして調べられます。逆に、アプリのユーザーに強い権限を渡していると、この 1 本も埋まってしまいます。

ログインできたら、上限と現在の値を並べて見ます。

SHOW GLOBAL VARIABLES LIKE 'max_connections';

SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected',
  'Threads_running',
  'Max_used_connections',
  'Max_used_connections_time',
  'Connection_errors_max_connections'
);

それぞれの意味は次のとおりです。

変数 意味
Threads_connected いま開いている接続の数
Threads_running Sleep していない (処理中の) スレッドの数
Max_used_connections 起動してから同時に使われた接続数の最大
Max_used_connections_time Max_used_connections がその値になった時刻
Connection_errors_max_connections max_connections に当たって断った接続の累計

Max_used_connections が max_connections と同じ (または +1) で、Connection_errors_max_connections が 0 でなければ、上限に当たったことは確かです。Max_used_connections_time を見れば、いつ当たったかが分かります。その時刻のアプリのデプロイ、バッチの開始、アクセスの増え方と突き合わせると、原因の見当がつきやすくなります。

原因を 3 つに分けて見分ける

上限に当たる理由は大きく 3 つに分かれます。どれなのかで直し方がまったく違うので、最初に見分けます。

  1. 接続の漏れ。アプリが接続を閉じずに持ち続け、何もしていない接続が溜まっている
  2. クエリの詰まり。遅いクエリやロック待ちで処理が終わらず、新しい接続が次々に増えている
  3. 本当に足りない。アプリのサーバーやプロセスが増え、正しく使っても上限を超える

見分けるには、接続を「誰が」「どこから」「何をしているか」で集計します。

SELECT
  USER,
  SUBSTRING_INDEX(HOST, ':', 1) AS client_host,
  COMMAND,
  COUNT(*)  AS connections,
  MAX(TIME) AS max_seconds_in_state
FROM information_schema.PROCESSLIST
GROUP BY USER, client_host, COMMAND
ORDER BY connections DESC;

HOST には ホスト名:ポート が入るので、ポートを切り落としてクライアントのホスト単位にまとめています。TIME は、その接続が今の状態になってからの秒数です。

結果の読み方の目安を書きます。

COMMAND が Sleep の行が大半で、TIME が数百秒から数千秒ある場合は、接続の漏れを疑います。アプリが接続を開いたまま使っていません。特定のホストやユーザーに偏っていれば、そのアプリが発生源です。

COMMAND が Query の行が多く、Threads_running も高い場合は、クエリの詰まりです。処理が終わらないので接続が返されず、アプリは新しい接続を開き続けます。次の SQL で、長く動いているクエリを見ます。

SELECT ID, USER, HOST, DB, TIME, STATE, LEFT(INFO, 200) AS query
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Query'
ORDER BY TIME DESC
LIMIT 20;

STATE に Waiting for table metadata lock が並んでいれば、ALTER TABLE などのメタデータロック待ちです。InnoDB の行ロック待ちは STATE に出ないこともあるので、詳しくは ロック待ちの調べ方 を参照してください。遅いクエリそのものが原因なら MySQL のスロークエリの調べ方 が役に立ちます。

Sleep も Query も特別に偏っておらず、ホストの数やプロセスの数に比例して増えている場合は、本当に足りていない可能性があります。この場合は後で説明する見積もりをします。

応急処置: 接続を空ける

サービスを止めないことが先決なら、まず枠を空けます。

漏れが原因で Sleep の接続が溜まっているなら、長く Sleep している接続を KILL で切ります。次の SQL は 10 分以上 Sleep している接続を切る KILL 文を作るだけで、実行はしません。中身を確かめてから流してください。

SELECT CONCAT('KILL ', ID, ';') AS kill_stmt, USER, HOST, TIME
FROM information_schema.PROCESSLIST
WHERE COMMAND = 'Sleep'
  AND TIME > 600
  AND USER NOT IN ('system user', 'event_scheduler', 'rdsadmin')
ORDER BY TIME DESC;

接続を切ると、その接続で開いていたトランザクションはロールバックされます。アプリによっては、切れた接続を使おうとしてエラーになります。止めてよい接続かどうかは、ホストとユーザーから判断してください。Amazon RDS では、KILL の代わりに RDS が用意している CALL mysql.rds_kill(ID); を使えます。

上限そのものを一時的に上げることもできます。max_connections は動的に変えられる変数なので、再起動はいりません。

SET GLOBAL max_connections = 300;

SET GLOBAL の値は再起動で元に戻ります。残したい場合は、MySQL 8.0 以降なら SET PERSIST max_connections = 300;、それ以外は設定ファイル (my.cnf の [mysqld]) に書きます。RDS ではパラメータグループで変えます。既定のパラメータグループは編集できないので、自分で作ったパラメータグループを使っている必要があります。

ただし、上げるのはあくまで時間稼ぎです。漏れが原因なら、上げた分もいずれ埋まります。詰まりが原因なら、接続を増やすと同時に動くクエリが増え、DB がさらに重くなることがあります。

根本の対処

接続の漏れを直す

接続の漏れはアプリのコードの問題です。よくあるのは、例外が起きた経路で接続を閉じていない、ORM やドライバの使い方を誤って接続をプールに返していない、といったものです。言語の try-with-resources、with 文、defer などで、どの経路でも接続が閉じられる書き方にします。

MySQL 側の安全網は wait_timeout です。何もしていない接続を、ここで指定した秒数で MySQL が切ります。既定値は 28800 秒 (8 時間) なので、漏れた接続は 8 時間残ります。これを短くすると漏れた接続は早く消えますが、コネクションプールが持っている待機中の接続も切られます。プールの「接続の最大寿命」の設定を wait_timeout より短くしておかないと、アプリが切られた接続を使って MySQL server has gone away のようなエラーになります。wait_timeout を変えるときは、プールの設定と組にして決めてください。

クエリの詰まりを直す

詰まりが原因なら、上限を上げても解決しません。長く動いているクエリや、ロックを待っているクエリを特定して、インデックスを足す、トランザクションを短くする、重いバッチの時間をずらす、といった手を打ちます。

必要な接続数を見積もる

アプリがコネクションプールを使っているなら、DB に開かれる接続の最大数はおおむね次の掛け算で決まります。

アプリのサーバー台数 × 1 台あたりのプロセス数 × プールの最大接続数

たとえば、Web サーバー 4 台でそれぞれ 4 プロセスが動き、プールの最大を 10 にしていれば、最大 160 本です。これに、バッチ、管理画面、監視ツール、手作業の接続を足します。既定の 151 では足りません。

この掛け算は、オートスケールでサーバーが増えたときやデプロイの切り替えで新旧のプロセスが同時に動くときに、一時的に大きくなります。ふだんは足りていても、デプロイのたびにエラーが出るなら、これが原因かもしれません。

上限を上げるときは、メモリを確かめます。接続ごとにバッファなどのメモリを使うので、上限を大きくしすぎると、接続が増えたときに DB サーバーのメモリが足りなくなります。上限を上げるより、プールの最大数を見直す、サーバーレス関数のように接続を共有できない実行環境の前に RDS Proxy や ProxySQL のようなプロキシを置く、といった方法で接続の本数を減らすほうが安全なことが多いです。

Amazon RDS の max_connections の既定値

RDS for MySQL の max_connections の既定値は、インスタンスクラスのメモリから計算されます。式は {DBInstanceClassMemory/12582880} で、メモリ 12 MB あたり 1 接続というおおまかな計算です。DBInstanceClassMemory は OS や RDS の管理プロセスが使う分を引いたメモリなので、カタログのメモリ量より小さくなります。

AWS のドキュメントの例では 8 GiB のインスタンスクラス (db.m7g.large など) で約 630 です。小さいクラスほど差し引かれる割合が大きく、db.t3.micro では約 60 です。開発用の小さいインスタンスでは、アプリを少し並べただけで上限に当たります。実際の値は SHOW GLOBAL VARIABLES LIKE 'max_connections'; で確かめてください。

既定値はメモリに比例するので、インスタンスクラスを大きくすると上限も上がります。パラメータグループで値を固定していると、この自動の増加は効きません。Aurora MySQL は別の式を使うので、同じ計算は当てはまりません。

CloudWatch では DatabaseConnections が接続数です。これを max_connections と並べて見ておくと、上限に近づいていることに事前に気づけます。

上限に達しても入れる管理用の接続

CONNECTION_ADMIN の 1 本は誰かが先に使えばなくなります。MySQL 8.0.14 以降では管理用の接続を別のアドレスとポートで受け付けられます。

[mysqld]
admin_address=127.0.0.1
admin_port=33062

admin_address を設定したときだけ有効になり、変えるには再起動が必要です。この口には SERVICE_CONNECTION_ADMIN 権限を持つユーザーだけが入れ、管理用の接続の数には上限がありません。自前で MySQL を運用しているなら、障害のときに入れなくなる事態を避けるために設定しておく価値があります。

PostgreSQL の「too many clients already」

PostgreSQL では、同じ状況で FATAL: sorry, too many clients already が出ます。上限は同じく max_connections で、既定値はふつう 100 です。

PostgreSQL の max_connections は、変えるのに再起動が必要です。MySQL のように動かしたまま上げることはできません。RDS for PostgreSQL の既定値は LEAST({DBInstanceClassMemory/9531392}, 5000) です。

superuser_reserved_connections (既定 3) の分はスーパーユーザーのために空けてあります。残りがこの数になると、一般のユーザーは断られ、remaining connection slots are reserved for ... というメッセージが出ます (文言はバージョンによって違います)。PostgreSQL 16 からは reserved_connections で pg_use_reserved_connections ロールを持つユーザー向けの枠も別に取れます。

接続の内訳は pg_stat_activity で見ます。

SELECT usename, application_name, client_addr, state, count(*) AS connections
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY usename, application_name, client_addr, state
ORDER BY connections DESC;

state が idle の接続が多ければ、MySQL の Sleep と同じく漏れかプールの持ちすぎです。PostgreSQL で特に気をつけたいのは idle in transaction です。トランザクションを開いたまま何もしていない接続で、枠を使うだけでなくロックを持ち続け、VACUUM の妨げにもなります。

SELECT pid, usename, application_name,
       now() - state_change AS idle_for,
       left(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change;

切るときは SELECT pg_terminate_backend(pid); を使います。idle_in_transaction_session_timeout を設定すると、この状態が指定の時間を超えたセッションを PostgreSQL が自動で切ります (既定は 0 で無効)。何もしていない普通のセッションを切る idle_session_timeout もありますが、ドキュメントは、コネクションプールを通した接続にこれを使うとプールが予期しない切断に対応できないことがあると注意しています。

PostgreSQL は接続ごとにサーバープロセスを 1 つ作るので、接続数が増えるほどメモリなどの負担が増えます。接続数が多い構成では、PgBouncer のようなコネクションプーラーを DB の前に置くのが一般的です。VACUUM との関係は PostgreSQL の VACUUM と autovacuum で説明しています。

エラーが出る前に気づくには

Too many connections は、出てから対処すると利用者に影響が出たあとになります。接続数は急に上限に達することもありますが、漏れの場合は時間をかけて少しずつ増えていくことが多いので、推移を見ていれば手前で気づけます。

見ておきたいのは、Threads_connected (PostgreSQL なら pg_stat_activity の件数) と max_connections の比、Threads_running、Connection_errors_max_connections の増え方の 3 つです。たとえば、比が 8 割を超えたら調べる、Connection_errors_max_connections が 1 でも増えたら知らせる、といった決め方ができます。

この確認を続けるには

この記事の確認は、上の SQL を cron で定期的に実行して記録する、監視ツールで Threads_connected や CloudWatch の DatabaseConnections にアラートを設定する、といった方法で続けられます。ただ、接続数はデプロイやバッチの時間帯で大きく揺れるので、固定の閾値だけだと鳴りすぎたり、ゆっくり増える漏れを見逃したりしがちです。

AI に任せる方法もあります。Scandock は、接続文字列を登録すると AI がデータベースの構成を読んで監視項目を決め、固定の閾値ではなく値の推移や過去の通知の結末を踏まえて異常かどうかを判断するサービスです。異常と判断すると、読み取り専用のクエリで関連するデータを調べ、原因・影響・対処案をつけて Slack やメールなどに知らせます。MySQL、PostgreSQL、Amazon RDS などに対応しています。

参考

関連する記事