OLTP □ Demand for real-time analytics on transactional data □ High throughput analytics è completely in memory – Massive RAMs (>1TB/node) enable this for many apps Example: ▪ Available-To-Promise Check – Perform real-time ATP check directly on transactional data during order entry, without materialized aggregates of available stocks. ▪ Dunning – Search for open invoices interactively instead of scheduled batch runs. ▪ Operational Analytics – Instant customer sales analytics with always up-to-date data. HYRISE | Martin Grund / Jens Krueger | Nov. 2011 2
□ Completely in main memory ▪ Efficiently executed both OLTP and OLAP requests □ Key idea: Vertically partition tables ▪ New algorithms to find the best partitioning for all tables □ Based on a workload profile □ Using a cache-miss based cost model □ Scalable to huge number of tables, wide relations – E.g., Many SAP apps have 10K+ tables w/ 100+ columns ▪ New algorithms for efficiently (re-)compressing data HYRISE | Martin Grund / Jens Krueger | Nov. 2011 3
with main memory ▪ Motivation for disk-based column stores, remains valid for main memory; Avoid loading data that is not accessed. HYRISE | Martin Grund / Jens Krueger | Nov. 2011 4 ▪ Accessing memory with different strides introduces different latencies Sequential accesses 10x-100x faster 1 10 100 1000 8 64 512 4K 32K 256K 2M CPU Cycles per Value Stride in Bytes CPU Registers Main Memory Flash Hard Disk Higher Performance Lower Price / Higher Latency CPU Caches
complex? ▪ Detailed customer data analysis from SAP installations of 12 companies (~32 billion event records analyzed) ▪ Enterprise applications have □ Extremely wide schemas – up to 300 attributes on heavily used tables □ Thousands of tables – every ERP installation ~ 70k □ Changing workload HYRISE | Martin Grund / Jens Krueger | Nov. 2011 6
50 % 60 % 70 % 80 % 90 % 100% Typical Tranasactional Customer Database TPC-C Workload Lookup / Read Table Scan Range Select Insert Modification Delete Write: Read: Example Enterprise Workload HYRISE | Martin Grund / Jens Krueger | Nov. 2011 7 ▪ Range selects occur often ▪ Real world is more complicated than single tuple access ▪ With new applications the “read”-gap will even increase
□ OLAP Systems for analytical scenarios ▪ Our View: Single System □ Main Memory □ Vertically partitioned and compressed □ Single copy of data (no redundancy) – To reduce maintenance and overhead of multiple copies Key challenge: How to perform vertical partitioning to optimize performance on a given hybrid workload HYRISE | Martin Grund / Jens Krueger | Nov. 2011 8
four key aspects □ In-Memory Data Storage – Predicting access costs □ Layout Decisions □ Optimizing Query Execution □ Compression ▪ Layout Engine integrates cost model and workload data HYRISE | Martin Grund / Jens Krueger | Nov. 2011 10 Task Based Executor Query Engine Layout Engine Main Memory Storage Layer Cost Model Workload Data
of non-overlapping containers (partitions) □ Each container consists of one or more attributes ▪ Uses workload as input to find best partitioning ▪ The performance of each workload operator on a given layout is calculated based on cache misses ▪ Container overhead cost defines the cost of loading data that is not accessed by a query operator HYRISE | Martin Grund / Jens Krueger | Nov. 2011 11 C1 (a1) C2 (a2 .. a6) C2 (a7 .. a8) r = (a1 ... a8)
of basic accesses to a container □ Based on access to multiple attributes over all rows (projection) and access to all attributes of a container to a selection of rows (selectivity) ▪ Cache misses are precisely calculated, using the offset and width of the columns projected from the container □ Not enough to calculate #accessed bytes à understand how the accessed data is laid out HYRISE | Martin Grund / Jens Krueger | Nov. 2011 12 Cache Line Width
cache misses for □ Full projections / partial projections □ Selections – capturing both independent and overlapping selections ▪ More complex operators can be composed out of the basic elements ▪ Experiments show that cache misses are a good predictor for performance of in-memory database systems. HYRISE | Martin Grund / Jens Krueger | Nov. 2011 13
is easy and can be done through exhaustive enumeration ▪ Enterprise applications have super-wide schemas □ Up to 300 attributes in our study ▪ è millions of possible layouts HYRISE | Martin Grund / Jens Krueger | Nov. 2011 15
the number of possible layouts in practice 1. Candidate Generation □ Determine all primary partitions (the largest partitions that will not incur any container overhead cost) 2. Candidate Merging □ Inspect all permutations of primary partitions to generate partitions that minimize the overall cost 3. Layout Generation □ Generate all valid layouts by exhaustively exploring all possible combinations of partitions from the second phase HYRISE | Martin Grund / Jens Krueger | Nov. 2011 16
Largest partition that does not incur container overhead cost ▪ Each operation on a table implicitly splits the attributes into two subsets □ The order of the operations can be ignored ▪ Recursively splitting each set of attributes of the workload into subsets for each operation HYRISE | Martin Grund / Jens Krueger | Nov. 2011 17
Nov. 2011 18 ORG PHONE COMPANY EMAIL NAME ID Table Query 1 - Select ID,NAME from Table where ORG = 9 Query 2 - Select ID,COMPANY from Table where ORG = 9 ID NAME ORG ID COMPANY ORG OP 1 OP 2 OP 3 OP 4
ID NAME ORG PHONE EMAIL COMPANY ORG OP 2 ID NAME PHONE EMAIL COMPANY ORG ID COMPANY OP 3 ORG OP 4 ID EMAIL PHONE ORG NAME COMPANY ID EMAIL PHONE ORG NAME COMPANY Candidate Generation HYRISE | Martin Grund / Jens Krueger | Nov. 2011 19
Identify partitions that reduce the overall cost for the workload □ Based on the assumption that the access cost for two partitions with the same attribute set can be independently computed □ Calculation based on the cost model HYRISE | Martin Grund / Jens Krueger | Nov. 2011 20
Nov. 2011 21 Primary Permutation Subset 1 12,000 ✔11,764 Subset 2 12,000 ✔11,764 Subset 3 12,000 ✖36,764 Will be inserted into the global candidate list Only an excerpt, 5 attributes Generate 31 permutations. ID COMPANY ID NAME Primary Partitions Merged Permutation ORG ID COMPANY ID ID ORG ID NAME ID COMPANY vs ID NAME
result of phase 2 ▪ Exhaustively explore all combinations ▪ A valid layout contains all attributes exactly once HYRISE | Martin Grund / Jens Krueger | Nov. 2011 23
Nov. 2011 24 ✔ EMAIL PHONE COMPANY ORG ID NAME COMPANY EMAIL PHONE ORG NAME ID ORG EMAIL PHONE NAME COMPANY ID ORG COMPANY NAME EMAIL PHONE ID 27.7 28.2 28.2 28.5 Cost in 1000
the scalability of the original algorithm degrades ▪ Proposal: approximation that clusters frequently used attributes, by generating optimal sub-layouts for each cluster of primary partitions HYRISE | Martin Grund / Jens Krueger | Nov. 2011 25
the SAP Sales and Distribution scenario □ Total benchmark size of 28 GB data ▪ 13 Queries □ 9 OLTP Queries with typical CRUD operations □ 3 OLAP-like Queries with high selectivity □ 1 Planning like query with incrementally decreasing selectivity HYRISE | Martin Grund / Jens Krueger | Nov. 2011 27
250 300 350 400 450 500 Thousands Row Column HYRISE HYRISE | Martin Grund / Jens Krueger | Nov. 2011 28 ▪ HYRISE uses 4x less cycles than the all row layout, and is about 1.6 times faster than the all column layout ▪ Depending on the query weight HYRISE’s advantage can vary
Grund / Jens Krueger | Nov. 2011 30 ¨ Inserting new tuples directly into a compressed structure is prohibitively expensive ¨ New values are written to a dedicated write-optimized delta partition Differential Store table i column j Delta Partition Main Partition Attribute Vector Dictionary 000 100 010 delta frank hotel 001 011 101 apple charlie inbox Delta (Uncompressed) 0 1 2 3 4 bravo charlie golf charlie young Inverted Index 000 001 010 011 100 101 ... ... 1, 3, ... 2, ... 0,... ... hotel delta frank delta 100 010 011 010 ... 0 1 2 3 M ij U ij M I ij M D ij Write Ops Read Ops
with one write- (delta) and one read-optimized (main) partition. ▪ Update – Any modification operation on the table resulting in an entry in the delta partition. ▪ Main Partition – Compressed and read-optimized part of the column. Consists of a order-preserving dictionary and a attribute vector with bit-compressed value ids. ▪ Delta Partition – Uncompressed write-optimized part of the column where all updates are stored until the merge process is completed. ▪ Merge Process – Applies compression to delta and main partition to create new main partition HYRISE | Martin Grund / Jens Krueger | Nov. 2011 31
main partition ▪ Requirements □ Is performed while the system is operational … hence works on a copy of the data □ Minimal time of increased resource utilization ▪ Phases □ Prepare merge □ Attribute merge 1. Merge Dictionaries 2. Update compressed values □ Commit Merge ▪ Runtime complexity depends on □ The number of distinct values in both main and delta □ The number of tuples in main and delta HYRISE | Martin Grund / Jens Krueger | Nov. 2011 32 prepare merge all attributes merged attribute merge commit merge
Nov. 2011 33 0000 0001 0010 0011 0100 0101 0110 0111 1000 000 001 010 011 100 101 0 1 2 3 4 hotel delta frank delta hotel delta frank delta Main (Compressed) Main Dictionary MJ UJ M 100 010 011 010 ... N M apple charlie delta frank hotel inbox Merge of two partitions Delta (Uncompressed) DJ bravo charlie golf charlie young N D Partition after merge (Compressed) Merged Dictionary 0110 0011 0100 0011 ... apple bravo charlie delta frank golf hotel inbox young bravo charlie 0001 0010 ... Partition from Delta Partition from Main golf charlie young bravo charlie golf young (0) (1, 3) (2) (4) (#): Index to Delta Partition CSB+ tree
– more cores! □ How to parallelize the merge process? ▪ Dividing the columns within a table amongst the available threads with task queue based parallelization scheme ▪ Parallelization … □ of the dictionary merge on each column amongst the available threads – Parallel duplicate removal, however more memory traffic needed, which is evenly split across all threads □ of the update-values phase – Each thread is assigned a chunk of the input table, streaming the input, applying the mapping, writing the result HYRISE | Martin Grund / Jens Krueger | Nov. 2011 34
2011 36 1 2 4 8 16 32 64 128 0.10% 1% 10% 100% UpdateRate(KUpdatesperSec) %ofUniqueValues 1Million 10Million 100Million 1Billion ▪ Enterprise systems expect ~2k Updates/s, which can be easily achieved à 16k Updates/s peak, can be handled as well
Merge): ▪ Complexity in O(|CM |+|CD |) if dictionary encoding changes ▪ Change of dictionary encoding necessary if: a) Main dictionary DM is re-ordered b) Bit-width of DM not sufficient any more HYRISE | Martin Grund / Jens Krueger | Nov. 2011 38 Val. 1 a 2 c 3 d Dictionary DM b Val. 1 a 2 b 3 c 4 d Dictionary D’M changed encoding Val. 00 a 01 b 10 c 11 d e Val. 000 a 001 b 010 c 011 d 100 e changed encoding Dictionary DM Dictionary D’M a) re-ordering of DM b) insufficient bit-width
cost at low write overhead ▪ Idea: don’t change the existing main encoding, only add values from the delta that exist already in DM or do not cause re-ordering of DM HYRISE | Martin Grund / Jens Krueger | Nov. 2011 40 CM CD |CM | C’M C’D fraction of values that can be added without complete re-encoding of main ▪ Complexity now in O(|CD |) instead of O(|CM |+|CD |) ▪ Expected fraction f depends on pdf of distinct values partial merge |CD | |C’M |=|CM |+f *|CD | |C’D |=|CD |-f *|CD |
the status of the delta store require a merge? □ Is it feasible to conduct a partial merge? ▪ Strategies are aware of the read cost overhead and merge cost ▪ Trigger strategies: □ Delta size > fraction of main □ Read cost > optimal read cost * factor HYRISE | Martin Grund / Jens Krueger | Nov. 2011 41 Insert value Trigger strategy Trigger merge? No! Merge strategy Partial Merge Full Merge Yes!
Trigger a merge when |CD | reaches a threshold size st relative to |CM | ▪ Fraction of optimum (fro) strategy: □ Trigger a merge at n-th write, if current select cost reach a defined distance fopt from the theoretically opt. cost (|CD |=0) (selectCostopt(n)) □ Since selectCost(n) and selectCostopt(n) are only defined by current size of |CD | and |CM |, both can be easily computed during runtime HYRISE | Martin Grund / Jens Krueger | Nov. 2011 42 efined fraction fopt of selectCostopt(n). Thus, a merge is triggered is true. As selectCost(n) and selectCostopt(n) are defined by the ta structures, both values can easily be calculated at runtime. selectCost(n) > fopt · selectCostopt(n) (4) main (frm) When applying the fraction of main (frm) strategy, gered, whenever |CD | reaches a threshold size st relative to |CM |. st · | CM |<| CD | (5) erge vs. Partial Merge he performance functions defined in Section 2.2 to choose between artial merge whenever a merge event is triggered. If the expected fter a partial merge selectCost P is comparable to that after a full ost F , a partial merge will be executed. We define the factor fto to tolerated overhead with regard to the merge costs saved, thus a s performed whenever (6) evaluates true, a full merge otherwise. If s a dictionary overflow , we always choose a full merge operation. selectCost P ⇥ fto · selectCost F (6) 0 0 0 ion of read optimum (fro) The basic idea of this strategy is to maintain -cost level that has a defined distance to the theoretical optimal read-costs. fine the optimal read-costs after n inserts, denoted by selectCostopt(n), worst case costs of a range query with |CD | = 0, i.e., selectCostopt(n) bes the maximum costs of a range query when the di erential store is fully d into the main store. The fro strategy allows selectCost(n) to grow until eeds a defined fraction fopt of selectCostopt(n). Thus, a merge is triggered time (4) is true. As selectCost(n) and selectCostopt(n) are defined by the f the data structures, both values can easily be calculated at runtime. selectCost(n) > fopt · selectCostopt(n) (4) ion of main (frm) When applying the fraction of main (frm) strategy, ge is triggered, whenever |CD | reaches a threshold size st relative to |CM |. st · | CM |<| CD | (5)
observed zipf and uniform distributions ▪ To evaluate the performance of our approach, we measured selectCost and amortized write cost insertCost(n) after every insert □ selectCost are worst case cost of range select □ insertCost(n) computes the avg. number of write operations per insert (w. merge) HYRISE | Martin Grund / Jens Krueger | Nov. 2011 43
mixed (OLTP + OLAP) workloads □ Novel algorithms to find optimal workload aware vertical partitioning – Using a highly accurate cache-miss based model ▪ Scalable merge algorithm for modern multi-core processors ▪ Scalable merge strategies required to even further improve the update performance of compressed databases HYRISE | Martin Grund / Jens Krueger | Nov. 2011 46
grundprinzip
inesmontani
tammyeverts
rkendrick25
aleyda
soudai
jmmastey
cassininazir
iamctodd
.jpg){kind=avatar}
cargoodrich
aki_iinuma
techseoconnect
