Automatic shun when backend connection fail

[Mickael]

Hello,

I followed your configuration howto about monitoring, but there's some points I don't understand :

ProxySQL effectively detects when one of my backend is down :

mysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start DESC LIMIT 10;
+-------------+------+------------------+----------------------+------------------------------------------------------------------------------------------------------------+
| hostname    | port | time_start       | connect_success_time | connect_error                                                                                              |
+-------------+------+------------------+----------------------+------------------------------------------------------------------------------------------------------------+
| 172.16.20.2 | 3306 | 1464527348404022 | 0                    | Lost connection to MySQL server at 'handshake: waiting for inital communication packet', system error: 110 |
| 172.16.20.4 | 3306 | 1464527348404022 | 517                  | NULL                                                                                                       |
| 172.16.20.3 | 3306 | 1464527348404022 | 469                  | NULL                                                                                                       |
| 172.16.20.1 | 3306 | 1464527348404022 | 478                  | NULL                                                                                                       |
| 172.16.20.2 | 3306 | 1464527346403921 | 0                    | Lost connection to MySQL server at 'handshake: waiting for inital communication packet', system error: 110 |
| 172.16.20.4 | 3306 | 1464527346403921 | 563                  | NULL                                                                                                       |
| 172.16.20.3 | 3306 | 1464527346403921 | 541                  | NULL                                                                                                       |
| 172.16.20.1 | 3306 | 1464527346403921 | 482                  | NULL                                                                                                       |
| 172.16.20.2 | 3306 | 1464527344403791 | 0                    | Lost connection to MySQL server at 'handshake: waiting for inital communication packet', system error: 110 |
| 172.16.20.4 | 3306 | 1464527344403791 | 478                  | NULL                                                                                                       |
+-------------+------+------------------+----------------------+------------------------------------------------------------------------------------------------------------+

But in the runtime_mysql_servers / mysql_servers, the backend still have the ONLINE status

Here is my ProxySQL configuration : http://pastebin.com/raw/PKb2RY0F

How can I manage to automatically shun a non-responding backend ?

Thank's

Mickael

[René Cannaò]

Hi Mickael,

mysql_servers (and its runtime version runtime_mysql_servers) are configuration tables , not status tables. That means that unless configure a server to be OFFLINE_SOFT or OFFLINE_HARD , ProxySQL will try to resume operation on that host. There are some exceptions that I will describe shortly.

When a host fails, like in your case, ProxySQL won't disable the host immediately.

If a host fails mysql-shun_on_failures (in your case, 1) times in a second, the host will be shunned for mysql-shun_recovery_time_sec (in your case, 60) seconds. when mysql-shun_recovery_time_sec seconds passed, ProxySQL will try to resume communication to the host.

This design tries to handle situation in which a servers becomes temporarily unavailable: network issue, excessive load, max_connections reached, restarted, etc.

Although, when the node is internally shunned, you won't see that from mysql_servers and runtime_mysql_servers, because as said they are configuration tables and not status tables.

If you want to check the status of a server, you should check table "stats_mysql_connection_pool" : this table reports the internal status of the servers.

Additionally, I strongly recommend to use the latest version from branch v1.2.0 .
Although v1.2.0g is marked as pre-release, I recommend to use that one as branch v1.2.0 is the actively developed branch and the one that is getting all the new features, bug fixes, and enhancements.

Thank you,

René

--
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+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Anugrah Dwi Ardianto]

On Monday, May 30, 2016 at 4:42:20 AM UTC+7, René Cannaò wrote:

Hi Mickael,

mysql_servers (and its runtime version runtime_mysql_servers) are configuration tables , not status tables. That means that unless configure a server to be OFFLINE_SOFT or OFFLINE_HARD , ProxySQL will try to resume operation on that host. There are some exceptions that I will describe shortly.

When a host fails, like in your case, ProxySQL won't disable the host immediately.

If a host fails mysql-shun_on_failures (in your case, 1) times in a second, the host will be shunned for mysql-shun_recovery_time_sec (in your case, 60) seconds. when mysql-shun_recovery_time_sec seconds passed, ProxySQL will try to resume communication to the host.

This design tries to handle situation in which a servers becomes temporarily unavailable: network issue, excessive load, max_connections reached, restarted, etc.

Although, when the node is internally shunned, you won't see that from mysql_servers and runtime_mysql_servers, because as said they are configuration tables and not status tables.

If you want to check the status of a server, you should check table "stats_mysql_connection_pool" : this table reports the internal status of the servers.

Additionally, I strongly recommend to use the latest version from branch v1.2.0 .
Although v1.2.0g is marked as pre-release, I recommend to use that one as branch v1.2.0 is the actively developed branch and the one that is getting all the new features, bug fixes, and enhancements.

Thank you,

René

Hi René!
Apologize for reviving this old thread, I'd like to confirm since you said mysql_servers table is a _configuration_ table, does that mean in the case of OP, when a node is actually died, we should set the status column to OFFLINE_HARD/OFFLINE_SOFT manually?
Is this also mean that the replication hostgroup actually changes mysql_servers table to remove and add servers?

Thanks
 

[René Cannaò]

Hi Anugrah,

Yes, when a node dies, ProxySQL detects that the node is dead and stop using it. Nonetheless, as the node is still configured, it tries to resume communication with it.

This is useful if the node died temporarily (like a network issue, or a graceful restart).

If you don't want to use the node anymore, you must delete it from mysql_servers .

Note that there is no harm if a node is configured but not available: ProxySQL is designed to handle backend failures.

About replication hostgroup: yes, the internal monitoring module reconfigure mysql_servers table.

Thanks,

René

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

[aaron....@cbsinteractive.com]

Hi Rene,

With AWS RDS read replicas can be added and removed at anytime.  How does ProxySQL handle the command from the client if the backend server is removed from service while the command is still running?

Regards,

Aaron