1 / 89

MonetDB, Cracking and recycling

MonetDB, Cracking and recycling. Martin Kersten CWI Amsterdam. Try to maximize performance. Present. Materialized Views. Potency. Paste. Cracking. B-tree, Hash Indices. Find a trusted fortune teller. Indices in database systems focus on:

nat
Download Presentation

MonetDB, Cracking and recycling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. MonetDB, Cracking and recycling Martin Kersten CWI Amsterdam

  2. Try to maximize performance Present Materialized Views Potency Paste Cracking B-tree, Hash Indices

  3. Find a trusted fortune teller • Indices in database systems focus on: • All tuples are equally important for fast retrieval • There are ample resources to maintain indices • MonetDB cracks the database into pieces based on actual query load

  4. Cracking algorithms Physical reorganization happens per column based on selection predicates. Split a piece of a column intwonew pieces A<10 A<10 A>=10

  5. Cracking algorithms Physical reorganization happens per column Split a piece of a column intwonew pieces Split a piece of a column inthree new pieces A<5 A<10 A<10 5<A<10 5<A<10 A>=10 A>=10

  6. Cracking example select A>5 and A<10 17 3 8 6 2 12 13 4 15

  7. Cracking example select A>5 and A<10 17 17 3 3 8 8 6 6 2 2 15 15 13 13 4 4 12 12

  8. Cracking example select A>5 and A<10 >=10 17 17 3 3 8 8 6 6 2 2 15 15 13 13 4 4 12 12 >=10

  9. Cracking example select A>5 and A<10 >=10 17 17 3 3 8 8 6 6 2 2 15 15 13 13 4 4 12 12

  10. Cracking example select A>5 and A<10 >=10 17 17 3 3 8 8 6 6 2 2 15 15 13 13 <=5 4 4 12 12

  11. Cracking example select A>5 and A<10 >=10 17 17 3 3 8 8 6 6 4 2 2 15 15 13 13 <=5 4 12 12

  12. Cracking example select A>5 and A<10 >=10 17 3 3 8 8 6 6 4 2 2 15 15 13 13 <=5 4 17 12 12

  13. Cracking example select A>5 and A<10 >=10 17 4 3 3 8 8 6 6 2 2 15 15 13 13 <=5 4 17 12 12

  14. Cracking example select A>5 and A<10 17 4 3 3 8 8 6 6 2 2 15 15 13 13 >=10 4 17 12 12

  15. Cracking example select A>5 and A<10 17 4 3 3 8 8 6 6 2 2 15 15 >=10 13 13 4 17 12 12

  16. Cracking example select A>5 and A<10 17 4 3 3 8 8 6 6 <=5 2 2 15 15 13 13 4 17 12 12

  17. Cracking example select A>5 and A<10 17 4 3 3 <=5 8 8 6 6 <=5 2 2 15 15 13 13 4 17 12 12

  18. Cracking example select A>5 and A<10 17 4 3 3 >5 and <10 8 8 6 6 <=5 2 2 15 15 13 13 4 17 12 12

  19. Cracking example select A>5 and A<10 17 4 3 3 >5 and <10 8 8 6 6 2 <=5 2 15 15 13 13 4 17 12 12

  20. Cracking example select A>5 and A<10 17 4 3 3 >5 and <10 8 6 6 2 <=5 2 8 15 15 13 13 4 17 12 12

  21. Cracking example select A>5 and A<10 17 4 3 3 >5 and <10 8 2 6 6 <=5 2 8 15 15 13 13 4 17 12 12

  22. Cracking example select A>5 and A<10 17 4 3 3 8 2 6 6 >5 and <10 2 8 15 15 13 13 4 17 12 12

  23. Cracking example select A>5 and A<10 17 4 <= 5 3 3 8 2 6 6 > 5 2 8 15 15 13 13 >= 10 4 17 12 12

  24. Cracking example Improve data access for future queries select A>5 and A<10 17 4 <= 5 3 3 8 2 6 6 > 5 2 8 15 15 13 13 >= 10 4 17 12 12

  25. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 <= 5 3 3 8 2 6 6 > 5 2 8 15 15 13 13 >= 10 4 17 12 12

  26. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 4 <= 5 <= 5 3 3 3 8 2 2 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  27. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 4 <= 5 <= 5 3 3 3 8 2 2 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  28. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 4 <= 5 <= 5 3 3 3 8 2 2 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  29. racking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 >3 and <14 17 4 4 <= 5 <= 5 3 3 3 <=3 8 2 2 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  30. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 >3 and <14 17 4 4 <= 5 <= 5 3 3 2 3 <=3 8 2 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  31. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 >3 and <14 17 4 <= 5 <= 5 3 3 2 3 <=3 8 2 4 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  32. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 >3 and <14 17 4 2 <= 5 <= 5 3 3 3 <=3 8 2 4 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  33. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <= 5 <= 5 3 3 3 <=3 8 2 4 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  34. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  35. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  36. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 >= 10 >= 10 4 17 17 12 12 12

  37. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 15 13 13 13 12 >= 10 >= 10 4 17 17 12 12

  38. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 13 13 13 12 >= 10 >= 10 4 17 17 12 12 15

  39. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 12 13 13 13 >= 10 >= 10 4 17 17 12 12 15

  40. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 12 13 13 13 >= 10 >= 10 4 17 17 12 12 15

  41. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 12 13 13 13 >= 10 >= 10 4 17 17 12 12 15

  42. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 12 >=10 13 13 13 >= 10 4 17 17 >= 14 12 12 15

  43. Cracking example Improve data access for future queries select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 > 3 6 6 6 > 5 > 5 2 8 8 15 15 12 >=10 13 13 13 >= 10 4 17 17 >= 14 12 12 15

  44. Cracking example Improve data access for future queries The more we crack the more we learn select A>5 and A<10 select A>3 and A<14 17 4 2 <=3 <= 5 3 3 3 8 2 4 >3 6 6 6 > 5 > 5 2 8 8 15 15 12 >=10 13 13 13 >= 10 4 17 17 >= 14 12 12 15

  45. Design Thefirsttime a range query is posed on an attribute A, a cracking DBMS makes acopyof column A, called the cracker column of A A cracker column iscontinuouslyphysically reorganized based on queries thatneedto touch attribute such as the result is in a contiguous space For each cracker column, there is a cracker index Cracker Index Cracker Column

  46. Try to avoid useless investments A simple range query

  47. Try to avoid useless investments TPC-H query 6

  48. Try to avoid useless investments • Cracking is easy in a column store and is part of the critical execution path • Cracking works under high volume updates

  49. Updates • Base columns are updated as normally • We need to update the cracker column and the cracker index • Efficiently • Maintain the self-organization properties • Two issues: • When • How

  50. When to propagate updates in cracking • Follow the workload to maintain self-organization • Updates become part of query processing • When an update arrives, it is not applied • For each cracker column there is • a pending insertions column • and a pending deletions column • Pending updates are appliedonly when a query needs the specific values

More Related