Evaluating Readyset Caching for MySQL

Evaluating Readyset Caching for MySQL

Readyset is a database caching solution for MySQL and PostgreSQL . For applications that have increased load on your primary database, or use scale-out infrastructure to support a high-read workload, ReadySet can be a drop-in solution to address current performance issues.

There are many different ways to implement a caching solution with your database application. Each solution has a number of relative complexities including coding implementation, caching strategy, cache invalidation and high availability. Readyset has a number of key benefits, but whats sets this product apart as a next-generation cache is the ability to be deployed in MySQL and PostgreSQL without any application code changes. Under the hood, Readyset leverages MySQL binary replication or PostgreSQL WAL to build and maintain cached data. The implementation of Readyset with your application is achieved simply by changing the database endpoint used as Readyset is wire-compatible. Readyset pairs well with ProxySQL, and can be leveraged in a number of different situations with production systems.

The supported database versions include MySQL 8.0+ and PostgreSQL 13+. You can run Readyset on-prem, self-hosted and with hosting providers including AWS RDS, AWS RDS Aurora, GCP CloudSQL and Azure SQL Database. It provides support for a broad range of supported SQL queries .

For more information checkout this Getting Started Tutorial that provides the commands and examples to start experimenting with your existing application.

In an upcoming post I will be demonstrating Readyset performance under load during benchmark testing with a varying number of realistic queries. In this introductory post I start with simple queries to easily show the greatly improved query latency compared with direct MySQL queries.

Example IMDb Dataset (20GB)

The following SQL statements use an adapted IMDb dataset (approximately 20GB) running on MySQL 8.4.

Example Aggregate Query

In queries that process a lot of rows, you will see an immediate benefit in query performance and reduced database load. This is an example which users a covering index to show the range of genres for IMDb titles.

The example query select genre, count(*) from title_genre group by genre; processes a table with 17M rows and executes in ~2000ms when connecting to MySQL, while connected to ReadySet this takes ~3ms.

A more specific index only query select count(*) from title_genre where genre='Sci-Fi'; which processes a much smaller set of rows executes in ~19ms in MySQL, and ~4ms in ReadySet. A count(*) SQL statement for example can take ~900ms verses 3ms.

These are examples that only showing one benefit of using Readyset, that is improved latency of queries. Another key benefit for a highly concurrent RDBMS under load not shown here is the decreased impact on the database server that is no longer having to execute additional queries. With a sufficient reduction in load, you can save money on infrastructure with databases that are now over provisioned.

Query Example MySQL ReadySet
GROUP BY 2000ms 3ms
WHERE 19ms 4ms
COUNT 900ms 3ms

Analysis

Example Table Structure

readyset@127.0.0.1 (imdb) [13:14:14] > show create table title_genre\G
*************************** 1. row ***************************
       Table: title_genre
