Hash join in commercial database with flash memory SSD
Sang‐Shin Park, Sang-Won Lee · 2010
Hash join is one of important operations in database system, and its performance may slow down because of disk I/O in hash table overflow phenomenon. In that phenomenon, the more overflow of hash table occurs, the more disk I/O arise, so join performance go from bad to worse. Dominant disk I/O patterns of hash join are sequential writes and random reads, then flash memory SSD is in a more advantageous position than magnetic disk on those I/O patterns. Therefore, using a flash memory SSD instead of magnetic disk as a temporary storage of database will prevent existing performance degradation. In this paper, we show calculated costs by query optimizer in some test cases first. And then, we also show empirically hash join performance in these test cases on temporary table space created on magnetic disk and flash memory SSD. Consequently, the average response time with flash memory SSD was about nine times, minimum and twenty times, max faster than that with magnetic disk. And you might encounter problems that estimated cost by query optimizer and real performance do not correspond on magnetic disk. But, that problem was solved on flash memory SSD.