SSD-Aware Temporary Data Management Policy for Improving Query Performance

Abstract

As data volume grows rapidly and queries become more complex, database engine has to deal with larger amount of temporary data when performing operators such as sort and join. Generally, these temporary data are organized as small temporary files stored in HDD. Due to the poor access performance on HDD, processing time of I/O operations on these files has a direct impact on the response time of queries. Compared to HDD, solid-state drive (SSD) offers more random IOPS and comparable sequential bandwidth. So, using SSD to replace HDD may be an ideal pattern when dealing with temporary data. In this paper, we find out that query processing performing improvement is unsatisfactory if we store temporary files in SSD directly. Thus, we propose a SSD aware temporary data management policy called STDM. STDM takes both the I/O behaviors of temporary data and SSD physical characteristics into account. Temporary data are cached in memory at first, and then written to SSD in an append-only fashion. In this way, we reduce random writes and take full advantage of fast random reads on SSD. We implement a prototype based on PostgreSQL and evaluate its performance on TPC-H benchmark. Experiment result shows that STDM improves query processing performance dramatically compared to the traditional method for organizing temporary data.