Skip to content

clarifications on intermediate result caching and load times #728

Closed

Description

@nwoolmer

Intermediate result caching

There are some impressive hot run results from SiriusDB, who have done some cool engineering to integrate GPU accelerated queries with DuckDB. Kudos to them, and it will be interesting to see how the commoditisation of GPU compute may (or may not) increase the viability of GPU acclerated databases.

However, it does raise a question about a grey area that still exists, which (this time) impacts hot runs too.

https://github.com/sirius-db/sirius/blob/75ad0a274150deb338c77760999993b20a0bcff7/docs/README.md?plain=1#L224-L235

The GPU caching region is a memory region where the raw data is stored in GPUs, whereas the GPU processing region is where intermediate results are stored in GPUs (hash tables, join results .etc).
For example, to set the caching region as 1 GB and the processing region as 2 GB, we can run the following command:

Quite sensibly, the raw data is cached on the GPU, but also intermediate results. The ClickBench guidelines state the following:

General rules regarding caching:

    Query result caches should be disabled.
    Caching source data (e.g. buffer pools) is fine.
    Caches for intermediate data (e.g. hash tables) are generally okay, however if such caches are located near the end of the query execution pipeline, the effects are similar to query result caching and such caches should thus be disabled.

Which doesn't explicitly rule out operator caching, but does rule it out in the event that it becomes effectively a results cache.

In the benchmark scripts, 80 GB is allocated to raw data, and 40 GB to intermediate results:

GPU_CACHING_SIZE='80 GB'
GPU_PROCESSING_SIZE='40 GB'
CPU_PROCESSING_SIZE="100 GB"

There is also the 100 GB CPU cache which has similar naming, and may cache intermediate results too?

In another case, there were previous question marks about the naming of another database's execution plan cache, which does not cache results or intermediate data: #662

Other databases could certainly improve their numbers by caching partially executed operator data, and yet it cannot be predicted which operators would be acceptable to cache, and which would not.

I am curious what kind of partially-executed query data is cached and how this impacts the results, and whether this can be accepted for other databases.

Load times

I am finding personally that the load time metric has become less useful. There is prior art on this issue: #678

For Cloud systems, this is a difficult metric to compare against, especially since many offerings will substitute fsync durability for replication.

Additionally, the dataset is static, and not representive of use cases where ingestion is critical i.e. real-time analytics.

I am wondering if there is more useful profiling data that could be presented instead, for example, CPU impact, memory usage etc.

Activity

  1. changed the title [-]clarifications on intermediate result caching and load times[/-] [+]clarifications on intermediate result caching and load times and tuning[/+] on Dec 16, 2025
  2. changed the title [-]clarifications on intermediate result caching and load times and tuning[/-] [+]clarifications on intermediate result caching and load tiimes[/+] on Dec 16, 2025
  3. changed the title [-]clarifications on intermediate result caching and load tiimes[/-] [+]clarifications on intermediate result caching and load times[/+] on Dec 18, 2025
  4. rschu1ze commented on Feb 16, 2026

    @rschu1ze
    Member

    We are indeed in a grey zone. I can't tell what the raw and intermediate caches in SiriusDB entail. Such caches are in principle okay as per the benchmark rules as long as no query results are cached (which would speed up hot runs a lot).

    @Yifei-yang7 - you contributed #711 (thanks), can you tell more about @nwoolmer 's following question?

    I am curious what kind of partially-executed query data is cached and how this impacts the results, and whether this can be accepted for other databases.

    Besides that ...

    I am finding personally that the load time metric has become less useful. There is prior art on this issue: #678
    For Cloud systems, this is a difficult metric to compare against, especially since many offerings will substitute fsync durability for replication.

    There is no benchmark rule which forbids not doing fsync. In the interest of a fairer comparison, fsync has been enabled for a few entries in the scope of the issue you linked (one could argue that this puts these entries at a disadvantages in relation to the actual user experience). But anyways, this is all a matter of interpretation 😄

    Additionally, the dataset is static, and not representive of use cases where ingestion is critical i.e. real-time analytics.

    True, ClickBench is an insert-once benchmark. This will also not change in the current iteration of the benchmark, perhaps we can design ClickBench 2.0 with a more versatile workload at some point.

    I am wondering if there is more useful profiling data that could be presented instead, for example, CPU impact, memory usage etc.

    All of these values are interesting and relevant, however they are also hard to capture compared to the end-to-end user experience (= load/cold/hot wall clock times and byte size on disk). E.g .SiriusDB will presumably consume little CPU. Does that make it more efficient than other databases? Only if you don't factor in the need for a GPU. To compensate for that, one will need to look at the performance-per-energy consumption (people have actually done that). In that sense, I suggest to keep it simple and dumb and don't break the existing metrics further down.

    Allow me to close this issue, I'm still curious to hear from @Yifei-yang7 .

  5. nwoolmer commented on Feb 16, 2026

    @nwoolmer
    Author

    Hey Robert, thanks for coming back to me on this.

    I agree on all fronts, but for this:

    There is no benchmark rule which forbids not doing fsync.

    Unfortunately, the ingestion time counts for 10%. Entries which are beholden to the theoretical minimum read/write time of the gp2 volume are artificially disadvantaged for the combined metric, which is also the default view for the results.

    I believe at one point, there was an entry with an ingestion time of 7 seconds, likely due to the data already being hot and no fsync being performed.

    It seems a bit grey!

  6. Yifei-yang7 commented on Feb 16, 2026

    @Yifei-yang7
    Contributor

    Hi Robert, thanks for pinging me here! Sirius only caches raw table data where the available GPU memory is specified by GPU_CACHING_SIZE. Sirius doesn't cache any intermediate or final query results. The processing region (CPU/GPU_PROCESSING_SIZE) is simply for how much memory Sirius can allocate during query execution (so that the system can gracefully throws exceptions instead of killed due to OOM), same as the buffer manager configurations in most existing systems.

  7. nwoolmer commented on Feb 17, 2026

    @nwoolmer
    Author

    Hi @Yifei-yang7 , thanks for the clarification, and I look forward to seeing more, it is a very cool project!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions