Characterizing Database Workloads via a Comprehensive I/O Analysis

Abstract

Nowadays high-performance storage like NVMe is widely used for various area where massive I/O is required. Although the ideal read or write speed on those high-performance storage is much improved than previous ones, in practical usages there are many factors which are making performance degradation. One of them is occurred when the mixed read and write workload is executed. The read action cannot execute but wait until write action made a lock on same physical device for writing. Many OLTP database workloads are consisted of mixed read and write. So if those cases are happen frequently, high performance device cannot be utilized its capability. Workloads have each characteristic. TPC-C has a table which is read only. If this table move its table file location to physically separate one, read action to this table doesn't need to wait until the write action's completion. As a result, the entire performance can be increased according to the read only table's query frequency. In this paper I/O analysis can be used to identify the characteristics of database workloads. After the analysis is completed, the experiments which the separation of read only tables and other read/write mixed ones are executed. Without any other configuration except the location of table file, the performance TpmC from TPC-C will be increased 7% on NVMe storage. So more proactive read/write separation would be effective to improve the entire OLTP database workloads.