Readyset Docs

This demo walks through caching a slow query against a sample IMDB dataset and comparing latencies in Grafana. First, we'll spin up a local Readyset instance, sample database, and Grafana dashboard using Docker.

Prerequisites

Docker Engine version >= 19.03.0 and Docker Compose V2 OR Docker Desktop >= 4.1.0
A MySQL or Postgres client, depending on the primary database

Set up Readyset

This sets up a Readyset instance, a primary database, and a Grafana dashboard on your local machine using Docker.

If you want to connect Readyset to your own database instead, please follow the directions in the install Readyset documentation.

Download and run the Readyset Docker compose file:

curl -L -o compose.yml                                  \
  "https://readyset.io/quickstart/compose.postgres.yml" \
  && docker compose pull                                \
  && docker compose up -d

curl -L -o compose.yml                                  \
  "https://readyset.io/quickstart/compose.mysql.yml"    \
  && docker compose pull                                \
  && docker compose up -d

Steps

1. Download the IMDB dataset & load it into your database

First, download the IMDB dataset for MySQL:

curl -L -O "https://readyset.io/quickstart/imdb-mysql.sql"

Then, connect to your Readyset instance and import the dataset. With the Docker setup above, you can use the following command to connect and import:

mysql -h127.0.0.1 -uroot -P3307 testdb -preadyset <  imdb-mysql.sql

First, download the IMDB dataset for Postgres:

curl -L -O "https://readyset.io/quickstart/imdb-postgres.sql"

Then, connect to your Readyset instance and import the dataset. With the Docker setup above, you can use the following command to connect and import:

PGPASSWORD=readyset psql -h127.0.0.1 -Upostgres -p5433 testdb < imdb-postgres.sql

2. Connect to Readyset & make sure the tables have been imported

It could take a few seconds for Readyset to import the tables. You can check whether they've been successfully imported by connecting to Readyset through your database client and running:

SHOW READYSET TABLES;

Once the table is imported, you will see that the status of that table is 'snapshotted'.

3. Run a query against your primary database

Once the tables have been imported into Readyset, we can start running queries.

Let's try this one, which counts how many titles released in 2000 had an average rating that was higher than 5:

SELECT count(*) FROM title_ratings
JOIN title_basics ON title_ratings.tconst = title_basics.tconst
WHERE title_basics.startyear = 2000 AND title_ratings.averagerating > 5;

By default, this query is proxied to the primary database.

4. Cache the query in Readyset

We can cache it in Readyset by running the following:

CREATE CACHE FROM SELECT count(*) FROM title_ratings
JOIN title_basics ON title_ratings.tconst = title_basics.tconst
WHERE title_basics.startyear = 2000 AND title_ratings.averagerating > 5;

We can check the Grafana dashboard to see that the query was successfully cached, and compare latencies. With the Docker setup above, you can open Grafana by going to localhost:4000 in your browser.

Here, we can see that the 99th percentile latencies for this query fell from over 80ms against Postgres to < 1ms against Readyset.

The first time you execute a cached query, it'll be a cache miss, which is slower than the average cache hit. To get a sense for cache hit latencies, issue the query a handful of times to see the cache hit latency reflected in the query latency percentiles in Grafana.

5. Update the underlying data

The query you've run a few times returns the count of titles released in 2000 that had a rating greater than 5 (i.e. 2,418 titles). "Battlefield Earth" was a movie released in 2000 that received poor ratings. For instance, run this query:

SELECT title_basics.tconst, title_basics.primarytitle, title_ratings.averagerating, title_ratings.numvotes
FROM title_basics
INNER JOIN title_ratings on title_ratings.tconst = title_basics.tconst
WHERE title_basics.primarytitle = 'Battlefield Earth';

You'll see it scored an average rating of 2.5:

  tconst   |   primarytitle    | averagerating | numvotes
-----------+-------------------+---------------+----------
 tt0185183 | Battlefield Earth |           2.5 |    80451
(1 row)

It was, indeed, an awful movie. Nevertheless, historical revisionism is fun when you have full control of the data. You can grab the id for "Battlefield Earth" (tt0185183) and update its average rating accordingly:

UPDATE title_ratings
SET averagerating = 5.1
WHERE tconst = 'tt0185183';

6. The cache is auto-updated!

Rerun the previously cached query that returns the count of titles:

SELECT count(*) FROM title_ratings
JOIN title_basics ON title_ratings.tconst = title_basics.tconst
WHERE title_basics.startyear = 2000 AND title_ratings.averagerating > 5;

The count has been increased by one (i.e. 2,419 vs 2,418), without you ever invalidating or refreshing the cache yourself.

 count
-------
  2419
(1 row)

Caching Slow IMDB Queries