Automatic MySQL Query Cache with Readyset and ProxySQL

In our previous blog post we announced Readyset version stable-240328 comes with the ability to integrate with ProxySQL. This version inspired the creation of a dedicated scheduler for integrating Readyset with ProxySQL, bringing the ability to automatic inspect your workload and cache problematic queries:

Use Scheduler to Integrate Readyset and ProxySQL

To easily integrate Readyset and ProxySQL, we have created a scheduler to automatically redirect queries supported by Readyset to the hostgroup Readyset is configured.

The scheduler will query stats_mysql_query_digest to gather the list of queries for a specific hostgroup ( configured in the scheduler via the source_hostgroup parameter). For each query, it will connect to readyset and validate if the query is supported by readyset.

If the query is supported by readyset, the scheduler automatically creates a new cache entry for that query.

As with all cache systems, you start with your cache in a cold state and as your cache starts to handle your traffic, it becomes hot and most of the access to your cache will result in a cache hit. Having this in mind, the scheduler can mirror the traffic for this particular query for a period of time, before fully switching the query rule to set readyset as a destination for this query, this allows for readyset to warm-up the dataflow graphs responsible for manipulating the query.

This behavior is configured by warmup_time . If set to 0, the query will be redirected to readyset without warm-up time, if set to any number > 0 the query will first be mirror to readyset, meaning that readyset will receive a copy of the query request, while the client will still be served by the original destination server. Once warmup_time seconds have elapsed, the query rule will be updated and Readyset will start to be the server responsible for handling the query request.

ProxySQL Configuration To Add Readyset

Assuming you already have ProxySQL configured and running, you will need to:

• Add Readyset server as a new hostgroup for cache queries. A good practice here is to also add one of your read replicas with a small height, in case Readyset becomes unavailable, the read replica will be serving the read traffic.

INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, comment) VALUES (99, '127.0.0.1', 3307, 1000, 'Readyset'), (10, '127.0.0.1', 3306, 1, 'Read Replica');
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

• Download Readyset ProxySQL Scheduler from https://github.com/readysettech/proxysql_scheduler/releases
• Adjust /etc/readyset_proxysql_scheduler.cnf to reflect your Readyset and ProxySQL configuration
• Add a new scheduler:

INSERT INTO scheduler (active, interval_ms, filename, arg1) VALUES (1, 10000, '/usr/bin/readyset_proxysql_scheduler', '--config=/etc/readyset_proxysql_scheduler.cnf');
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;

Monitor

Readyset proxysql scheduler will produce monitor output into STDOUT which will be saved to proxysql.log. Here is an example of it’s output:

2024-03-25 12:25:15 [INFO] Scheduler starting id: 3 , filename: /etc/readyset_proxysql_scheduler
2024-03-25 12:25:15 [INFO] Readyset[310640]: Running readyset_scheduler
2024-03-25 12:25:15 [INFO] Readyset[310640]: Going to test query support for SELECT c FROM sbtest1 WHERE id=?
2024-03-25 12:25:15 [INFO] Readyset[310640]: Query is supported, adding it to proxysql and readyset
2024-03-25 12:25:15 [INFO] Readyset[310640]: Inserted warm-up rule
2024-03-25 12:25:15 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command
2024-03-25 12:25:15 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0xA30EC60F40188745', with epoch '1711380315'
2024-03-25 12:25:15 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command
2024-03-25 12:25:15 [INFO] Readyset[310640]: Finished readyset_scheduler


2024-03-25 12:25:25 [INFO] Scheduler starting id: 3 , filename: /etc/readyset_proxysql_scheduler
2024-03-25 12:25:25 [INFO] Readyset[310658]: Running readyset_scheduler
2024-03-25 12:25:25 [INFO] Readyset[310658]: Finished readyset_scheduler


2024-03-25 12:25:35 [INFO] Scheduler starting id: 3 , filename: /etc/readyset_proxysql_scheduler
2024-03-25 12:25:35 [INFO] Readyset[310664]: Running readyset_scheduler
2024-03-25 12:25:35 [INFO] Readyset[310664]: Updated rule ID 84 from warmup to destination
2024-03-25 12:25:35 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command
2024-03-25 12:25:35 [INFO] Computed checksum for 'LOAD MYSQL QUERY RULES TO RUNTIME' was '0xF5081488C884CBD4', with epoch '1711380335'
2024-03-25 12:25:35 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command
2024-03-25 12:25:35 [INFO] Readyset[310664]: Finished readyset_scheduler

In the first run, the scheduler discovered SELECT c FROM sbtest1 WHERE id=? as a new query and attempted to validate Readyset support for this query. The query was supported, a new cache entry was created at Readyset set using CREATE CACHE FROM SELECT c FROM sbtest1 WHERE id=?. The scheduler is configured with warmup_time of 10 seconds, thus a warm-up rule was added to just mirror this query to readyset.

In the next interaction of the scheduler nothing happens, as it happened at exactly 10 seconds after the query was added and the warm-up time has not elapsed.

Then in the third run, the scheduler updated the rule from warm-up (mirror the query) to destination and from this point forward, Readyset is responsible for all the requests to this query.

Summary

Readyset ProxySQL Scheduler can seamlessly improve performance of your problematic queries without the need of human intervention or application changes. Give it a try at https://github.com/readysettech/proxysql_scheduler and if you have any questions please reach out to us via slack or github. 

Readyset Cloud in Production for MySQL Workloads

If you use MySQL and want a fully-managed version of Readyset in production to improve the performance of your workloads, join our MySQL design partner program today.

In this program, Readyset will assist with a production-ready implementation of Readyset Cloud. As a design partner, you will offer direct influence on our MySQL-compatibility roadmap. Spots are limited. Schedule time today.