Sep 19, 2024

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
319

Removing 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/24

To 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_username and monitor_password need to be the same ones you used when creating the monitor user.
The admin_credentials are only valid from inside the container, to connect to the ProxySQL admin interface so choose your threat model here but leaving it as admin:admin is probably fine.
The mysql_users must 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.120s

Next, 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.113s

64% 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.