1 / 32

C-Store: A Column-oriented DBMS

C-Store: A Column-oriented DBMS. By New England Database Group. Current DBMS Gold Standard. Store fields in one record contiguously on disk Use B-tree indexing Use small (e.g. 4K) disk blocks Align fields on byte or word boundaries

caleb-kim
Download Presentation

C-Store: A Column-oriented DBMS

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. C-Store: A Column-oriented DBMS By New England Database Group

  2. Current DBMS Gold Standard • Store fields in one record contiguously on disk • Use B-tree indexing • Use small (e.g. 4K) disk blocks • Align fields on byte or word boundaries • Conventional (row-oriented) query optimizer and executor (technology from 1979) • Aries-style transactions

  3. Terminology -- “Row Store” Record 1 Record 2 Record 3 Record 4 E.g. DB2, Oracle, Sybase, SQLServer, …

  4. Row Stores are Write Optimized • Can insert and delete a record in one physical write • Good for on-line transaction processing (OLTP) • But not for read mostly applications • Data warehouses • CRM

  5. Elephants Have Extended Row Stores • With Bitmap indices • Better sequential read • Integration of “datacube” products • Materialized views But there may be a better idea…….

  6. Column Stores

  7. At 100K Feet…. • Ad-hoc queries read 2 columns out of 20 • In a very large warehouse, Fact table is rarely clustered correctly • Column store reads 10% of what a row store reads

  8. C-Store (Column Store) Project • Brandeis/Brown/MIT/UMass-Boston project • Usual suspects participating • Enough coded to get performance numbers for some queries • Complete status later

  9. We Build on Previous Pioneering Work…. • Sybase IQ (early ’90s) • Monet (see CIDR ’05 for the most recent description)

  10. C-Store Technical Ideas • Code the columns to save space • No alignment • Big disk blocks • Only materialized views (perhaps many) • Focus on Sorting not indexing • Automatic physical DBMS design

  11. C-store (Column Store) Technical Ideas • Optimize for grid computing • Innovative redundancy • Xacts – but no need for Mohan • Data ordered on anything, Not just time • Column optimizer and executor

  12. How to Evaluate This Paper…. • None of the ideas in isolation merit publication • Judge the complete system by its (hopefully intelligent) choice of • Small collection of inter-related powerful ideas • That together put performance in a new sandbox

  13. Code the Columns • Work hard to shrink space • Use extra space for multiple orders • Fundamentally easier than in a row store • E.g. RLE works well

  14. No Alignment • Densepack columns • E.g. a 5 bit field takes 5 bits • Current CPU speed going up faster than disk bandwidth • Faster to shift data in CPU than to waste disk bandwidth

  15. Big Disk Blocks • Tunable • Big (minimum size is 64K)

  16. Only Materialized Views • Projection (materializedview) is some number of columns from a fact table • Plus columns in a dimension table – with a 1-n join between Fact and Dimension table • Stored in order of a storage key(s) • Several may be stored!!!!! • With a permutation, if necessary, to map between them

  17. Only Materialized Views • Table (as the user specified it and sees it) is not stored! • No secondary indexes (they are a one column sorted MV plus a permutation, if you really want one)

  18. Example User view: EMP (name, age, salary, dept) Dept (dname, floor) Possible set of MVs: MV-1 (name, dept, floor) in floor order MV-2 (salary, age) in age order MV-3 (dname, salary, name) in salary order

  19. Different Indexing

  20. Automatic Physical DBMS Design • Not enough 4-star wizards to go around • Accept a “training set” of queries and a space budget • Choose the MVs auto-magically • Re-optimize periodically based on a log of the interactions

  21. Optimize for Grid Computing • I.e. shared-nothing • Dewitt (Gamma) was right • Horizontal partitioning and intra-query parallelism as in Gamma

  22. Innovative Redundancy • Hardly any warehouse is recovered by a redo from the log • Takes too long! • Store enough MVs at enough places to ensure K-safety • Rebuild dead objects from elsewhere in the network • K-safety is a DBMS-design problem!

  23. XACTS – No Mohan • Undo from a log (that does not need to be persistent) • Redo by rebuild from elsewhere in the network

  24. XACTS – No Mohan • Snapshot isolation (run queries as of a tunable time in the recent past) • To solve read-write conflicts • Distributed Xacts • Without a prepare message (no 2 phase commit)

  25. Storage (sort) Key(s) is not Necessarily Time • That would be too limiting • So how to do fast updates to densepack column storage that is not in entry sequence?

  26. Solution – a Hybrid Store Write-optimized Column store (Much like Monet) (Batch rebuilder) Tuple mover Read-optimized Column store (What we have been talking about so far)

  27. Column Executor • Column operations – not row operations • Columns remain coded – if possible • Late materialization of columns

  28. Column Optimizer • Chooses MVs on which to run the query • Most important task • Build in snowflake schemas • Which are simple to optimize without exhaustive search • Looking at extensions

  29. Current Performance • 100X popular row store in 40% of the space • 10X popular column store in 70% of the space • 7X popular row store in 1/6th of the space • Code available with BSD license

  30. Structure Going Forward • Vertica • Very well financed start-up to commercialize C-store • Doing the heavy lifting • University Research • Funded by Vertica

  31. Vertica • Complete alpha system in December ‘05 • Everything, including DBMS designer • With current performance! • Looking for early customers to work with (see me if you are interested)

  32. University Research • Extension of algorithms to non-snowflake schemas • Study of L2 cache performance • Study of coding strategies • Study of executor options • Study of recovery tactics • Non-cursor interface • Study of optimizer primitives

More Related