Create Table: CREATE TABLE `title_genre` (
  `title_id` int NOT NULL,
  `genre` varchar(20) NOT NULL,
  UNIQUE KEY `idx_title_genre_pk` (`title_id`,`genre`),
  KEY `idx_title_genre_genre` (`genre`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

Query Execution Plans (QEP)

mysql> explain select genre, count(*) from title_genre group by genre\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: title_genre
   partitions: NULL
         type: index
possible_keys: idx_title_genre_pk,idx_title_genre_genre
          key: idx_title_genre_genre
      key_len: 82
          ref: NULL
         rows: 15884485
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Executing a query on a fully indexed column.

mysql> explain select count(*) from title_genre where genre='Sci-Fi'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: title_genre
   partitions: NULL
         type: ref
possible_keys: idx_title_genre_pk,idx_title_genre_genre
          key: idx_title_genre_genre
      key_len: 82
          ref: const
         rows: 228024
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

Select Title Genre Queries with MySQL

micro-mysql (13:05:41)> select genre, count(*) from title_genre group by genre;
------------------------------------------------------------
genre	count(*)
------------------------------------------------------------
Action	472609
Adult	13
Adventure	436479
Animation	571295
Biography	123460
Comedy	2233962
Crime	474891
Documentary	1093684
Drama	3232695
Family	845454
Fantasy	243473
Film-Noir	865
Game-Show	436017
History	171223
Horror	216856
Music	428833
Musical	93953
Mystery	235744
News	1082239
Reality-TV	642533
... ... Output truncated at 20 rows.
28 rows (2041.312 ms query, 0.146 ms result)
28 rows (2067.026 ms query, 0.022 ms result)
28 rows (2040.701 ms query, 0.033 ms result)
micro-mysql (13:05:41)> select count(*) from title_genre where genre='Sci-Fi';
------------------------------------------------------------
count(*)
------------------------------------------------------------
118726
1 rows (22.377 ms query, 0.014 ms result)
1 rows (17.376 ms query, 0.006 ms result)
1 rows (17.342 ms query, 0.012 ms result)
Average: 1 rows (19.032 ms query, 0.011 ms result, 3 executions)
------------------------------------------------------------

Select Title Genre Queries with Readyset

micro-mysql (13:03:35)> select genre,count(*) from title_genre group by genre;
------------------------------------------------------------
genre	count(*)
------------------------------------------------------------
Action	472609
Adult	13
Adventure	436479
Animation	571295
Biography	123460
Comedy	2233962
Crime	474891
Documentary	1093684
Drama	3232695
Family	845454
Fantasy	243473
Film-Noir	865
Game-Show	436017
History	171223
Horror	216856
Music	428833
Musical	93953
Mystery	235744
News	1082239
Reality-TV	642533
... ... Output truncated at 20 rows.
28 rows (2.766 ms query, 0.094 ms result)
28 rows (3.493 ms query, 0.014 ms result)
28 rows (3.077 ms query, 0.009 ms result)
Average: 28 rows (3.112 ms query, 0.039 ms result, 3 executions)
------------------------------------------------------------
micro-mysql (13:03:35)> select count(*) from title_genre where genre='Sci-Fi';
------------------------------------------------------------
count(*)
------------------------------------------------------------
118726
1 rows (3.799 ms query, 0.009 ms result)
1 rows (4.331 ms query, 0.011 ms result)
1 rows (3.977 ms query, 0.007 ms result)
Average: 1 rows (4.036 ms query, 0.009 ms result, 3 executions)
------------------------------------------------------------

Next Steps

Readyset offers blazing fast query performance for your application all without touching your existing codebase. You can implement Readyset in a testing environment in hours, not the months it would take to build out a new infrastructure and modify your application.

Be sure to checkout the Readyset Docs to understand more how a real-time SQL caching engine for MySQL and PostgreSQL can greatly improve your database workload and query performance. You can also view a recent presentation from MySQL Beligan Days at FOSDEM 2025.

There is nothing to stop you trying out with Readyset Cloud at no cost to see the performance benefits for yourself.

If I can help in evaluating your production system needs and the benefits of implementing Readyset please reach out.

Tagged with: Caching SQL MySQL PostgreSQL

Related Posts

Creating a More Realistic Benchmark

Common benchmark approaches fall into two general categories, synthetic testing and realistic testing. You have the most generic operations from a synthetic test, starting with the most simple example using a single table, a single column, and for a single DML operation.

Read more

Testing, Benchmarking, Evaluating

Testing and benchmarking are widely used terms in software technology, each serving a distinct purpose and goal. With the increasing adoption of AI in software development, the term evaluating has become significant and with this the re-emergence of what is quality assurance.

Read more

Your Attack Vector Extends Beyond Production Systems

A common data security issue is the unprotected copying of production data to non-production environments without any redaction, masking, or filtering. This practice poses a serious risk. A malicious actor will target the weakest link in your infrastructure, including non-production accounts and the developer systems accessing them.

Read more