An architecture for recycling intermediates in a column-store

Abstract

Automatically recycling (intermediate) results is a grand challenge for state-of-the-art databases to improve both query response time and throughput. Tuples are loaded and streamed through a tuple-at-a-time processing pipeline avoiding materialization of intermediates as much as possible. This limits the opportunities for reuse of overlapping computations to DBA-defined materialized views and function/result cache tuning. In contrast, the operator-at-a-time execution paradigm produces fully materialized results in each step of the query plan. To avoid resource contention, these intermediates are evicted as soon as possible. In this paper we study an architecture that harvests the byproducts of the operator-at-a-time paradigm in a column store system using a lightweight mechanism, the recycler. The key challenge then becomes selection of the policies to admit intermediates to the resource pool, their retention period, and the eviction strategy when facing resource limitations. The proposed recycling architecture has been implemented in an open-source system. An experimental analysis against the TPC-H ad-hoc decision support benchmark and a complex, real-world application (SkyServer) demonstrates its effectiveness in terms of self-organizing behavior and its significant performance gains. The results indicate the potentials of recycling intermediates and charters a route for further development of database kernels.

Scaling up analytical queries with column-stores

“…Such techniques help avoiding recomputing identical queries but cannot be predicted by the optimizer nor used in a canonical way. Another option, implemented in MonetDB, is to recycle intermediate results and share them with subsequent queries [16]. Recent research on work sharing, however, offers ad-hoc "collaboration" of the concurrent queries minimizing the overall work done and the number of data accesses.…”

Just-In-Time Data Distribution for Analytical Query Processing

“…The intermediate results of the subqueries are shipped to the master server (10). Finally, it wraps up the query execution and sends the results to the user (11). Recycler.…”

“…Recycler. A crucial component of the Octopus architecture is the MonetDB Recycler [11]. It is an extension of MonetDB execution model with capability to store and reuse intermediate results in query loads with overlapping computations.…”

“…If the set intersection of the subplans of the arguments is not empty, meaning that they all belong to at least one common subplan, the instruction is assigned to the same subplan(s) (lines [11][12]. Following this general rule, the data access instructions to small query tables are replicated to all subplans.…”

“…It creates distributed execution plans and delegates subquery execution to available worker nodes, referred to as octopus tentacles. Data are shipped just-in-time (JIT) to the workers and kept in their caches using the recycler mechanism [11]. The run-time scheduler allocates subqueries on tentacles based on up-to-date status information.…”