Block Allocation Method for Efficiently Managing Temporary Files of Hash Joins on SSDs


KIPS Transactions on Computer and Communication Systems, Vol. 11, No. 12, pp. 429-436, Dec. 2022
https://doi.org/10.3745/KTCCS.2022.11.12.429,   PDF Download:
Keywords: Hash Join, Temporary File, SSD, Trim, fallocate
Abstract

Temporary files are generated when the Hash Join is performed on tables larger than the memory. During the join process, each temporary file is deleted sequentially after it completes the I/O operations. This paper reveals for that the fallocate system call and file deletion-related trim options significantly impact the hash join performance when temporary files are managed on SSDs rather than hard disks. The experiment was conducted on various commercial and research SSDs using PostgreSQL, a representative open-source database. We find that it is possible to improve the join performance up to 3 to 5 times compared to the default combination depending on whether fallocate and trim options are used for temporary files. In addition, we investigate the write amplification and trim command overhead in the SSD according to the combination of the two options for temporary files.


Statistics
Show / Hide Statistics

Statistics (Cumulative Counts from September 1st, 2017)
Multiple requests among the same browser session are counted as one view.
If you mouse over a chart, the values of data points will be shown.


Cite this article
[IEEE Style]
J. Kim and S. Lee, "Block Allocation Method for Efficiently Managing Temporary Files of Hash Joins on SSDs," KIPS Transactions on Computer and Communication Systems, vol. 11, no. 12, pp. 429-436, 2022. DOI: https://doi.org/10.3745/KTCCS.2022.11.12.429.

[ACM Style]
Joontae Kim and Sangwon Lee. 2022. Block Allocation Method for Efficiently Managing Temporary Files of Hash Joins on SSDs. KIPS Transactions on Computer and Communication Systems, 11, 12, (2022), 429-436. DOI: https://doi.org/10.3745/KTCCS.2022.11.12.429.