Optimizing databases by learning hidden parameters of solid state drives

Solid State Drives (SSDs) are complex devices with varying internal implementations, resulting in subtle dierences in behavior between devices. In this paper, we demonstrate how a database engine can be optimized for a particular de-vice by learning its hidden parameters. This can not only improve an application's performance, but also potentially increase the lifetime of the SSD. Our approach for optimiz-ing a database for a given SSD consists of three steps: learn-ing the hidden parameters of the device, proposing rules to analyze the I/O behavior of the database, and optimizing the database by eliminating violations of these rules. We obtain two different characteristics of an SSD, namely the request size profile and the location profile, from which we learn multiple internal parameters. Based on these pa-rameters, we propose rules to analyze the I/O behavior of a database engine. Using these rules, we uncover sub-optimal I/O patterns in SQLite3 and MariaDB when running on our experimental SSDs. Finally, we present three techniques to optimize these database engines: (1) use-hot-locations on SSD-S, which improves the SELECT operation throughput of SQLite3 and MariaDB by 29% and 27% respectively; it also improves the performance of YCSB on MariaDB by 1%-22% depending on the workload mix, (2) write-aligned-stripes on SSD-T, reduces the wear-out caused by SQLite3 write-ahead log (WAL) file by 3.1%, and (3) contain-write-in-flash-page on SSD-T, which reduces the wear-out caused by the MariaDB binary log file by 6.7%.