ProxySQL support for Galera

ProxySQL provides the following parameters and configuration tables specifically for Galera:

Health check monitoring parameters can be found in `global_variables`
Hostgroup behaviour is defined in the mysql_galera_hostgroups table.
Backend monitoring logs can be found in the mysql_server_galera_log table.

Configuring ProxySQL

Health Check Configuration

The global variables used to control timeout and interval check are defined in the global_variables table:

mysql> select * from global_variables where variable_name like '%monitor_galera%';
+----------------------------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------------------------+----------------+
| mysql-monitor_galera_healthcheck_interval | 1000 |
| mysql-monitor_galera_healthcheck_timeout | 600 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
+----------------------------------------------------+----------------+
3 rows in set (0.01 sec)

The mysql-monitor_galera_healthcheck_interval and mysql-monitor_galera_healthcheck_timeout variables are defined in milliseconds and control the frequency that each node is polled to identify the state of the node within the Galera cluster. The default values should be suitable for most implementations.

Hostgroup Configuration

Unlike regular async replication, Galera hostgroup are NOT defined in mysql_replication_hostgroups table. Instead there is a separate table that is specifically designed for Galera hostgroups. The reason for this is that more advanced topology support is needed in order to accommodate the deployment options available for Galera (e.g. controlled number of writers, cluster level replication thresholds etc.). The table has the following definition:

mysql> SHOW CREATE TABLE mysql_galera_hostgroupsG
*************************** 1. row ***************************
table: mysql_galera_hostgroups
Create Table: CREATE TABLE mysql_galera_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1,2)) NOT NULL DEFAULT 0,
max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
comment VARCHAR,
UNIQUE (reader_hostgroup),
UNIQUE (offline_hostgroup),
UNIQUE (backup_writer_hostgroup))
1 row in set (0.00 sec)

Colum Name	Description
writer_hostgroup	The id of the hostgroup that will contain all the Galera nodes that are active writers.
backup_writer_hostgroup	The id of the hostgroup that will contain all the Galera nodes that are standby writers i.e. nodes that have `read_only=0` in MySQL which exceed the number of defined `max_writers` (see definition below).
reader_hostgroup	The id of the hostgroup that will contain all the Galera nodes that are readers i.e. nodes that are configured with read_only=1 in MySQL will be assigned to this hostgroup.
offline_hostgroup	When ProxySQL’s health checks for a backend Galera node fail, the node will be moved to the offline_hostgroup.
active	Boolean value (0 or 1) to enable or disable the defined hostgroup configuration
max_writers	ProxySQL will limit the number of Read-Write instances populated in the writer hostgroup based on the value defined in `max_writers` for the cluster. Any nodes exceeding this number are placed into the `backup_writer_hostgroup` and ProxySQL will move these to the `writer_hostgroup` to actively serve traffic as needed.
writer_is_also_reader	This parameter has three possible values: (0, 1, 2). – writer_is_also_reader=0: nodes with `read_only=0` will be placed either in the writer_hostgroup and in the backup_writer_hostgroup after a topology change, these will be excluded from the reader_hostgroup. – writer_is_also_reader=1: nodes with `read_only=0` will be placed in the writer_hostgroup or backup_writer_hostgroup and are all also placed in reader_hostgroup after a topology change. – writer_is_also_reader=2 : Only the nodes with `read_only=0` which are placed in the in the backup_writer_hostgroup are also placed in the reader_hostgroup after a topology change i.e. the nodes with `read_only=0` exceeding the defined `max_writers`.
max_writers	This parameter is used to control the number of nodes in the writer hostgroup: nodes in excess of this value will be put into the backup_writer_hostgroup.

Conforming to ProxySQL’s multi-layer configuration system, 3 version of this table are present:

main.mysql_galera_hostgroups: in memory config.
main.runtime_mysql_galera_hostgroups: runtime config disk.
disk.mysql_galera_hostgroups: persistent config.

Galera monitor node flowchart

This chart describes the states in which the nodes are set depending of it’s current state and cluster changes:

Cluster monitor flowchart

This chart describes how the cluster state changes when a change is detected in one node, or when configuration is changed:

Node promotion order when using a single writer

Although the concept of promotion isn’t really present in a Galera cluster, you may want to configure a single writer for your Galera cluster. If this single writer becomes unavailable ProxySQL will need to identify a suitable candidate to replace this node in the writer_hostgroup. Each time this pseudo-promotion operation is required to happen, nodes are sorted based on certain fixed criteria and the first node is selected. The selection order equates to the following query 'SELECT * FROM runtime_mysql_servers ORDER BY weight DESC, hostname DESC, port DESC' (of course, this is applied only for nodes with read_only=0). The process of selecting a new writer is triggered when adding a new writer node to the cluster or a previously unavailable node is once again available.

SST considerations

Since v2.1.0 ProxySQL honors the Galera variable ‘wsrep_sst_donor_rejects_queries’. When true, this variable prevents a DONOR node from receiving client sessions during a blocking SST. ProxySQL will honor this behavior in the following scenario:

During a SST a DONOR node that is not a desync ‘read_only’ node where WSREP_LOCAL_RECV_QUEUE is bigger than ‘max_transactions_behind’, and ‘wsrep_sst_donor_rejects_queries’ is false, will continue operating normally. If this node is a ‘read_only’ node having WSREP_LOCAL_RECV_QUEUE bigger than ‘max_transactions_behind’, it will be moved to the ‘OFFLINE_HOSTGROUP’.

As previously stated, ProxySQL only prevents a node from being moved to the OFFLINE_HOSTGROUP when the previously described conditions holds and the node is in DONOR state. To further clarify this, after an SST, a DONOR node can transition into JOINED state before reaching the SYNC state, the speed of this transition depends on how fast the node can catch up with the cluster. ProxySQL won’t prevent any node in the JOINED state from being moved to the OFFLINE_HOSTGROUP.

Extra writers appear as SHUNNED

It’s normal that ProxySQL shows extra writers as SHUNNED in the writer hostgroup, this is expected behavior. ProxySQL always tries to keep transitions as smooth as possible for clients, it’s because of this, that instead of completely removing a server from the writer_hosgtroup, action that would kill all the current connections to that server, sets it to SHUNNED. The intention behind this behavior, is making the transition from one primary writer to another completely transparent to clients which have already running transactions in the current writer. This way running transactions wont be affected from a change in the state of the current primary writer.

Extra info

For a tutorial on how to get started into Galera configuration, please refer to this blogpost.

Galera Configuration