ProxySQL official file translation

Keywords: MySQL SQL Database github

14_ProxySQL_Configuration

Note: during the writing time of the article from April 201904 to may 201905, subsequent official updates in github were not written

~
~
How to configure proxysql in the simplest way

This simple HOWTO describes how to configure some components of ProxySQL step by step. Note, however, that this is not a complete guide.

Suppose you already know the ProxySQL architecture, and this HOWTO assumes that you use the standard SQL management interface to reconfigure ProxySQL, and connect to port 6032 using simple (changeable) credentials by default:

$ mysql -u admin -padmin -h 127.0.0.1 -P6032

First, verify that nothing is configured in the following tables: MySQL servers, MySQL replication hostgroups, MySQL query rules.

mysql> \R Admin>
PROMPT set to 'Admin> '
Admin>
Admin> SELECT * FROM mysql_servers;
Empty set (0.00 sec)

Admin> SELECT * from mysql_replication_hostgroups;
Empty set (0.00 sec)

Admin> SELECT * from mysql_query_rules;
Empty set (0.00 sec)

1, Add backend database servers

In this demonstration, I use mysql_sandbox(MySQL sandbox tool https://launchpad.net/mysql-sandbox) to start three MySQL servers locally. Let's add them to ProxySQL.

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21891);
Query OK, 1 row affected (0.01 sec)

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21892);
Query OK, 1 row affected (0.01 sec)

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',21893);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname  | port  | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| 1            | 127.0.0.1 | 21891 | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 127.0.0.1 | 21892 | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 127.0.0.1 | 21893 | ONLINE | 1      | 0           | 1000            | 0                   |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)

Note: by default, mysql_sandbox will set read_only = 0 on slaves. Set global read only = 1 needs to be set manually on slaves.

2, Configure monitoring

ProxySQL often monitors the back-end servers it configures for it. To do this, it's important to configure some variables.

1. Add the credentials of the user required to monitor the backend (the user needs to be created in MySQL Server):

Admin> SELECT * FROM global_variables WHERE variable_name='mysql-monitor_username';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_username | monitor        |   <==Default
+------------------------+----------------+
1 row in set (0.00 sec)

Admin> SELECT * FROM global_variables WHERE variable_name='mysql-monitor_password';
+------------------------+----------------+
| variable_name          | variable_value |
+------------------------+----------------+
| mysql-monitor_password | monitor        |   <==Default
+------------------------+----------------+
1 row in set (0.00 sec)

Use the default values here to describe how to use them:

Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
Query OK, 1 row affected (0.00 sec)

Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
Query OK, 1 row affected (0.00 sec)

Then we configure various monitoring intervals:

Admin> SELECT * FROM global_variables WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
+----------------------------------+----------------+
| variable_name                    | variable_value |
+----------------------------------+----------------+
| mysql-monitor_connect_interval   | 60000          |
| mysql-monitor_ping_interval      | 10000          |
| mysql-monitor_read_only_interval | 1500           |
+----------------------------------+----------------+
3 rows in set (0.00 sec)

Admin> UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
Query OK, 3 rows affected (0.00 sec)

Admin> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
+----------------------------------------+---------------------------------------------------+
| variable_name                          | variable_value                                    |
+----------------------------------------+---------------------------------------------------+
| mysql-monitor_history                  | 600000                                            |
| mysql-monitor_connect_interval         | 2000                                              |
| mysql-monitor_connect_timeout          | 200                                               |
| mysql-monitor_ping_interval            | 2000                                              |
| mysql-monitor_ping_timeout             | 100                                               |
| mysql-monitor_read_only_interval       | 2000                                              |
| mysql-monitor_read_only_timeout        | 100                                               |
| mysql-monitor_replication_lag_interval | 10000                                             |
| mysql-monitor_replication_lag_timeout  | 1000                                              |
| mysql-monitor_username                 | monitor                                           |
| mysql-monitor_password                 | monitor                                           |
| mysql-monitor_query_variables          | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES |
| mysql-monitor_query_status             | SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS    |
| mysql-monitor_query_interval           | 60000                                             |
| mysql-monitor_query_timeout            | 100                                               |
| mysql-monitor_timer_cached             | true                                              |
| mysql-monitor_writer_is_also_reader    | true                                              |
+----------------------------------------+---------------------------------------------------+
17 rows in set (0.00 sec)

There are many variables in ProxySQL, some of which are not used or have nothing to do with it. Currently only those listed above are considered.
Changes related to MySQL Monitor in table global ﹣ variables will only take effect after running the LOAD MYSQL VARIABLES TO RUNTIME command,
And they will not be permanently stored to disk until SAVE MYSQL VARIABLES TO DISK is run.

Admin> LOAD MYSQL VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 110 rows affected (0.02 sec)

3, Backend's health check -- '

Now let's see if ProxySQL can communicate with these hosts. ProxySQL has several tables that store monitoring information.

Admin> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

Admin> SHOW TABLES FROM monitor;
+------------------------------------+
| tables                             |
+------------------------------------+
| mysql_server_connect_log           |
| mysql_server_galera_log            |
| mysql_server_group_replication_log |
| mysql_server_ping_log              |
| mysql_server_read_only_log         |
| mysql_server_replication_lag_log   |
+------------------------------------+
6 rows in set (0.00 sec)

