Balancing memory consumption and performance of in-memory database systems

In-memory database systems keep their data primarily resident in main memory to avoid performance penalties from accessing persistent storage mediums such as solid-state drives. However, this performance advantage increases costs due to the required capacities for storing the entire data set in expensive main memory. Moreover, hardware capacity constraints prevent in-memory systems from storing larger-than-memory data sets. As more databases are moved to the cloud, for which the vendor bears the costs, there is an increasing interest in lowering the main memory consumption. Such reductions can introduce performance regressions to a level where the reduction eliminates all the performance advantages of in-memory systems. Consequently, reductions of the main memory consumption must be balanced with the runtime performance of the system. One way to achieve such a balance is by exploiting workload information. For workload patterns often observed in real-world systems, we can reduce the consumed main memory with negligible performance impacts by handling data adjusted to their access frequencies and patterns. For our in-memory database system Hyrise, we propose automated processes for optimizing physical database designs to balance runtime performance and memory consumption. Physical database design optimizations require predicting the impact of changing design alternatives accurately. The ability to predict this impact facilitates automated decisions without the necessity of applying physical changes to the system upfront. We present a framework for predicting the effects of changing physical database designs and introduce an efficient linear regression-based prediction model. The model has an accuracy on par with complex state-of-the-art models while being orders of magnitude faster in training. The presented model is robust against incomplete training sets and out-of-sample predictions. For users, such as database administrators, the framework allows for fast predictions of the costs and runtime effects of automated design changes. This thesis further presents two approaches that exploit workload information to lower the main memory consumption: data encoding and tiering. The encoding approach optimizes configurations of encoding schemes for a given data set, workload, and main memory budget. For several analytical benchmarks, we show a reduced main memory consumption and simultaneous performance improvement over the default encoding. For the analytical TPC-H benchmark, we demonstrate a 29 % increased throughput while requiring 50 % less main memory. Our approach outperforms state-of-the-art encoding advisors. Additionally, we demonstrate how configurations determined by automated optimization processes can be made more robust to address the requirements of real-world applications. The data tiering approach uses hybrid storage layouts and determines Pareto-optimal data placements. For access patterns observed in large real-world enterprise systems, the optimized storage layouts mitigate performance penalties of accesses to persistent storage. Moreover, our approach evicts substantial parts of the table data while (i) retaining the in-memory performance for analytical accesses and (ii) adding only minor performance penalties of less than 5 % when accessing evicted data.