Using ProxySQL caching to reduce MySQL query latency
In a previous post I discussed an issue where 1ms of increased latency to a MySQL server was causing a problem due to thousands of queries.
Generally, there are only two ways to handle this problem:
- Bring the servers closer together (according to the speed of light);
- Reduce the number of queries being sent to the database server.
In our case, the database server was already only 2ms away and they didn't want to run it on the same server as the webserver, so option 1 was unlikely. Leaving us with option 2 - reduce the number of queries.
Looking through the queries being sent with a crude uniq, most of them were repeated:
$ wc -l sample.sql
4198 ./sample.sql
$ cat sample.sql | sort -u | wc -l
319Removing all of the duplicates would remove around 4 seconds of loading time, but of course nothing is ever that simple - to remove all of the duplicates we would need to pick through the codebase, find where the queries are generated, and carefully rewrite those sections - plus if the data was needed in more than one place we'd need to handle that.
Yet reinstating the problem, we only need to reduce the number of queries sent to the remote server, not the number of queries generated. If we add a local cache, we could prevent some duplicates being sent to the remote server at all.
ProxySQL offers transparent caching - you can start ProxySQL in a container, tell the application to use it instead of the MySQL server, and ProxySQL will re-route the queries to the real database servers, caching the results if we want.
Configuring ProxySQL
First, ProxySQL needs a user to on the remote database server to monitor health:
CREATE USER 'proxysql'@'webserver_ip' IDENTIFIED BY 'ziepaeLeijauC4teeRoh';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'proxysql'@'webserver_ip';Next, create the Docker Compose configuration file docker-compose.yaml. I'll use a hardcoded private IP address:
services:
proxysql:
container_name: mysql-cache
build:
context: .
dockerfile_inline: |
FROM proxysql/proxysql
COPY proxysql.cnf /etc/proxysql.cnf
expose:
- 3306
networks:
silvermouse_proxysql:
ipv4_address: 172.20.0.2
networks:
silvermouse_proxysql:
driver: bridge
ipam:
driver: default
config:
- subnet: 172.20.0.0/24To begin with, I'll use a ProxySQL configuration with no caching. Create the ProxySQL configuration file proxysql.cnf in the same directory as your Compose file:
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6032"
}
mysql_variables=
{
interfaces="0.0.0.0:3306"
monitor_username="proxysql"
monitor_password="ziepaeLeijauC4teeRoh"
}
mysql_servers =
(
{ address="remote_mysql_hostname" , port=3306 , hostgroup=1, max_connections=100 },
)
mysql_users =
(
{ username = "remote_db_user", password = "secure_password", default_hostgroup = 1, active = 1 },
)- The
monitor_usernameandmonitor_passwordneed to be the same ones you used when creating the monitor user. - The
admin_credentialsare only valid from inside the container, to connect to the ProxySQL admin interface so choose your threat model here but leaving it asadmin:adminis probably fine. - The
mysql_usersmust be the same as you would use to connect to the remote server. ProxySQL uses them to authenticate local connections, and then uses the same credentials to connect to the backend database.
That's it to begin with begin with for configuration. docker compose up -d to bring the ProxySQL server online.
Query analysis without caching
Next, run your sample SQL file against ProxySQL without caching (if you don't have one, you can always just run it without caching for a day or two):
$ time cat ~/sample.sql | mysql -h172.20.0.2 -uremote_db_user -psecure_password >/dev/null
mysql: [Warning] Using a password on the command line interface can be insecure.
real 0m5.836s
user 0m0.743s
sys 0m0.120sNext, let's have a look at the top
docker exec -it mysql-cache mysql -uadmin -padmin -P6032
MySQL [(none)]> SELECT count_star, sum_time, digest, digest_text FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 15;
+------------+----------+--------------------+-------------------------------------------------------------------------------------------+
| count_star | sum_time | digest | digest_text |
+------------+----------+--------------------+-------------------------------------------------------------------------------------------+
| 1497 | 0 | 0x77F060C88B214206 | SET NAMES ? |
| 488 | 737981 | 0x83EFC80F38C85397 | SELECT DATE_ADD(?,INTERVAL ? DAY) |
| 144 | 205722 | 0xD0A9ABFFB18A73EE | SELECT Tue FROM workshop_labour WHERE Code = ? AND Depot = ? |
| 144 | 182194 | 0xAA2FAC167B2A50B0 | SELECT Sun FROM workshop_labour WHERE Code = ? AND Depot = ? |
| 144 | 195482 | 0xA5E4401ABAF029C4 | SELECT Mon FROM workshop_labour WHERE Code = ? AND Depot = ? |
| 144 | 187877 | 0xBAFD1D5DB85FD1C8 | SELECT Wed FROM workshop_labour WHERE Code = ? AND Depot = ? |
| 144 | 196041 | 0xBB2A08D05656FE04 | SELECT Thu FROM workshop_labour WHERE Code = ? AND Depot = ? |
| 128 | 164689 | 0x31DB18A4CB5DB7B1 | SELECT Sat FROM workshop_labour WHERE Code = ? AND Depot = ? |
| 128 | 159310 | 0xCF4F5E45A2FA0672 | SELECT Fri FROM workshop_labour WHERE Code = ? AND Depot = ? |
| 9 | 13264 | 0x78A59BD7AA066742 | SELECT order_status FROM order_parameters LIMIT ? |
| 9 | 15284 | 0xAA8E75728BAEFFA1 | SELECT config_list FROM workshop_parameters |
| 9 | 11402 | 0xCB407AFAF2F7510E | SELECT default_date FROM order_parameters LIMIT ? |
| 6 | 7123 | 0xB602500ED3B3FE62 | SELECT VAT_Rate_Description FROM accounts_vat_rate |
| 5 | 6442 | 0x6379E4AAEE0C1C2B | SELECT * FROM accounts_file WHERE CustomerID = ? |
| 5 | 7627 | 0xF6928D8397781073 | SELECT * FROM counts_file |
+------------+----------+--------------------+-------------------------------------------------------------------------------------------+
20 rows in set (0.009 sec)Over 30% of the queries are coming from those first 8 SELECT queries, which in this case were safe to cache, so time to cache them and try again.
Configuring ProxySQL to cache queries
I decided to cache the selected queries from the previous section based on their digest. Add the following to the end of the proxysql.cnf configuration file, the cache_ttl is in milliseconds, I've selected 2 seconds for everything apart from DATE_ADD:
mysql_query_rules =
(
# SELECT DATE_ADD(?,INTERVAL ? DAY)
{ rule_id=1, active=1, digest="0x83EFC80F38C85397", cache_ttl=30000, apply=1 },
# SELECT Sun FROM workshop_labour WHERE Code = ? AND Depot = ?
{ rule_id=2, active=1, digest="0xAA2FAC167B2A50B0", cache_ttl=2000, apply=1 },
# SELECT Tue FROM workshop_labour WHERE Code = ? AND Depot = ?
{ rule_id=3, active=1, digest="0xD0A9ABFFB18A73EE", cache_ttl=2000, apply=1 },
# SELECT Mon FROM workshop_labour WHERE Code = ? AND Depot = ?
{ rule_id=4, active=1, digest="0xA5E4401ABAF029C4", cache_ttl=2000, apply=1 },
# SELECT Wed FROM workshop_labour WHERE Code = ? AND Depot = ?
{ rule_id=5, active=1, digest="0xBAFD1D5DB85FD1C8", cache_ttl=2000, apply=1 },
# SELECT Thu FROM workshop_labour WHERE Code = ? AND Depot = ?
{ rule_id=6, active=1, digest="0xBB2A08D05656FE04", cache_ttl=2000, apply=1 },
# SELECT Sat FROM workshop_labour WHERE Code = ? AND Depot = ?
{ rule_id=7, active=1, digest="0x31DB18A4CB5DB7B1", cache_ttl=2000, apply=1 },
# SELECT Fri FROM workshop_labour WHERE Code = ? AND Depot = ?
{ rule_id=8, active=1, digest="0xCF4F5E45A2FA0672", cache_ttl=2000, apply=1 },
)Rebuild the container with docker compose up --build --force-recreate -d
Retest with caching enabled:
$ time cat ~/sample.sql | mysql -h172.20.0.2 -uremote_db_user -psecure_password >/dev/null
mysql: [Warning] Using a password on the command line interface can be insecure.
real 0m2.104s
user 0m0.489s
sys 0m0.113s64% improvement in speed with minimal effort. The cached queries will be sent to hostgroup -1:
MySQL [(none)]> SELECT IIF(hostgroup = -1, "YES", "NO") as cached, digest_text, digest, count_star, sum_time FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 5;
+--------+-------------------------------------------------------------------------------------------+--------------------+------------+----------+
| cached | digest_text | digest | count_star | sum_time |
+--------+-------------------------------------------------------------------------------------------+--------------------+------------+----------+
| NO | SET NAMES ? | 0x77F060C88B214206 | 1497 | 0 |
| YES | SELECT DATE_ADD(?,INTERVAL ? DAY) | 0x83EFC80F38C85397 | 427 | 0 |
| YES | SELECT Sun FROM workshop_labour WHERE Code = ? AND Depot = ? | 0xAA2FAC167B2A50B0 | 136 | 0 |
| YES | SELECT Mon FROM workshop_labour WHERE Code = ? AND Depot = ? | 0xA5E4401ABAF029C4 | 136 | 0 |
| YES | SELECT Wed FROM workshop_labour WHERE Code = ? AND Depot = ? | 0xBAFD1D5DB85FD1C8 | 136 | 0 |
+--------+-------------------------------------------------------------------------------------------+--------------------+------------+----------+That's it. The reconfigure the application to use 172.20.0.2 instead of the remote MySQL server, and it will immediately start to benefit from cache.