Uploaded image for project: 'Percona Operator for MySQL based on Percona XtraDB Cluster'
  1. Percona Operator for MySQL based on Percona XtraDB Cluster
  2. K8SPXC-553

Change default configuration of ProxySQL to WRITERS_ARE_READERS=yes


    • Improvement
    • Status: Done
    • Medium
    • Resolution: Done
    • None
    • 1.7.0
    • None
    • None


      When a 3-node PXC cluster loses 2 nodes and has to handle traffic with a single node remaining, ProxySQL's (v.2) default configuration using the operator is of writer_is_also_reader=2 (from mysql_galera_hostgroups table), where:

      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.

      means there are no nodes left/added to the readers hostgroup.

      What could be done differently is to add a check for ProxySQL to verify the readers hostgroup, and when it finds it is empty and there is still one node left in the cluster (writer), to automatically include it in the readers hostgroup to handle reads.

      The suggestion, proposed by juan.arruti, is to change the default configuration as to make writer_is_also_reader=1 to avoid requiring manual intervention from a DBA for the cluster to continue to serve read traffic when the cluster starts to operate with a single node. Considering the Operator use proxysql-admin  tool to configure ProxySQL, it would be necessary to set WRITERS_ARE_READERS="yes" on proxysql-admin.cnf configuration file:

      # Determines if a node should be added to the reader hostgroup if it has
      # been promoted to the writer hostgroup.
      # If set to 'yes', then all writers (including backup-writers) are added to
      # the read hostgroup.
      # If set to 'no', then none of the writers (including backup-writers) are added.
      # If set to 'backup', then only the backup-writers will be added to
      # the read hostgroup.
      export WRITERS_ARE_READERS="backup"

      The caveat here is the fact ProxySQL is not currently fully supporting Galera clusters as it should, notably not honoring weights attributed to nodes following a change in the cluster hierarchy, as demonstrated by Marco Tusa in the blog post Support for Percona XtraDB Cluster in ProxySQL (Part One).




            slava.sarzhan Slava Sarzhan
            fernando.laudares@percona.com Fernando Laudares
            0 Vote for this issue
            7 Start watching this issue



              Time Tracking

                Original Estimate - Not Specified
                Not Specified
                Remaining Estimate - Not Specified
                Not Specified
                Time Spent - 2 hours

                Smart Checklist