ProxySQL でエラー

ProxySQL 経由で show database すると 10 秒くらい待たされて "Max connect timeout reached while reaching hostgroup 1 after 10000ms" がでて、結局応答は戻らない。この現象は何なのか。

環境

CentOS release 6.10 (Final)
proxysql-1.4.13-1.x86_64
mysql-5.1.73-8.el6_8.x86_64
mysql-server-5.1.73-8.el6_8.x86_64
master サーバーと slave サーバー
master から slave サーバーへレプリケーションしています
slave サーバーは read only
slave では ProxySQL にて select のみ受け付け、他クエリは master に転送している

現象が出たのはどっちのサーバー?

slave サーバー
以下ProxySQL と MySQL のコンソール画面で状態を確認していきますが、master サーバーで実行しているものと slave サーバーで実行しているものがあります。

エラーメッセージでそのままググる

execute failed: Max connect timeout reached while reaching hostgroup 1 after 10000ms

ProxySQL - 柔軟で強力なMySQLプロキシ層（google 翻訳）

まず、MySQLの最大接続数は "max_connections" 変数で制御されます。proxysql の最大接続数は、"mysql_users.max_connections" と "mysql_servers.max_connections" 2 つの設定があります。
"mysql_servers.max_connections" に達すると、接続のいくつかは "mysql-connect_timeout_server_max" に達するまで待ってから、proxysql はエラーメッセージ

SQLSTATE[HY000]: General error: 9001 Max connect timeout reached while reaching hostgroup 1 after 10000ms

を返すでしょう。
"mysql_users.max_connections" に達すると、クライアントにエラーメッセージ

1040: Too many connections

が表示されます。
バックエンドのグローバル変数 "max_connections" に達し、proxysql にバックエンドとの "ConnFree" がない場合、クライアントは "ConnFree" との接続を確立できますが、すべてのクエリは

