Feature request: Writer is not reader unless there is only 1 node left

[michaeldg]

Hello,

I would like to be able to configure Galera and Replication hostgroups to redirect read traffic to different hosts (and not to the writer) if there are enough readers available. Curently, the reader hostgroup is empty if there is only 1 node left in the cluster (and writer_is_also_reader = 0 or 2).

Currently, there is reader_is_also_writer=1 to always add the writer to the reader hostgroup,
Or there is reader_is_also_writer=2 to add backup writers to the reader hostgroup,
or there is reader_is_also_writer=0 to not add (backup) writer(s) to the reader hostgroup.

If writer_is_also_reader=0, the reader host group would have no hosts available if there is only 1 node left in the cluster. This feature request is to request a parameter in the galera, group replication and asynchronous replication host groups to specify the number of reader nodes that are desired. For example, when mysql_galera_hostgroups.minimum_readers (default 0) is higher then 0, and that amount of nodes is not in the reader host group, the (backup) writer node(s) are added to the reader host group as well.

Alternatively, a 4th option for writer_is_also_reader could be implemented:
reader_is_also_writer=3 to add the writer to the reader host group, when the reader host group is empty.

This new feature would allow users to configure their 3-node cluster for faster writes when the cluster is fully operational. When the cluster is degraded due to issues, this feature would allow the application to keep running on 1 node.

Thank you for taking this into consideration,
Michaël

[michaeldg]

I thought this was already possible and opened a documentation request about it in #3685. From that ticket it seams such functionality is not currently possible in ProxySQL.

[TDYK]

@michaeldg Hello! Please, check, if your installation has a galera scheduler in your package - in Debian it is /usr/share/proxysql/tools/proxysql_galera_checker.sh. Update it according to your admin user/pass etc.

Configure your proxysql instance:

select * from scheduler \G
*************************** 1. row ***************************
         id: 1
     active: 1
interval_ms: 1000
   filename: /usr/share/proxysql/tools/proxysql_galera_checker.sh
       arg1: 200
       arg2: 300
       arg3: 1
       arg4: 0
       arg5: /var/log/proxysql/proxysql-scheduler.log
    comment: 
    
select * from mysql_galera_hostgroups \G
*************************** 1. row ***************************
       writer_hostgroup: 200
backup_writer_hostgroup: 400
       reader_hostgroup: 300
      offline_hostgroup: 1
                 active: 1
            max_writers: 1
  writer_is_also_reader: 1
max_transactions_behind: 100
                comment: 

select * from global_variables where variable_name='mysql-monitor_enabled' \G 
*************************** 1. row ***************************
 variable_name: mysql-monitor_enabled
variable_value: false

select hostgroup_id, hostname, weight from mysql_servers where hostgroup_id in (200, 300);
+--------------+-----------------------------+--------+
| hostgroup_id | hostname                    | weight |
+--------------+-----------------------------+--------+
| 200          | DB-01 | 1000   |
| 200          | DB-02| 995    |
| 200          | DB-03| 990    |
| 200          | DB-04| 985    |
| 200          | DB-05| 980    |
| 300          | DB-01| 100    |
| 300          | DB-02| 995    |
| 300          | DB-03| 990    |
| 300          | DB-04| 985    |
| 300          | DB-05| 980    |
+--------------+-----------------------------+--------+

This is not ideal in balancing read requests, but you always have 1 writer and, at least, one reader node.

[JavierJF]

Hi @michaeldg,

ProxySQL already offers a mode which provides almost equivalent functionality that the one that is being requested here, this is, the combination of setting max_writers=1, together with writer_is_also_reader=1. Normally you don't want to exclude the writer from the reader hostgroup, specially if you are already considering using it as a backup writer. This specific question has been included in the FAQ because it's a relatively common scenario.

Because the previously mentioned I don't see clear benefits for this FR for the general case.

Thanks for the feedback, I will close the issue. Regards, Javier.

[JavierJF]

Hi @TDYK,

there is no need for the proxysql_galera_checker.sh script for monitoring the Galera cluster, ProxySQL already offers native Galera monitoring. This mode is extensively documented here.

Thank you, Javier.

[TDYK]

@JavierJF Hello! Yes, that is why i mentioned "mysql-monitor_enabled: false". Sometimes that is a strict policy to not include writer node in reader/backup reader list. I totally agree it is not a point for "feature request".

[JavierJF]

Hi @TDYK,

I see, thanks for the clarification and your comments. I get the mention to the scheduler script now. Thanks!