ProxySQL support for Galera

How to enable support for Galera

To enable support for Galera it’s necessary to:

Launch ProxySQL with monitoring enabled (without providing the -M command line flag).
Configure properly the table mysql_galera_hostgroups.

Configure ProxySQL

Global variables

Global variables to control timeout and interval check:

    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)

Configuration table

In ProxySQL’s Admin, there is a table that holds the definition for the Galera hostgroups that are required to be managed.

    mysql> SHOW CREATE TABLE mysql_galera_hostgroups\G
    *************************** 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)

Column Name	Description
writer_hostgroup	The id of the hostgroup that will contain all the members that are writer.
backup_writer_hostgroup	If the group is running in multi-primary mode, there are multi writers (read_only=0) but if the amount of these writer is larger than the max_writers, the extra nodes are located in this backup writer group.
reader_hostgroup	The id of the hostgroup that will contain all the members that are reader: nodes that have read_only=1 will be assigned to this hostgroup.
offline_hostgroup	When ProxySQL’s monitoring determines a host is offline, it will be put such host into the offline_hostgroup.
active	Boolean value (0 or 1), when enabled, ProxySQL monitors the hostgroups and moves accordingly the server in the appropriate hostgroups.
max_writers	Limit the amount of nodes in the writer hostgroup: nodes in excess of this value will be put into the backup_writer_hostgroup.
writer_is_also_reader	Flag with three possible values: (0, 1, 2). – writer_is_also_reader=0: nodes in the writer_hostgroup and in the backup_writer_hostgroup wont be in reader hostgroup. – writer_is_also_reader=1: nodes in writer_hostgroup and backup_writer_hostgroup are also in reader hostgroup. – writer_is_also_reader=2 : nodes in backup_writer_hostgroup are also in reader_hostgroup. We can also define the action taken for the nodes in terms of the `READ_ONLY` flag: – writer_is_also_reader=0: Nodes with `READ_ONLY=0` are removed for reader_hostgroup placed in hostgroups backup_writer_hostgroup and writer_hostgroup as corresponds. – writer_is_also_reader=1: The nodes in the writer_hostgroup and in the backup_writer_hostgroup are also in the reader_hostgroup. – writer_is_also_reader=2: The nodes in the backup_writer_hostgroup are also in the reader hostgroup.
max_writers	Limit the amount of nodes in the writer hostgroup: nodes in excess of this value will be put into the backup_writer_hostgroup.

To conform with ProxySQL’s Admin 3 layers configuration system, 3 tables exist:

mysql_galera_hostgroups: In memory config.
runtime_mysql_galera_hostgroups: Runtime config.
disk.mysql_galera_hostgroups: Persistent config.

Galera monitor node flowchart

This chart describes the conditions and final monitoring actions taken over a node based on its current state and current cluster state:

Cluster monitor cluster 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

Each time a promotion operation is required to happen, nodes are sorted based on that fixed criteria, and then, the first node is selected. The selection order used when promoting a node between hostgroups is 'ORDER BY weight DESC, hostname DESC, port DESC'. This is for example the selection order used when a new writer node enters the cluster (or it’s again available) and the process of selecting a new writer takes place.

SST considerations

Since v2.1.0 ProxySQL honors 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 to operate 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.

Writers selection

The nodes to be placed as writers in the writer hostgroup are deterministically selected from the currently available cluster nodes based on weight + hostname + port. You can check how this selection is exercised in the different flowcharts. The same selection is used when the node is going to be set initially in a target hostgroup, or for deciding whether a server already present in a hostgroup should be replaced or not.

Writers selection – Failovers

The server to replace a writer during a failover is selected using the same previously described policy. This is true for when the server is going to be initially replaced to the failover, and also when the server original status is recovered. The event of the previous failover has no impact in the selection of the next target writer.

Deterministic monitoring actions based on current cluster state is a common monitoring policy explained in more detail in the FAQ.

Extra writers with SHUNNED status

It’s normal for extra writers to appear as SHUNNED in the writer hostgroup, this is expected behavior. For example, assuming max_writers=1, this is a normal state that will take place after a failover for the replacement writer. After the original writer status is recovered, the deterministic selection (if no config has been changed) will target the previous writer as the new candidate writer, thus imposing the SHUNNED state on the current one.

This status is imposed as an effort for keeping transitions as smooth as possible for clients. If instead the server was completely remove from the writer_hosgtroup, that would kill all the current connections to that server, instead setting it as SHUNNED preserves currently opened connections. This way the transition from one primary writer to another is completely transparent to clients which have already running transactions in the current writer.

Extra info

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

Galera Configuration