I'm newbie with proxysql, this my enviroment: Centos 7 , proxysql-1.4.13 . I define 2 host group ids : host group id 2 for mysql server that can write , host group id 3 for mysql server that can read.
All queries that begin with insert, update, delete, alter should be routed to host group id 2.
All queries that begin with select should be routed to host group id 3. So here are my rules :
Admin> select rule_id,active,digest,match_digest,destination_hostgroup,flagIN,flagOUT,next_query_flagIN,sticky_conn,apply from mysql_query_rules;
+---------+--------+--------------------+--------------+-----------------------+--------+---------+-------------------+-------------+-------+
| rule_id | active | digest | match_digest | destination_hostgroup | flagIN | flagOUT | next_query_flagIN | sticky_conn | apply |
+---------+--------+--------------------+--------------+-----------------------+--------+---------+-------------------+-------------+-------+
| 101 | 1 | NULL | ^insert | 2 | 0 | NULL | NULL | NULL | 1 |
| 102 | 1 | NULL | ^update | 2 | 0 | NULL | NULL | NULL | 1 |
| 103 | 1 | NULL | ^delete | 2 | 0 | NULL | NULL | NULL | 1 |
| 104 | 1 | NULL | ^alter | 2 | 0 | NULL | NULL | NULL | 1 |
| 105 | 1 | NULL | ^select | 3 | 0 | NULL | NULL | NULL | 1 |
+---------+--------+--------------------+--------------+-----------------------+--------+---------+-------------------+-------------+-------+
And they works fine. By the way, are these rules ok ? Should I replace match_digest by match_pattern ?
However , my application has 1 feature that insert data into a table (create booking) then select (almost immediately) new data from that table . So if select query fails (because new data has not replicated to host group id 3 yet), application feature will run wrong.
I want to route the select query right after insert query to host group id 2 so it will get new data successfully.
I read proxysql document https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_query_rules and this discussion https://github.com/sysown/proxysql/pull/825 , I think this is solution for me ,isn't it ? I still don't understand about these flagIN,flagOUT,next_query_flagIN,sticky_conn stuff clearly but I will give it a try.
I know the insert query digest is 0xCDD6DB677604AFA7
The select query digest is 0x0DCD2E8ADF6A66CB Then I add 2 new rules:
Admin> select rule_id,active,digest,match_digest,destination_hostgroup,flagIN,flagOUT,next_query_flagIN,sticky_conn,apply from mysql_query_rules;
+---------+--------+--------------------+--------------+-----------------------+--------+---------+-------------------+-------------+-------+
| rule_id | active | digest | match_digest | destination_hostgroup | flagIN | flagOUT | next_query_flagIN | sticky_conn | apply |
+---------+--------+--------------------+--------------+-----------------------+--------+---------+-------------------+-------------+-------+
| 1 | 1 | 0xCDD6DB677604AFA7 | NULL | 2 | 0 | NULL | 1 | 1 | 1 |
| 2 | 1 | 0x0DCD2E8ADF6A66CB | NULL | 2 | 1 | NULL | NULL | NULL | 1 |
| 101 | 1 | NULL | ^insert | 2 | 0 | NULL | NULL | NULL | 1 |
| 102 | 1 | NULL | ^update | 2 | 0 | NULL | NULL | NULL | 1 |
| 103 | 1 | NULL | ^delete | 2 | 0 | NULL | NULL | NULL | 1 |
| 104 | 1 | NULL | ^alter | 2 | 0 | NULL | NULL | NULL | 1 |
| 105 | 1 | NULL | ^select | 3 | 0 | NULL | NULL | NULL | 1 |
+---------+--------+--------------------+--------------+-----------------------+--------+---------+-------------------+-------------+-------+
They work fine, the select query right after insert query is route to host group id 2 so it gets new data successfully and application feature runs ok.
But am I doing right ? I'm confused because stats_mysql_query_rules result: Before application feature run:
Admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1 | 20 |
| 2 | 20 |
| 101 | 33 |
| 102 | 0 |
| 103 | 2 |
| 104 | 0 |
| 105 | 903 |
+---------+------+
After application feature run (10 bookings):
Admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1 | 30 |
| 2 | 30 |
| 101 | 43 |
| 102 | 0 |
| 103 | 2 |
| 104 | 0 |
| 105 | 1313 |
+---------+------+
Why rule_id 101 hits rate increase from 33 --> 43 ? So rule_id 101 (match_digest ^insert) also match insert query in application feature ? Does it mean I'm doing wrong ?
