Initial Configuration

This guide describes how to configure the basic components of ProxySQL step by step. The guide assumes you understand ProxySQL’s overall architecture and that ProxySQL is already installed on your operating system with the default configuration. ProxySQL’s internals can be reconfigured using the standard SQL ProxySQL Admin interface, accessible via any MySQL command line client (available by default on port 6032):

$ mysql -u admin -padmin -h 10.10.0.1 -P6032 --prompt 'ProxySQL Admin> '

Verify that the configuration is empty by checking that there are no entries in the mysql_servers, mysql_replication_hostgroups and mysql_query_rules tables.

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

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

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

Add backends

For the purpose of this guide, 3x MySQL server backends will be configured. This is achieved by adding them to the mysql_servers table. When configuring remote backend MySQL servers it is ONLY required to specify port if it is non-default (i.e. not 3306):

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

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

ProxySQL Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.0.0.3',3306);
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            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)

NOTE: The backend MySQL servers have read_only = 1 configured on all replicas. ProxySQL considers backend instances with a read_only = 0 as WRITER instances so this should only be set on your primary MySQL servers (or all primaries in case of multi-primary replication). Make sure this is properly set by running set global read_only = 1 and configuring this in the my.cnf file as ProxySQL will make routing decisions based on this value.

Configure monitoring

ProxySQL constantly monitors the MySQL server backends configured to identify the health status. The credentials for monitoring the backends need to be created in MySQL and also configured in ProxySQL along with the environment specific check intervals.

To create the user in MySQL connect to the PRIMARY and execute:

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

Then add the credentials of the monitor user to ProxySQL:

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

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

Then configure the various monitoring intervals:

ProxySQL 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)

ProxySQL 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)

Changes made to the MySQL Monitor in table global_variables will be applied after executing the LOAD MYSQL VARIABLES TO RUNTIME statement. To persist the configuration changes across restarts the SAVE MYSQL VARIABLES TO DISK must also be executed.

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

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

Backend’s health check

Once the configuration is active verify the status of the MySQL backends in the monitor database tables in ProxySQL Admin:

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

Each check type has a dedicated logging table, each should be checked individually:

ProxySQL Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+------+------------------+----------------------+---------------+
| hostname  | port | time_start_us    | connect_success_time | connect_error |
+-----------+------+------------------+----------------------+---------------+
| 10.10.0.1 | 3306 | 1456968814253432 | 562                  | NULL          |
| 10.10.0.2 | 3306 | 1456968814253432 | 309                  | NULL          |
| 10.10.0.3 | 3306 | 1456968814253432 | 154                  | NULL          |
+-----------+------+------------------+----------------------+---------------+
3 rows in set (0.00 sec)

ProxySQL Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+------+------------------+-------------------+------------+
| hostname  | port | time_start_us    | ping_success_time | ping_error |
+-----------+------+------------------+-------------------+------------+
| 10.10.0.1 | 3306 | 1456968828686787 | 124               | NULL       |
| 10.10.0.2 | 3306 | 1456968828686787 | 62                | NULL       |
| 10.10.0.3 | 3306 | 1456968828686787 | 57                | NULL       |
+-----------+------+------------------+-------------------+------------+
3 rows in set (0.01 sec)

One important thing to note here is that connect and ping monitoring is done based on the configured mysql_servers even before this is loaded to RUNTIME. This approach is intentional: in this way it is possible to perform basic health checks before adding the nodes in production.

After verifying that the servers are being monitored correctly and are healthy activate the configuration:

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

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname  | port  | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
| 1            | 10.10.0.1 | 3306  | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.2 | 3306  | ONLINE | 1      | 0           | 1000            | 0                   |
| 1            | 10.10.0.3 | 3306  | ONLINE | 1      | 0           | 1000            | 0                   |
+--------------+-----------+-------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)

MySQL replication hostgroups

Cluster topology changes are monitored based on MySQL replication hostgroups configured in ProxySQL. ProxySQL understands the replication topology by monitoring the value of read_only on servers configured in hostgroups that are configured in mysql_replication_hostgroups.

This table is empty by default and should be configured by specifying a pair of READER and WRITER hostgroups, for example:

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','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR,
UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

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

