General error: 9001 Max connect timeout reached while reaching hostgroup 10 after 10075ms

[Chen Wei Hsu]

Hi,

My app server connect to db through proxysql, and it works at the beginning. My initial setup was single write with one master and two slaves, but then it came the replication lag issue. Hence, I then use one database for both READ and WRITE. I know this does not make sense when using proxysql, but I want to preserve the architecture in case we can solve replication lag in the near future. When changing setup, I do load new setting to runtime and save it to disk. It seems fine at the beginning, but few hours later, the WRITE server becomes SHUNNED.

mysql> select * from mysql_servers;
+--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status | weight  | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.31.21.111 | 3306 | ONLINE | 1000000 | 0           | 1000            | 1                   | 0       | 0              | WRITE   |
| 11           | 172.31.21.111 | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READ    |
+--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)


mysql> select * from runtime_mysql_servers;
+--------------+---------------+------+---------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname      | port | status  | weight  | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+---------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
| 11           | 172.31.21.111 | 3306 | ONLINE  | 1000    | 0           | 1000            | 0                   | 0       | 0              | READ    |
| 10           | 172.31.21.111 | 3306 | SHUNNED | 1000000 | 0           | 1000            | 1                   | 0       | 0              | WRITE   |
+--------------+---------------+------+---------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

This does not make sense at all because if this server is SHUNNED, both READ and WRITE should be SHUNNED.

Checking at application side I get the error like this:

"SQLSTATE[HY000]: General error: 9001 Max connect timeout reached while reaching hostgroup 10 after 10075ms"

I try to connect to the same database from proxysql, and it seems fine. Then I try to connect to database directly from app server, and it seems fine. Then I try to access some records and this is where I found the differences.

Proxysql to database:

connect to database => fast

use database => fast

select some record from database => fast

App server to database:

connect to database => fast

use database => fast

select some record from database => fast

App server connect to database through proxysql

connect to database => fast

use database => Extremely slow

select some record from database => fast

Is there anything that might cause this issue?

[René Cannaò]

Hi Chen,

The error log is the first thing you should look into.

Thanks

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Chen Wei Hsu]

Hi Rene,

/var/lib/proxysql/proxysql.log

/usr/bin/proxysql_galera_checker: line 81: [: /var/lib/proxysql/: binary operator expected

/usr/bin/proxysql_galera_checker: line 104: $CHECKER_PIDFILE: ambiguous redirect

/usr/bin/proxysql_galera_checker: line 81: [: /var/lib/proxysql/: binary operator expected

/usr/bin/proxysql_galera_checker: line 104: $CHECKER_PIDFILE: ambiguous redirect

/usr/bin/proxysql_galera_checker: line 81: [: /var/lib/proxysql/: binary operator expected

/usr/bin/proxysql_galera_checker: line 104: $CHECKER_PIDFILE: ambiguous redirect

/var/lib/proxysql/proxysql_galera_check.log

[2018-05-30 21:30:38] Could not create galera checker PID file

[2018-05-30 21:30:38] Could not create galera checker PID file

[2018-05-30 21:30:41] Could not create galera checker PID file

[2018-05-30 21:30:41] Could not create galera checker PID file

proxysql_node_monitor.log

// nothing

pxc-cluster_proxysql_galera_check.log

[2018-05-30 21:31:32] Could not create galera checker PID file

[2018-05-30 21:31:35] Could not create galera checker PID file

[2018-05-30 21:31:32] Could not create galera checker PID file

[2018-05-30 21:31:35] Could not create galera checker PID file

I follow the instructions in proxysql and use proxysql-admin tool to setup the configuration.

proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable

So I am not quite sure why this happened, but I cannot see any issue that is related to mysql connection.

I also test the proxysql_galera_checker myself, and I see the same errors in log.

# proxysql_galera_checker  10 11 0 1 test.log
/usr/bin/proxysql_galera_checker: line 81: [: /var/lib/proxysql/: binary operator expected
/usr/bin/proxysql_galera_checker: line 104: $CHECKER_PIDFILE: ambiguous redirect

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.