接続を確認する — mysqlコマンド・SHOW PROCESSLIST・wait_timeout

MySQLとは何かでは、MySQLがサーバーであり、接続には確立コストと本数の上限があることを見ました。

この章では、それを実際に自分の目で確認します。 いま何本の接続が開いているのか、誰が開いているのか、それはいつ切れるのか。ここが見えるようになると、コネクションプールの挙動もそのまま観察できるようになります。

学習者

接続って目に見えないものだと思っていました。数えられるんですか？

数えられます。しかも1本ずつ、誰が何をしているかまで見えます。

Dockerで動かす

ローカルにMySQLを直接インストールしなくても、Dockerがあれば1ファイルで起動できます。

services:
  mysql:
    image: mysql:8.4.0
    ports:
      - '3306:3306'
    environment:
      MYSQL_ROOT_PASSWORD: rootpass
      MYSQL_DATABASE: myapp
    volumes:
      - mysql_data:/var/lib/mysql
 
volumes:
  mysql_data:

docker compose up -d

mysqlコマンドで接続する

コンテナの中に入って接続します。

docker compose exec mysql mysql -uroot -p

接続に成功すると、プロンプトが mysql> に変わります。

接続できたら、まずは足元を確認します。

SELECT VERSION();
SHOW DATABASES;

いま開いている接続を見る

ここがこの章の中心です。SHOW PROCESSLIST を実行すると、その瞬間にサーバーへ繋がっている接続が一覧で出ます。

SHOW PROCESSLIST;

Id     User             Host                db     Command  Time     State                    Info
5      event_scheduler  localhost           NULL   Daemon   1650425  Waiting on empty queue   NULL
93020  app              172.19.0.1:58270    myapp  Sleep    5962                              NULL
93021  app              172.19.0.1:58292    myapp  Sleep    5962                              NULL
93022  app              172.19.0.1:58272    myapp  Sleep    5962                              NULL
93023  app              172.19.0.1:58306    myapp  Sleep    5962                              NULL
93024  app              172.19.0.1:58296    myapp  Sleep    5962                              NULL
93025  app              172.19.0.1:58322    myapp  Sleep    5962                              NULL
93619  root             localhost           NULL   Query    0        init                     SHOW PROCESSLIST

列の意味は次の通りです。

この出力、よく見ると面白いことが分かります。

先生

app ユーザーの接続が6本、全部 Sleep で Time が5962秒。1時間半以上、何もしていないのに繋ぎっぱなしだね。

これはコネクションプールが動いている姿です。アプリケーションが起動時に接続を数本作り、使い終わっても閉じずに保持している。だから Sleep のまま何時間も居座ります。

逆に、この行数がじわじわ増え続けているなら、それは接続を閉じ忘れているサインです。

本数を数える

一覧を目で数える代わりに、集計値を直接見ることもできます。

SHOW STATUS WHERE Variable_name IN (
  'Threads_connected', 'Threads_running', 'Max_used_connections', 'Connections'
);

Variable_name         Value
Connections           93619
Max_used_connections  31
Threads_connected     7
Threads_running       2

ここは名前が紛らわしいので、表で整理します。

見るべきものは目的で変わります。

• いま危ないか を知りたい → Threads_connected と max_connections を比べる
• これまで危なかったか を知りたい → Max_used_connections を見る
• Connections は累積なので、大きくても異常ではありません

上限と、切断のタイミング

接続まわりで最初に覚えるべき設定は2つです。

SHOW VARIABLES WHERE Variable_name IN ('max_connections', 'wait_timeout');

Variable_name    Value
max_connections  151
wait_timeout     28800

wait_timeout の 28800秒 は8時間です。8時間まったく使われなかった接続は、サーバー側から一方的に切られます。

学習者

サーバーが勝手に切るなら、アプリ側はそれを知らないままですよね？

その通りで、ここが実務で厄介なところです。アプリのプールは「まだ使える接続」だと思って持っているのに、実体はもう切れている。次にその接続を使おうとした瞬間にエラーになります。

Error: Connection lost: The server closed the connection.

夜間にアクセスが途絶えるサービスで、朝いちばんのリクエストだけ失敗する——という現象の典型的な原因がこれです。対処はコネクションプールの章で扱います。

接続が埋まったときの調べ方

Too many connections が出たとき、やることは決まっています。

SELECT User, Host, db, Command, COUNT(*) AS cnt
FROM information_schema.PROCESSLIST
GROUP BY User, Host, db, Command
ORDER BY cnt DESC;

SHOW PROCESSLIST と同じ情報がテーブルとして引けるので、どのユーザー・どのホストが何本占有しているかを集計できます。犯人がアプリなのか、バッチなのか、誰かの開発環境なのかがここで分かります。

特定の接続を切りたい場合は Id を指定します。

KILL 93020;

よくあるハマりどころ

Sleepの接続を見て「無駄だ」と切ってしまう

Sleep はプールが接続を保持している正常な状態です。ここを切って回ると、アプリ側は次のリクエストで接続の作り直しが発生し、かえって遅くなります。 問題にすべきは Sleep の存在ではなく、本数が想定を超えて増え続けていないかです。

localhostとホスト名の違いでハマる

MySQLクライアントは、ホストに localhost を指定するとTCPではなくUNIXソケットで繋ごうとします。127.0.0.1 を指定するとTCPになります。 Dockerのコンテナ内から localhost を指定して繋がらない場合、それはコンテナ自身を指しているためです。別コンテナからは、Composeで定義したサービス名（上の例なら mysql）をホストに指定します。

wait_timeout を短くして解決しようとする

「切れるのが困る」からと wait_timeout を伸ばすと、死んだ接続が長く残ります。逆に短くすると、切断エラーの頻度が上がります。 wait_timeout はアプリ側のプール設定と組みで考えるものです。サーバー側だけをいじって解決しようとすると、たいてい別の問題に移動するだけになります。

ちゃんと使うためのポイント

• SHOW PROCESSLIST で、いま繋がっている接続を1本ずつ確認できる
• Command が Sleep の接続は、プールが保持しているアイドル接続であることが多い
• Threads_connected が現在値、Max_used_connections が最大の記録値
• max_connections は既定で151、wait_timeout は既定で8時間
• 接続が埋まったら information_schema.PROCESSLIST を集計して占有元を特定する

テーブルを作ってデータを入れるでは、接続したその先へ進みます。テーブルを作り、データを追加・更新・削除する MySQL ならではの書き方を扱います。

ここで観察した Sleep の接続を自分の手で作り出すのは、Node.jsからMySQLに接続するです。

参考リンク

• MySQL 8.4 Reference Manual — Server System Variables
• MySQL 8.4 Reference Manual — Connection Interfaces