Not the answer you need?
Register and ask your own question!

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

xkatmaixkatmai EntrantCurrent User Role Supporter
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.

Comments

  • xkatmaixkatmai Entrant Current User Role Supporter
    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);
    
  • xkatmaixkatmai Entrant Current User Role Supporter
    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?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.