##
适用于Postgres和MySQL的实时SQL缓存引擎
1、什么是ReadySet
ReadySet is a transparent database cache for Postgres & MySQL that gives you the performance and scalability of an in-memory key-value store without requiring that you rewrite your app or manually handle cache invalidation. ReadySet sits between your application and database and turns even the most complex SQL reads into lightning-fast lookups. Unlike other caching solutions, it keeps cached query results in sync with your database automatically by utilizing your database’s replication stream. It is wire-compatible with Postgres and MySQL and can be used along with your current ORM or database client.
2、Readyset Demo for PostgreSQL
1. 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
2. Import sample data
curl -L -s "https://readyset.io/quickstart/imdb-postgres.sql" \
| psql 'postgresql://postgres:readyset@127.0.0.1:5433/testdb'
Data loading will be slow on Macs with Apple Silicon.
3. Connect and explore the dataset
Connect to Readyset.
psql 'postgresql://postgres:readyset@127.0.0.1:5433/testdb'
Enable query timing.
\timing
Run a sample query. Note that since we have not created a cache, this query is served directly by Postgres.
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;
You’ll see similar results:
count
-------
2418
(1 row)
Time: 154.980 ms
4. Cache a query!
Query performance will be slow on Macs with Apple Silicon.
Using the CREATE CACHE FROM SQL extension, cache the query in Readyset like so:
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;
Now that Readyset is going to cache the query, run the same query to populate the cache:
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;
Same results as earlier:
count(*)
----------
2418
(1 row)
Time: 154.980 ms
This most recent query ran against Postgres, but now the Readyset cache has been populated.
Run the query again and this time it’s served by Readyset:
count(*)
----------
2418
(1 row)
Time: 2.073 ms
5. View Cached Queries
Use the SHOW CACHES command to see which queries are currently being cached by Readyset:
query id | cache name | query text | fallback behavior | count
--------------------+--------------------+--------------------------------------------------------+-------------------+-------
q_bccd97aea07c545f | q_bccd97aea07c545f | SELECT +| fallback allowed | 4
| | count(*) +| |
| | FROM +| |
| | "title_ratings" +| |
| | JOIN "title_basics" ON ( +| |
| | "title_ratings"."tconst" = "title_basics"."tconst"+| |
| | ) +| |
| | WHERE +| |
| | ( +| |
| | ("title_basics"."startyear" = $1) +| |
| | AND ("title_ratings"."averagerating" > 5) +| |
| | ) | |
(1 row)
6. Update the underlying data
You’ll notice that query you’ve run a few times returns the count of movies in '00 that had a rating greater than 5 (i.e. 2,418 movies). “Battlefield Earth” was a movie released in '00 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)
Time: 63.621 ms
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';
7. The cache is auto-updated!
Rerun the previously cached query that returns the count of movies:
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;
And bingo! The count has been increased by one (i.e 2,419 vs 2,418).
count(*)
----------
2419
(1 row)
Time: 2.617 ms
8. Try more queries!
Explore the dataset and test Readyset’s performance with additional queries.
View proxied queries:
SHOW PROXIED QUERIES;
Remove a cache:
DROP CACHE <query id>;
3、Readyset Demo for MySQL
1. Download and run the Readyset Docker compose file
curl -L -o compose.yml \
"https://readyset.io/quickstart/compose.mysql.yml" \
&& docker compose pull \
&& docker compose up -d
2. Import sample data
curl -L -s "https://readyset.io/quickstart/imdb-mysql.sql" \
| mysql -h127.0.0.1 -uroot -P3307 testdb -preadyset
Data loading will be slow on Macs with Apple Silicon.
3. Connect and explore the dataset
Connect to Readyset.
mysql -h127.0.0.1 -uroot -P3307 testdb -preadyset
Run a sample query.
Note that since we have not created a cache, this query is served by MySQL.
Query:
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;
Results:
+----------+
| count(*) |
+----------+
| 2418 |
+----------+
1 row in set (0.69 sec)
4. Cache a query!
Query performance will be slow on Macs with Apple Silicon.
Using the CREATE CACHE FROM SQL extension, cache the query in Readyset.
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;
Now that Readyset is going to cache the query, run the query to populate the cache.
Query:
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;
Results:
+----------+
| count(*) |
+----------+
| 2418 |
+----------+
1 row in set (0.69 sec)
That query ran on the database, but now the Readyset cache has been populated. Run the query again, and this time it is served by Readyset.
Results:
+----------+
| count(*) |
+----------+
| 2418 |
+----------+
1 row in set (0.03 sec)
5. View Cached Queries
Use the SHOW CACHES command to see which queries are currently being cached by Readyset:
mysql> show caches;
+--------------------+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------+
| query id | cache name | query text | fallback behavior | count |
+--------------------+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------+
| q_cb4448c088a2c2f6 | q_cb4448c088a2c2f6 | SELECT count(*) FROM `title_ratings` JOIN `title_basics` ON (`title_ratings`.`tconst` = `title_basics`.`tconst`) WHERE ((`title_basics`.`startyear` = $1) AND (`title_ratings`.`averagerating` > 5)) | fallback allowed | 2 |
+--------------------+--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------+-------+
1 row in set (0.02 sec)
6. Update the underlying data
You’ll notice that query you’ve run a few times returns the count of movies in '00 that had a rating greater than 5 (i.e. 2,418 movies). “Battlefield Earth” was a movie released in '00 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 in set (0.13 sec)
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';
7. The cache is auto-updated!
Rerun the previously cached query that returns the count of movies:
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;
And bingo! The count has been increased by one (i.e 2,419 vs 2,418).
+----------+
| count(*) |
+----------+
| 2419 |
+----------+
1 row in set (0.03 sec)
8. Try more queries!
Explore the dataset and test Readyset’s performance with additional queries.
View proxied queries:
SHOW PROXIED QUERIES;
Remove a cache:
DROP CACHE <query id>;
4、其他demo要求
4.1、Database Requirements
For Readyset to work properly, the following database settings are required:
- Logical replication must be turned on in your database
- Readyset must have superuser permissions
For directions on how to check and alter these configurations, please see our database reference documentation. The steps to configure these settings vary by database type and hosting provider.
4.2、Supported Database Versions
| Database Type | Versions | Support Level |
|---|---|---|
| PostgreSQL | 13.0+ | Beta |
| MySQL | 8.0 | Alpha |