Not all tables in the current monitor will be used. Now we can use the following query to check the related tables:

Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+-----------+-------+------------------+----------------------+---------------+
| hostname  | port  | time_start_us       | connect_success_time | connect_error |
+-----------+-------+------------------+----------------------+---------------+
| 127.0.0.1 | 21891 | 1456968814253432 | 562                  | NULL          |
| 127.0.0.1 | 21892 | 1456968814253432 | 309                  | NULL          |
| 127.0.0.1 | 21893 | 1456968814253432 | 154                  | NULL          |
| 127.0.0.1 | 21891 | 1456968812252146 | 689                  | NULL          |
| 127.0.0.1 | 21892 | 1456968812252146 | 424                  | NULL          |
| 127.0.0.1 | 21893 | 1456968812252146 | 174                  | NULL          |
| 127.0.0.1 | 21891 | 1456968810251585 | 569                  | NULL          |
| 127.0.0.1 | 21892 | 1456968810251585 | 316                  | NULL          |
| 127.0.0.1 | 21893 | 1456968810251585 | 155                  | NULL          |
| 127.0.0.1 | 21891 | 1456968808250762 | 570                  | NULL          |
+-----------+-------+------------------+----------------------+---------------+
10 rows in set (0.00 sec)

Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+-----------+-------+------------------+-------------------+------------+
| hostname  | port  | time_start_us       | ping_success_time | ping_error |
+-----------+-------+------------------+-------------------+------------+
| 127.0.0.1 | 21891 | 1456968828686787 | 124               | NULL       |
| 127.0.0.1 | 21892 | 1456968828686787 | 62                | NULL       |
| 127.0.0.1 | 21893 | 1456968828686787 | 57                | NULL       |
| 127.0.0.1 | 21891 | 1456968826686385 | 99                | NULL       |
| 127.0.0.1 | 21892 | 1456968826686385 | 46                | NULL       |
| 127.0.0.1 | 21893 | 1456968826686385 | 42                | NULL       |
| 127.0.0.1 | 21891 | 1456968824685162 | 135               | NULL       |
| 127.0.0.1 | 21892 | 1456968824685162 | 61                | NULL       |
| 127.0.0.1 | 21893 | 1456968824685162 | 57                | NULL       |
| 127.0.0.1 | 21891 | 1456968822684689 | 215               | NULL       |
+-----------+-------+------------------+-------------------+------------+
10 rows in set (0.01 sec)

Based on the above results, we can conclude that all configured servers are healthy.
Special attention should be paid here: even before loading the table into RUNTIME, connect and ping will be monitored according to the contents of MySQL servers.
This is intentional: in this way, you can perform basic health checks before adding nodes to production.

After confirming that the backend service is normal, it can be loaded into the RUNTIME layer to enable:

Admin> SELECT * FROM runtime_mysql_servers;
Empty set (0.00 sec)

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

Admin> SELECT * FROM runtime_mysql_servers;
+--------------+-----------+---—---+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname  | port  | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+----—--+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 127.0.0.1 | 21891 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 127.0.0.1 | 21892 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 127.0.0.1 | 21893 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------+----—--+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

4, Configure MySQL replication hostgroups

Check another table in monitor mode, monitor. MySQL? Server? Read? Only? Log:

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
Empty set (0.00 sec)

This table is currently empty. The reason is that ProxySQL only checks servers that have read only set in the host group configured in MySQL replication hostgroups.

Check MySQL replication hostgroups and find that it is also empty:

Admin> SELECT * FROM mysql_replication_hostgroups;
Empty set (0.00 sec)

But what is the function of this table? Use this table to configure the listed host groups as read-write, read-only host groups in pairs. ProxySQL monitors the read only values of all servers in the specified host group and assigns servers to read-write or read-only host groups based on the read only values.

For example:
To view the table structure of this table:

Admin> SHOW CREATE TABLE mysql_replication_hostgroups \G
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
    check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only',
    comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

Insert read-write, read-only group configuration information:

Admin> INSERT INTO mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,check_type,comment) VALUES (1,2,'read_only','cluster1');
Query OK, 1 row affected (0.00 sec)

The read-write group number is 1, and the read-only group number is 2;

Admin> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+----------+
| writer_hostgroup | reader_hostgroup | check_type | comment  |
+------------------+------------------+------------+----------+
| 1                | 2                | read_only  | cluster1 |
+------------------+------------------+------------+----------+
1 row in set (0.00 sec)

When setup is complete, all servers configured in host group 1 or 2 will automatically move to the correct host group (initially, all servers we configured are in group 1):
1) If their read_only = 0, they will be automatically moved to host group 1;
2) If their read_only = 1, they will be automatically moved to host group 2;

However, this algorithm is still not running at this time, because the new configuration is not loaded into the RUNTIME layer:

Admin> select * from mysql_servers;
+--------------+-----------+---—---+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname  | port  | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+----—--+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 127.0.0.1 | 21891 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 127.0.0.1 | 21892 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 127.0.0.1 | 21893 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------+----—--+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

Use the same LOAD command as MYSQL SERVERS to LOAD MySQL replication hostgroups to the RUNTIME layer:
In fact, LOAD MYSQL SERVERS TO RUNTIME processes both MySQL servers and MySQL replication hostgroups tables.

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

To view MySQL service groups:

Admin> select * from mysql_servers;
+--------------+-----------+---—---+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname  | port  | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+----—--+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1            | 127.0.0.1 | 21891 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 127.0.0.1 | 21892 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 127.0.0.1 | 21893 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------+----—--+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

Wait a few seconds and check the status again:

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10;
+-----------+-------+------------------+--------------+-----------+-------+
| hostname  | port  | time_start_us       | success_time | read_only | error |
+-----------+-------+------------------+--------------+-----------+-------+
| 127.0.0.1 | 21891 | 1456969634783579 | 762          | 0         | NULL  |
| 127.0.0.1 | 21892 | 1456969634783579 | 378          | 1         | NULL  |
| 127.0.0.1 | 21893 | 1456969634783579 | 317          | 1         | NULL  |
| 127.0.0.1 | 21891 | 1456969632783364 | 675          | 0         | NULL  |
| 127.0.0.1 | 21892 | 1456969632783364 | 539          | 1         | NULL  |
| 127.0.0.1 | 21893 | 1456969632783364 | 550          | 1         | NULL  |
| 127.0.0.1 | 21891 | 1456969630783159 | 493          | 0         | NULL  |
| 127.0.0.1 | 21892 | 1456969630783159 | 626          | 1         | NULL  |
| 127.0.0.1 | 21893 | 1456969630783159 | 572          | 1         | NULL  |
| 127.0.0.1 | 21891 | 1456969628782328 | 433          | 0         | NULL  |
+-----------+-------+------------------+--------------+-----------+-------+
10 rows in set (0.01 sec)

