Uploaded image for project: 'ProxySQL Admin Scripts'
  1. ProxySQL Admin Scripts
  2. PSQLADM-12

WRITERS ARE READERS is not working and is confusing

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.4.10
    • Component/s: None
    • Labels:
      None

      Description

      According to documentation arg4 from scheduler is:

      WRITERS ARE READERS - If set to 1 (default), ONLINE nodes in the writer hostgroup will prefer not to be ONLINE in the reader hostgroup.

       

      This is actually confusing, 1 = true and 0 = false, so if I set it to true it means writers are not readers?

       

      While testing this feature, I cannot seem to make it work. My goal is to make .71 (my writer) to  also be part of reader hg:

      Having it set to 1:

       

      mysql> SELECT * FROM scheduler;
      
      +----+--------+-------------+------------------------------+------+------+------+------+---------------------------------------------+---------+
      
      | id | active | interval_ms | filename                     | arg1 | arg2 | arg3 | arg4 | arg5                                        | comment |
      
      +----+--------+-------------+------------------------------+------+------+------+------+---------------------------------------------+---------+
      
      | 10 | 1      | 3000        | /bin/proxysql_galera_checker | 10   | 11   | 1    | 1    | /var/lib/proxysql/proxysql_galera_check.log |         |
      
      +----+--------+-------------+------------------------------+------+------+------+------+---------------------------------------------+---------+
      
      1 row in set (0.00 sec)
      
      
      
      mysql> SELECT * FROM mysql_servers;
      
      +--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
      
      | hostgroup_id | hostname      | port | status | weight  | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
      
      +--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
      
      | 11           | 192.168.19.72 | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READ    |
      
      | 10           | 192.168.19.71 | 3306 | ONLINE | 1000000 | 0           | 1000            | 0                   | 0       | 0              | WRITE   |
      
      | 11           | 192.168.19.73 | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READ    |
      
      +--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
      
      3 rows in set (0.00 sec)
      
      Sun Dec 24 16:53:50 UTC 2017 ###### proxysql_galera_checker.sh SUMMARY ######
      
      Sun Dec 24 16:53:50 UTC 2017 Hostgroup writers 10
      
      Sun Dec 24 16:53:50 UTC 2017 Hostgroup readers 11
      
      Sun Dec 24 16:53:50 UTC 2017 Number of writers 1
      
      Sun Dec 24 16:53:50 UTC 2017 Writers are readers 1
      
      Sun Dec 24 16:53:50 UTC 2017 log file /var/lib/proxysql/proxysql_galera_check.log
      
      Sun Dec 24 16:53:50 UTC 2017 ###### HANDLE WRITER NODES ######
      
      Sun Dec 24 16:53:50 UTC 2017 --> Checking WRITE server 10:192.168.19.71:3306, current status ONLINE, wsrep_local_state 4
      
      Sun Dec 24 16:53:50 UTC 2017 server 10:192.168.19.71:3306 is already ONLINE: 1 of 1 write nodes
      
      Sun Dec 24 16:53:50 UTC 2017 ###### HANDLE READER NODES ######
      
      Sun Dec 24 16:53:51 UTC 2017 --> Checking READ server 11:192.168.19.72:3306, current status ONLINE, wsrep_local_state 4
      
      Sun Dec 24 16:53:51 UTC 2017 server 11:192.168.19.72:3306 is already ONLINE
      
      Sun Dec 24 16:53:51 UTC 2017 --> Checking READ server 11:192.168.19.73:3306, current status ONLINE, wsrep_local_state 4
      
      Sun Dec 24 16:53:51 UTC 2017 server 11:192.168.19.73:3306 is already ONLINE
      
      Sun Dec 24 16:53:51 UTC 2017 ###### SUMMARY ######
      
      Sun Dec 24 16:53:51 UTC 2017 --> Number of writers that are 'ONLINE': 1 : hostgroup: 10
      
      Sun Dec 24 16:53:51 UTC 2017 --> Number of readers that are 'ONLINE': 2 : hostgroup: 11
      
      Sun Dec 24 16:53:51 UTC 2017 ###### Not loading mysql_servers, no change needed ######
      

      Changing it to 0:

       

      mysql> UPDATE scheduler SET arg4 = 0;
      
      Query OK, 1 row affected (0.00 sec)
      
      
      
      mysql> LOAD SCHEDULER TO RUNTIME;
      
      Query OK, 0 rows affected (0.00 sec)
      
      
      
      mysql> SELECT * FROM mysql_servers;
      
      +--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
      
      | hostgroup_id | hostname      | port | status | weight  | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
      
      +--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
      
      | 11           | 192.168.19.72 | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READ    |
      
      | 10           | 192.168.19.71 | 3306 | ONLINE | 1000000 | 0           | 1000            | 0                   | 0       | 0              | WRITE   |
      
      | 11           | 192.168.19.73 | 3306 | ONLINE | 1000    | 0           | 1000            | 0                   | 0       | 0              | READ    |
      
      +--------------+---------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
      
      3 rows in set (0.00 sec)
      
      
      
      
      
      Sun Dec 24 16:55:12 UTC 2017 ###### proxysql_galera_checker.sh SUMMARY ######
      Sun Dec 24 16:55:12 UTC 2017 Hostgroup writers 10
      Sun Dec 24 16:55:12 UTC 2017 Hostgroup readers 11
      Sun Dec 24 16:55:12 UTC 2017 Number of writers 1
      Sun Dec 24 16:55:12 UTC 2017 Writers are readers 0
      Sun Dec 24 16:55:12 UTC 2017 log file /var/lib/proxysql/proxysql_galera_check.log
      Sun Dec 24 16:55:12 UTC 2017 ###### HANDLE WRITER NODES ######
      Sun Dec 24 16:55:12 UTC 2017 --> Checking WRITE server 10:192.168.19.71:3306, current status ONLINE, wsrep_local_state 4
      Sun Dec 24 16:55:12 UTC 2017 server 10:192.168.19.71:3306 is already ONLINE: 1 of 1 write nodes
      Sun Dec 24 16:55:12 UTC 2017 ###### HANDLE READER NODES ######
      Sun Dec 24 16:55:12 UTC 2017 --> Checking READ server 11:192.168.19.72:3306, current status ONLINE, wsrep_local_state 4
      Sun Dec 24 16:55:12 UTC 2017 server 11:192.168.19.72:3306 is already ONLINE
      Sun Dec 24 16:55:12 UTC 2017 --> Checking READ server 11:192.168.19.73:3306, current status ONLINE, wsrep_local_state 4
      Sun Dec 24 16:55:12 UTC 2017 server 11:192.168.19.73:3306 is already ONLINE
      Sun Dec 24 16:55:12 UTC 2017 ###### SUMMARY ######
      Sun Dec 24 16:55:12 UTC 2017 --> Number of writers that are 'ONLINE': 1 : hostgroup: 10
      Sun Dec 24 16:55:12 UTC 2017 --> Number of readers that are 'ONLINE': 2 : hostgroup: 11
      Sun Dec 24 16:55:12 UTC 2017 ###### Not loading mysql_servers, no change needed ######
      
      

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  kenn.takara Kenn Takara
                  Reporter:
                  marcelo.altmann Marcelo Altmann
                • Votes:
                  2 Vote for this issue
                  Watchers:
                  5 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 4 days, 3 hours
                    4d 3h