1 / 64

Column-Stores vs. Row-Stores How Different are they Really?

Column-Stores vs. Row-Stores How Different are they Really?. Daniel J. Abadi, Samuel Madden, and Nabil Hachem, SIGMOD 2008. Presented By, Paresh Modak(09305060) Souman Mandal(09305066). Contents. Column-store introduction Column-store data model

carrington
Télécharger la présentation

Column-Stores vs. Row-Stores How Different are they Really?

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. Column-Stores vs. Row-StoresHow Different are they Really? Daniel J. Abadi, Samuel Madden, and Nabil Hachem, SIGMOD 2008 Presented By, Paresh Modak(09305060) Souman Mandal(09305066)

  2. Contents • Column-store introduction • Column-store data model • Emulation of Column Store in Row store • Column store optimization • Experiment and Results • Conclusion

  3. Row Store and Column Store • In row store data are stored in the disk tuple by tuple. • Where in column store data are stored in the disk column by column Figure taken form [2]

  4. Row Store and Column Store • Most of the queries does not process all the attributes of a particular relation. • For example the query Select c.name and c.address From CUSTOMES as c Where c.region=Mumbai; • Only process three attributes of the relation CUSTOMER. But the customer relation can have more than three attributes. • Column-stores are more I/O efficient for read-only queries as they read, only those attributes which are accessed by a query.

  5. Row Store and Column Store • So column stores are suitable for read-mostly, read-intensive, large data repositories

  6. Why Column Stores? • Can be significantly faster than row stores for some applications • Fetch only required columns for a query • Better cache effects • Better compression (similar attribute values within a column) • But can be slower for other applications • OLTP with many row inserts, .. • Long war between the column store and row store camps :-) • This paper tries to give a balanced picture of advantages and disadvantages, after adding/ subtracting a number of optimizations for each approach

  7. Column Stores - Data Model • Standard relational logical data model • EMP(name, age, salary, dept) • DEPT(dname, floor) • Table – collection of projections • Projection – set of columns • Horizontally partitioned into segments with segment identifier

  8. Column Stores - Data Model • To answer queries, projections are joined using Storage keys and join indexes • Storage Keys: • Within a segment, every data value of every column is associated with a unique Skey • Values from different columns with matching Skey belong to the same logical row

  9. Column Stores – Data Model • Join Indexes • T1 and T2 are projections on T • M segments in T1 and N segments in T2 • Join Index from T1 to T2 is a table of the form: • (s: Segment ID in T2, k: Storage key in Segment s) • Each row in join index matches corresponding row in T1 • Join indexes are built such that T could be efficiently reconstructed from T1 and T2

  10. Column Stores – Data Model • Construct EMP(name, age, salary) from EMP1 and EMP3 using join index on EMP3

  11. Compression • Trades I/O for CPU • Increased column-store opportunities: • Higher data value locality in column stores • Techniques such as run length encoding far more useful • Schemes • Null Suppression • Dictionary encoding • Run Length encoding • Bit-Vector encoding • Heavyweight schemes

  12. Query Execution - Operators • Select: Same as relational algebra, but produces a bit string • Project: Same as relational algebra • Join: Joins projections according to predicates • Aggregation: SQL like aggregates • Sort: Sort all columns of a projection

  13. Query Execution - Operators • Decompress: Converts compressed column to uncompressed representation • Mask(Bitstring B, Projection Cs) => emit only those values whose corresponding bits are 1 • Concat: Combines one or more projections sorted in the same order into a single projection • Permute: Permutes a projection according to the ordering defined by a join index • Bitstring operators: Band – Bitwise AND, Bor – Bitwise OR, Bnot – complement

  14. Row Store Vs Column Store • How much of the buzz around column-stores is marketing hype? • Do you really need to buy Sybase IQ or Vertica? • How far will your current row-store take you? • Can you get column-store performance from a row-store? • Can you simulate a column-store in a row-store?

  15. Row Store Vs Column Store • Now the simplistic view about the difference in storage layout leads to that one can obtain the performance benefits of a column-store using a row-store by making some changes to the physical structure of the row store. • This changes can be • Vertically partitioning • Using index-only plans • Using materialized views

  16. Vertical Partitioning • Process: • Full Vertical partitioning of each relation • Each column =1 Physical table • This can be achieved by adding integer position column to every table • Adding integer position is better than adding primary key • Join on Position for multi column fetch • Problems: • “Position” - Space and disk bandwidth • Header for every tuple – further space wastage • e.g. 24 byte overhead in PostgreSQL

  17. Vertical Partitioning: Example

  18. Index-only plans • Process: • Add B+Tree index for every Table.column • Plans never access the actual tuples on disk • Headers are not stored, so per tuple overhead is less • Problem: • Separate indices may require full index scan, which is slower • Eg: SELECT AVG(salary) FROM emp WHERE age > 40 • Composite index with (age, salary) key helps.

  19. Index-only plans: Example

  20. Materialized Views • Process: • Create ‘optimal' set of MVs for given query workload • Objective: • Provide just the required data • Avoid overheads • Performs better • Expected to perform better than other two approach • Problems: • Practical only in limited situation • Require knowledge of query workloads in advance

  21. Materialized Views: Example • Select F.custID from Facts as F where F.price>20

  22. Optimizing Column oriented Execution • Different optimization for column oriented database • Compression • Late Materialization • Block Iteration • Invisible Join

  23. Compression • Low information entropy (high data value locality) leads to High compression ratio • Advantage • Disk Space is saved • Less I/O • CPU cost decrease if we can perform operation without decompressing • Light weight compression schemes do better

  24. Compression • If data is sorted on one column that column will be super-compressible in row store • eg. Run length encoding Figure taken form [2]

  25. Late Materialization • Most query results entity-at-a-time not column-at-a-time • So at some point of time multiple column must be combined • One simple approach is to join the columns relevant for a particular query • But further performance can be improve using late-materialization

  26. Late Materialization • Delay Tuple Construction • Might avoid constructing it altogether • Intermediate position lists might need to be constructed • Eg: SELECT R.a FROM R WHERE R.c = 5 AND R.b = 10 • Output of each predicate is a bit string • Perform Bitwise AND • Use final position list to extract R.a

  27. Late Materialization • Advantages • Unnecessary construction of tuple is avoided • Direct operation on compressed data • Cache performance is improved (PAX)

  28. N-ary storage model (NSM)

  29. Decomposition Storage Model(DSM) • High degree of spatial locality for sequential access of single attribute • Performance deteriorates significantly for queries that involve multiple attributes

  30. PAX - Partition Attributes Across* • Cache utilization and performance is very important • In-page data placement is the key to high cache performance • PAX groups together all values of each attribute within each page • Only affects layout inside the pages, incurs no storage penalty and does not affect I/O behavior • Exhibits superior cache performance and utilization over traditional methods * A. Ailamaki, D. J. DeWitt, et. al. “Weaving relations for cache performance” VLDB, 2001.

  31. PAX Model • Maximizes inter-record spatial locality within each column in the page • Incurs minimal record reconstruction cost • Orthogonal to other design decisions because it only affects the layout of data stored on a single page

  32. Block Iteration • Operators operate on blocks of tuples at once • Iterate over blocks rather than tuples • Like batch processing • If column is fixed width, it can be operated as an array • Minimizes per-tuple overhead • Exploits potential for parallelism • Can be applied even in Row stores – IBM DB2 implements it

  33. Star Schema Benchmark • SSBM is a data warehousing benchmark derived from TPC-H • It consist of a single fact table LINE-ORDER • There are four dimension table. • CUSTOMER • PART • SUPPLIER • DATE • LINEORDER table consist of 60,000,000 tuples • SSBM consist of thirteen queries divided into four category

  34. Star Schema Benchmark Figure taken form [1]

  35. Invisible Join • Queries over data warehouse (particularly modeled with star schema) often have following structure • Restrict set of tuple in the fact table using selection predicates on dimension table • Perform some aggregation on the restricted fact table • Often grouping by other dimension table attribute • For each selection predicate and for each aggregate grouping join between fact table and dimension table is required

  36. Invisible Join • Find Total revenue from Asian customers who purchase a product • supplied by an Asian supplier between 1992 and 1997 grouped by • nation of the customer, supplier and year of transaction

  37. Invisible Join • Traditional plan for this type of query is to pipeline join in order of predicate selectivity • Alternate plan is late materialized join technique • But both have disadvantages • Traditional plan lacks all the advantages described previously of late materialization • In the late materialized join technique group by columns need to be extracted in out-of-position order

  38. Invisible Join • Invisible join is a late materialized join but minimize the values that need to be extracted out of order • Invisible join • Rewrite joins into predicates on the foreign key columns in the fact table • These predicates evaluated either by hash-lookup • Or by between-predicate rewriting

  39. Invisible Join Phase 1 Figure taken form [1]

  40. Invisible Join Phase 2 Figure taken form [1]

  41. Invisible Join Phase 3 Figure taken form [1]

  42. Invisible Join • Between-Predicate rewriting • Use of range predicates instead of hash lookup in phase 1 • Useful if contiguous set of keys are valid after applying a predicate • Dictionary encoding for key reassignment if not contiguous • Query optimizer is not altered. Predicate is rewritten at runtime

  43. Between-predicate rewriting Figure taken form [2]

  44. Experiments • Goal • Comparison of attempts to emulate a column store in a row-store with baseline performance of C-Store • Is it possible for an unmodified row-store to obtain the benefits of column oriented design • Effect of different optimization technique in column-store

  45. Experiment setup • Environment • 2.8GHz Dual Core Pentium(R) workstation • 3 GB RAM • RHEL 5 • 4 disk array mapped as a single logical volume • Reported numbers are average of several runs • Warm buffer (30% improvement for both systems) • Data read exceeds the size of buffer pool

  46. C-Store Vs Commercial row oriented DB • RS: Base System X • CS: Base C-Store case • RS (MV): System X with optimal collection of MVs • CS (Row-MV): Column store constructed from RS(MV) Figure taken form [1] System X= Commercial row oriented database

  47. Results and Analysis • From the graph we can see • C-Store out performs System X by a • Factor of six in the base case • Factor of three when System x use materialized view • However CS (Row-MV) perform worse than RS (MV) • System X provide advance performance feature • C-Store has multiple known performance bottleneck • C-Store doesn't support Partitioning, multithreading

  48. Column Store simulation in Row Store • Partitioning improves the performance of row store if done on a predicate of the query • Authors found it improve the speed by a factor of two • System X implement star join • Optimizer will bloom filters if it feels necessary • Other configuration parameter • 32 KB disk pages • 1.5 GB maximum memory for sort joins, intermediate result • 500 MB buffer pool

  49. Different configuration of System X • Experimented with five different configuration • Traditional row oriented representation with bitmap and bloom filter • Traditional (bitmap): Biased to use bitmaps; might be inferior sometimes • Vertical Partitioning: Each column is a relation • Index-Only: B+Tree on each column • Materialized Views: Optimal set of views for every query

  50. Different configuration of System X Figure taken form [1]

More Related