Could not able to add new query_rules and all traffic went to single writer

Hi @SlavaSarzhan and team, Greetings, I had successfully deployed the pxc-operator and pxc-db with proxysql using helm in k8s. And did do the sysbench test using oltp_read_write.lua script observed that all the reads & writes went to only one writer.
ProxySQLAdmin> select hostgroup,ConnUsed, MaxConnUsed, Queries from stats_mysql_connection_pool;
±----------±---------±------------±--------+
| hostgroup | ConnUsed | MaxConnUsed | Queries |
±----------±---------±------------±--------+
| 11 | 0 | 7 | 109148 |
| 11 | 0 | 0 | 0 |
| 11 | 0 | 0 | 0 |
| 10 | 0 | 1 | 1 |
| 10 | 0 | 0 | 0 |
| 10 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
±----------±---------±------------±--------+
and then i tried re-route my top 2 queries to hostgroup 10 which is reader and tried to add query rules for that as below but could not see any get added.

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES(13,1,‘mssandbox’,’^SELECT c FROM sysbenchdb WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES(14,1,‘mssandbox’,‘DISTINCT c FROM sysbenchdb’,10,1);
Query OK, 1 row affected (0.00 sec)
ProxySQLAdmin> LOAD MYSQL QUERY RULES TO RUNTIME;
ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘mssandbox’ ORDER BY rule_id;
Empty set (0.00 sec)

  1. Please help or suggest on how to divert the traffic to various and also how to add new query rules.
  2. i went through [K8SPXC-735] ProxySQL uses only one server for reads - Percona JIRA is it still ok to use percona xtradbcluster with proxysql in production based on that issue?

Thank you so much,
Ajay

1 Like

Hello Mind Posting below from ProxSQL:

select * from runtime_mysql_servers order by 1,5;
select username,active,default_hostgroup,max_connections from mysql_users;
select * from runtime_mysql_galera_hostgroups;
1 Like

Hi @mughees52, posted the responses below.

ProxySQLAdmin> select * from runtime_mysql_servers order by 1,5;
±-------------±----------------------------------------------±-----±----------±--------±--------±------------±----------------±--------------------±--------±---------------±--------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
±-------------±----------------------------------------------±-----±----------±--------±--------±------------±----------------±--------------------±--------±---------------±--------+
| 10 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | SHUNNED | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | SHUNNED | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
±-------------±----------------------------------------------±-----±----------±--------±--------±------------±----------------±--------------------±--------±---------------±--------+
8 rows in set (0.00 sec)

ProxySQLAdmin> select username,active,default_hostgroup,max_connections from mysql_users;
±-------------±-------±------------------±----------------+
| username | active | default_hostgroup | max_connections |
±-------------±-------±------------------±----------------+
| clustercheck | 1 | 11 | 10000 |
| monitor | 1 | 11 | 10000 |
| operator | 1 | 11 | 10000 |
| replication | 1 | 11 | 10000 |
| root | 1 | 11 | 10000 |
| xtrabackup | 1 | 11 | 10000 |
| mssandbox | 1 | 11 | 10000 |
| clustercheck | 1 | 11 | 10000 |
| monitor | 1 | 11 | 10000 |
| operator | 1 | 11 | 10000 |
| replication | 1 | 11 | 10000 |
| root | 1 | 11 | 10000 |
| xtrabackup | 1 | 11 | 10000 |
| mssandbox | 1 | 11 | 10000 |
±-------------±-------±------------------±----------------+
14 rows in set (0.00 sec)

ProxySQLAdmin> select * from runtime_mysql_galera_hostgroups;
±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+
| 11 | 12 | 10 | 13 | 1 | 1 | 1 | 100 | NULL |
±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+
1 row in set (0.00 sec)

1 Like

Hi @mughees52, i have added the user to the hg 10 but it was moved to 11 after running the tests.
INSERT INTO mysql_users (username,password,default_hostgroup,active) VALUES(‘msandbox’,‘msandbox’,10,1);

ProxySQLAdmin> select username,active,default_hostgroup,max_connections from mysql_users;
±-------------±------------------------------------------±-------±--------±------------------±---------------±--------------±-----------------------±-------------±--------±---------±----------------±--------+
| username | active | default_hostgroup | max_connections |
±-------------±------------------------------------------±-------±--------±------------------±---------------±--------------±-----------------------±-------------±--------±---------±----------------±--------+
| msandbox | 1 | 10 | 10000

test:
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-db=sysbenchdb --mysql-host=pxc-db-proxysql.pxc.svc.cluster.local --mysql-user=msandbox --mysql-password=msandbox --db-driver=mysql --threads=10 --tables=10 --skip-trx=true --table-size=1000 --time=50 --report-interval=10 --mysql-ignore-errors=1062 run

after test:

ProxySQLAdmin> select hostgroup,ConnUsed, MaxConnUsed, Queries from stats_mysql_connection_pool;
±----------±---------±------------±--------+
| hostgroup | ConnUsed | MaxConnUsed | Queries |
±----------±---------±------------±--------+
| 11 | 0 | 7 | 180832 |
| 11 | 0 | 0 | 0 |
| 11 | 0 | 0 | 0 |
| 10 | 0 | 1 | 1 |
| 10 | 0 | 0 | 0 |
| 10 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
±----------±---------±------------±--------+
8 rows in set (0.00 sec)

ProxySQLAdmin> select username,active,default_hostgroup,max_connections from mysql_users;
±-------------±-------±------------------±----------------+
| username | active | default_hostgroup | max_connections |
±-------------±-------±------------------±----------------+
| clustercheck | 1 | 11 | 10000 |
| monitor | 1 | 11 | 10000 |
| operator | 1 | 11 | 10000 |
| replication | 1 | 11 | 10000 |
| root | 1 | 11 | 10000 |
| xtrabackup | 1 | 11 | 10000 |
| mssandbox | 1 | 11 | 10000 |
| msandbox | 1 | 11 | 10000 |
| clustercheck | 1 | 11 | 10000 |
| monitor | 1 | 11 | 10000 |
| operator | 1 | 11 | 10000 |
| replication | 1 | 11 | 10000 |
| root | 1 | 11 | 10000 |
| xtrabackup | 1 | 11 | 10000 |
| mssandbox | 1 | 11 | 10000 |
| msandbox | 1 | 11 | 10000 |
±-------------±-------±------------------±----------------+
16 rows in set (0.00 sec)

make the weight in mysql_servers as same for all nodes . so proxysql can load balance equally :slight_smile:

I see pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local weight is 1000000

1 Like

Hi @mughees52 , which is not come by default? do we have to add manually every-time it changes?

1 Like

Check your query rules again. Run the two INSERTs, then SELECT from mysql_query_rules. Verify they are present. Then SAVE MYSQL QUERY RULES TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME;

Also, make sure they are proper regex. Keep them simple. “^SELECT” alone is fine.

1 Like

hey @matthewb, replicated the same steps as you mentioned. Please see below outputs for queries.
ProxySQLAdmin> select rule_id,active,username,match_digest,destination_hostgroup from mysql_query_rules;
±--------±-------±-------------±--------------------±----------------------+
| rule_id | active | username | match_digest | destination_hostgroup |
±--------±-------±-------------±--------------------±----------------------+
| 1 | 1 | clustercheck | ^SELECT.*FOR UPDATE | 11 |
| 2 | 1 | clustercheck | ^SELECT | 10 |
| 3 | 1 | monitor | ^SELECT.*FOR UPDATE | 11 |
| 4 | 1 | monitor | ^SELECT | 10 |
| 5 | 1 | operator | ^SELECT.*FOR UPDATE | 11 |
| 6 | 1 | operator | ^SELECT | 10 |
| 7 | 1 | replication | ^SELECT.*FOR UPDATE | 11 |
| 8 | 1 | replication | ^SELECT | 10 |
| 9 | 1 | root | ^SELECT.*FOR UPDATE | 11 |
| 10 | 1 | root | ^SELECT | 10 |
| 11 | 1 | xtrabackup | ^SELECT.*FOR UPDATE | 11 |
| 12 | 1 | xtrabackup | ^SELECT | 10 |
±--------±-------±-------------±--------------------±----------------------+
12 rows in set (0.00 sec)
ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘mssandbox’,’^SELECT.*FOR UPDATE’,11,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (14,1,‘mssandbox’,’^SELECT’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)

ProxySQLAdmin> select rule_id,active,username,match_digest,destination_hostgroup from mysql_query_rules;±--------±-------±-------------±--------------------±----------------------+
| rule_id | active | username | match_digest | destination_hostgroup |
±--------±-------±-------------±--------------------±----------------------+
| 1 | 1 | clustercheck | ^SELECT.*FOR UPDATE | 11 |
| 2 | 1 | clustercheck | ^SELECT | 10 |
| 3 | 1 | monitor | ^SELECT.*FOR UPDATE | 11 |
| 4 | 1 | monitor | ^SELECT | 10 |
| 5 | 1 | operator | ^SELECT.*FOR UPDATE | 11 |
| 6 | 1 | operator | ^SELECT | 10 |
| 7 | 1 | replication | ^SELECT.*FOR UPDATE | 11 |
| 8 | 1 | replication | ^SELECT | 10 |
| 9 | 1 | root | ^SELECT.*FOR UPDATE | 11 |
| 10 | 1 | root | ^SELECT | 10 |
| 11 | 1 | xtrabackup | ^SELECT.*FOR UPDATE | 11 |
| 12 | 1 | xtrabackup | ^SELECT | 10 |
| 14 | 1 | mssandbox | ^SELECT | 10 |
±--------±-------±-------------±--------------------±----------------------+
13 rows in set (0.00 sec)

rule_id number 13 was not added. but 14 got added. i will run the sysbench read and read_write scripts and will post the results here soon.

Thank you,

1 Like

Why did you load/save users when we are dealing with rules? Insert rule 13, select to verify, save to disk, load to run. Insert 14, select to verify, save, load.

2 Likes

Sorry my bad. did the right ones now. but still same. i did change weight of mysql servers but after a very few seconds it was moved back to original.

ProxySQLAdmin> update mysql_servers set weight=1;

Query OK, 8 rows affected (0.00 sec)

ProxySQLAdmin> load mysql servers to runtime;

Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> save mysql servers to disk;

ProxySQLAdmin> select * from mysql_servers;
±-------------±----------------------------------------------±-----±----------±-------±-------±------------±----------------±--------------------±--------±---------------±--------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
±-------------±----------------------------------------------±-----±----------±-------±-------±------------±----------------±--------------------±--------±---------------±--------+
| 11 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
±-------------±----------------------------------------------±-----±----------±-------±-------±------------±----------------±--------------------±--------±---------------±--------+
8 rows in set (0.00 sec)

after a moment it went back original:

ProxySQLAdmin> select * from mysql_servers;
±-------------±----------------------------------------------±-----±----------±-------±--------±------------±----------------±--------------------±--------±---------------±--------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
±-------------±----------------------------------------------±-----±----------±-------±--------±------------±----------------±--------------------±--------±---------------±--------+
| 11 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
±-------------±----------------------------------------------±-----±----------±-------±--------±------------±----------------±--------------------±--------±---------------±--------+
8 rows in set (0.00 sec)

and all the load again went to the one with high wighted writer. Could you please advise me to make the load distributed when using application-level users (not system-level users eg:root). (note: if i use the root user for test (sysbench read and write) then i could see the load is splitting between writer and readers)

Thank you,
Ajay

1 Like

@matthewb i could able to add query rules but it is not just after one attempt of (insert,verify,save and load) for one particular query, i had to repeat insert, verify, save and load more than 7 times at-least in order to stick one query into the table. is there any solution to make them stick into table after one attempt? please suggest. Thank you.

1 Like

Hi @ajay

What version of ProxySQL are you on? There is an open bug related to 2.3.2 where read/write splitting is not working as expected - perhaps this applies to you as well

1 Like

HI @Michael_Coburn,
bash-4.4$ proxysql --version
ProxySQL version 2.0.18-percona-1.1, codename Truls.

Sorry, i might confused you and team here, because of mixing up issues above regarding weight and query rules.
Regarding query rules: As per @renecannao comments on https://github.com/sysown/proxysql/issues/941, by default “ProxySQL is configured to send all the traffic to the master” and that is what happened in my case too. (which is answered my question in the 2nd part of this issue’s title statement).
And here i need to add query rules to split the traffic, if there is a query rule present in the mysql_query_table then queries are splitting between my read and write hostgroups regardless of user(whether system-level/unprivileged).
So, my current question on query rules here is :i could able to add query rules through proxysql admin interface but it is not just after one attempt of (insert,verify,save and load) for one particular query, i had
to repeat insert, verify, save and load more than 7 times at-least in order to stick one query into the
table. is there any solution to make them stick into table after single attempt of (insert,verify,save and
load)?

1 Like

below is the reference for my problem for which i asked a question in my previous/above comment:

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,’^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
Empty set (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,’^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,’^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
ERROR 1045 (28000): ProxySQL Admin Error: UNIQUE constraint failed: mysql_query_rules.rule_id
ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
±------------------------------------±----------------------+
| match_digest | destination_hostgroup |
±------------------------------------±----------------------+
| ^SELECT c FROM sbtest3 WHERE id=?$ | 10 |
±------------------------------------±----------------------+
1 row in set (0.00 sec)

ProxySQLAdmin> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)

ProxySQLAdmin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
Empty set (0.00 sec)

ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
Empty set (0.00 sec)

ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
Empty set (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,’^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
Empty set (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,’^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,’^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,’^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
ERROR 1045 (28000): ProxySQL Admin Error: UNIQUE constraint failed: mysql_query_rules.rule_id
ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
±------------------------------------±----------------------+
| match_digest | destination_hostgroup |
±------------------------------------±----------------------+
| ^SELECT c FROM sbtest3 WHERE id=?$ | 10 |
±------------------------------------±----------------------+
1 row in set (0.00 sec)

ProxySQLAdmin> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)

ProxySQLAdmin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)

ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
Empty set (0.00 sec)

regardless of what match_digest (^SELECT.*FOR UPDATE, ^SELECT) i use the result is same.

1 Like

Look in /var/lib/proxysql and view the logs. Look for errors when you save rules and load them.

1 Like

hi @matthewb ,
bash-4.4$ ls
lost+found proxysql-ca.pem proxysql-cert.pem proxysql-key.pem proxysql.db proxysql_stats.db

i did not find any .log file here in this directory. i tried googling for how to view proxysql logs but keep on ending at Query Logging - ProxySQL which i believe useful for me in my next steps once i could able to add query rules by single attempt.

could you please suggest me how to proceed here.

thank you so much.

1 Like

Hi @matthewb, please see attached logs file. I’ve run insert, verify, save and load for 15min range and displayed logs were copied to the below file.
proxysql.log (428.1 KB).

i believe below logs are specific to this issue from proxysql.log:
2022-02-07 19:01:50 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer pxc-db-proxysql-2.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032, version 1845, epoch 1644260510, checksum 0x2450519F267D1DE4 . Not syncing yet …

2022-02-07 19:01:50 [INFO] Cluster: detected a peer pxc-db-proxysql-2.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032 with mysql_query_rules version 1845, epoch 1644260510, diff_check 1. Own version: 331, epoch: 1644260497. Proceeding with remote sync

2022-02-07 19:01:50 [INFO] Cluster: detected a peer pxc-db-proxysql-2.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032 with mysql_query_rules version 1845, epoch 1644260510, diff_check 2. Own version: 331, epoch: 1644260497. Proceeding with remote sync

2022-02-07 19:01:50 [INFO] Cluster: detected peer pxc-db-proxysql-2.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032 with mysql_query_rules version 1845, epoch 1644260510

2022-02-07 19:01:50 [INFO] Cluster: Fetching MySQL Query Rules from peer pxc-db-proxysql-2.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032 started
2022-02-07 19:01:50 [INFO] Cluster: Fetching MySQL Query Rules from peer pxc-db-proxysql-2.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032 completed

2022-02-07 19:01:50 [INFO] Cluster: Loading to runtime MySQL Query Rules from peer pxc-db-proxysql-2.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032

2022-02-07 19:01:50 [INFO] Cluster: Saving to disk MySQL Query Rules from peer pxc-db-proxysql-2.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032

2022-02-07 19:01:51 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer pxc-db-proxysql-1.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032, version 1845, epoch 1644260510, checksum 0x2450519F267D1DE4 . Not syncing yet …

2022-02-07 19:01:51 [INFO] Cluster: checksum for mysql_query_rules from peer pxc-db-proxysql-1.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032 matches with local checksum 0x2450519F267D1DE4 , we won’t sync.

2022-02-07 19:01:51 [INFO] Cluster: detected a new checksum for mysql_query_rules from peer pxc-db-proxysql-0.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032, version 1530, epoch 1644260510, checksum 0x2450519F267D1DE4 . Not syncing yet …

2022-02-07 19:01:51 [INFO] Cluster: checksum for mysql_query_rules from peer pxc-db-proxysql-0.pxc-db-proxysql-unready.pxc.svc.cluster.local:6032 matches with local checksum 0x2450519F267D1DE4 , we won’t sync.

2022-02-07 19:01:55 [INFO] Received SAVE MYSQL SERVERS FROM RUNTIME command

1 Like

Posting my global variables related to query rules:

| admin-cluster_check_interval_ms | 200 |
| admin-cluster_check_status_frequency | 100 |
| admin-cluster_mysql_query_rules_diffs_before_sync | 1 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_diffs_before_sync | 1 |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_users_diffs_before_sync | 1 |
| admin-cluster_mysql_users_save_to_disk | true |
| admin-cluster_password | insecure-proxyadmin-password |
| admin-cluster_proxysql_servers_diffs_before_sync | 1 |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-cluster_username | proxyadmin

I’ve changed the values to default value as well, but the query rules are still not syncing after a one attempt. please help me here.

Thank you,

1 Like