Now, all the MySQL backend servers that are either configured in hostgroup 1 or 2 will be placed into their respective hostgroup based on their read_only value:

  • If they have read_only=0 , they will be moved to hostgroup 1
  • If they have read_only=1 , they will be moved to hostgroup 2

To enable the replication hostgroup load mysql_replication_hostgroups to runtime using the same LOAD command used for MySQL servers since 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)

The read_only check results are logged to the mysql_servers_read_only_log table in the monitor database:

Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;
+-----------+-------+------------------+--------------+-----------+-------+
| hostname  | port  | time_start_us    | success_time | read_only | error |
+-----------+-------+------------------+--------------+-----------+-------+
| 10.10.0.1 | 3306  | 1456969634783579 | 762          | 0         | NULL  |
| 10.10.0.2 | 3306  | 1456969634783579 | 378          | 1         | NULL  |
| 10.10.0.3 | 3306  | 1456969634783579 | 317          | 1         | NULL  |
+-----------+-------+------------------+--------------+-----------+-------+
3 rows in set (0.01 sec)

Allright, ProxySQL is monitoring the read_only value for the servers.
And it also created hostgroup2 to where it has moved servers with read_only=1 (readers) from hostgroup1.

Admin> SELECT * FROM mysql_servers;
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| hostgroup_id | hostname  | port | status | weight | compression | max_connections | max_replication_lag |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
| 1            | 10.10.0.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 2            | 10.10.0.2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
| 2            | 10.10.0.3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   |
+--------------+-----------+------+--------+--------+-------------+-----------------+---------------------+
3 rows in set (0.00 sec)

As a final step, persist the configuration to disk.

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

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

MySQL Users

After configuring the MySQL server backends in mysql_servers the next step is to configure mysql users.

This is performed by adding entries to the mysql_users table:

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 0,
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,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)