ERROR 1040 (#HY00): Too many connections

を返します。"Shun" と proxysql.log に記録されます。

"mysql-connect_timeout_server_max" （ms）待たされる

待たされるのはこの定数の仕業。コネクションがなくなると、"mysql-connect_timeout_server_max"（ms）待たされて MySQL に接続できなかったと戻すみたい。"reaching hostgroup..." と省略しているから google で引っかからない。

mysql-connect_timeout_server_max

The timeout for connecting to a backend server from the proxy. When this timeout is reached, an error is returned to the client with code #28000 and the message "Max connect timeout reached while reaching hostgroup...".

"mysql-connect_timeout_server_max" のみかた

slave

$ mysql -uadmin -padmin -h slave -P 6032 --prompt='Admin>'
Admin>select * from global_variables where variable_name="mysql-connect_timeout_server_max";
+----------------------------------+----------------+
| variable_name                    | variable_value |
+----------------------------------+----------------+
| mysql-connect_timeout_server_max | 10000          |
+----------------------------------+----------------+
1 row in set (0.00 sec)

connection pool のせいでコネクションがなくなる?

コネクション食っているのは pool しているかなと思って調べてみる。
スタート時は 0 で、クライアントからのコネクションが終わったら "Hostgroup Manager" に戻され、"Hostgroup Manager" が問題ないと判断し、"connection pool" がいっぱいでなかったら "connection pool" に加えられる。"mysql-ping_interval_server_msec" で idele connection を ping する間隔が決定される。

mysql-free_connections_pct

ProxySQL uses a connection pool to connect to backend servers.
Connections to backend are never pre-allocated if there is no need, so at start up there will 0 connections to the backend.
（略）
When the connection completes serving the client's request, it is sent back to the the Hostgroup Manager. If the Hostgroup Manager determines that the connection is safe to share and the connection pool isn't full, it will place it in the connection pool.
（略）
The time interval between two such rounds of pings for idle connections is controlled by the variable "mysql-ping_interval_server_msec".

どのくらい connection pool として保持されるのかの計算式も書いてある

mysql-free_connections_pct(%) * mysql_servers.max_connections

それぞれ 10% と 1000 （両方ともデフォルト）だと、100 コネクションをプールしていることになる。

"mysql-connect_timeout_server_max" のみかた

slave

$ mysql -uadmin -padmin -h slave -P 6032 --prompt='Admin>'
Admin>select * from global_variables where variable_name="mysql-free_connections_pct";
+----------------------------+----------------+
| variable_name              | variable_value |
+----------------------------+----------------+
| mysql-free_connections_pct | 10             |
+----------------------------+----------------+
1 row in set (0.01 sec)

"mysql_servers.max_connections" のみかた

"max_connections" はデフォルトの 1000 から 75 に変更済

slave

mysql -uadmin -padmin -h slave -P 6032 --prompt='Admin>'
Admin>select * from mysql_servers;
+--------------+------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+
| hostgroup_id | hostname   | port  | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment   |
+--------------+------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+
| 1            | master     | 13306 | ONLINE | 1      | 0           | 75              | 0                   | 0       | 0              | master rw |
| 2            | master     | 13306 | ONLINE | 0      | 0           | 75              | 0                   | 0       | 0              | master ro |
| 2            | slave      | 13306 | ONLINE | 1      | 0           | 75              | 0                   | 0       | 0              | slave ro  |
+--------------+------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+
3 rows in set (0.00 sec)

対策: connection pool を減らす

MySQL の "max_connections" は 150 で ProxySQL がデフォルト限界値 100 コネクションまでプールしてしまったとすればコネクションが足りないことは起こり得るかもしれない
Max connect timeout reached while reaching hostgroup 1 after 10000ms の通り hostgroup 1 なので（mysql_servers 参照）エラーメッセージが出ているのは slave サーバーだけど、そもそもの原因、コネクションが足りないのは master サーバーである
しかし slave サーバーの ProxySQL がタイムアウトを出しているので slave サーバーに対して対策を行う

master

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

でも "Max_used_connections" は 56 とありコネクションが足りない状態が起こったとは考えにくい

master

mysql> show global status like '%Max_used_connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 56    |
+----------------------+-------+
1 row in set (0.00 sec)

結局 slave サーバーの ProxySQL "mysql_servers.max_connections" を 75、"mysql-free_connections_pct" を 10% とし、connection pool を（上記計算式より） 7.5 としてみる。

対策: Too many connections まで待つ?

"mysql_users.max_connections" に達すると、クライアントにエラーメッセージ "Too many connections" 、とあるので、"Too many connections" が出ないかどうか、値を 65 まで小さくしてみます（デフォルトは 10000 です）。
バックエンドのグローバル変数 "max_connections" を超えても "Too many connections" とありますが、"mysql_users.max_connections" でカバーでできてるみたいなのでこちらには手を入れません

"mysql_users.max_connections" のみかた

"max_connections" はデフォルトの 10000 から 65 に変更すみ

slave

$ mysql -uadmin -padmin -h slave -P 6032 --prompt='Admin>'
Admin>SELECT username, default_hostgroup, max_connections FROM mysql_users;
+--------------+-------------------+-----------------+
| username     | default_hostgroup | max_connections |
+--------------+-------------------+-----------------+
| usera        | 1                 | 65              |
| userb        | 1                 | 65              |
+--------------+-------------------+-----------------+
2 rows in set (0.00 sec)

バックエンドのグローバル変数 "max_connections" のみかた

slave

$ mysql -uadmin -padmin -h slave -P 6032 --prompt='Admin>'
Admin>select  * from global_variables where variable_name like 'mysql-max_connections';
+-----------------------+----------------+
| variable_name         | variable_value |
+-----------------------+----------------+
| mysql-max_connections | 2048           |
+-----------------------+----------------+
1 row in set (0.00 sec)

"ConFree" のみかた

"max_connections" に至る前に "ConnFree" も関連するので、みかたをメモしておきます。

slave

$ mysql -uadmin -padmin -h slave -P 6032 --prompt='Admin>'
Admin>select * from stats_mysql_connection_pool;
+-----------+------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host   | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 1         | master     | 13306    | ONLINE | 0        | 2        | 2      | 0       | 484     | 143797          | 3936            | 2504       |
| 2         | master     | 13306    | ONLINE | 0        | 0        | 0      | 0       | 0       | 0               | 0               | 2504       |
| 2         | slave      | 13306    | ONLINE | 0        | 2        | 2      | 0       | 3562    | 1232049         | 5594717         | 70         |
+-----------+------------+----------+--------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
3 rows in set (0.00 sec)

他の原因は?

"mysql_users" に登録されているいずれかのユーザーの "hostgroup" が "mysql_servers" にない場合とある。下記リンクは、hostgroup 1 と 2 はあって、hostgroup 0 がないけど、mysql_users の default server が 0 のユーザーがいますよっていう例。

Question 11 : Configure Default Hostgroup.

Max connect timeout reached while reaching hostgroup 0 after 10000ms

"hostgroup" のみかたは上記 "mysql_users.max_connections" のみかたの中にあります。