Memory Management during Run Generation in External Sorting – Larson & Graefe

1. Memory Management during Run Generation in External Sorting – Larson & Graefe

2. Need For External Merge Sort: • One of most often used operations • Used in joins • Used in duplicate elimination • User requests “sort” It is important for the algorithm to degrade gracefully. The query optimizer does not know ahead the exact number of records (vs. amount of memory available).

3. External Sorting • External N-way merge sort • (Usually) two phases: Run generation and merge • During run generation, create one run at a time (load-sort-store): • Load records • Sort array of pointers to records (quicksort) • Scan pointer array while outputting records

4. Run Generation • Problem: • Discontinuity in the cost function (thus it is difficult to estimate cost of a sort operator) due to load-sort-store • Note: That’s why (naively) hash join is preferred over sort-merge join • Contribution: • Sorting algorithm with smooth cost function (graceful degradation)

5. This Paper • Idea • Similar to hybrid-hash: Keep parts of the previous run in-memory • Problem: Memory management during initial run generation

6. Assumptions • Ascending sort order • Sort must copy records into its own workspace • No specialized disk page/file management • Single thread of execution • Variable-length records, no vertical partitioning of records possible

7. Assumptions (Contd.) • Fixed amount of sort workspace (main memory) available, divided into extents • An extent is a sequence of segments • Record and free segments, one bit indication in header • Each segment holds one record. • Segment type and length stored in segment header • Free segments store pointers for space management • Free segments never touch • Separate output buffer (one or several pages for asynch. I/O)

8. Variable-Length Fields and Records: • Human readable text data (non-numeric) • Even in numeric records, NULL values will usually be not allocated • Any non-traditional data (pictures, sound) • Padding records is NOT a good idea! Managing variable length records deterred researches from using this approach

9. Memory Management Algorithms • First fit (find first space that matches) • Next fit (same as above, but move around) • Best fit (find closest, larger match) Collapse free spaces Move records

10. Run Formation • First, do not output the run from memory until needed! That is, only output records from memory when need space for new record • These ‘left over’ records can be used in first merge step of external sort – reduces IO • Depends on the fan-in of input buffers. If fan-in maximal, there is little records left in memory for this optimization.

11. Replacement Selection • If we keep track of the highest key of a record written to disk from current output, we can add next record to the current output provided its key is higher • Pointer array no longer efficient – use priority heap (‘tree of losers’) • Runs on average twice the size of the available memory Result: steady IO exploits pre-sorting

12. Merging • Always merge as many runs as possible • Keep final run in memory to eliminate unnecessary IO • Possibly reverse record order when writing to disk (not used)

13. Memory Management • Next Fit • free segments implemented as linked list • first check the list • second consider moving records • finally throw current record out • example: inserting 120 byte record 100 free, N occupied, 60 free, 80 occupied

14. 120 bytes 100 bytes 80 bytes 60 bytes 60 bytes 140 bytes

15. Next Fit Structure

16. Memory Management (cont) • Best Fit • unbalanced binary tree • go down the tree until right fit found or move one to right • need to know neighboring segments for merging

17. Best Fit Structure

18. Results • Best fit memory management algorithm achieved 90% memory utilization • Runs were 1.8x size of available memory • Degrades Gracefully!