Browse > Article
http://dx.doi.org/10.3745/KTCCS.2022.11.12.429

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

Joontae, Kim (성균관대학교 소프트웨어학과)
Sangwon, Lee (성균관대학교 소프트웨어융합대학)
Publication Information
KIPS Transactions on Computer and Communication Systems / v.11, no.12, 2022 , pp. 429-436 More about this Journal
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.
Keywords
Hash Join; Temporary File; SSD; Trim; fallocate;
Citations & Related Records
연도 인용수 순위
  • Reference
1 J. Kim, H. Kim, S. Lee, and Y. Won, "FTL design for TRIM command," In The Fifth International Workshop on Software Support for Portable Storage, pp.7-12, 2010.
2 G. Kim and D. Shin, "Performance analysis of SSD write using TRIM in NTFS and EXT4," 2011 6th International Conference on Computer Sciences and Convergence Information Technology (ICCIT), pp.422-423, 2011.
3 J. M. Patel, M. J. Carey, and M. K. Vernon, "Accurate modeling of the hybrid hash join algorithm," In Proceedings of the 1994 ACM SIGMETRICS Conference on Measurement and Modeling of Computer Systems, pp.56-66, 1994.
4 S. W. Lee, B. Moon, and C. Park, "Advances in flash memory SSD technology for enterprise database applications," In Proceedings of the 2009 ACM SIGMOD International Conference on Management of data, pp.863-870, 2009.
5 K. Smith, "Garbage collection," SandForce, Flash Memory Summit, Santa Clara, CA, pp.1-9, 2011.
6 H. Son, Y. Lee, Y. Kim, and J.-S. Kim, "An analysis on the performance of TRIM commands on SSDs and its application to the Ext4 file system," KIISE Transactions on Computing Practices, Vol.21, No.1, pp.52-57, 2015. https://doi.org/10.5626/ktcp.2015.21.1.52   DOI
7 J. T. Kim, and S. W. Lee, "Analysis of the effect of TRIM on hash Join," 2021 KDBC. Korean Database Conference. https://dbsociety.kr/kdbc/kdbc2021/KDBC2021_Proceedings.pdf (pp.6-9), 2021.
8 Y. H. Song, S. H. Jung, S. W. Lee, and J. S. Kim, "Cosmos openSSD: A PCIe-based open source SSD platform," Proc. Flash Memory Summit, pp.1-30, 2014.
9 J. W. Kwak, S. J. Lee, K. B. Park, J. W. Jeong, and Y. H. Song, "Cosmos+ OpenSSD: Rapid Prototype for Flash Storage Systems," ACM Transactions on Storage, Vol.16, No.3, Article 15, pp.35, 2020. https://doi.org/10.1145/3385073.   DOI