Challenges

The challenge of improving the predictability of database systems is twofold. First, we need to diagnose the problem and figure out the root causes of performance unpredictability before we can actual solve the problems. This is made difficult by the growing complexity of modern database systems, with hundreds of possible root causes (I/Os, contention, scheduling choice, etc.). For this, we propose a profiling tool VProfiler that aims to identify the root causes of transaction latency variance using a novel notion of variance tree. We also have a diagnosing tool, DBSeer, to automatically diagnose performance anormalies and reason about the most likely causes from log files. The second challenge is to figure out solutions after identifying the root causes. We propose several techniques to tackle the root causes we discover from our case studies on the popular open source databases, including traditional ones and modern ones, and some of those techniques have been adopted by some real-life open source database systems. Our Variance-Aware-Transaction-Scheduling (VATS) algorithm has made its way into MariaDB, while our Largest-Dependency-Set-First (LDSF) algorithm has been merged by MySQL, the most popular open source database system in the world.

Performance

We tested our VATS algorithm using the five different workloads, each with different degree of contention. The client runs the OLTP-Bench framework to send queries to MySQL, which runs on another server running Red Hat Enterprise Linux 6.5 with 2 Intel(R) Xeon(R) CPU E5-2450 processors and 2.10GHz cores. The MySQL server is configured with a 30GB buffer pool, enough to hold most of the data in the database in it. The five workloads are TPC-C, SEATS, TATP, Epinions and YCSB, the first three of which are contended workloads and the last two have little contention. VATS works best on workloads with contention, since in workloads with no contention, there is no scheduling decisions to make. On average, VATS achieves 2.9x improvement in mean latency, 2.8x improvement in variance and 1.5x improvement in 99th percentile across the contended workloads.

For the LDSF algorithm, all experiments were performed using a 5 GB buffer pool on a Linux server with 16 Intel(R) Xeon(R) CPU E5-2450 processors and 2.10GHz cores. The clients were run on a separate machine, submitting transactions to MySQL 5.7 running on the server. We used the OLTP-Bench tool to run the TPC-C workload. We also modified this tool to run a microbenchmark (please find details about the microbenchmark and the algorithms involved in the figure in our paper). OLTP-Bench generated transactions at a specified rate, and client threads issued these transactions to MySQL. The latency of each transaction was calculated as the time from when it was issued until it finished. In all experiments, we controlled the number of transactions issued per second within a safe range to prevent MySQL from falling into a thrashing regime. bLDSF outperforms FIFO by 6.5x in throughput and 300x in average latency. Although bLDSF works better than LDSF, we implemented LDSF in MySQL because of its simplicity.