ProxySQL is monitoring the server's read only value. It also creates hostgroup2, which moves the server with read only = 1 (reader) from hostgroup1.

Everything looks good. It's time to save the configuration to disk:

Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.03 sec)

Admin> SAVE MYSQL VARIABLES TO DISK;
Query OK, 110 rows affected (0.00 sec)

5, Set MySQL users (used to connect to the backend)

After configuring the server in mysql_servers, you also need to configure MySQL users. This is implemented by using MySQL ﹣ users table:

Admin> SELECT * FROM mysql_users;
Empty set (0.00 sec)

Admin> SHOW CREATE TABLE mysql_users \G
*************************** 1. row ***************************
       table: mysql_users
Create Table: CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend))
1 row in set (0.00 sec)

The table is initially empty.

1) Add the business account on the backend Mysql to mysql_users (make sure that the account already exists on MySQL):

--Create user on MYSQL

mysql> CREATE DATABASE sbtest ;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP ON sbtest.* TO 'msandbox'@'188.188.0.%' IDENTIFIED BY '123456'; 
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','123456',1);
Query OK, 1 row affected (0.00 sec)

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('msandbox','123456',1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| root     | 123456   | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
| msandbox | 123456   | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
2 rows in set (0.00 sec)

For this table, leave most fields as default. The most important fields configured here are: username, password, default ﹣ hostgroup
The meaning of user name and password should be very clear. Default? Hostgroup is a host group. If a specific query does not match the query rules, the traffic generated by the specific user will be sent to the host group (more on this later).

2) Again, load the configuration into the RUNTIME layer for it to take effect, and save it to disk so it stays the same on reboot.

Admin> SELECT * FROM runtime_mysql_users;
Empty set (0.00 sec)

Admin> SELECT * FROM runtime_mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| root     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 0       | 1        | 10000           |         |
| msandbox | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 0       | 1        | 10000           |         |
| root     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 0        | 10000           |         |
| msandbox | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 0        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
4 rows in set (0.00 sec)

Each user added has a pair of records, because each user has a user record in the front end and the back end (ProxySQL uses the back-end MySQL user for connection authentication);

Obtain user password from RUNTIME layer and update MEMORY layer:

Admin> SAVE MYSQL USERS FROM RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM runtime_mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| root     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 0       | 1        | 10000           |         |
| msandbox | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 0       | 1        | 10000           |         |
| root     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 0        | 10000           |         |
| msandbox | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 0        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
4 rows in set (0.00 sec)

Persist user information to DISK layer:

Admin> SELECT * FROM disk.mysql_users;
Empty set (0.00 sec)

Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SELECT * FROM disk.mysql_users;
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password                                  | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| root     | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
| msandbox | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 1      | 0       | 1                 |                | 0             | 1                      | 0            | 1       | 1        | 10000           |         |
+----------+-------------------------------------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
2 rows in set (0.00 sec)

3) We can now try to connect from different terminals (the default connection port is 6033):

[root@localhost ~]# mysql -u msandbox -p123456 -h 188.188.0.71 -P6033 -e "SELECT 1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

Note: the default connection port is 6033, not 6032(ProxySQL management port) or 3306(MySQL port).

[root@localhost ~]# mysql -u msandbox -p123456 -h 188.188.0.71 -P6033 -e "SELECT @@port"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  21891 |
+--------+

The port of the backend MySQL service is queried here.

It seems to work, and it's no surprise that the query is sent to the server (Master) on listening port 21891, because the default group of user msandbox is hostgroup1, and currently there are no query rules available for that user.

6, Functional tests

Now you can try some "benchmark" tests to verify that ProxySQL is working properly.

If you have created a sysbench table, you can run the load test with the following command:
Here, sysbench-1.0.14 is used for testing.

-- Test data initial
[root@localhost ~]# sysbench oltp_common --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=5  prepare

-- Perform a read-write test
sysbench oltp_read_write --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=5 \
--time=200 --histogram --report-interval=10 --db-ps-mode=disable run

Note: if -- DB PS mode = disable is set, the effect is that each thread always executes all SQL in a Session; if not, a new connection will be created for each execution.

During the test, you will find that all requests are concentrated on the Master, but no requests arrive on the Slave.

7, ProxySQL information collection [ProxySQL Statistics]

ProxySQL collects a lot of real-time statistics in the stats Library:

Admin> SHOW SCHEMAS;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.00 sec)

Admin> SHOW TABLES FROM stats;
+--------------------------------------+
| tables                               |
+--------------------------------------+
| global_variables                     |
| stats_memory_metrics                 |
| stats_mysql_commands_counters        |
| stats_mysql_connection_pool          |
| stats_mysql_connection_pool_reset    |
| stats_mysql_errors                   |
| stats_mysql_errors_reset             |
| stats_mysql_global                   |
| stats_mysql_gtid_executed            |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist              |
| stats_mysql_query_digest             |
| stats_mysql_query_digest_reset       |
| stats_mysql_query_rules              |
| stats_mysql_users                    |
| stats_proxysql_servers_checksums     |
| stats_proxysql_servers_metrics       |
| stats_proxysql_servers_status        |
+--------------------------------------+
18 rows in set (0.00 sec)

There are many tables in the stats architecture. We will analyze them.

