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:

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:

 

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.

Extra info

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