Revisiting Reuse in Main Memory Database Systems

Abstract

Reusing intermediates in databases to speed-up analytical query processing has been studied in the past. Existing solutions typically require intermediate results of individual operators to be materialized into temporary tables to be considered for reuse in subsequent queries. However, these approaches are fundamentally ill-suited for use in modern main memory databases. The reason is that modern main memory DBMSs are typically limited by the bandwidth of the memory bus, thus query execution is heavily optimized to keep tuples in the CPU caches and registers. To that end, adding additional materialization operations into a query plan not only add additional traffic to the memory bus but more importantly prevent the important cache-and registerlocality opportunities resulting in high performance penalties.In this paper we study a novel reuse model for intermediates, which caches internal physical data structures materialized during query processing (due to pipeline breakers) and externalizes them so that they become reusable for upcoming operations. We focus on hash tables, the most commonly used internal data structure in main memory databases to perform join and aggregation operations. As queries arrive, our reuse-aware optimizer reasons about the reuse opportunities for hash tables, employing cost models that take into account hash table statistics together with the CPU and data movement costs within the cache hierarchy. Experimental results, based on our HashStash prototype demonstrate performance gains of 2× for typical analytical workloads with no additional overhead for materializing intermediates.

Cache-Based Multi-Query Optimization for Data-Intensive Scalable Computing Frameworks

“…Caching to recycle work. Finally, we consider previous works [6,14,18,24,31] that address the problem of reusing intermediate query results, which is cast as a general caching problem. Our work substantially differs from those approaches in that they mainly focus on cache eviction, where past queries are used to decide what to keep in memory, in an on-line fashion.…”

“…Instead, in this work we focus on the off-line constrained optimization problem of cache admission: the goal is to decide the best content to store in the cache, rather than selecting which to evict if space is needed. The only work that considers the reuse of intermediate results when analyzing the overall execution plan of multiple queries is [14]. Nevertheless, they focus on small problem instances which do not require the general, cost-based approach we present in this work.…”

Intermittent query processing

“…memory size) [34,45]. Dursun et al consider reusing intermediate data structures from join algorithms in main-memory databases [25]. ReCache studies the same problem for heterogeneous data sources [8].…”

ReCache

“…The costs are computed based on fine-grained timing measurements and sizes of the cached data. In addition to caching operator results, [16] also proposes caching internal data structures created as a side-effect of join operations. ReCache adds to this body of work by taking into account the cost of reusing a cached result, adding support for lower-overhead caches that only cache offsets of satisfying tuples, and automatically choosing the fastest data layout for the cache.…”