Join Processing for Flash SSDs Remembering Past Lessons

Join Processing for Flash SSDs: Remembering Past Lessons Jaeyoung Do, Jignesh M. Patel Department of Computer Sciences University of Wisconsin-Madison

Flash Solid State Drives (SSDs) • Low power consumption • High shock resistance • Fast Random Access 35 Flash Densities 30 25 GB ▶ Benefits of Flash SSDs 20 15 “Flash is disk, disk is tape, and tape is dead” 10 • 1995. 16 MB NAND Flash • 2005. 16 GB NAND Flash • 2010. 1 TB NAND Flash (predicted by Samsung) 0 12 $/MB ▶ Flash Densities 5 1999 2000 2001 2002 2003 2004 2005 2006 Flash Prices 9 6 3 ▶ Flash Prices 0 • Decreasing continuously Da. Mo. N 09 Oct. Apr. Oct. 2004 2005 2006 2007 2008 2

Flash SSDs for DBMSs ▶ Many previous works with flash SSDs • • • Flash-based DBMS [Gray ACM Queue 08, Graefe Da. Mo. N 07] In-Page Logging [Lee VLDB 07] Transaction Processing [Lee VLDB 08] B+ Tree Index [Li ICDE 09] I/O Benchmarks [Bouganim CIDR 09] ▶ We focus on Join algorithms • New join algorithms [Shah Da. Mo. N 08, Tsirogiannis SIGMOD 09] What lessons learnt for magnetic HDDs still apply to flash SSDs? Da. Mo. N 09 3

Goals ▶Demonstrate the importance of recalling past lessons about efficient joins in magnetic HDDs ▶ Explore the effects of various parameters for joins on flash SSDs 1. Not inventing new join algorithms 2. Providing better insights for join performance on flash SSDs Da. Mo. N 09 4

Our Approach ▶ Investigate four popular ad hoc join algorithms • • Block Nested Loops Join (BNL) Sort-Merge Join (SM) Grace Hash Join (GH) Hybrid Hash Join (HH) ▶ Conduct experiments as varying various parameters • • • Memory buffer pool size Page size I/O unit size Da. Mo. N 09 5

Roadmap ▶ Introduction / Goals ▶ Ad hoc join algorithms ▶ Experimental Results ▶ Conclusion / Future Work Da. Mo. N 09 6

Assumptions ▶ Blocked I/O is available ▶ We use the buffer allocation strategy tuned for magnetic HDDs [Haas et al. VLDB 97] Da. Mo. N 09 7

IS= Block Nested Loops Join Input Buffer for R Result Input Buffer for S Buffer Pool R S Disk IS = Da. Mo. N 09 , IR=B-IS 8

Sort-Merge Join Buffer Pool Working Space Input Buffer Output Buffer Result Input Buffer R S Disk Da. Mo. N 09 9

Grace Hash Join Buffer Pool Input Bucket 1 Buffer Input Buffer for R Result Input Buffer for S h Bucket k R S Disk Da. Mo. N 09 10

Hybrid Hash Join Buffer Pool Result Buffer Pool In Bucket 1 Memory Bucket Input Buffer for R Input h Buffer Bucket k Input Buffer for S Result R S Disk Da. Mo. N 09 11

Roadmap ▶ Introduction / Goals ▶ Ad-hoc join algorithms ▶ Experimental Results ▶ Conclusion / Future Work Da. Mo. N 09 12

Experimental Setup ▶ A single-thread and light-weight database engine SSD HDD None Avg. Seek Time 12 ms 0. 35 ms • OCZ Core Series 2. 5” SATA 60 GB 120 MB/sec • TOSHIBA 5400 RPM 320 GB Avg. Latency 5. 56 ms Read Data Transfer Rate 34 MB/sec 80 MB/sec Write Data Transfer Rate 34 MB/sec 230. 99 $ (3. 85 $/GB) Price 129. 99 $ (0. 36 $/GB) ▶ Flash SSD and magnetic HDD ▶ Data Set: TPC-H • Customer : 730 MB • Orders : 5 GB Source: OCZ and TOSHIBA ▶ Platform • • Dual Core 3. 2 GHz Intel Pentium, Red Hat Max. DB buffer pool size 600 MB Da. Mo. N 09 13

