Proxysql configuration automatically overwritten

MySQL & MariaDB Percona Operator for MySQL
1

Im trying to adjust the weight of the pxc nodes but the configuration is automatically overwritten.

Query mysql_servers

mysql> select hostgroup_id,hostname,status,weight from mysql_servers order by hostgroup_id;
+--------------+-----------------------------------------------------------------------+--------+---------+
| hostgroup_id | hostname                                                              | status | weight  |
+--------------+-----------------------------------------------------------------------+--------+---------+
| 10           | cluster-dev-pxc-0.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000000 |
| 10           | cluster-dev-pxc-1.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 10           | cluster-dev-pxc-2.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 11           | cluster-dev-pxc-0.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000000 |
| 11           | cluster-dev-pxc-1.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 11           | cluster-dev-pxc-2.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 12           | cluster-dev-pxc-1.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 12           | cluster-dev-pxc-2.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
+--------------+-----------------------------------------------------------------------+--------+---------+

Update weights

mysql> update mysql_servers set weight=2000 where hostname='cluster-dev-pxc-2.cluster-dev-pxc.cluster-dev.svc.cluster.local' and hostgroup_id=11;
Query OK, 1 row affected (0.00 sec)

Query mysql_servers again

mysql> select hostgroup_id,hostname,status,weight from mysql_servers order by hostgroup_id;
+--------------+-----------------------------------------------------------------------+--------+---------+
| hostgroup_id | hostname                                                              | status | weight  |
+--------------+-----------------------------------------------------------------------+--------+---------+
| 10           | cluster-dev-pxc-0.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000000 |
| 10           | cluster-dev-pxc-1.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 10           | cluster-dev-pxc-2.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 11           | cluster-dev-pxc-0.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000000 |
| 11           | cluster-dev-pxc-1.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 11           | cluster-dev-pxc-2.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 12           | cluster-dev-pxc-1.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
| 12           | cluster-dev-pxc-2.cluster-dev-pxc.cluster-dev.svc.cluster.local | ONLINE | 1000    |
+--------------+-----------------------------------------------------------------------+--------+---------+

In proxysql logs the following keeps repeating every 5s

2023-11-22 19:01:46 [INFO] Received SAVE MYSQL QUERY RULES FROM RUNTIME command
2023-11-22 19:01:46 [INFO] Received SAVE MYSQL USERS FROM RUNTIME command
2023-11-22 19:01:46 [INFO] Received SAVE MYSQL VARIABLES FROM RUNTIME command
2023-11-22 19:01:47 [INFO] Received LOAD MYSQL USERS TO RUNTIME command
2023-11-22 19:01:47 [INFO] Received SAVE MYSQL USERS TO DISK command
2023-11-22 19:01:47 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command
2023-11-22 19:01:47 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command

I have read all the available docs but could not figure out how to adjust the configuration without getting restored in 5 sec.

Image: percona/percona-xtradb-cluster-operator:1.10.0-proxysql-8.0.25

Any help would be appreciated

2

Did you “load MySQL servers from the in-memory database to the runtime data structures” & “persist the MySQL servers from the in-memory database to the on-disk database” using the below commands just after running the update command?

like

mysql> update mysql_servers set weight=2000 where hostname='cluster-dev-pxc-2.cluster-dev-pxc.cluster-dev.svc.cluster.local' and hostgroup_id=11;

mysql> LOAD MYSQL SERVERS TO RUNTIME;
mysql> SAVE MYSQL SERVERS TO DISK;
3

No, I didnt and that was the problem.
If I run the update statement and immediately after the LOAD … and SAVE… commands it works fine. Thanks!

4

Another thing I noticed. After a node crashes pxc-0 will always be added to the writer group with weight=1000000
It’s similar to what’s described here, but I don’t have --writer-node defined in proxysql config or as command line argument