1. Table stats.stats > MySQL > connection > pool

Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+-----------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host  | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 1         | 127.0.0.1 | 21891    | ONLINE       | 0        | 4        | 11     | 66      | 5           | 3262254 | 0                 | 156040355       | 6371749875      | 99         |
| 1         | 127.0.0.1 | 21892    | OFFLINE_HARD | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 97         |
| 2         | 127.0.0.1 | 21893    | ONLINE       | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 96         |
| 2         | 127.0.0.1 | 21892    | ONLINE       | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 97         |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
4 rows in set (0.00 sec)

Note: Currently, when the server is deleted (completely deleted or removed from the host group), it is marked "offline" internally rather than actually deleted. That's why it displays the server on port 21892 as "offline Ou hard" for host group 1.
This table returns information about the traffic sent to each server. As expected, all traffic is sent to the server on primary server port 21891.

2. Table stats > MySQL > commands > counters

Admin> 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 |
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| BEGIN        | 60686611      | 163092    | 2550      | 126541    | 24450   | 9549    | 2        | 0        | 0         | 0         | 0      | 0      | 0       | 0        |
| COMMIT       | 651883996     | 163019    | 13        | 3         | 0       | 134329  | 26377    | 2294     | 3         | 0         | 0      | 0      | 0       | 0        |
| CREATE_INDEX | 799325        | 14        | 0         | 4         | 0       | 0       | 0        | 2        | 5         | 3         | 0      | 0      | 0       | 0        |
| CREATE_TABLE | 668337        | 30        | 0         | 9         | 4       | 2       | 1        | 10       | 4         | 0         | 0      | 0      | 0       | 0        |
| DELETE       | 66235607      | 163109    | 66        | 134225    | 21610   | 7137    | 70       | 1        | 0         | 0         | 0      | 0      | 0       | 0        |
| DROP_TABLE   | 130117        | 32        | 0         | 5         | 0       | 18      | 3        | 6        | 0         | 0         | 0      | 0      | 0       | 0        |
| INSERT       | 71401062      | 163144    | 65        | 135608    | 21000   | 6413    | 2        | 2        | 25        | 29        | 0      | 0      | 0       | 0        |
| SELECT       | 973021665     | 2283433   | 2272      | 1695366   | 421243  | 164544  | 7        | 1        | 0         | 0         | 0      | 0      | 0       | 0        |
| UPDATE       | 142844582     | 326280    | 134       | 255645    | 54795   | 15573   | 125      | 8        | 0         | 0         | 0      | 0      | 0       | 0        |
| SHOW         | 8005          | 9         | 0         | 5         | 1       | 3       | 0        | 0        | 0         | 0         | 0      | 0      | 0       | 0        |
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
10 rows in set (0.00 sec)

Table stats > MySQL > commands > counters returns details about the type of statement executed and the distribution of execution time!

3. Table stats > MySQL > query > Digest

The table stats > MySQL > commands > counters provides very useful information. Can we get more details about executed queries? The stats > MySQL > query > digest table helps:

Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 10;
+-----------+------------+----------+----------------+--------------------+--------------------------------------------------------------------+------------+------------+------------+-----------+----------+----------+
| hostgroup | schemaname | username | client_address | digest             | digest_text                                                        | count_star | first_seen | last_seen  | sum_time  | min_time | max_time |
+-----------+------------+----------+----------------+--------------------+--------------------------------------------------------------------+------------+------------+------------+-----------+----------+----------+
| 1         | sbtest     | msandbox |                | 0x695FBF255DBEB0DD | COMMIT                                                             | 163019     | 1556684776 | 1556685389 | 651883996 | 96       | 57674    |
| 1         | sbtest     | msandbox |                | 0xBF001A0C13781C1D | SELECT c FROM sbtest1 WHERE id=?                                   | 328166     | 1556684776 | 1556685389 | 112340379 | 83       | 4543     |
| 1         | sbtest     | msandbox |                | 0x0250CB4007721D69 | SELECT c FROM sbtest3 WHERE id=?                                   | 327446     | 1556684776 | 1556685389 | 112260374 | 82       | 4517     |
| 1         | sbtest     | msandbox |                | 0x03744DC190BC72C7 | SELECT c FROM sbtest5 WHERE id=?                                   | 327416     | 1556684776 | 1556685389 | 111997649 | 83       | 11095    |
| 1         | sbtest     | msandbox |                | 0x9AF59B998A3688ED | SELECT c FROM sbtest2 WHERE id=?                                   | 324866     | 1556684776 | 1556685389 | 111375893 | 86       | 9565     |
| 1         | sbtest     | msandbox |                | 0x9D058B6F3BC2F754 | SELECT c FROM sbtest4 WHERE id=?                                   | 322922     | 1556684776 | 1556685389 | 110558550 | 80       | 9678     |
| 1         | sbtest     | msandbox |                | 0xFAD1519E4760CBDE | BEGIN                                                              | 163092     | 1556684776 | 1556685389 | 60686611  | 61       | 5466     |
| 1         | sbtest     | msandbox |                | 0x44BCB144058686EB | SELECT DISTINCT c FROM sbtest3 WHERE id BETWEEN ? AND ? ORDER BY c | 32783      | 1556684776 | 1556685389 | 35102860  | 112      | 2626     |
| 1         | sbtest     | msandbox |                | 0x283AA9863F85EFC8 | SELECT DISTINCT c FROM sbtest4 WHERE id BETWEEN ? AND ? ORDER BY c | 32563      | 1556684776 | 1556685389 | 35056979  | 86       | 6810     |
| 1         | sbtest     | msandbox |                | 0x847CD40BA8EA5175 | SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN ? AND ? ORDER BY c | 32695      | 1556684776 | 1556685389 | 34881271  | 87       | 3293     |
+-----------+------------+----------+----------------+--------------------+--------------------------------------------------------------------+------------+------------+------------+-----------+----------+----------+
10 rows in set (0.01 sec)

