External Sorting Chapter 13 Ramakrishnan Gherke and Chapter

External Sorting Chapter 13: Ramakrishnan & Gherke and Chapter 2. 3: Garcia Molina et al. CPSC 404, Laks V. S. Lakshmanan 1

Motivation A classic problem in computer science! v Data requested in sorted order v – v e. g. , find students in increasing gpa order Sorting – is first step in bulk loading (i. e. , creating) B+ tree index. – useful also for eliminating duplicate copies in a collection of records & for aggregation (Why? ) – (later) for efficient joins of relations, i. e. , Sort merge algo. v Problem: sort 1 Gb of data with 50 Mb of RAM. CPSC 404, Laks V. S. Lakshmanan 2

I/O Computation Model disk I/O (read/write a block) very expensive compared to processing it once it’s in memory. v random block accesses very common. v reasonable model for computation using secondary storage count only disk I/O’s. v CPSC 404, Laks V. S. Lakshmanan 3

Desiderata for good DBMS algorithms overlap I/O and cpu processing as much as possible. v use as much data from each block read as possible; depends on record clustering. v cluster blocks that are accessed together in consecutive pages. v buffer frequently accessed blocks in memory. v CPSC 404, Laks V. S. Lakshmanan 4

Sorting Example Setup: v 10 M records of 100 bytes = 1 Gb file. – Stored on Megatron 747 disk, with 4 Kb blocks, each holding 40 records + header information. – Entire file takes 250, 000 blocks = 1000 cylinders. 50 M available main memory = 12, 800 blocks = 1/20 th of file. v Sort by primary key field. v CPSC 404, Laks V. S. Lakshmanan 5

Merge Sort v v Common main memory sorting algorithms don't optimize disk I/O's. Variants of Merge Sort do better. Merge = take two sorted lists and repeatedly choose the smaller of the ``heads'' of the lists (head = first of the unchosen). Example: merge 1, 3, 4, 8 with 2, 5, 7, 9 = 1, 2, 3, 4, 5, 7, 8, 9. Merge Sort based on recursive algorithm: divide records into two parts; recursively mergesort the parts, and merge the resulting lists. CPSC 404, Laks V. S. Lakshmanan 6

Two Phase, Multiway Merge Sort still not very good in disk I/O model. v log 2 n passes, so each record is read/written from disk log 2 n times. v 2 PMMS: 2 reads + 2 writes per block. Phase 1 v 1. Fill main memory with records. v 2. Sort using favorite main memory sort. v 3. Write sorted sublist to disk. v 4. Repeat until all records have been put into one of the sorted lists. v CPSC 404, Laks V. S. Lakshmanan 7

2 PPMS (contd. ) Phase 2 v Use one buffer for each of the sorted sublists and one buffer for an output block. Pointers to first unchosen Select the smallest unchosen Output buffer CPSC 404, Laks V. S. Lakshmanan 8

2 PPMS (contd. ) v v Initially load input buffers with the first blocks of their respective sorted lists. Repeatedly run a competition among the first unchosen records of each of the buffered blocks. – Move the record with the least key to the output block; it is now ``chosen. '' v Manage the buffers as needed: – If an input block is exhausted, get the next block from the same list (i. e. , file). – If the output block is full, write it to disk. CPSC 404, Laks V. S. Lakshmanan 9

Analysis of Naive Implementation Assume blocks are stored at random, so average access time is about 15 ms. v File stored on 250, 000 blocks, read and written once in each phase. v 1, 000 disk I/O's * 0. 015 seconds = 15, 000 seconds = 250 minutes = 4+ hours. Problem and Interlude: How many records can you sort with 2 PMMS, under our assumptions about records, main memory, and the disk? What would you do if there were more? v CPSC 404, Laks V. S. Lakshmanan 10

Improving the Running Time of 2 PMMS Here are some techniques that sometimes make secondary memory algorithms more efficient: 1. Group blocks by cylinder. 2. One big disk several smaller disks. 3. Mirror disks = multiple copies of the same data. 4. ``Prefetching'' or ``double buffering. '' 5. Disk scheduling; the ``elevator'' algorithm. CPSC 404, Laks V. S. Lakshmanan 11

Cylindrification If we are going to read or write blocks in a known order, place them by cylinder, so once we reach that cylinder we can read block after block, with no seek time or rotational latency. Application to Phase 1 of 2 PMMS: 1. Initially, records on 1000 consecutive cylinders. 2. Load main memory from 50 consecutive cylinders. v Order of blocks read is unimportant, so only time besides transfer time is one random seek and 49 1 cylinder seeks (neglect). v Time to transfer 12, 800 blocks at 0. 5 ms/block = 6. 4 sec. 3. Write each sorted list onto 50 consecutive cylinders, so write time is also about 6. 4 sec. But in Phase 2: Blocks are read from the 20 sublists in a data dependent order, so we cannot take much advantage of cylinders. CPSC 404, Laks V. S. Lakshmanan 12

Prefetching v v v If we have extra space for main memory buffers, consider loading buffers in advance of need. Similarly, keep output blocks buffered in main memory until it is convenient to write them to disk. Example: Phase 2 of 2 PMMS With 50 Mb of main memory, we can afford to buffer two cylinders for each sublist and for the output (one cylinder = 1 Mb). Consume one cylinder for each sublist while the other is being loaded from main memory. Similarly, write one output cylinder while the other is being constructed. Thus, seek and rotational latency are almost eliminated, lowering total read and write times to about two minutes each. CPSC 404, Laks V. S. Lakshmanan 13

Using B+ Trees for Sorting Scenario: Table to be sorted has B+ tree index on sorting column(s). v Idea: Can retrieve records in order by traversing leaf pages. v Is this a good idea? v Cases to consider: v – – B+ tree is clustered Good idea! B+ tree is not clustered Could be a very bad idea! CPSC 404, Laks V. S. Lakshmanan 14

Clustered B+ Tree Used for Sorting v v Cost: root to the left most leaf, then retrieve all leaf pages (Alternative 1) If Alternative 2 is used? Additional cost of retrieving data records: each page fetched just once. Index (Direct search) Data Entries ("Sequence set") Data Records CPSC 404, Laks V. S. Lakshmanan 15

Unclustered B+ Tree Used for Sorting v Alternative (2) for data entries; each data entry contains rid of a data record. In general, one I/O per data record! Index (Directs search) Data Entries ("Sequence set") Data Records CPSC 404, Laks V. S. Lakshmanan 16

Sorting from Raw Data – Merge Sort Ø Idea: given B buffer pages of main memory – (Sort phase) read in B pages of data each time and sort internally – suppose entire data stored in m * B pages – sort phase produces m sorted runs of B pages each – (Merge phase) repeatedly merge two sorted runs § § Ø (Pass 0: the sort phase) Pass 1 produces m/2 sorted runs of 2 B pages each Pass 2 produces m/4 sorted runs of 4 B pages each continue until one sorted run of m * B pages produced 2 way merge can be optimized to (B 1) way merge (see Ch. 11. 2) CPSC 404, Laks V. S. Lakshmanan 17

Conclusion: Sorting Records! v Sorting has become a blood sport! – v Parallel sorting is the name of the game. . . Datamation: Sort 1 M records of size 100 bytes Typical DBMS: 15 minutes – World record: 3. 5 seconds – 12 CPU SGI machine, 96 disks, 2 GB of RAM – v New benchmarks proposed: – – Minute Sort: How many can you sort in 1 minute? Dollar Sort: How many can you sort for $1. 00? CPSC 404, Laks V. S. Lakshmanan 18