Effect of Blocked I/O (HDD) 500 MB Buffer Pool, 8 KB Page Size Join Time (sec) 2000 Non-Blocked I/O 2. 1 X CPU time I/O time 2. 2 X 1500 1000 2. 0 X 2. 3 X BNL SM GH HH 500 0 BNL SM GH HH HDD Da. Mo. N 09 14

Effect of Blocked I/O (SSD) 500 MB Buffer Pool, 8 KB Page Size Join Time (sec) 2000 Non-Blocked I/O CPU time I/O time 1500 1. 7 X 1000 1. 6 X 1. 7 X 500 1. 9 X 0 BNL SM GH HH SSD BNL SM GH HH Using blocked I/O is critical Da. Mo. N 09 15

Joins are I/O Bound? (HDD) 8 KB Page Size, Blocked I/O 200 MB Join. Time (sec) 1600 1200 Buffer Pool Size 500 MB CPU time I/O time 0. 68 0. 62 0. 65 800 0. 69 0. 34 0. 58 0. 61 400 0 BNL SM GH HH HDD BNL SM GH Da. Mo. N 09 0. 31 HH 16

Joins are I/O Bound? (SSD) 8 KB Page Size, Blocked I/O 200 MB Join Time (sec) 1600 Buffer Pool Size 500 MB CPU time I/O time 1200 800 1. 78 1. 35 1. 09 0. 64 400 0 1. 69 0. 70 BNL SM GH HH SSD 1. 79 0. 8 BNL SM GH HH Joins may become CPU-bound sooner Da. Mo. N 09 17

Effect of Varying the Page Size (SSD) 500 MB Buffer Pool, Blocked I/O 700 BNL SM GH HH CPU time I/O time Join Time (sec) 600 500 400 300 200 100 0 2 8 32 Page size (KB) When using Blocked I/O, the page size has a small impact on join performance Da. Mo. N 09 18

Performance Tendency (SSD) 8 KB Page Size, Blocked I/O 800 Buffer Pool Size 400 MB 200 MB 600 MB CPU time I/O time Join Time (sec) 600 400 200 0 BNL SM GH HH 1. Superiority of HH Da. Mo. N 09 19

Performance Tendency (SSD) 8 KB Page Size, Blocked I/O 800 200 MB Buffer Pool Size 400 MB 600 MB CPU time I/O time Join Time (sec) 600 400 200 0 BNL SM GH HH 2. Compatibility of BNL Da. Mo. N 09 20

More details in our Da. Mo. N’ 09 paper Performance Tendency (SSD) 8 KB Page Size, Blocked I/O 800 Buffer Pool Size 400 MB 200 MB 1. 6 X 1. 7 X Join Time (sec) 600 MB 2. 8 X CPU time I/O time 400 200 0 BNL SM GH HH 3. GH is not the winner! [Shah et al. Da. Mo. N 08] Da. Mo. N 09 21

Conclusions / Future Work ▶ Traditional Join optimizations continue to be important with flash SSDs • • • Blocked I/O dramatically improves join performance Buffer allocation strategy has an impact on join performance It is even more critical to consider both CPU and I/O costs ▶ Future Work • Expand the range of hardware, and consider other HDDbased configurations • Derive detailed cost models for existing join algorithms, and explore the optimal buffer allocations for flash SSDs Da. Mo. N 09 22

Backup Slides Da. Mo. N 09 23

Joins are I/O Bound? CPU time I/O time 8 KB Page Size, Blocked I/O 1200 MB Buffer Pool Size HDD 500 MB 0. 78 Join Time (sec) 1000 800 600 0. 73 0. 76 0. 79 0. 65 0. 40 0. 80 400 0. 34 200 0 BNL SM GH HH HDD Da. Mo. N 09 Avg. Seek Time 8. 6 msec Avg. Latency 4. 2 msec Read Data Transfer Rate 45 MB/sec Write Data Transfer Rate 44 MB/sec Dell 500 GB 720 RPM SATA HDD 24