Too much information here makes formatting reality difficult. Let's get just a few important indicators:

Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 10;
+----+-----------+------------+--------------------------------------------------------------------+
| hg | sum_time  | count_star | digest_text                                                        |
+----+-----------+------------+--------------------------------------------------------------------+
| 1  | 651883996 | 163019     | COMMIT                                                             |
| 1  | 112340379 | 328166     | SELECT c FROM sbtest1 WHERE id=?                                   |
| 1  | 112260374 | 327446     | SELECT c FROM sbtest3 WHERE id=?                                   |
| 1  | 111997649 | 327416     | SELECT c FROM sbtest5 WHERE id=?                                   |
| 1  | 111375893 | 324866     | SELECT c FROM sbtest2 WHERE id=?                                   |
| 1  | 110558550 | 322922     | SELECT c FROM sbtest4 WHERE id=?                                   |
| 1  | 60686611  | 163092     | BEGIN                                                              |
| 1  | 35102860  | 32783      | SELECT DISTINCT c FROM sbtest3 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 35056979  | 32563      | SELECT DISTINCT c FROM sbtest4 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 34881271  | 32695      | SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN ? AND ? ORDER BY c |
+----+-----------+------------+--------------------------------------------------------------------+
10 rows in set (0.01 sec)

All traffic will be sent to hostgroup1. Now, how to send a specific query to Slave? Continue with the query rules section below

Eight. MySQL Query Rules] [MySQL query rules

1. Add query rule

The MySQL query rules table has many fields. It is a very powerful tool to control the traffic through ProxySQL.
The table is defined as follows:

Admin> SHOW CREATE TABLE mysql_query_rules \G
*************************** 1. row ***************************
       table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
    rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
    username VARCHAR,
    schemaname VARCHAR,
    flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0,
    client_addr VARCHAR,
    proxy_addr VARCHAR,
    proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535), digest VARCHAR,
    match_digest VARCHAR,
    match_pattern VARCHAR,
    negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
    re_modifiers VARCHAR DEFAULT 'CASELESS',
    flagOUT INT CHECK (flagOUT >= 0), replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END),
    destination_hostgroup INT DEFAULT NULL,
    cache_ttl INT CHECK(cache_ttl > 0),
    cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL,
    cache_timeout INT CHECK(cache_timeout >= 0),
    reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
    timeout INT UNSIGNED CHECK (timeout >= 0),
    retries INT CHECK (retries>=0 AND retries <=1000),
    delay INT UNSIGNED CHECK (delay >=0),
    next_query_flagIN INT UNSIGNED,
    mirror_flagOUT INT UNSIGNED,
    mirror_hostgroup INT UNSIGNED,
    error_msg VARCHAR,
    OK_msg VARCHAR,
    sticky_conn INT CHECK (sticky_conn IN (0,1)),
    multiplex INT CHECK (multiplex IN (0,1,2)),
    gtid_from_hostgroup INT UNSIGNED,
    log INT CHECK (log IN (0,1)),
    apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
    comment VARCHAR)
1 row in set (0.00 sec)

Now you can configure ProxySQL to send the first two queries to the Slave server and all other queries to the master server.

To view the currently set query rules:

Admin> SELECT * FROM mysql_query_rules ;
Empty set (0.00 sec)

Insert query rule:

-- Will be right sbtest1 The following two queries of the table are sent to Slave. 
Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'msandbox','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);
Query OK, 1 row affected (0.00 sec)

Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'msandbox','DISTINCT c FROM sbtest1',2,1);
Query OK, 1 row affected (0.00 sec)

To view the insertion results:

Admin> 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 | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 10      | 1      | msandbox | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 20      | 1      | msandbox | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | DISTINCT c FROM sbtest1             | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
2 rows in set (0.00 sec)

Admin> SELECT rule_id,active,username,match_digest,destination_hostgroup,apply FROM mysql_query_rules ;
+---------+--------+----------+-------------------------------------+-----------------------+-------+
| rule_id | active | username | match_digest                        | destination_hostgroup | apply |
+---------+--------+----------+-------------------------------------+-----------------------+-------+
| 10      | 1      | msandbox | ^SELECT c FROM sbtest1 WHERE id=\?$ | 2                     | 1     |
| 20      | 1      | msandbox | DISTINCT c FROM sbtest1             | 2                     | 1     |
+---------+--------+----------+-------------------------------------+-----------------------+-------+
2 rows in set (0.00 sec)

Several notes:
1) The query rules are processed in the order of rule ﹣ ID;
2) Only rules with active = 1 are processed, i.e. open.
Because query rules are a very powerful tool, it will be difficult to troubleshoot if the configuration is wrong (we all like regular expressions, don't we? ), by default, active is 0 (active = 0). Before you enable them, you should carefully check the regular expressions of the rules!
3) The first rule example uses the caret (^) and dollar sign ($):
These are special regular expression characters that mark the beginning and end of a pattern. In this case, this means that match'u digest or match'u pattern should exactly match the query.
4) Compared with the first rule example, the second rule example does not use carets or dollars: matches can be anywhere in the query.
5) Pay special attention to regular expressions to avoid rules that should not match them!
6) You may notice that the question mark (?) has been escaped. It has a special meaning in regular expressions; as mentioned above, special attention should be paid to regular expression syntax!
7) apply = 1 means that no other rules will be checked if they match.








According to the above description, the query of MySQL query rules table can be simplified as follows:

Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='msandbox' ORDER BY rule_id;
+-------------------------------------+-----------------------+
| match_digest                        | destination_hostgroup |
+-------------------------------------+-----------------------+
| ^SELECT c FROM sbtest1 WHERE id=\?$ | 2                     |
| DISTINCT c FROM sbtest1             | 2                     |
+-------------------------------------+-----------------------+
2 rows in set (0.00 sec)

For these two specific rules, the query is sent to Slave. If the query does not match the rule, it is sent to the default [hostgroup (1 for user msandbox).

2. Clear old query statistics records

Next, let's reset the contents of the table stats > MySQL > query > digest. To achieve this, we can simply run any query on stats > MySQL > query > digest > reset, for example:

Admin> SELECT * FROM stats_mysql_query_digest_reset LIMIT 1;

Querying stats > MySQL > query > digest > reset will atomically retrieve the contents of the stats > MySQL > query > digest table and truncate it!

3. Load rules to RUNTIME layer

Now we can load the query rule to the RUNTIME layer to make it effective:

Admin> SELECT * FROM runtime_mysql_query_rules;
Empty set (0.00 sec)

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM runtime_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 | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 10      | 1      | msandbox | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 20      | 1      | msandbox | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | DISTINCT c FROM sbtest1             | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
2 rows in set (0.00 sec)

4. Persist query rules to DISK layer

Admin> SELECT * FROM disk.mysql_query_rules;
Empty set (0.00 sec)

Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SELECT * FROM disk.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 | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 10      | 1      | msandbox | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 20      | 1      | msandbox | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | DISTINCT c FROM sbtest1             | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
2 rows in set (0.00 sec)

5. Retest

Finally, we re execute the sysbench load:

-- Perform a read-write test
sysbench oltp_read_write --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=5 \
--time=200 --histogram --report-interval=10 --db-ps-mode=disable run

6. View query statistics again

1) Table stats.stats > MySQL > connection > pool

Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+-----------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| hostgroup | srv_host  | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 1         | 127.0.0.1 | 21891    | ONLINE       | 0        | 4        | 12     | 66      | 5           | 5548772 | 0                 | 250629298       | 11033778165     | 93         |
| 1         | 127.0.0.1 | 21892    | OFFLINE_HARD | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 94         |
| 2         | 127.0.0.1 | 21893    | ONLINE       | 0        | 3        | 3      | 0       | 3           | 140634  | 0                 | 3477406         | 176787576       | 91         |
| 2         | 127.0.0.1 | 21892    | ONLINE       | 0        | 3        | 3      | 0       | 3           | 140419  | 0                 | 3472382         | 176972544       | 94         |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
4 rows in set (0.00 sec)

This time, two MySQL in group 2 are also assigned to the query.

2) Table stats > MySQL > query > Digest

Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 15;
+----+-----------+------------+--------------------------------------------------------------------+
| hg | sum_time  | count_star | digest_text                                                        |
+----+-----------+------------+--------------------------------------------------------------------+
| 1  | 528005107 | 128310     | COMMIT                                                             |
| 1  | 85388244  | 258648     | SELECT c FROM sbtest3 WHERE id=?                                   |
| 1  | 84775526  | 257258     | SELECT c FROM sbtest5 WHERE id=?                                   |
| 1  | 84527137  | 257098     | SELECT c FROM sbtest4 WHERE id=?                                   |
| 1  | 84271753  | 255208     | SELECT c FROM sbtest2 WHERE id=?                                   |
| 2  | 46785931  | 255528     | SELECT c FROM sbtest1 WHERE id=?                                   |
| 1  | 43321724  | 128379     | BEGIN                                                              |
| 1  | 27591062  | 25737      | SELECT DISTINCT c FROM sbtest2 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 27147997  | 25698      | SELECT DISTINCT c FROM sbtest3 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 27139402  | 25743      | SELECT DISTINCT c FROM sbtest5 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 27046866  | 25702      | SELECT DISTINCT c FROM sbtest4 WHERE id BETWEEN ? AND ? ORDER BY c |
| 2  | 26084914  | 25529      | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 15539338  | 25714      | SELECT c FROM sbtest2 WHERE id BETWEEN ? AND ? ORDER BY c          |
| 1  | 15535214  | 25786      | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          |
| 1  | 15499890  | 25664      | SELECT c FROM sbtest3 WHERE id BETWEEN ? AND ? ORDER BY c          |
+----+-----------+------------+--------------------------------------------------------------------+
15 rows in set (0.00 sec)

As expected, two types of queries are sent to hostgroup2 (Slave). Table stats > MySQL > query > digest allows you to aggregate results, for example:

Admin> SELECT hostgroup hg, SUM(sum_time), SUM(count_star) FROM stats_mysql_query_digest GROUP BY hostgroup;
+----+---------------+-----------------+
| hg | SUM(sum_time) | SUM(count_star) |
+----+---------------+-----------------+
| 1  | 1421564103    | 2286517         |
| 2  | 72870845      | 281057          |
+----+---------------+-----------------+
2 rows in set (0.00 sec)

9, Query Caching

One popular use of ProxySQL is as a query cache. By default, queries are not cached, but you can set cache TTL (in milliseconds) in MySQL query rules.
Suppose we also cache all queries sent to Slave for 5 seconds.

1. Modify query rules

Admin> UPDATE mysql_query_rules SET cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2;
Query OK, 2 rows affected (0.00 sec)

2. Load changes to the RUNTIME layer

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT * FROM runtime_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 | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 10      | 1      | msandbox | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | 5000      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
| 20      | 1      | msandbox | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | DISTINCT c FROM sbtest1             | NULL          | 0                    | CASELESS     | NULL    | NULL            | 2                     | 5000      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+-------------------------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
2 rows in set (0.00 sec)

3. Clear old query statistics records

Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
Empty set (0.01 sec)

4. Test again

-- Perform a read-write test
sysbench oltp_read_write --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=5 \
--time=200 --histogram --report-interval=10 --db-ps-mode=disable run

