search

readysettech / readyset

Readyset is a MySQL and Postgres wire-compatible caching layer that sits in front of existing databases to speed up queries and horizontally scale read throughput. Under the hood, ReadySet caches the results of cached select statements and incrementally updates these results over time as the underlying data changes.
https://readyset.io
Other
4.28k stars 117 forks source link

Large data sets cause ReadySet to hang #917

Open Milo123459 opened 10 months ago

Milo123459 commented 10 months ago

Describe the problem Please describe the issue you observed, and any steps we can take to reproduce it:

When an extremely large amount of data is added and a query is created and cached, subsequent queries to a cached query hang.

To Reproduce What did you do? Describe in your own words.

If possible, provide steps to reproduce the behavior:

Create this data:

CREATE TABLE testing (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  t_b BOOLEAN
);

INSERT INTO testing (t_b)
    SELECT 
        random() > 0.5 as t_b FROM generate_series(1, 10e6) AS g(id)

Run this query: SELECT * FROM testing WHERE t_b = false Cache that query, subsequent runs will hang

Expected behavior A clear and concise description of what you expected to happen.

The query will execute and not hang

Additional data / screenshots If the problem is query-related, include a copy of the SQL query and the schema of the supporting tables.

If applicable, add screenshots to help explain your problem.

Environment

Additional context What was the impact? I couldn't execute my queries

Add any other context about the problem here.

ethan-readyset commented 9 months ago

@Milo123459 Hiya -- thanks for the report! The first query you make after creating a cache performs what's called an "upquery", which is effectively a cache miss. When this happens, we need to traverse the dataflow graph for the query all the way to the base tables to compute the results for the query and stash them in the "reader map", which is the data structure that holds the cached data.

I did some digging, and in your example, most of the time during the "hang" is being spent inserting data into this reader map. There are some potential optimizations we can make here, so thanks for bringing this to our attention!