The table is initially empty, to add users specify the username, password and default_hostgroup for basic configuration:

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

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('stnduser','stnduser',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 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root     |          | 1      | 0       | 1                 | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |
| stnduser | stnduser | 1      | 0       | 1                 | NULL           | 0             | 0                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
2 rows in set (0.00 sec)

By defining the default_hostgroup we are specifying which backend servers a user should connect to BY DEFAULT (i.e. this will be the default route for traffic coming from the specific user, additional rules can be configured to re-route however in their absence all queries will go to the specific hostgroup).

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

ProxySQL is now ready to serve traffic on port 6603 (by default):


mysql -u stnduser -p stnduser -h 10.10.0.1 -P6033 -e
"SELECT @@port"
Warning: Using a password on the command line interface can be insecure.
+--------+
| @@port |
+--------+
|  3306  |
+--------+

This query was sent to the server listening on port 3306 , the primary, as this is the server configured on hostgroup1 and is the default for user stnduser.

Functional tests

Sysbench is a useful tool to verify that ProxySQL is functional and benchmark system performance.

Assuming you have already configured sysbench (see Sysbench info here), you can run a load test against ProxySQL locally using the following command:

sysbench --report-interval=5 --num-threads=4 --num-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='stnduser' --mysql-password='stnduser' --oltp-table-size=10000 --mysql-host=127.0.0.1 --mysql-port=6033 run

[ output omitted ]

Note: For older versions of sysbench, report-interval should be removed and --db-ps-mode=disable added.

sysbench --num-threads=4 --max-requests=0 --max-time=20 --test=tests/db/oltp.lua --mysql-user='stnduser' --mysql-password='stnduser' --oltp-table-size=10000 --mysql-host=10.10.0.1 --mysql-port=6033 --db-ps-mode=disable run

[ output omitted ]

ProxySQL Statistics, the foundation for Query Rules

ProxySQL collects a lot of real time statistics in the stats schema, each table provides specific information about the behavior of ProxySQL and the workload being processed:

ProxySQL Admin> SHOW TABLES FROM stats;
+--------------------------------+
| tables                         |
+--------------------------------+
| stats_mysql_query_rules        |
| stats_mysql_commands_counters  |
| stats_mysql_processlist        |
| stats_mysql_connection_pool    |
| stats_mysql_query_digest       |
| stats_mysql_query_digest_reset |
| stats_mysql_global             |
+--------------------------------+
7 rows in set (0.00 sec)

stats_mysql_connection_pool

ProxySQL Admin> SELECT * FROM stats.stats_mysql_connection_pool;
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| hostgroup | srv_host  | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
| 1         | 10.10.0.1 | 3306     | ONLINE       | 0        | 4        | 5      | 0       | 144982  | 7865186         | 278734683       |
| 2         | 10.10.0.1 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
| 2         | 10.10.0.2 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
| 2         | 10.10.0.3 | 3306     | ONLINE       | 0        | 0        | 0      | 0       | 0       | 0               | 0               |
+-----------+-----------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+
4 rows in set (0.00 sec)

The stats_mysql_connection_pool table shows information related to the MySQL backends and the connection and overall traffic. The status of each server is tracked based on the health check results. Healthy servers will have a status of ONLINE, servers termporarily removed are SHUNNED and when a server is removed (completely removed, or moved away from a hostgroup), it is internally marked as OFFLINE_HARD.

stats_mysql_commands_counters

The stats_mysql_commands_counters table returns detailed information about the type of statements executed, and the distribution of execution time:

ProxySQL 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   | 1921940       | 7249      | 4214      | 2106      | 570     | 340     | 14       | 5        | 0         | 0         | 0      | 0      | 0       | 0        |
| COMMIT  | 5986400       | 7249      | 119       | 3301      | 1912    | 1864    | 44       | 8        | 1         | 0         | 0      | 0      | 0       | 0        |
| DELETE  | 2428829       | 7249      | 325       | 5856      | 585     | 475     | 5        | 3        | 0         | 0         | 0      | 0      | 0       | 0        |
| INSERT  | 2260129       | 7249      | 356       | 5948      | 529     | 408     | 6        | 2        | 0         | 0         | 0      | 0      | 0       | 0        |
| SELECT  | 40461204      | 101490    | 12667     | 69530     | 11919   | 6943    | 268      | 149      | 13        | 1         | 0      | 0      | 0       | 0        |
| UPDATE  | 6635032       | 14498     | 333       | 11149     | 1597    | 1361    | 42       | 16       | 0         | 0         | 0      | 0      | 0       | 0        |
+---------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
6 rows in set (0.00 sec)

stats_mysql_query_digest

Query information is tracked in the stats_mysql_query_digest which provides query counts per backend, reponse times per query as well as the actual query text as well as the query digest which is a unique identifier for every query type:

ProxySQL Admin> SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname         | username | digest             | digest_text                                                          | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 1         | sbtest             | stnduser | 0x13781C1DBF001A0C | SELECT c FROM sbtest1 WHERE id=?                                     | 72490      | 1456971810 | 1456971830 | 17732590 | 23       | 58935    |
| 1         | sbtest             | stnduser | 0x704822A0F7D3CD60 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c | 7249       | 1456971810 | 1456971830 | 9629225  | 20       | 121604   |
| 1         | sbtest             | stnduser | 0xADF3DDF2877EEAAF | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          | 7249       | 1456971810 | 1456971830 | 6650716  | 26       | 76159    |
| 1         | sbtest             | stnduser | 0x5DBEB0DD695FBF25 | COMMIT                                                               | 7249       | 1456971810 | 1456971830 | 5986400  | 64       | 59229    |
| 1         | sbtest             | stnduser | 0xCCB481C7C198E52B | UPDATE sbtest1 SET k=k+? WHERE id=?                                  | 7249       | 1456971810 | 1456971830 | 3948930  | 44       | 47860    |
| 1         | sbtest             | stnduser | 0x7DD56217AF7A5197 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     | 7249       | 1456971810 | 1456971830 | 3235986  | 22       | 24624    |
| 1         | sbtest             | stnduser | 0xE75DB8313E268CF3 | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                | 7249       | 1456971810 | 1456971830 | 3211197  | 51       | 29569    |
| 1         | sbtest             | stnduser | 0x5A23CA36FB239BC9 | UPDATE sbtest1 SET c=? WHERE id=?                                    | 7249       | 1456971810 | 1456971830 | 2686102  | 23       | 27779    |
| 1         | sbtest             | stnduser | 0x55319B9EE365BEB5 | DELETE FROM sbtest1 WHERE id=?                                       | 7249       | 1456971810 | 1456971830 | 2428829  | 29       | 11676    |
| 1         | sbtest             | stnduser | 0x10634DACE52A0A02 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              | 7249       | 1456971810 | 1456971830 | 2260129  | 61       | 13711    |
| 1         | sbtest             | stnduser | 0x4760CBDEFAD1519E | BEGIN                                                                | 7249       | 1456971810 | 1456971830 | 1921940  | 30       | 39871    |
| 1         | information_schema | stnduser | 0x9DD5A40E1C46AE52 | SELECT ?                                                             | 1          | 1456970758 | 1456970758 | 1217     | 1217     | 1217     |
| 1         | information_schema | stnduser | 0xA90D80E5831B091B | SELECT @@port                                                        | 1          | 1456970769 | 1456970769 | 273      | 273      | 273      |
| 1         | information_schema | stnduser | 0x52A2BA0B226CD90D | select @@version_comment limit ?                                     | 2          | 1456970758 | 1456970769 | 0        | 0        | 0        |
+-----------+--------------------+----------+--------------------+----------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
14 rows in set (0.00 sec)

Key query information can be filtered out to analyze the core traffic workload with a simple query:

ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                          |
+----+----------+------------+----------------------------------------------------------------------+
| 1  | 17732590 | 72490      | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 9629225  | 7249       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1  | 6650716  | 7249       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          |
| 1  | 5986400  | 7249       | COMMIT                                                               |
| 1  | 3948930  | 7249       | UPDATE sbtest1 SET k=k+? WHERE id=?                                  |
| 1  | 3235986  | 7249       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     |
| 1  | 3211197  | 7249       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                |
| 1  | 2686102  | 7249       | UPDATE sbtest1 SET c=? WHERE id=?                                    |
| 1  | 2428829  | 7249       | DELETE FROM sbtest1 WHERE id=?                                       |
| 1  | 2260129  | 7249       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              |
| 1  | 1921940  | 7249       | BEGIN                                                                |
| 1  | 1217     | 1          | SELECT ?                                                             |
| 1  | 273      | 1          | SELECT @@port                                                        |
| 1  | 0        | 2          | select @@version_comment limit ?                                     |
+----+----------+------------+----------------------------------------------------------------------+
14 rows in set (0.00 sec)

In the information provided it is clear that all traffic is sent to the primary instance on hostgroup1, in order to re-route this workload to a replica in hostgroup2 query rules are required.

MySQL Query Rules

Query rules are a very powerful vehicle to control traffic passing through ProxySQL and are configured in the mysql_query_rules table:

ProxySQL 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 NOT NULL DEFAULT 0,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
delay INT UNSIGNED,
error_msg VARCHAR,
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0)
1 row in set (0.01 sec)

