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:
- 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:
- 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:
- 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
:
- 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:
Now the next time we execute the query via ProxySQL it will automatically be served from Readyset Cache:
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.
Authors