Readyset | 100x scale, zero code changes

Getting Started with Readyset on MySQL!

4 min read

•

14 days ago

Optimizing MySQL and PostgreSQL performance is crucial for modern applications, especially those with read-heavy workloads. Readyset is a drop-in SQL caching engine that sits between your application and the database. It automatically caches query results to improve response times without requiring application changes.

In this post, we'll cover:

Installing Readyset on Ubuntu 24.04 using a .deb package

Configuring Readyset to connect to an upstream MySQL database

Running queries and caching results using MySQL's employee's sample database

Managing cached queries to optimize performance

Step 1: Prerequisites

Before installing Readyset, ensure you have a running MySQL or PostgreSQL database. Readyset requires access to the database to snapshot the schema and cache queries.

Key Recommendations

Readyset should be installed on a separate host from the database to avoid resource contention.

3The database server should allow Readyset to connect remotely.

For this tutorial, we assume:

MySQL is running on 10.0.0.11:3306;
Readyset is running on 10.0.0.12:3307;
A database named employees is available;
A MySQL user (`readyset`@`10.0.0.%`) with appropriate permissions exists:

CREATE USER IF NOT EXISTS `readyset`@`10.0.0.%` 
IDENTIFIED WITH caching_sha2_password BY '<password>';

GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES 
ON employees.* 
TO `readyset`@`10.0.0.%`;

GRANT BACKUP_ADMIN, REPLICATION CLIENT, REPLICATION SLAVE 
ON *.* TO `readyset`@`10.0.0.%`;

If you haven't already set up the employees sample database, you can download it here and import it into MySQL.

Step 2: Installing Readyset on Ubuntu 24.04

2.1 Download the Readyset .deb Package

wget https://github.com/readysettech/readyset/releases/download/stable-250123/readyset_1.11.0-1_amd64.deb

2.2 Install Readyset Using dpkg

dpkg -i readyset_1.11.0-1_amd64.deb

After installation, you should see:

Selecting previously unselected package readyset.
(Reading database ... 168043 files and directories currently installed.)
Preparing to unpack readyset_1.11.0-1_amd64.deb ...
Unpacking readyset (1.11.0-1) ...
Setting up readyset (1.11.0-1) ...
Created symlink /etc/systemd/system/multi-user.target.wants/readyset.service → /usr/lib/systemd/system/readyset.service.

Step 3: Configuring Readyset

Before starting Readyset, you must configure its connection settings in /etc/readyset/readyset.conf.

3.1 Open the Configuration File

vim /etc/readyset/readyset.conf

3.2 Set the Database Connection

Modify or add the following lines:

UPSTREAM_DB_URL="mysql://readyset:<password>@10.0.0.11:3306/employees"
LISTEN_ADDRESS="0.0.0.0:3307"

UPSTREAM_DB_URL: Readyset needs this to connect to MySQL and snapshot the schema.
LISTEN_ADDRESS: it defaults to 3307 for MySQL deployments and 5433 for PostgreSQL deployments, ensuring compatibility when running it on a separate host. You can bind it to a specific IP/DNS address or not.

3.3 Start the Readyset Service

systemctl start readyset

To check if it's running:

systemctl status readyset

Step 4: Connecting to Readyset

Once Readyset is running, connect using the MySQL client:

mysql -h 10.0.0.12 -P 3307 -u readyset -p

Since Readyset acts as a proxy, queries sent here will be cached automatically. For connecting to Readyset, I am assuming you have already created the below user on MySQL:

Step 5: Running Your First Cached Query

Run a real-world query using MySQL's employees database:

SELECT b.dept_name
             , c.first_name
             , c.last_name
             , c.gender, c.hire_date 
FROM dept_emp a, departments b, employees c 
WHERE ((a.dept_no=b.dept_no AND a.emp_no=c.emp_no) 
    AND (last_name='Pelz' AND first_name='Samphel'));

First Execution

1 row in set (0.38 sec)

Since this is the first run, Readyset still doesn't have a cache for this query, causing it to be proxied straightaway to MySQL. You can verify it using EXPLAIN LAST STATEMENT:

MySQL processes it as usual.

Step 6: Checking Proxied Queries

To check if Readyset intercepted the query:

SHOW PROXIED QUERIES\G

Expected output:

*************************** 1. row ***************************
          query id: q_497640df754b2626
     proxied query: SELECT `b`.`dept_name`, `c`.`first_name`, `c`.`last_name`, `c`.`gender`, `c`.`hire_date`
                    FROM `dept_emp` AS `a`, `departments` AS `b`, `employees` AS `c`
                    WHERE (((`a`.`dept_no` = `b`.`dept_no`) AND (`a`.`emp_no` = `c`.`emp_no`))
                    AND ((`last_name` = $1) AND (`first_name` = $2)))
readyset supported: yes
             count: 1

Step 7: Creating a Cache for the Query

To explicitly cache this query:

CREATE CACHE ALWAYS my_query FROM q_497640df754b2626;

I named the cache I created "my_query." If you don't give it a name, a string like "q_497640df754b2626" will be created automatically to identify your cache uniquely.

Expected output:

Query OK, 0 rows affected (1.11 sec)

Step 8: Verifying the Cache

Check if the query is cached:

SHOW CACHES WHERE query_id="q_497640df754b2626"\G

Expected output:

*************************** 1. row ***************************
         query id: q_497640df754b2626
       cache name: my_query
       query text: SELECT `b`.`dept_name`, `c`.`first_name`, `c`.`last_name`, `c`.`gender`, `c`.`hire_date`
                    FROM `dept_emp` AS `a`, `departments` AS `b`, `employees` AS `c`
                    WHERE (((`a`.`dept_no` = `b`.`dept_no`) AND (`a`.`emp_no` = `c`.`emp_no`))
                    AND ((`last_name` = $1) AND (`first_name` = $2)))
fallback behavior: no fallback
            count: 0

At this point, Readyset is caching this query!

Step 9: Running the Cached Query

Rerun the query, but now using Readyset:

SELECT b.dept_name
             , c.first_name
             , c.last_name
             , c.gender, c.hire_date 
FROM dept_emp a, departments b, employees c 
WHERE ((a.dept_no=b.dept_no AND a.emp_no=c.emp_no) 
    AND (last_name='Pelz' AND first_name='Samphel'));

Cached Execution

1 row in set (0.00 sec)

Query execution time dropped to 0.00 sec! 🚀

Readyset served the result from the cache, avoiding a full database query. We can validate it once again using the EXPLAIN LAST STATEMENT. Observe below where the query was executed:

mysql> EXPLAIN LAST STATEMENT\G
*************************** 1. row ***************************
Query_destination: readyset
   Readyset_error: ok
1 row in set (0.00 sec)

Step 10: Removing a Cached Query

To remove the cache, run:

DROP CACHE my_query;

Verify that it's gone:

SHOW CACHES;

Expected output:

Empty set (0.01 sec)

Conclusion

Using Readyset, you can significantly improve MySQL query performance with minimal effort.

With Readyset running on a separate host, you reduce database server overhead while ensuring cached queries execute efficiently.

Connect with US:

Play with our Cloud Demo: https://readyset.cloud/
Our Slack: https://readysetcommunity.slack.com/ssb/redirect
Our Github: https://github.com/readysettech/readyset

Authors

Wagner Bianchi