Implementation of a Prefetch method for Secondary Index Scan in MySQL InnoDB Engine

Dasom Hwang, Sang-Won Lee

http://doi.org/

Flash SSDs have many advantages over the existing hard disks such as energy efficiency, shock resistance, and high I/O throughput. For these reasons, in combination with the emergence of innovative technologies such as 3D-NAND and V-NAND for cheaper cost-per-byte, flash SSDs have been rapidly replacing hard disks in many areas. However, the existing database engines, which have been developed mainly assuming hard disks as the storage, could not fully exploit the characteristics of flash SSDs (e.g. internal parallelism). In this paper, in order to utilize the internal parallelism intrinsic to modern flash SSDs for faster query processing, we implemented a prefetching method using asynchronous input/output as a new functionality for secondary index scans in MySQL InnoDB engine. Compared to the original InnoDB engine, the proposed prefetching-based scan scheme shows three-fold higher performance in the case of 16KB-page sizes, and about 4.2-fold higher performance in the case of 4KB-page sizes.