5. View stats > MySQL > query > Digest

Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest where digest_text like 'SELECT%sbtest1%' ORDER BY sum_time DESC ;
+----+----------+------------+--------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                        |
+----+----------+------------+--------------------------------------------------------------------+
| 2  | 23806132 | 128797     | SELECT c FROM sbtest1 WHERE id=?                                   |
| 2  | 13463802 | 12767      | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 7612531  | 12616      | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c          |
| 1  | 5591611  | 12755      | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?                     |
| 1  | 5260845  | 12806      | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ?                |
| 2  | 409      | 1          | SELECT DISTINCT c FROM sbtest1 WHERE id=?                          |
| 1  | 216      | 1          | SELECT DISTINCAT c FROM sbtest1 WHERE id=?                         |
| -1 | 0        | 7          | SELECT DISTINCT c FROM sbtest1 WHERE id=?                          |
| -1 | 0        | 24         | SELECT c FROM sbtest1 WHERE id=?                                   |
+----+----------+------------+--------------------------------------------------------------------+
9 rows in set (0.00 sec)

It can be found that:
1) They are still sent to hostgroup2;
2) If they appear in the query cache, they will not be sent to any host group and marked as a special host group-1;

Through the above experiments, we can control the memory usage and the life cycle of the result set through cache TTL in MySQL query rules: it is wise to choose cache TTL until we can get more control over the query cache.

Special attention:
Currently, it is not possible to define the maximum amount of memory used by the query cache, or force selective or full refresh of the query cache.

10, Query Rewrite

ProxySQL supports multiple ways to match queries, such as flagIN, username, schemaname.

The most common way to match queries is to write regular expressions that match the text of the query itself. In order to match the query text, ProxySQL provides two mechanisms, using two different fields:

  • Match ABCD Digest: a regular expression for the command summary of a query statement (the SQL statement whose variable values are replaced by?); the text of the query statement can be viewed in stats > MySQL > query > digest.query > digest.
  • Match ABCD pattern: a regular expression for the original SQL text

Why do these different mechanisms exist?
The summary of the query may be much smaller than the query itself (for example, an INSERT statement with a few megabytes of data), so running regular expressions on smaller strings is definitely faster. So if you don't need to try to match specific text in a query,
It is recommended to use match [digest (it is faster). However, if the query is to be rewritten, it must match the original query (using match [pattern], because it is the original query that needs to be rewritten.

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

1. Override configuration example

Rewrite the statement containing both DISTINCT and ORDER BY c to the statement of DISTINCT 1:

1) Add query rule

Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (30,1,'msandbox','DISTINCT(.*)ORDER BY c','DISTINCT\1',1);
Query OK, 1 row affected (0.00 sec)

Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                   | NULL            | 10000     | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 10000     | 1     |
| 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)

2) Load into the runtime layer

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

3) Clear old query statistics records

Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;

4) Perform tests

-- Perform a read-write test
sysbench oltp_read_write --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=5 \
--time=200 --histogram --report-interval=10 --db-ps-mode=disable run

5) View stats > MySQL > query > Digest

Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC limit 10;
+----+-----------+------------+--------------------------------------------------------------------+
| hg | sum_time  | count_star | digest_text                                                        |
+----+-----------+------------+--------------------------------------------------------------------+
| 1  | 262096703 | 65249      | COMMIT                                                             |
| 1  | 42813558  | 133794     | SELECT c FROM sbtest2 WHERE id=?                                   |
| 1  | 41657095  | 130914     | SELECT c FROM sbtest4 WHERE id=?                                   |
| 1  | 41641341  | 130064     | SELECT c FROM sbtest3 WHERE id=?                                   |
| 1  | 41610083  | 129484     | SELECT c FROM sbtest5 WHERE id=?                                   |
| 2  | 23794597  | 128544     | SELECT c FROM sbtest1 WHERE id=?                                   |
| 1  | 21205569  | 65282      | BEGIN                                                              |
| 2  | 13508337  | 13160      | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c |
| 1  | 12705099  | 13233      | SELECT DISTINCT c FROM sbtest2 WHERE id BETWEEN ? AND ?            |
| 1  | 12153358  | 13101      | SELECT DISTINCT c FROM sbtest4 WHERE id BETWEEN ? AND ?            |
+----+-----------+------------+--------------------------------------------------------------------+
10 rows in set (0.00 sec)

There seems to be something wrong because it doesn't seem to have been rewritten. This is intentional, so we can troubleshoot it now.

6) Rule diagnosis

A very useful table for troubleshooting is stats. Stats? MySQL? Query? Rules:

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply 
    -> FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| hits | rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 4    | 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                   | NULL            | 10000     | 1     |
| 4    | 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 10000     | 1     |
| 16   | 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)

There seems to be something wrong: rule with rule Ou id = 30 has 0 hits!

The problem is that rules with rule UU id = 20 also match queries that match in rule UU id = 30, and queries that don't match in rule 20, because apply = 1 is set in 20, this will prevent them from reaching rule 30.
(because when apply = 1, unmatched queries are sent to the default group for processing)

7) Revise the rule

Admin> UPDATE mysql_query_rules SET apply=0 WHERE rule_id=20;
Query OK, 1 row affected (0.00 sec)

Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                   | NULL            | 10000     | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 10000     | 0     |
| 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)

8) Load the modified configuration into the runtime layer

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT rule_id,apply FROM runtime_mysql_query_rules;
+---------+-------+
| rule_id | apply |
+---------+-------+
| 10      | 1     |
| 20      | 0     |
| 30      | 1     |
+---------+-------+
3 rows in set (0.00 sec)

Note: when you run LOAD MYSQL QUERY RULES TO RUNTIME, you will not only reset the internal query processing structure, but also reset the counters in stats.stats'mysql'query'rules.

Admin> SELECT * FROM stats.stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 10      | 0    |
| 20      | 0    |
| 30      | 0    |
+---------+------+
3 rows in set (0.00 sec)

9) Clear old query statistics records

Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;

10) Test again

-- Perform a read-write test
sysbench oltp_read_write --mysql_storage_engine=innodb --db-driver=mysql --mysql-host=188.188.0.71 --mysql-port=6033 \
--mysql-user='msandbox' --mysql-password='123456' --mysql-db=sbtest --threads=4 --table_size=10000 --tables=5 \
--time=200 --histogram --report-interval=10 --db-ps-mode=disable run

