Identifying the Major Sources of Variance in Transaction Latencies: Towards More Predictable Databases

Abstract

Decades of research have sought to improve transaction processing performance and scalability in database management systems (DBMSs). However, significantly less attention has been dedicated to the predictability of performance: how often individual transactions exhibit execution latency far from the mean? Performance predictability is vital when transaction processing lies on the critical path of a complex enterprise software or an interactive web service, as well as in emerging database-as-a-service markets where customers contract for guaranteed levels of performance. In this paper, we take several steps towards achieving more predictable database systems. First, we propose a profiling framework called VProfiler that, given the source code of a DBMS, is able to identify the dominant sources of variance in transaction latency. VProfiler automatically instruments the DBMS source code to deconstruct the overall variance of transaction latencies into variances and covariances of the execution time of individual functions, which in turn provide insight into the root causes of variance. Second, we use VProfiler to analyze MySQL and Postgres - two of the most popular and complex open-source database systems. Our case studies reveal that the primary causes of variance in MySQL and Postgres are lock scheduling and centralized logging, respectively. Finally, based on VProfiler's findings, we further focus on remedying the performance variance of MySQL by (1) proposing a new lock scheduling algorithm, called Variance-Aware Transaction Scheduling (VATS), (2) enhancing the buffer pool replacement policy, and (3) identifying tuning parameters that can reduce variance significantly. Our experimental results show that our schemes reduce overall transaction latency variance by 37% on average (and up to 64%) without compromising throughput or mean latency.