Question about proxysql weighting

I jsut setup a dev instance running mariadb 10.5 galera cluster and using proxysql 2.0.14 on rhel 8. I setup the system in the mysql_galera_hostgroups setup.

Hostgroup 1 I made the writer, 2 the reader, 3 the backup writer. I also enabled writer is a reader.

I then added machines to them

  1. servera

  2. serverb,serverc

weights 1,1

  1. serverb,serverc

weights 10000,1000

The issue I am having is when I look at the runtime_mysql_servers I am seeing all the weights based off the backup writer hostgroup weights and not the reader weights,

Did me adding a hostgroup 3 override hostgroup 2?

Admin> SELECT * FROM mysql_galera_hostgroups;

±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+

| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |

±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+

| 1 | 3 | 2 | 4 | 1 | 1 | 1 | 0 | NULL |

±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+

1 row in set (0.000 sec)

Admin> select hostgroup_id, comment, hostname, status, weight from runtime_mysql_servers;

±-------------±--------±-------------±--------±--------+

| hostgroup_id | comment | hostname | status | weight |

±-------------±--------±-------------±--------±--------+

| 1 | | 10.76.10.222 | SHUNNED | 1000 |

| 3 | | 10.76.10.221 | ONLINE | 10000 |

| 3 | | 10.76.10.222 | ONLINE | 1000 |

| 2 | | 10.76.10.221 | ONLINE | 10000 |

| 2 | | 10.76.10.220 | ONLINE | 1000000 |

| 2 | | 10.76.10.222 | ONLINE | 1000 |

| 1 | | 10.76.10.221 | SHUNNED | 10000 |

| 1 | | 10.76.10.220 | ONLINE | 1000000 |

±-------------±--------±-------------±--------±--------+

8 rows in set (0.003 sec)

proxy_mysql_server { ‘10.76.10.220:3306-1’:

hostname => ‘10.76.10.220’,

port => 3306,

hostgroup_id => 1,

weight => 1000000,

}

proxy_mysql_server { ‘10.76.10.221:3306-2’:

hostname => ‘10.76.10.221’,

port => 3306,

hostgroup_id => 2,

weight => 1,

}

proxy_mysql_server { ‘10.76.10.222:3306-2’:

hostname => ‘10.76.10.222’,

port => 3306,

hostgroup_id => 2,

weight => 1,

}

proxy_mysql_server { ‘10.76.10.221:3306-3’:

hostname => ‘10.76.10.221’,

port => 3306,

hostgroup_id => 3,

weight => 10000,

}

proxy_mysql_server { ‘10.76.10.222:3306-3’:

hostname => ‘10.76.10.222’,

port => 3306,

hostgroup_id => 3,

weight => 1000,

}

Should also point out when I pull hostgroup 1 and 2 only from mysql_servers it shows the correct weight in there:

±-------------±-------------±-------±--------±--------+

| hostgroup_id | hostname | status | weight | comment |

±-------------±-------------±-------±--------±--------+

| 1 | 10.76.10.220 | ONLINE | 1000000 | |

| 2 | 10.76.10.220 | ONLINE | 1 | |

| 2 | 10.76.10.221 | ONLINE | 1 | |

| 2 | 10.76.10.222 | ONLINE | 1 | |

±-------------±-------------±-------±--------±--------+

@Thaylin Please use the formatting features of the forum so that your console output is readable.

If you add a server to the backup_writer with weight X, then, if that host gets moved into reader or writer group, the original weight X will be used.

Sorry for the formatting. So I should set all the weights to 1? I assumed I needed to weight the writers.

mysql_servers will always show you what you inserted. That data is not affected by the running config. runtime_mysql_servers will get modified by proxysql as things change. ProxySQL will never select more than 1 writer at a time in this mode so I do not think it is necessary to weight the writers.

thank you