Singlewrite question (COMMIT/INSERT/DELETE - not writes?)

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hey guys so i have a question: i am running a 3 nodes cluster in singlewrite mode.

The output for the command counters is this:

SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
+-------------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| Command | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+-------------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| COMMIT | 20865606 | 4623 | 0 | 117 | 0 | 4010 | 433 | 63 | 0 | 0 | 0 | 0 | 0 | 0 |
| DELETE | 1288116 | 14 | 0 | 2 | 8 | 0 | 0 | 0 | 0 | 4 | 0 | 0 | 0 | 0 |
| INSERT | 2869945 | 2866 | 0 | 945 | 1083 | 806 | 24 | 7 | 1 | 0 | 0 | 0 | 0 | 0 |
| ROLLBACK | 1934 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| SELECT | 1151298943 | 403262 | 4 | 325913 | 37732 | 32645 | 2269 | 2771 | 423 | 1295 | 158 | 45 | 5 | 2 |
| START_TRANSACTION | 1398141 | 4624 | 0 | 4591 | 24 | 9 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| UPDATE | 1011583 | 1833 | 0 | 1128 | 597 | 106 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| SHOW | 4727 | 4 | 0 | 0 | 1 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
8 rows in set (0.00 sec)

However when looking at the rule hits it shows all queries going to the second hostgroup - the reader group:

SELECT rule_id, hits, destination_hostgroup hg FROM mysql_query_rules NATURAL JOIN stats_mysql_query_rules;
+---------+--------+----+
| rule_id | hits | hg |
+---------+--------+----+
| 1 | 0 | 10 |
| 2 | 242624 | 11 |
+---------+--------+----+
2 rows in set (0.00 sec)

I am using the default query rules:

lbSQLAdmin> select * from mysql_query_rules;
+---------+--------+--------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
+---------+--------+--------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| 1 | 1 | User | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | 1 | User | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 11 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
+---------+--------+--------------+------------+--------+-------------+------------+------------+--------+---------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
2 rows in set (0.00 sec)

So i should be adding query rules for commit/delete/insert etc to route them to hg 10 (the writer group) ? i thought that by using the proxysql-admin tool the setup would be done automatically.

2

Hey guys, just wanted to give a quick update: I have modified the query rules like below and things work. There’s just 1 tiny question left. Am I doing the regex right for the queries 5 7 9 and 11 ?

(1,1,'user','^COMMIT',10,1),
(2,1,'user','^DELETE',10,1),
(3,1,'user','^INSERT',10,1),
(4,1,'user','^ROLLBACK',10,1),
(5,1,'user','^START .* TRANSACTION$',10,1),
(6,1,'user','^UPDATE',10,1),
(7,1,'user','^CREATE .* TEMPORARY$',10,1),
(8,1,'user','^SAVEPOINT',10,1),
(9,1,'user','^SET',10,1),
(9,1,'user','^UNLOCK .* TABLES$',10,1),
(10,1,'user','^SELECT',11,1);
(11,1,'user','^SHOW .* TABLE STATUS$',11,1);
(12,1,'user','^SHOW',11,1);
(13,1,'user','^UNKNOWN',11,1);
3

i enabled the rules today, but it still looks like any queries that have a space in the commands simply don’t get parsed. am i doing the regex wrong?

INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'user','^COMMIT',10,1);
(2,1,'user','^DELETE',10,1),
(3,1,'user','^INSERT',10,1),
(4,1,'user','^ROLLBACK',10,1),
(5,1,'user','^START .* TRANSACTION$',10,1),
(6,1,'user','^UPDATE',10,1),
(7,1,'user','^CREATE .* TEMPORARY$',10,1),
(8,1,'user','^SAVEPOINT',10,1),
(9,1,'user','^SET',10,1),
(10,1,'user','^UNLOCK .* TABLES$',10,1),
(11,1,'user','^SELECT',11,1),
(12,1,'user','^SHOW .* TABLE .* STATUS$',11,1),
(13,1,'user','^SHOW',11,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK; # if you want this change to be permanent

rules 5 7 10 and 12 get no hits:

SELECT rule_id, hits, destination_hostgroup hg FROM mysql_query_rules NATURAL JOIN stats_mysql_query_rules;
+---------+--------+----+
| rule_id | hits | hg |
+---------+--------+----+
| 1 | 4382 | 10 |
| 2 | 88 | 10 |
| 3 | 1791 | 10 |
| 4 | 211 | 10 |
| 5 | 0 | 10 |
| 6 | 13352 | 10 |
| 7 | 0 | 10 |
| 8 | 1 | 10 |
| 9 | 1053 | 10 |
| 10 | 0 | 10 |
| 11 | 668617 | 11 |
| 12 | 0 | 11 |
| 13 | 1053 | 11 |
+---------+--------+----+
13 rows in set (0.00 sec)

can anyone point me in the right direction by any chance or tell me what i am doing wrong?