ProxySQL Series: Mirroring MySQL Queries

Apr 13, 2018

Mins to Read

All

This is our third blog in the ProxySQL Series

This blog post explains one of the very important use case of ProxySQL called Mirroring.

Mirroring in ProxySQL is not an equivalent to MySQL replication. Also it does not guarantee data consistency across the servers but still there are a few cases where this feature of ProxySQL can take advantage.

Let us consider we have Master-Slave replication running behind ProxySQL with MySQL 5.6 Writes and Reads are served by Master and Slave respectively. For more information kindly refer previous blog in series of ProxySQL.

In this post we will cover how we can test our production queries before going for database upgrade to newer version(5.7/8.0) or with different MySQL forks ( MariaDB / Percona ).

Scenario

To validate all production read queries on MySQL version 5.7

We have to create a new MySQL instance with MySQL 5.7 , Restore backup on this new instance and set it as a slave of existing production Master in MySQL 5.6. (as shown in above diagram).

Enable Mirroring in ProxySQL:

Add new server details into ProxySQL under hostgroup 7 and then your final mysql_servers table will look like below.

 
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (7,'172.17.0.3',3306);
Admin> LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; 

mysql> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+------------+------+--------+--------+
| hostgroup_id | hostname   | port | status | weight |
+--------------+------------+------+--------+--------+
| 0            | 172.17.0.1 | 3306 | ONLINE | 100    |
| 1            | 172.17.0.2 | 3306 | ONLINE | 100    |
| 1            | 172.17.0.1 | 3306 | ONLINE | 100    |
| 7            | 172.17.0.3 | 3306 | ONLINE | 100    |
+--------------+------------+------+--------+--------+
4 rows in set (0.00 sec)

Create query rule to match with all incoming SELECT and mirror on hostgroup 7 And your final mysql_query_rules table will look like below.

 
# Mirror Read Traffic to new host
INSERT INTO mysql_query_rules (rule_id,active,username,schemaname,match_digest,mirror_hostgroup,apply) VALUES (11,1,"sysbench","sbtest",'^SELECT',7,1);

# Load And Save Configuration
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

mysql> SELECT rule_id,active,username,schemaname,match_digest,destination_hostgroup,mirror_hostgroup,apply,mirror_flagOUT FROM mysql_query_rules;
+---------+--------+----------+------------+---------------------+-----------------------+------------------+-------+----------------+
| rule_id | active | username | schemaname | match_digest        | destination_hostgroup | mirror_hostgroup | apply | mirror_flagOUT |
+---------+--------+----------+------------+---------------------+-----------------------+------------------+-------+----------------+
| 1       | 1      | sysbench | sbtest     | ^SELECT             | 1                     | NULL             | 0     | NULL           |
| 2       | 1      | sysbench | sbtest     | ^SELECT.*FOR UPDATE | 0                     | NULL             | 1     | NULL           |
| 11      | 1      | sysbench | sbtest     | ^SELECT             | NULL                  | 7                | 1     | NULL           |
+---------+--------+----------+------------+---------------------+-----------------------+------------------+-------+----------------+
3 rows in set (0.00 sec)

Start Sending Traffic

Let us simulate a load using sysbench with minimum time to increase the visibility.

 
root@mydbops-labs21:/tmp# sysbench --test=oltp --oltp-table-size=10000 --mysql-db=sbtest --mysql-user=sysbench --db-driver=mysql --mysql-password=sysbench --mysql-host=127.0.0.1 --max-time=1 --oltp-read-only=off --max-requests=0 --mysql-port=6033 --num-threads=1 --db-ps-mode=disable run
sysbench 0.4.12:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Threads started!

Time limit exceeded, exiting...
Done.

