[ProxySQL]Change of weight value on mysql_servers table

[Young_Heon.Kim]

Hi
I was set up PXC & ProxySQL for some testing.
I made host group 0 that included host1 and host2.
And I would like to use these servers like this:
host1 : primary for write transaction
host2 : secondary for write transaction

So I expected that host1 processes most of write transaction, and host2 processes write transaction if host1 is down.
And then I expected host1 processes write transaction again, if host1 is recovered.

I set mysql_server table like this.
±-------------±-------------±-------±----------+
| hostgroup_id | hostname | status | weight |
±-------------±-------------±-------±----------+
| 0 | host1 | ONLINE | [COLOR=#FF0000]1000000 |
| 0 | host2 | ONLINE | [COLOR=#FF0000]1000 |
±-------------±-------------±-------±----------+

It is working well at first as I expect.
But if host1 is stop and start, weight value of host1 is changed default value(1000).
±-------------±-------------±-------±-------+
| hostgroup_id | hostname | status | weight |
±-------------±-------------±-------±-------+
| 0 | host1 | ONLINE | [COLOR=#FF0000]1000 |
| 0 | host2 | ONLINE | [COLOR=#FF0000]1000 |
±-------------±-------------±-------±-------+

How can I do not to change weight value of host1 ?

[krissfr]

Hi, i just want to report that i have the same issue with a 3 node pxc and proxySQL.
ProxySQL version is 1.3.7-1-1 (from percona repo).

Does it come from the proxysql_galera_checker script that reload the mysql_server table without passing the weight argument ?

Can an expert from Percona look at this issue ?

thanks

[Reiner030]

Hi, since the solution is yet not written here and I found this page in some research for server weight min/max values…

To activate the weight you had to query also:

LOAD MYSQL SERVERS TO RUNTIME;

and to make them reboot/restart save with:

SAVE MYSQL SERVERS TO DISK;

Same also to mysql query rules, mysql users, global_variables (for LOAD/SAVE sadly referenced as “MYSQL VARIABLES” which isn’t good documented).

https://github.com/sysown/proxysql/wiki/Main-(runtime)#runtime-tables
https://github.com/sysown/proxysql/wiki/DISK-(disk)

[lorraine.pocklington]

Hey there Reiner030 - thank you for taking the time find this information and to update this post. Appreciated!

[ramesh.sivaraman]

Hi Young Heon.Kim / krissfr / Reiner030 ,

If you install proxysql from percona repo you will get proxysql-admin tool to configure your nodes in ProxySQL database.
proxysql-admin tool support two modes. singlewrite mode and loadbal mode.

With singlewrite mode, one node will be writer node and remaining nodes will act as reader nodes. When writer node goes offline the scheduler scripts (proxysql_galera_checker and proxysql_node_monior) will promote one of the reader nodes as the writer. We can also control a priority order of what host is most desired to be the writer at any given time using host priority file.

Please go through this doc to get more info : https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html#automatic-configuration