11) View rule hits again

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply
    -> FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules ORDER BY mysql_query_rules.rule_id;
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| hits | rule_id | match_digest                        | match_pattern          | replace_pattern | cache_ttl | apply |
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 4    | 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                   | NULL            | 10000     | 1     |
| 4    | 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 10000     | 0     |
| 20   | 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)

Rule 30 has been hit 20 times this time. It has been rewritten. It looks good: -)
How about the execution of the inquiry?

12) View stats > MySQL > query > Digest

Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest WHERE digest_text like '%sbtest1%' ORDER BY sum_time DESC;
+----+----------+------------+-------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                 |
+----+----------+------------+-------------------------------------------------------------+
| 1  | 6127168  | 4856       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 2  | 4264263  | 7359       | SELECT c FROM sbtest1 WHERE id=?                            |
| 1  | 4081063  | 4856       | UPDATE sbtest1 SET k=k+? WHERE id=?                         |
| 1  | 3497644  | 4856       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?            |
| 1  | 3270527  | 4856       | DELETE FROM sbtest1 WHERE id=?                              |
| 1  | 3193123  | 4856       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?       |
| 1  | 3124698  | 4856       | UPDATE sbtest1 SET c=? WHERE id=?                           |
| 1  | 2866474  | 4856       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)     |
| 2  | 1889996  | 1633       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?   |
| -1 | 0        | 3223       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?   |
| -1 | 0        | 41201      | SELECT c FROM sbtest1 WHERE id=?                            |
+----+----------+------------+-------------------------------------------------------------+
11 rows in set (0.00 sec)

be careful:
Rules with rule UU id = 20 and rule UU id = 30 can be combined into one rule. They are separated here to describe the importance of application fields, and not only multiple rules can match the same query, but also multiple rules can transform settings and apply them to the same query.

-- >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

2. Several examples of query rewriting.

We want to rewrite the following query:
Will query
SELECT c FROM sbtest1 WHERE id=?
Convert to in:
SELECT c FROM sbtest2 WHERE id=?



But for the value of ID, only the value between 1000 and 3999 is known...; this is meaningless, it just shows some potential, including some complex sharding capabilities!!
Do you want to use regular expression matching? : -)

1) Add corresponding rule

Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,replace_pattern,apply) VALUES (5,1,'msandbox','^SELECT (c) FROM sbtest(1) WHERE id=(1|2|3)(...)$','SELECT c FROM sbtest2 WHERE id=\3\4',1);
Query OK, 1 row affected (0.00 sec)

Note: select "c" and "1" (in sbtest1) just to display the syntax.

2) View rule addition results

Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
| rule_id | match_digest                        | match_pattern                                     | replace_pattern                     | cache_ttl | apply |
+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
| 5       | NULL                                | ^SELECT (c) FROM sbtest(1) WHERE id=(1|2|3)(...)$ | SELECT c FROM sbtest2 WHERE id=\3\4 | NULL      | 1     |
| 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                                              | NULL                                | 10000     | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL                                              | NULL                                | 10000     | 0     |
| 30      | NULL                                | DISTINCT(.*)ORDER BY c                            | DISTINCT\1                          | NULL      | 1     |
+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
4 rows in set (0.00 sec)

3) Load the modified configuration into the runtime layer

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> SELECT rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM runtime_mysql_query_rules ORDER BY rule_id;
+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
| rule_id | match_digest                        | match_pattern                                     | replace_pattern                     | cache_ttl | apply |
+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
| 5       | NULL                                | ^SELECT (c) FROM sbtest(1) WHERE id=(1|2|3)(...)$ | SELECT c FROM sbtest2 WHERE id=\3\4 | NULL      | 1     |
| 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                                              | NULL                                | 10000     | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL                                              | NULL                                | 10000     | 0     |
| 30      | NULL                                | DISTINCT(.*)ORDER BY c                            | DISTINCT\1                          | NULL      | 1     |
+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
4 rows in set (0.00 sec)

4) Clear old query statistics records

Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;

5) Test again

The execution consists of 123 IDS:

mysql> SELECT c FROM sbtest1 WHERE id=1123;

Does it work? Obviously:)

6) View rule hits again

Admin> SELECT hits, mysql_query_rules.rule_id, match_digest, match_pattern, replace_pattern, cache_ttl, apply FROM mysql_query_rules NATURAL JOIN stats.stats_mysql_query_rules;
+------+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
| hits | rule_id | match_digest                        | match_pattern                                     | replace_pattern                     | cache_ttl | apply |
+------+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
| 3    | 5       | NULL                                | ^SELECT (c) FROM sbtest(1) WHERE id=(1|2|3)(...)$ | SELECT c FROM sbtest2 WHERE id=\3\4 | NULL      | 1     |
| 7    | 10      | ^SELECT c FROM sbtest1 WHERE id=\?$ | NULL                                              | NULL                                | 10000     | 1     |
| 4    | 20      | DISTINCT c FROM sbtest1             | NULL                                              | NULL                                | 10000     | 0     |
| 20   | 30      | NULL                                | DISTINCT(.*)ORDER BY c                            | DISTINCT\1                          | NULL      | 1     |
+------+---------+-------------------------------------+---------------------------------------------------+-------------------------------------+-----------+-------+
4 rows in set (0.00 sec)

Every time a test query statement is executed, Rule 5 hit increases by 1.

11, Persist configuration to DISK layer

The configuration involved in the experiment is persisted to the DISK layer.

Admin> SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.02 sec)

Admin> SAVE MYSQL USERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

Admin> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.02 sec)

~
~
complete!

Posted by sri2002 on Wed, 29 Apr 2020 00:17:06 -0700