Integrating Readyset with ProxySQL for MySQL workloads

ProxySQL is a high-performance, high-availability proxy for MySQL, serving as an intermediary between MySQL clients and servers to optimize and manage database traffic. It provides advanced query routing, directing queries to the most appropriate database server based on predefined rules, such as the query type or server load, thus enhancing performance and spreading the load evenly across servers.

Open and closing connections in MySQL means creating and deleting new OS threads, which under the hood among other things means allocating and deallocating memory. In a high volume system, this can degrade performance. With this in mind, MySQL has the possibility to re-utilize a connection, via COM_CHANGE_USER (REF). This command from MySQL protocol allows libraries to utilize the same connection for a different user, re-authenticating and avoiding the need of MySQL server to close and open a new connection.

Multiple libraries utilize this approach when returning a connection back to a connection pool, either executing a COM_RESET_CONNECTION (REF)  or COM_CHANGE_USER. ProxySQL also utilizes this approach to better utilize the connection resources.

Starting at Readyset version stable-240328 Readyset supports for COM_CHANGE_USER ( commit ) and COM_RESET_CONNECTION ( commit ) allowing users to add Readyset as a mysql server into ProxySQL.

Configuring ProxySQL with Readyset

In order to configure ProxySQL to work with Readyset users need to:

1. Adjust monitor user. Readyset is currently a single user application. The same users your application uses to connect has to be the user ProxySQL uses to monitor the server. In the admin interface, execute:

SET mysql-monitor_username = 'root';
SET mysql-monitor_password = 'root';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

2. Add Readyset server into mysql_servers table. A good practice here is to also add one or more of your read replicas with a small height, in case Readyset becomes unavailable, the read replica will be serving the read cache traffic:

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

3. Redirect queries to Readyset. Here you can redirect all read traffic to Readyset, which will be proxied back to the database Readyset is connected with or you can only redirect problematic queries that are supported to Readyset. Here is one example of redirecting a specific query by using the generated digest collected from stats_mysql_query_digest:

mysql> SELECT * FROM stats_mysql_query_digest WHERE digest_text 
LIKE 'SELECT MIN%'\G
*************************** 1. row ***************************
        hostgroup: 11
       schemaname: employees
         username: root
   client_address:
           digest: 0xd08a266fffe0340c
      digest_text: SELECT MIN(s.salary) FROM salaries s JOIN employees e ON s.emp_no = e.emp_no WHERE e.gender = ?
       count_star: 2
       first_seen: 1711635175
        last_seen: 1711635252
         sum_time: 1089964
         min_time: 66789
         max_time: 1023175
sum_rows_affected: 0
    sum_rows_sent: 1
1 row in set (0,00 sec)

mysql> INSERT INTO mysql_query_rules 
(username, destination_hostgroup, active, digest, apply)
VALUES 
('root', 99, 1, '0xd08a266fffe0340c', 1);
Query OK, 1 row affected (0,00 sec)

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

mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0,01 sec)

4. At this point any new occurrence of this query will be redirected to Readyset and proxied back to MySQL by readyset. We can confirm this by executing SHOW PROXIED QUERIES. Next step required is to create the cache for this query on Readyset:

mysql> SHOW PROXIED QUERIES\G
*************************** 1. row ***************************
          query id: q_309ec37ca1ca3d3
     proxied query: SELECT min(`s`.`salary`) FROM `salaries` AS `s` JOIN `employees` AS `e` ON (`s`.`emp_no` = `e`.`emp_no`) WHERE (`e`.`gender` = $1)
readyset supported: yes
             count: 0
1 row in set (0,00 sec)

mysql> CREATE CACHE FROM SELECT MIN(s.salary) FROM salaries s 
JOIN employees e ON s.emp_no = e.emp_no WHERE e.gender = 'F';
Query OK, 0 rows affected (1,06 sec)

Now the next time we execute the query via ProxySQL it will automatically be served from Readyset Cache:

#### Before ####

mysql> SELECT MIN(s.salary) FROM salaries s JOIN employees e ON s.emp_no = e.emp_no WHERE e.gender = 'F';
+---------------+
| MIN(s.salary) |
+---------------+
|         38786 |
+---------------+
1 row in set (1,03 sec)

#### After ####

mysql> SELECT MIN(s.salary) FROM salaries s JOIN employees e ON s.emp_no = e.emp_no WHERE e.gender = 'F';
+--------------------------------------+
| min(`employees`.`salaries`.`salary`) |
+--------------------------------------+
|                                38786 |
+--------------------------------------+
1 row in set (0,00 sec)

Query response time dropped from 1 second to less than a millisecond.

Summary

Starting at Readyset version stable-240328 is now possible to integrate Readyset with ProxySQL. Users can still benefit from all the ProxySQL functionalities like transparent query routing and align it with the performance boost Readyset can bring to your MySQL read workloads. All 100% transparent to applications without requiring a single line of code change.

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, we can help make that happen. We've launched a MySQL design partner program to assist with a production-ready implementation of Readyset Cloud and to continue expanding our MySQL compatibility!

As a design partner, you will offer direct influence on our MySQL-compatibility roadmap. Spots are limited. Schedule time today.