The DBMS Foundation is the basis for our autonomous database. We support a number tuning options that can be used to optimize the system’s performance. Among them is the automatic selection of encoding mechanisms, the data-driven partitioning, and the automatic migration of data between tiers. Many of these are developed as parts of individual research projects. As such, they are subject to frequent changes. To facilitate the independent development of these tuning options, we have decoupled them from the Hyrise core and implement them in the form of plugins.

At the same time, many tuning options have shared requirements. For an efficient selection of encoding mechanisms, where less frequently accessed segments are compressed more heavily, the number of accesses to these segments has to be tracked. The same information is needed by the automatic tiering plugin. Overlaps between plugins cannot only be found in their input data, but also in internal mechanisms. For example, the mentioned plugins both aim at balancing two competing goals, i.e., reducing the DRAM footprint without negatively affecting the query throughput. In the long run, we plan for these shared requirements to be fulfilled by the driver in the Hyrise core.

The driver takes input parameters from runtime KPIs (e.g., the system utilization, the number of accesses to individual segments, and more), the constraints defined by the DBA, and the options provided by the different plugins. Based on these parameters, it makes decisions in a centralized manner. These decisions can then be realized by the different plugins.

In practice, automated database administration approaches are often distrusted. The Hyrise cockpit provides means for database administrators and developers to experiment with plugins that tune database systems autonomously. These experiments should lead to a better understanding of the functionality of such approaches and, in the end, increase trust in such solutions. The Hyrise cockpit intends to create confidence in autonomous solutions by allowing to compare the performance for complex workloads of conventional and autonomously configured systems side by side. The cockpit is demonstrated in the video below which was presented at ICDE 2021.

The scope of our research includes (I) data compression and tiering, (II) data replication and scale-out, (III) index selection (IV) and the joint tuning of these approaches for both relational and spatio-temporal workloads. We also explore (V) the usage of data dependencies in the context of query optimization.

Data compression and tiering are powerful methods to address the memory bottleneck and cost inefficiencies for in-memory databases. The automatic decision on which data compression technique to use in in-memory column stores is challenging due to trade-offs and non-obvious impacts on large workloads. we propose a solution for an automatic selection of a budget- constraint encoding in Hyrise, based on linear programming (LP) and greedy heuristics. The encoding configurations are robust with respect to runtime performance, adaptable and workload-aware. To ensure performance robustness, LP techniques are applied to achieve equally distributed performance gains over all queries. The results show the potential of significant memory budget reductions without a deterioration of runtime performance.

Similarly, data tiering promises to reduce the amount of data in main memory by moving infrequently used data to cheaper and more elastic lower memory and secondary storage tiers. The challenge is to find an optimal balance for the trade-off between performance and costs. We propose an automatic tiering for Hyrise, using LP, that addresses this challenge. Our approach tracks frequency and pattern of data accesses to identify rarely used data, which are moved to secondary memory tiers (e. g., NVM / SSDs). This method is applicable to column selection problems in general and ensures Pareto-efficiency for varying memory budgets. Since, aspects like selectivity, size and frequency of queries are taken into account, the resulting performance is op- timized and outperforms other heuristics.

Database replication and query load-balancing are important mechanisms to scale query throughput. The analysis of workloads allows load-balancing queries to replica nodes according to their accessed data. As a result, replica nodes must only store and synchronize subsets of the data. However, evenly balancing the load of large-scale workloads while minimizing the memory footprint is complex and challenging. Moreover, state-of-the-art allocation approaches are either time consuming or the resulting allocations are not memory-efficient. In our work, we used LP-based decomposition techniques to determine optimized data placements and workload distributions. We extended these solutions considering potential node failures. Further, we derived a heuristic solution to compute robust solutions for large, real-life workload instances providing a competitive performance for different potential as well as uncertain workload scenarios.

Indexes are essential for the efficient processing of database workloads. However, some index selection algorithms are either not fast or not highly competitive, as we found in our survey, which evaluates state-of-the-art approaches using our open-source evaluation platform. To overcome the observed limitations of existing approaches, we developed three new index selection algorithms, serving different purposes: (i) EXTEND determines (near-)optimal solutions with an iterative heuristic. The produced solutions outperform others in most evaluated cases while the selection runtime is up to 10× lower. (ii) SWIRL is based on reinforcement learning (RL) and — after training — delivers solutions instantaneously. SWIRL decreases selection runtimes by orders of magnitude, while the solution quality is within 2% of the best solutions. While EXTEND is universally applicable with a high solution quality, SWIRL requires training, but reduces runtimes. (iii) Our decomposition concept for solver-based index selection approaches allows to deal with larger candidate sets and makes it possible to address risk-averse problem versions, where multiple potential future workloads are taken into account.

Challenges for self-driving database systems, which tune their physical design and configuration autonomously, are manifold: such systems have to anticipate future workloads, find robust configurations efficiently, and incorporate knowledge gained by previous actions into later decisions. We present a theoretical, component-based framework for self-driving database systems that enables database integration and development of self-managing functionality with low overhead, by relying on separation of concerns. We started to implement joint tuning approaches in Hyrise, accounting for combined indexing, sorting, and compression configurations for spatio-temporal applications.

Efficient query optimization is usually based on metadata, such as cardinalities and other basic statistics. More advanced techniques consider data dependency types, such as functional, uniqueness, order, or inclusion constraints / dependencies. We identified 60 query optimization techniques for application areas like join, selection, sorting and set operations in the literature that are based on data dependencies.

Toward an efficient implementation and integration into commercial database systems, we laid out a vision for a workload-driven discovery system for query optimization. The dependency discovery is considered “lazy” since only those data dependency candidates are considered that are relevant for the observed workload. Our prototypical implementation in Hyrise identifies relevant data dependency candidates based on executed query plans and dynamically validates the candidates against the database, leading to performance improvements.

Besides our publications (see below), we are also documenting our progress with Hyrise in the Hyrise Wiki, on our Medium Blog and on the Hyrise Twitter channel.