CPU times of block nested loops join Magnetic HDD Flash SSD Page size User Kernel 2 KB 187. 5 sec 108. 4 sec 204. 4 sec 324. 0 sec 4 KB 192. 8 sec 49. 0 sec 205. 5 sec 157. 9 sec 8 KB 190. 5 sec 27. 9 sec 183. 6 sec 80. 6 sec 16 KB 186. 6 sec 15. 5 sec 188. 8 sec 39. 8 sec 32 KB 187. 2 sec 8. 6 sec 185. 9 sec 22. 4 sec Da. Mo. N 09 25

Buffer Allocations Da. Mo. N 09 26

Block Nested Loops Join ▶ BNL shows the biggest performance improvement Buffer Pool Size Algorithm 100 MB 200 MB 300 MB 400 MB 500 MB 600 MB BNL 1. 64 X 1. 59 X 1. 72 X 1. 73 X 1. 67 X 1. 65 X SM 1. 41 X 1. 45 X 1. 44 X 1. 45 X 1. 43 X 1. 48 X GH 1. 34 X 1. 29 X 1. 41 X 1. 33 X 1. 39 X 1. 30 X HH 1. 45 X 1. 55 X 1. 35 X 1. 51 X 1. 50 X 1. 65 X Da. Mo. N 09 27

SM and GH ▶ Random writes show poor performance with flash SSDs Buffer Pool Size 200 MB 500 MB Join Time (sec) 1000 CPU time I/O time 800 8 KB Page Size Blocked I/O 600 400 200 0 SM GH HDD SM GH Da. Mo. N 09 SDD 28

An Internal Structure of Flash SSDs Flash Chip Operation Time of NAND Flash block Time Flash page Page read 20 μs Page write 200 μs Block erase 1. 5 ms Samsung Electronics [2005] Example) Case 1) Write 256 KB in an I/O operation = one flash block erase + 64 flash page writes Case 2) Write 256 KB in 64 I/O operations = (at worst) 64 flash block erases + 64 flash page writes Da. Mo. N 09 29

An Internal Structure of Flash SSDs Mapping Table LBA PBA 1 1 2 2 3 3 Example) Sequential Writes –> 1, 2, 3 = one flash block erase + 3 flash page writes Random Writes -> 3, 100, 99, 1, 2 = (at worst) two flash block erases + 3 flash page moves + 3 flash page writes Da. Mo. N 09 30

Flash SSDs VS. Magnetic HDDs Flash SSDs Magnetic HDDs Enterprise 2. 5” SATA Consumer 2. 5” SATA Drive Type 15 K RPM 3. 5” SCSI 160 7200 RPM SATA Intel X 25 -E Memoright GT Model Seagate ST 3300655 LC Seagate Barracuda 7200. 12 64 GB 32 GB Capacity 300 GB 750 GB $ 749 ($12) $ 450 ($14) Price ($/GB) $440 ($1. 47) $ 94 ($0. 13) 20 ▶ Lower power consumption HDDs 10 SSDs 0 Power Consumption (W) 3000 ▶ Higher shock resistance SSDs 1500 HDDs Da. Mo. N 09 0 Shock Resistance (Gs/ms) 31

Flash SSDs VS. Magnetic HDDs Flash SSDs Magnetic HDDs Enterprise 2. 5” SATA Consumer 2. 5” SATA Drive Type 15 K RPM 3. 5” SCSI 160 7200 RPM SATA Intel X 25 -E Memoright GT Model Seagate ST 3300655 LC Seagate Barracuda 7200. 12 NONE Avg. Seek Time 4. 0 ms 8. 9 ms 0. 085 ms 0. 1 ms Avg. Latency 2 ms 4. 17 ms 222 MB/sec 87 MB/sec Read Transfer Rate 57 MB/sec 47 MB/sec 178 MB/sec 85 MB/sec Write Transfer Rate 55 MB/sec 44 MB/sec ▶ Fast Access Time • Fast Random Reads 4000 3000 4000 SSDs 3000 2000 1000 0 SSDs HDDs Random Read (IOPS) Da. Mo. N 09 1000 0 HDDs Random Write (IOPS) 32