MySQL の接続数が多すぎるエラーの原因と解決策

一般的なオンライン環境では、安全上の理由から、これら 2 つのパラメータを同じ値に設定することはできません。max_user_connections パラメータは、1 人のユーザーがすべての接続プールを占有するのを防ぐために、一定の余裕を持たせて少し小さめに設定することができます。

上記の問題を見たとき、私の最初の反応は、ログインしていくつかの接続を切断することでした。しかし、ログインしようとすると、ログインできなくなっていました。DBA 管理アカウント dba_admin でも接続を確立できなくなりました。エラーログを表示します。

2019-08-12T06:02:42.928412Z 0 [警告] 接続が多すぎます
2019-08-12T06:02:42.930181Z 0 [警告] 接続が多すぎます
2019-08-12T06:02:44.595199Z 0 [警告] 接続が多すぎます
2019-08-12T06:02:44.597160Z 0 [警告] 接続が多すぎます
2019-08-12T06:02:44.782042Z 0 [警告] 接続が多すぎます

いずれも接続数が多すぎます。幸い、開発環境なので、このような場合は、データベースサービスを停止して再起動し、最大接続数を再度増やすという方法を使用できます。もちろん、この増加量を考慮する必要があります。一般的には、wait_timeout 値、つまり待機タイムアウトと組み合わせて設定する必要があります。一般的に、wait_timeout 値が比較的大きい場合、接続数が多くなることが多く、接続ごとに約 2M のメモリが消費されます。接続数を増やすと、メモリが不足する可能性があります。wait_timeout 値を比較的小さく設定すると、接続が絶えず作成および破棄され、特定の IO リソースが浪費されます。

したがって、MySQL の公式ドキュメントで説明されているように、バランスの取れた値を取得する方法が鍵となります。

公式ドキュメントによると、数 GB のメモリがあれば、500 ～ 1000 の接続数を設定することができます。実際、この範囲の値であればほとんどのニーズを満たすことができます。

さらに、公式ドキュメントにも非常に優れた機能について記載されています。

mysqld は、実際には max_connections + 1 のクライアント接続を許可します。追加の接続は、SUPER 権限を持つアカウントが使用するために予約されています。権限を管理者に付与し、通常のユーザー (権限を必要としないはず) には付与しないことで、PROCESS 権限も持つ管理者は、権限のないクライアントが最大数接続されていても、サーバーに接続し、SHOW PROCESSLIST を使用して問題を診断できます。

実際、許可される接続の最大数は、max_connections + 1 接続の値です。最後の追加接続は、スーパー権限を持つユーザー用です。このように、接続が使い果たされた場合は、スーパー権限でログインし、show processlist メソッドを使用して接続を表示し、不要な接続をいくつか切断してサービスを再び利用できるようにすることができます。

しかし、実際のアプリケーションでは、利便性のために不要なアカウントにスーパー権限を割り当てることが多く、その結果、スーパー権限を持つ一般ユーザーが余分な接続を占有することになります。今日の問題では、このような設定は役に立たず、結局 1 つの接続では少なすぎるため、ログインできません。他のアカウントがすでにこのスーパー接続を占有している場合、このインスタンスには依然としてアクセスできません。

この場合、MySQL インスタンスを再起動し、my.cnf ファイルで最大接続数を変更する以外に、サービスを停止せずに処理する方法はありますか? 答えは「はい」です。ただし、前提条件があり、サービスは Percona サーバーである必要があります。MySQL の公式バージョンを使用する場合、この方法は機能しません。MariaDB でもこの方法を使用できると言われています。テストしたことがなく、よく理解していません。興味がある場合は、テストできます。

Percona Server 5.5.29 以降では、my.cnf に extra_port を追加するだけで、次回再起動したときにポートが使用可能になり、通常の接続と同じ bind_address でリッスンするようになります。 extra_port 変数が設定されていない場合、デフォルトでは追加のポートは使用できません。このポートが処理する接続の数を extra_max_connections で定義することもできます。デフォルト値は 1 です。

上記は、Percona-Server の公式サイトでの説明です。接続プールが占有された後のログイン問題は、他の 2 つのパラメータによって解決できます。

mysql:(なし) 22:12:51>> '%extra%' のような変数を表示します。
+----------------------------------+-------+
| 変数名 | 値 |
+----------------------------------+-------+
| 追加最大接続数 | 10 || 追加ポート | 43130 |
+----------------------------------+-------+
セット内の 2 行 (0.00 秒)

これらは 2 つのパラメータで、1 つは extra_max_connections、もう 1 つは extra_port です。これら 2 つのパラメータにより、サーバーに接続するための追加の接続と追加のポートを使用できるようになります。これにより、show processlist を使用して接続ステータスを監視し、不要な接続をいくつか切断し、サーバーを再び使用可能にすることができます。

ただし、MySQL コミュニティがこの機能と重複する admin_port を実装したため、Percona Server 8.0.14 以降では extra_port が削除されていることに注意してください。したがって、Percona Server 8.0.14 にアップグレードするときは、必ず my.cnf を編集し、extra_port がすでに定義されている場合は更新してください。

最後に、接続を見てみましょう。この例では、extra_port を 43130 に設定しています。

[root@ ログ]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P4313
パスワードを入力してください:
 MySQL モニターへようこそ。コマンドは ; または \g で終わります。
MySQL接続IDは71920です
サーバー バージョン: 5.7.16-10-log Percona Server (GPL)、リリース 10、リビジョン a0c7d0d

Copyright (c) 2000, 2011, Oracle およびその関連会社。無断複写・転載を禁じます。

OracleはOracle Corporationおよびその関連会社の登録商標です。
その他の名称は各社の商標である場合があります。
所有者。

ヘルプを表示するには、「help;」または「\h」と入力します。現在の入力ステートメントをクリアするには、「\c」と入力します。

[email protected]:(なし) 22:40:59>>終了
さよなら

---------------------------------------------------------------------------

[root@ ログ]# /usr/local/mysql/bin/mysql -udba_admin -p -h127.0.0.1 -P43130
パスワードを入力してください:
 MySQL モニターへようこそ。コマンドは ; または \g で終わります。
MySQL接続IDは71941です
サーバー バージョン: 5.7.16-10-log Percona Server (GPL)、リリース 10、リビジョン a0c7d0d

Copyright (c) 2000, 2011, Oracle およびその関連会社。無断複写・転載を禁じます。

OracleはOracle Corporationおよびその関連会社の登録商標です。
その他の名称は各社の商標である場合があります。
所有者。

ヘルプを表示するには、「help;」または「\h」と入力します。現在の入力ステートメントをクリアするには、「\c」と入力します。

[email protected]:(なし) 22:41:05>>