OLTP test statistics:
    queries performed:
        read:                            266
        write:                           95
        other:                           38
        total:                           399
    transactions:                        19     (18.04 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 361    (342.83 per sec.)
    other operations:                    38     (36.09 per sec.)

Test execution summary:
    total time:                          1.0530s
    total number of events:              19
    total time taken by event execution: 1.0522
    per-request statistics:
         min:                                 40.70ms
         avg:                                 55.38ms
         max:                                 82.73ms
         approx.  95 percentile:              82.67ms

Threads fairness:
    events (avg/stddev):           19.0000/0.00
    execution time (avg/stddev):   1.0522/0.00

Check STATS Table

Confirm created rules are getting hits.

 
mysql> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 266  |
| 2       | 0    |
| 11      | 266  |
+---------+------+
3 rows in set (0.00 sec)

Queries executed against each host :

 
mysql> select hostgroup,srv_host,srv_port,status,Queries from stats_mysql_connection_pool;
+-----------+------------+----------+--------+---------+
| hostgroup | srv_host   | srv_port | status | Queries |
+-----------+------------+----------+--------+---------+
| 0         | 172.17.0.1 | 3306     | ONLINE | 134     |
| 1         | 172.17.0.2 | 3306     | ONLINE | 143     |
| 1         | 172.17.0.1 | 3306     | ONLINE | 123     |
| 7         | 172.17.0.3 | 3306     | ONLINE | 266     |
+-----------+------------+----------+--------+---------+
4 rows in set (0.00 sec)

To check how what statements application has executed :

 
mysql> select Command,Total_Time_us,Total_cnt from stats_mysql_commands_counters where Total_Time_us> 0 ;
+-------------------+---------------+-----------+
| Command           | Total_Time_us | Total_cnt |
+-------------------+---------------+-----------+
| BEGIN             | 6877          | 19        |
| COMMIT            | 607616        | 19        |
| DELETE            | 5774          | 19        |
| INSERT            | 5488          | 19        |
| SELECT            | 346073        | 532       |
| SHOW_TABLE_STATUS | 2570          | 1         |
| UPDATE            | 19575         | 57        |
+-------------------+---------------+-----------+
7 rows in set (0.00 sec)

With Digest we can see same SELECT queries has been mirrored on hostgroup 7

 
mysql> SELECT hostgroup, username, digest_text, count_star, sum_time FROM stats_mysql_query_digest;
+-----------+----------+-------------------------------------------------------------------+------------+----------+
| hostgroup | username | digest_text                                                       | count_star | sum_time |
+-----------+----------+-------------------------------------------------------------------+------------+----------+
| 0         | sysbench | COMMIT                                                            | 19         | 607616   |
| 0         | sysbench | INSERT INTO sbtest values(?,?,?,?)                                | 19         | 5488     |
| 0         | sysbench | UPDATE sbtest set c=? where id=?                                  | 19         | 5582     |
| 0         | sysbench | UPDATE sbtest set k=k+? where id=?                                | 38         | 13993    |
| 1         | sysbench | SELECT DISTINCT c from sbtest where id between ? and ? order by c | 19         | 13792    |
| 0         | sysbench | DELETE from sbtest where id=?                                     | 19         | 5774     |
| 0         | sysbench | SHOW TABLE STATUS LIKE ?                                          | 1          | 2570     |
| 0         | sysbench | BEGIN                                                             | 19         | 6877     |
| 1         | sysbench | SELECT c from sbtest where id=?                                   | 190        | 114260   |
| 7         | sysbench | SELECT c from sbtest where id=?                                   | 190        | 117746   |
| 7         | sysbench | SELECT DISTINCT c from sbtest where id between ? and ? order by c | 19         | 24826    |
| 7         | sysbench | SELECT c from sbtest where id between ? and ?                     | 19         | 9445     |
| 1         | sysbench | SELECT c from sbtest where id between ? and ?                     | 19         | 12620    |
| 7         | sysbench | SELECT SUM(K) from sbtest where id between ? and ?                | 19         | 10228    |
| 7         | sysbench | SELECT c from sbtest where id between ? and ? order by c          | 19         | 14617    |
| 1         | sysbench | SELECT SUM(K) from sbtest where id between ? and ?                | 19         | 12037    |
| 1         | sysbench | SELECT c from sbtest where id between ? and ? order by c          | 19         | 16502    |
+-----------+----------+-------------------------------------------------------------------+------------+----------+
17 rows in set (0.00 sec)

Test DML Queries

In similar manner we can test DML (write ) queries from application by using below query rule.

 
INSERT INTO mysql_query_rules(rule_id,active,username,schemaname,match_digest,mirror_hostgroup,apply) VALUES (12,1,"sysbench","sbtest","^(INSERT|UPDATE|DELETE)",7,1);

We can check failed queries in ProxySQL Error logs under /var/lib/proxysql/proxysql.log

Example

A few issues commonly encountered in past while doing testing on prod :

 
2017-04-10 08:19:10 MySQL_Session.cpp:2243:handler(): [WARNING] Error during query on (7,172.17.0.3,3306): 1055, Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sbtest.i.pad' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

2017-04-21 05:58:01 MySQL_Session.cpp:2253:handler(): [WARNING] Error during query on (7,172.17.0.3,3306): 1364, Field 'headers' doesn't have a default value

 
#MYSQL 5.7.17 (Default Value)
mysql> show global variables like "%sql_mode%";
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Fixed this by removing ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES from sql_mode in MySQL 5.7

Important to note : ProxySQL mirroring should be only used for benchmarking ,testing, upgrade validation and any other activity that doesn’t require correctness. The process increased some CPU utilization on box. so if we have more number of ProxySQL configured, we can just configure mirroring on one-two proxy’s to avoid extra cpu usage on every box.

For more information : https://github.com/sysown/proxysql/blob/v1.4.3/doc/mirroring.md

MySQL

ProxySQL

No items found.