To configure ProxySQL to send the top 2 queries to the replica hostgroup2, and everything else to the primary the following rules would be required required:

ProxySQL Admin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (10,1,'stnduser','^SELECT c FROM sbtest1 WHERE id=\?$',2,1);
Query OK, 1 row affected (0.00 sec)

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

Key points about these query rules (and query rules in general):

  • Query rules are processed as ordered by rule_id
  • Only rules that have active=1 are processed
  • The first rule example uses caret (^) and dollar ($) : these are special regex characters that mark the beginning and the end of a pattern i.e. in this case match_digestormatch_pattern should completely match the query
  • The second rule in the example doesn’t use caret or dollar : the match could be anywhere in the query
  • The question mark is escaped as it has a special meaning in regex
  • apply=1 means that no further rules should be evaluated if the current rule was matched

The current rule configuration can be checked in the mysql_query_rules, note: this configuration is not yet active:

ProxySQL Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='stnduser' 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 2 specific rules, queries will be sent to slaves. If no rules match the query, the default_hostgroup configured for the user applies (that is 1 for user stnduser).

The stats_mysql_query_digest_reset can be queried to retrieve the previous workload and clear the contents of the stats_mysql_query_digest table , and truncate it, this is recommended before activating query rules to easily review the changes.

Load the query rules to runtime to activate changes :

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

