Hi There ! This guide entails a step by step guide on how to setup proxysql and configuring it for caching.
Installation (rhel,almalinux)
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo [proxysql_repo] name=ProxySQL repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/almalinux/\$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key EOF
[proxysql_repo]
name=ProxySQL repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/almalinux/\$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.7.x/repo_pub_key EOF”
yum install proxysql
clear
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
ProxySQL Admin>
SELECT * FROM mysql_servers;
SELECT * from mysql_replication_hostgroups;
SELECT * from mysql_query_rules;
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'127.0.0.1',3306);
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
Create proxy monitoring user mysql
sudo mysql
mysql
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor'; GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'monitor'@'%'; FLUSH PRIVILEGES;
Then add the credentials of the monitor user to ProxySQL: ProxySQL Admin>
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
ProxySQL Admin>
LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
Backend’s health check
Once the previous configuration is active, it’s time to promote the servers to runtime to enable monitoring: ProxySQL Admin>
LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
SELECT * FROM mysql_servers;
Once the configuration is active, it’s possible to 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)
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)
This way we can verify that the servers are being monitored correctly and are healthy.
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, although the MySQL backends might all be right now in a single hostgroup.
For example:
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');
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.
ProxySQL Admin>
LOAD MYSQL SERVERS TO RUNTIME;
The read_only check results are logged to the mysql_servers_read_only_log table in the monitor database:
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)
As a final step, persist the configuration to disk.
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;
MySQL Users
After configuring the MySQL server backends in mysql_servers the next step is to configure mysql users. We are creating a MySQL user with no particular restrictions: this is not a good practice the user should be configured with proper connection restrictions and privileges according to the setup and the application needs. To create the user in MySQL connect to the PRIMARY and execute: mysql>
CREATE USER 'stnduser'@'%' IDENTIFIED BY 'stnduser';
GRANT ALL PRIVILEGES ON *.* TO 'stnduser'@'%';
Time to configure the user into ProxySQL: this is performed by adding entries to the mysql_users table:
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('stnduser','stnduser',1);
SELECT * FROM mysql_users;
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; SAVE MYSQL USERS TO DISK;
ProxySQL is now ready to serve traffic on port 6033 (by default):
sudo mysql -u stnduser -pstnduser -h 127.0.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.
Sysbench
Sysbench is a useful tool to verify that ProxySQL is functional and benchmark system performance.
Installation
RHEL/CentOS:
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash sudo yum -y install sysbench
Mysql Config
In mysql my.cnf add
sudo vi /etc/my.cnf
innodb_data_home_dir = /data/mysql/ innodb_data_file_path = ibdata1:128M:autoextend innodb_log_group_home_dir = /data/mysql/ innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 32M innodb_log_file_size = 256M innodb_log_buffer_size = 16M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 innodb_doublewrite = 0 innodb_flush_method = O_DIRECT innodb_thread_concurrency = 0 innodb_max_dirty_pages_pct = 80 table_open_cache = 512 thread_cache = 512 query_cache_size = 0 query_cache_type = 0
Then
sudo systemctl stop mysql sudo systemctl start mysql
Start and prepare database to use
mysqladmin -uroot drop sbtest mysqladmin -uroot create sbtest
Introduction
We use the latest sysbench with Lua scripting support. Therefore the test names differ from sysbench <= 0.4. To get reasonable results we use a run time of 5 minutes.
Setup
To simulate load on mysqldb directly
- Set Test Dir
TEST_DIR="/usr/share/sysbench"
- Preparation
sysbench ${TEST_DIR}/oltp_read_write.lua --db-driver=mysql --mysql-user=stnduser --mysql-password=stnduser --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sbtest --tables=1 --table-size=2000000 prepare
- Running
NUM_THREADS="1 4 8 16 32 64 128"
for THREAD in $NUM_THREADS; do
echo "=== Running oltp_read_write.lua with $THREAD threads ==="
sysbench \
${TEST_DIR}/oltp_read_write.lua \
--db-driver=mysql \
--mysql-user=stnduser \
--mysql-password=stnduser \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-db=sbtest \
--tables=1 \
--table-size=2000000 \
--threads=$THREAD \
--time=300 \
--events=0 \
run
echo ""
done
- cleanup
sysbench \
${TEST_DIR}/oltp_read_write.lua \
--db-driver=mysql \
--mysql-user=stnduser \
--mysql-password=stnduser \
--mysql-host=127.0.0.1 \
--mysql-port=3306 \
--mysql-db=sbtest \
--tables=1 \
--table-size=2000000 \
cleanup
Functional Test With Sysbench
You can run a load test against ProxySQL locally using the following command:
- Set Test Dir
TEST_DIR="/usr/share/sysbench"
- Simulate the load
sysbench ${TEST_DIR}/oltp_read_write.lua \
--threads=4 \
--time=20 \
--report-interval=5 \
--events=0 \
run \
--mysql-user=stnduser \
--mysql-password=stnduser \
--mysql-host=127.0.0.1 \
--mysql-port=6033 \
--mysql-db=sbtest \
--table-size=10000
ProxySQL Statistics
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:
- Run
SHOW TABLES FROM stats;
- stats_mysql_connection_pool
SELECT * FROM stats.stats_mysql_connection_pool;
- 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:
SELECT * FROM stats_mysql_commands_counters WHERE Total_cnt;
- 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;
- Key query information can be filtered out to analyze the core traffic workload with a simple query:
SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
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. 6. MySQL Query Rules 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: 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);
INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (20,1,'stnduser','DISTINCT c FROM sbtest1',2,1);
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;
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 |
+----+----------+------------+----------------------------------------------------------------------+
<strong>| 2 | 14520738 | 50041 | SELECT c FROM sbtest1 WHERE id=? |
| 2 | 3203582 | 5001 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |</strong>
| 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;
LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
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 |
<strong>| -1 | 0 | 4082 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ?+? ORDER BY c |
| -1 | 0 | 51261 | SELECT c FROM sbtest1 WHERE id=? |</strong>
+----+----------+------------+----------------------------------------------------------------------+
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','DISTINCT1',1);
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 | DISTINCT1 | NULL | 1 |
+---------+-------------------------------------+------------------------+-----------------+-----------+-------+
3 rows in set (0.00 sec)
ProxySQL Admin>
LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec) This configuration would result in the following behavior: ProxySQL 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 | DISTINCT1 | 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
Disable Monitoring
Delete the SHUNNED server from mysql_servers:
DELETE FROM mysql_servers WHERE hostgroup_id = 2 AND hostname = '127.0.0.1' AND port = 3306; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
Disable monitoring
UPDATE global_variables SET variable_value='false' WHERE variable_name='mysql-monitor_enabled'; UPDATE global_variables SET variable_value='0' WHERE variable_name='mysql-monitor_groupreplication_healthcheck_interval'; UPDATE global_variables SET variable_value='0' WHERE variable_name='mysql-monitor_groupreplication_healthcheck_max_timeout_count'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
SELECT variable_name, variable_value FROM global_variables WHERE variable_name LIKE 'mysql-monitor_groupreplication%';
Enable Monitoring
UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-monitor_enabled'; UPDATE global_variables SET variable_value='5000' WHERE variable_name='mysql-monitor_groupreplication_healthcheck_interval'; UPDATE global_variables SET variable_value='4' WHERE variable_name='mysql-monitor_groupreplication_healthcheck_max_timeout_count'; LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO DISK;
socket=/var/lib/mysql/mysql2.sock port=3307 bind-address=127.0.0.1
Then, restart MySQL server:
systemctl restart mysqld
The server address is the WHM server, 127.0.0.1. The listening port is 3306 on the WHM server, taking over the local MySQL which is already running on port 3307. Further down, we specify the ProxySQL admin and monitoring users’ password. Then include MySQL server into the load balancing set and then choose “No” in the Implicit Transactions section.
Proxysql is already setup and configured on the server so we skip its installation and configuration.
The next step is to grant MySQL root user and import it into ProxySQL. Occasionally, WHM somehow connects to the database via TCP connection, bypassing the UNIX socket file. In this case, we have to allow MySQL root access from both root@localhost and root@127.0.0.1 (the IP address of WHM server) in our mysql database.
Thus, running the following statement on server is necessary:
mysql -uroot -pmysql> GRANT ALL PRIVILEGES ON *.* TO whm_cpanel_usr@'127.0.0.1' IDENTIFIED BY 'zEw)7!sd+8Xf' WITH GRANT OPTION;
Then, import ‘root’@’localhost’ user from our MySQL server into ProxySQL
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','zEw)7!sd+8Xf',1);
LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
Port 3306 is what ProxySQL should be listening to accept all MySQL connections. Port 6032 is the ProxySQL admin port, where we will connect to configure and monitor ProxySQL components like users, hostgroups, servers and variables.
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 488605
Server version: 8.0.41 MySQL Community Server - GPL
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Notice the server version is 8.0.41 (ProxySQL). If you can connect as above, we can configure the integration part as described in the next section.
Configure Unit Sock file for localhost connections
Tools using a socket, ProxySQL should listen on a UNIX socket too: Make sure the file is owned/readable by the mysql
chown mysql:mysql /tmp/proxysql.sock chmod 755 /tmp/proxysql.sock
Then restart ProxySQL:
systemctl restart proxysql
Then ensure /var/lib/mysql/mysql.sock symlink exists and points here:
/var/lib/mysql/mysql.sock -> /tmp/proxysql.sock
This lets anything using the default MySQL socket (localhost) hit ProxySQL.
Query Caching
Using ProxySQL to act as a query cache.
Note
Rule order matters: lower rule_id = higher priority. fast_forward = bypass full parse & speed routing. cache_ttl only applies to SELECT rules.
Hostgroups
We have two hostgroups already defined: hostgroup 1 → Primary/MySQL (writes & fallback reads) hostgroup 2 → Replica/MySQL (read-only)
- Verify
SELECT * FROM mysql_servers;
ProxySQL Admin>
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
UPDATE mysql_query_rules set cache_ttl=5000 WHERE active=1 AND destination_hostgroup=2; LOAD MYSQL QUERY RULES TO RUNTIME;
SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; --- After traffic passes through the new configuration the stats_mysql_query_digest will show the cached queries with a hostgroup value of “-1”: SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
Shell>
TEST_DIR="/usr/share/sysbench"
sysbench /usr/share/sysbench/oltp_read_write.lua \ --threads=4 \ --time=20 \ --report-interval=5 \ --events=0 \ run \ --mysql-user=stnduser \ --mysql-password=stnduser \ --mysql-host=127.0.0.1 \ --mysql-port=3306 \ --mysql-db=sbtest \ --table-size=10000
View mysql query digest stats
SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
Cache Optimization Settings
-- Delete existing rules DELETE FROM mysql_query_rules; -- Configure query cache rules for read/write split -- Rule 1: Direct all write operations to hostgroup 1 (your existing rule with a proper rule_id) INSERT INTO mysql_query_rules (rule_id, active, match_pattern, re_modifiers, destination_hostgroup, apply) VALUES (10, 1, '^(INSERT|UPDATE|DELETE|REPLACE|BEGIN|COMMIT|ROLLBACK)', 'CASELESS', 1, 1); -- Rule 2: Direct SELECT queries for single-row lookups with high cache value to read hostgroup with caching INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, cache_ttl, apply) VALUES (20, 1, '^SELECT c FROM sbtest1 WHERE id=\?$', 2, 600, 1); -- Rule 3: Cache the expensive range queries INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, cache_ttl, apply) VALUES (30, 1, '^SELECT .* FROM sbtest1 WHERE id BETWEEN \? AND \?', 2, 300, 1); -- Rule 4: Handle the single-row lookups (high frequency) INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, cache_ttl, apply) VALUES (40, 1, '^SELECT .* FROM sbtest1 WHERE id=\?', 2, 600, 1); -- Rule 5: Handle the SUM operation (analytical query) INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, cache_ttl, apply) VALUES (50, 1, '^SELECT SUM\(k\) FROM sbtest1 WHERE id BETWEEN \? AND \?', 2, 600, 1); -- Rule 6: Default rule to send remaining SELECT queries to read replica INSERT INTO mysql_query_rules (rule_id, active, match_pattern, re_modifiers, destination_hostgroup, apply) VALUES (100, 1, '^SELECT ', 'CASELESS', 2, 1); -- Load the new configuration into runtime LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK; -- End of rules -- Configure settings --SSL & Security mysql-have_ssl :Disabled SSL to reduced overhead: SET mysql-have_ssl = 0; --Improves resilience to backend failures mysql-connect_timeout_server Default: 10,000 ms SET mysql-connect_timeout_server = 1000; -- 1 second -- multiplexing SET mysql-multiplexing = 1; -- Optimize connection SET mysql-max_transaction_time = 2000; -- Cache ttl SET mysql-query_cache_ttl=60000; -- Default Cache Size 128 MB SET mysql-query_cache_size_MB = 300; -- Enable storing empty results SET mysql-query_cache_stores_empty_result=1; -- Adjust max connections SET mysql-max_connections = 2048; -- mysql-poll_timeout: Default: 1000 µs, Lower timeout for faster I/O polling: Helps in high-load scenarios SET mysql-poll_timeout = 500; -- Microseconds -- Adjusted based on CPU cores SET mysql-threads = 4; -- Configure connection free timeout to free up idle connections SET mysql-free_connections_pct = 120000; -- Apply and save SAVE MYSQL VARIABLES TO DISK; LOAD MYSQL VARIABLES TO RUNTIME; -- Adjust max connections per hostgroup for load balancing UPDATE mysql_servers SET max_connections=1000 WHERE hostgroup_id=1; -- Write hostgroup UPDATE mysql_servers SET max_connections=2000 WHERE hostgroup_id=2; -- Read hostgroup -- Improves write performance by skipping rules/cache [File ]. UPDATE mysql_users SET fast_forward = 1 WHERE username = 'stnduser'; LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;
Proxsyql Optimization
This guide highlights proxysql optimization
Connection Handling
-- Disable SSL to reduce overhead. Connections will be via localhost SET mysql-have_ssl = 0; -- Optimize connection limits SET mysql-max_connections = 1024; -- Better connection multiplexing SET mysql-multiplexing = 1; SET mysql-connection_delay_multiplex_ms = 100; -- Faster dead connection detection SET mysql-ping_interval_server_msec = 5000; -- Default 200 ms SET mysql-ping_timeout_server = 900; -- Default is 4 hours SET mysql-max_transaction_idle_time = 3600000;
Failover and latency
-- Set max latency to 2s for slow backends SET mysql-default_max_latency_ms = 2000;
Better Session Management
-- Optimize session idle timeouts -- Default 1ms SET mysql-session_idle_ms = 1000; -- Set connection wait timeout to 15 minutes SET mysql-wait_timeout = 900000; -- Connection age management set to 2h max SET mysql-connection_max_age_ms = 1800000; -- Sessions with active transactions running more than 2h are killed. SET mysql-max_transaction_time = 7200000;
Timeouts
Increase connection timeouts to reduce connection errors
-- Default: 10000ms SET mysql-connect_timeout_client = 10000; -- Terminate connections early SET mysql-connect_timeout_server=4000; -- Default 10s SET mysql-connect_timeout_server_max=8000; SET mysql-connect_retries_on_failure=3;
Warming
-- Enable connection warming for better performance SET mysql-connection_warming = 1;
Optimize throttling
-- Default is 1m which is okay SET mysql-throttle_connections_per_sec_to_hostgroup = 1000000; -- Improve connection retry logic SET mysql-connect_retries_on_failure = 3; SET mysql-connect_retries_delay = 1;
Thread management
Set worker threads to 8
-- Default is 4 SET mysql-threads = 8; -- Optimize monitor threads since there are 2 backends -- Default 128 SET mysql-monitor_threads_max = 32; -- Default 8 SET mysql-monitor_threads_min = 2; -- Default 128 SET mysql-monitor_threads_queue_maxsize = 64;
Buffer and Memory Utilization
1. Cache Utilization
-- Increase cache size for better hit ratio SET mysql-query_cache_size_MB = 2048; -- Keep 10% of connections for periods of spikes SET mysql-query_cache_soft_ttl_pct = 10; -- Don't cache empty results SET mysql-query_cache_stores_empty_result = 0; --Do not cache querries with warnings SET mysql-query_cache_handle_warnings = 0; -- Optimize eventslog for better cache analysis -- Default 10000 SET mysql-eventslog_table_memory_size = 50000;
2. Memory
-- Optimize result set handling -- Default is 4194304 (8MB) SET mysql-threshold_resultset_size = 8388608; -- Default is 524288 (1MB) SET mysql-threshold_query_length = 1048576; -- Default or Current: 67108864 (128MB) SET mysql-max_allowed_packet = 6710886; -- Optimize stack size for complex queries -- Default 1048576 (2MB) SET mysql-stacksize = 2097152;
Transaction Handling
-- Optimize transaction timeouts -- 30 mins max SET mysql-max_transaction_time = 3600000; -- Default 14400000ms (2hr) SET mysql-max_transaction_idle_time = 3600000; -- Better transaction multiplexing SET mysql-auto_increment_delay_multiplex = 3; SET mysql-auto_increment_delay_multiplex_timeout_ms = 5000; -- Step 6: Set idle connection timeout (optional) SET mysql-max_transaction_idle_time=300000;
Transaction:Advanced:- Improve autocommit handling
SET mysql-forward_autocommit=1; SET mysql-autocommit_false_not_reusable = true; SET mysql-autocommit_false_is_transaction = true;
Query Handling
-- Query Processing Optimization SET mysql-query_retries_on_failure = 2; -- Default 8640000 SET mysql-default_query_timeout = 60000000; -- Increase statement cache -- Default 10000 SET mysql-max_stmts_cache = 50000; -- Default 20 SET mysql-max_stmts_per_connection = 50; -- Better reset handling SET mysql-reset_connection_algorithm = 2; SET mysql-connpoll_reset_queue_length = 100;
Digest Optimization
-- Improve query digest analysis -- Default 2048 SET mysql-query_digests_max_digest_length = 1024; -- Default 65000 SET mysql-query_digests_max_query_length = 131072; -- Better query normalization SET mysql-query_digests_normalize_digest_text = 1; -- SET mysql-query_digests_lowercase = 1; -- SET mysql-query_digests_no_digits = 1; -- Enhanced grouping SET mysql-query_digests_grouping_limit = 3; SET mysql-query_digests_groups_grouping_limit = 1;
Load Handling Without Performance Degradation
-- Default 5 (more aggressive) SET mysql-shun_on_failures = 100; -- Default 10 (Recover faster) SET mysql-shun_recovery_time_sec = 1;
Monitoring optimization
Disable monitoring for now
-- Monitoring SET mysql-monitor_enabled = 0; SET mysql-monitor_ping_interval = 3000; SET mysql-monitor_read_only_interval = 2000; -- Better ping failure handling SET mysql-monitor_ping_max_failures = 2; -- Set connect interval to 5 seconds SET mysql-monitor_connect_interval = 5000;
Logging
Logging
-- Enable MySQL warnings logging SET mysql-log_mysql_warnings_enabled = 1;
Future Proofing
— Set query processor iterations to 1 SET mysql-query_processor_iterations = 1; — Statement cache — Set max statements per connection to 30 SET mysql-max_stmts_per_connection = 30; — Set query cache stickiness to 1 SET mysql-query_cache_stickiness = 1; — Set max packet size to 64MB SET mysql-max_allowed_packet = 67108864; — Set multiplexing delay to 10 ms SET mysql-connection_delay_multiplex_ms = 10; — Set default query timeout to 120 seconds SET mysql-default_query_timeout = 120000; — Optimize polling and timeouts SET mysql-poll_timeout = 1000; — Current: 2000ms SET mysql-poll_timeout_on_failure = 50; — Better query processing SET mysql-query_processor_iterations = 1000; — Current: 0 SET mysql-query_processor_regex = 2; — Current: 1 — Faster query routing SET mysql-query_rules_fast_routing_algorithm = 2; — Current: 1 — Better query normalization SET mysql-query_digests_normalize_digest_text = true; SET mysql-query_digests_lowercase = true; SET mysql-query_digests_no_digits = true; — cache stickiness SET mysql-query_cache_stickiness = 0; SET mysql-query_digests_max = 10000; SET mysql-query_digests_enable = true; SET mysql-query_digests_sample_rate = 100; SET mysql-query_digests_lifetime_ms = 60000; — Adjust server weights for load balancing — Writer Hostgroup UPDATE mysql_servers SET weight = 45 WHERE hostgroup_id = 1; — Hostgroup 2 handles reads (frequently used) UPDATE mysql_servers SET weight = 55 WHERE hostgroup_id = 2;