After traffic passes through the new configuration the stats_mysql_query_digest will reflect the changes in routing per query:

ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                          |
+----+----------+------------+----------------------------------------------------------------------+
| 2  | 14520738 | 50041      | SELECT c FROM sbtest1 WHERE id=?                                     |
| 2  | 3203582  | 5001       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| 1  | 3142041  | 5001       | COMMIT                                                               |
| 1  | 2270931  | 5001       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          |
| 1  | 2021320  | 5003       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     |
| 1  | 1768748  | 5001       | UPDATE sbtest1 SET k=k+? WHERE id=?                                  |
| 1  | 1697175  | 5003       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                |
| 1  | 1346791  | 5001       | UPDATE sbtest1 SET c=? WHERE id=?                                    |
| 1  | 1263259  | 5001       | DELETE FROM sbtest1 WHERE id=?                                       |
| 1  | 1191760  | 5001       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              |
| 1  | 875343   | 5005       | BEGIN                                                                |
+----+----------+------------+----------------------------------------------------------------------+
11 rows in set (0.00 sec)

The top 2 queries identified are sent to the hostgroup2 replicas.

Aggregated results can also be viewed in the  stats_mysql_query_digest table, for example:

ProxySQL 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  | 21523008      | 59256           |
| 2  | 23915965      | 72424           |
+----+---------------+-----------------+
2 rows in set (0.00 sec)

Query Caching

A popular use-case for ProxySQL is to act as a query cache. By default, queries aren’t cached, this is enabled by setting cache_ttl (in milliseconds) on a rule defined in mysql_query_rules .

To cache all the queries sent to replicas for 5 seconds update the cache_ttl on the query rules defined in the previous example:

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

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

ProxySQL Admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; -- we reset the counters
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

After traffic passes through the new configuration the stats_mysql_query_digest will show the cached queries with a hostgroup value of “-1”:

ProxySQL Admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                          |
+----+----------+------------+----------------------------------------------------------------------+
| 1  | 7457441  | 5963       | COMMIT                                                               |
| 1  | 6767681  | 5963       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c          |
| 2  | 4891464  | 8369       | SELECT c FROM sbtest1 WHERE id=?                                     |
| 1  | 4573513  | 5963       | UPDATE sbtest1 SET k=k+? WHERE id=?                                  |
| 1  | 4531319  | 5963       | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ?+?                     |
| 1  | 3993283  | 5963       | SELECT SUM(K) FROM sbtest1 WHERE id BETWEEN ? AND ?+?                |
| 1  | 3482242  | 5963       | UPDATE sbtest1 SET c=? WHERE id=?                                    |
| 1  | 3209088  | 5963       | DELETE FROM sbtest1 WHERE id=?                                       |
| 1  | 2959358  | 5963       | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?)              |
| 1  | 2415318  | 5963       | BEGIN                                                                |
| 2  | 2266662  | 1881       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0        | 4082       | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0        | 51261      | SELECT c FROM sbtest1 WHERE id=?                                     |
+----+----------+------------+----------------------------------------------------------------------+
13 rows in set (0.00 sec)

Query Rewrite

To match the text of a query ProxySQL provides 2 mechanisms:

  • match_digest : match the regular expression against the digest of the query which strips SQL query data (e.g. `SELECT c FROM sbtest1 WHERE id=?` as represented in stats_mysql_query_digest.query_digest
  • match_pattern : match the regular expression against the actual text of the query (e.g. `SELECT c FROM sbtest1 WHERE id=2`

The digest is always smaller than the query itself, running a regex against a smaller string is faster and it is recommended (for performance) to use match_digest. To rewrite queries or match against the query text itself use match_pattern.

For example:

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


ProxySQL 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            | 5000      | 1     |
| 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 5000      | 1     |
| 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)

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

This configuration would result in the following behavior:

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 |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
| 48560 | 10      | ^SELECT c FROM sbtest1 WHERE id=\?  | NULL                   | NULL            | 5000      | 1     |
| 4856  | 20      | DISTINCT c FROM sbtest1             | NULL                   | NULL            | 5000      | 0     |
| 4856  | 30      | NULL                                | DISTINCT(.*)ORDER BY c | DISTINCT\1      | NULL      | 1     |
+-------+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.01 sec)
Feel confident to move on to more advanced configuration, here is a link on How to set up ProxySQL Read/Write Split