Benchmarking Hybrid OLTP&OLAP Database Systems Florian Funke - PowerPoint PPT Presentation

Benchmarking Hybrid OLTP&OLAP Database Systems Florian Funke Alfons Kemper Thomas Neumann TU München

The Real-Time Business Intelligence Challenge ◮ OLTP and OLAP: very different workloads ◮ Separate systems ⇒ Extract-Transform-Load process required ◮ Costly ◮ Stale data in OLAP system ◮ Unsuitable for real-time Business Intelligence ⇒ Hybrid DBMS ◮ Various approaches to reconcile OLAP and OLTP ◮ Update staging ◮ Versioning ◮ . . . ⇒ Benchmark for hybrid DBMS ++OLAP ++OLTP Dedicated OLAP-Engines Dedicated OLTP-Engines MonetDB, Vertica, VoltDB, SAP P*Time, SAP T-REX (BWA), TimesT en, SolidDB, IBM ISAO (BLINK) many Start-ups --OLTP --OLAP

The Real-Time Business Intelligence Challenge ◮ OLTP and OLAP: very different workloads ◮ Separate systems ⇒ Extract-Transform-Load process required ◮ Costly ◮ Stale data in OLAP system ◮ Unsuitable for real-time Business Intelligence ⇒ Hybrid DBMS ◮ Various approaches to reconcile OLAP and OLTP ◮ Update staging ◮ Versioning ◮ . . . Hybrid OLTP&OLAP ⇒ Benchmark for hybrid DBMS In-Memory DB-Systems SAP NewDB, SanssouciDB, HyPer ++OLAP ++OLTP Dedicated OLAP-Engines Dedicated OLTP-Engines MonetDB, Vertica, VoltDB, SAP P*Time, SAP T-REX (BWA), TimesT en, SolidDB, IBM ISAO (BLINK) many Start-ups --OLTP --OLAP

Related Work ◮ OLTP ◮ TPC-C & TPC-E ◮ OLAP ◮ TPC-H & TPC-DS ◮ Composite Benchmark for Online Transaction Processing by HPI ◮ Focus: Comparing DBMS for specific use case ⇒ CH-BenCHmark

Benchmark Design ◮ Primary design goal: Comparability of DBMS ◮ Merge of TPC benchmarks ◮ Schema: TPC-C + 3 relations of TPC-H ◮ OLTP workload: TPC-C transactions ◮ OLAP workload: Adapted TPC-H queries

Benchmark Design: Schema Warehouse District W W · 10 History W · 30 k + Customer Stock W · 100 k W · 30 k Neworder W · 9 k + Orderline Item Order 100 k W · 300 k + W · 30 k +

Benchmark Design: Schema Warehouse District W W · 10 Region History 5 W · 30 k + Supplier Nation Customer Stock W · 100 k W · 30 k 10 k 62 Neworder W · 9 k + Orderline Item Order 100 k W · 300 k + W · 30 k +

Benchmark Design: Schema Warehouse District W W · 10 Region History 5 W · 30 k + Supplier Nation Customer Stock W · 100 k W · 30 k 10 k 62 Neworder W · 9 k + Orderline Item Order 100 k W · 300 k + W · 30 k + Computed relationships: ◮ Stock ↔ Supplier ◮ Customer ↔ Nation

Benchmark Design: OLTP Workload NewOrder ◮ 5 TPC-C transactions: (10 OrderLines) ◮ New-Order (44%) 44% ◮ Payment (44%) StockLevel Payment ◮ Order-Status (4%) 4% 44% ◮ Delivery (4%) ◮ Stock-Level (4%) ◮ Distribution & semantics: TPC-C O ) ◮ Differences to TPC-C s r y r d r e e d e v r r i O S l e ◮ No simulated terminals 4 t D 0 a % 1 t % u h s c 4 t ◮ No think-time a b (

Benchmark Design: OLAP Workload ◮ 22 TPC-H queries, adapted to schema, but with original ◮ Business semantics ◮ Syntactical structure ◮ Query 5 TPC-H SELECT n_name , SUM( l_extendedprice * (1 - l_discount)) AS revenue FROM customer , orders , lineitem , supplier , nation , region WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey Q 1 Q π(1) AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey Q 2 Q π(2) AND r_name = ’[REGION]’ AND o_orderdate >= DATE ’[DATE]’ AND o_orderdate < DATE ’[DATE]’ + INTERVAL ’1’ YEAR Q 3 Q π(3) GROUP BY n_name ORDER BY revenue DESC ... ... ◮ Query 5 CH-BenCHmark ... ... ... SELECT n_name , SUM(ol_amount) AS revenue FROM customer , "order", orderline , stock , supplier , nation , region Q 20 Q π(20) WHERE c_id=o_c_id AND c_w_id=o_w_id AND c_d_id=o_d_id AND ol_o_id=o_id AND ol_w_id=o_w_id AND ol_d_id=o_d_id Q 21 Q π(21) AND ol_w_id=s_w_id AND ol_i_id=s_i_id AND mod (( s_w_id * s_i_id) ,10000)=su_suppkey AND ascii(SUBSTRING(c_state , 1, 1))= su_nationkey Q 22 Q π(22) AND su_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name=’[REGION]’ AND o_entry_d >=’[DATE]’ GROUP BY n_name ORDER BY revenue DESC

Benchmark Parameters ◮ Size: Number of warehouses ◮ OLTP sessions: Random TPC-C transactions ◮ OLAP sessions: 22 TPC-H queries ◮ Isolation level ◮ Data freshness NewOrder Q 1 Q π(1) (10 OrderLines) 44% Q 2 Q π(2) l e v e Payment L Q 3 Q π(3) % k c 44% 4 o t S ... ... ... ... ... OrderStatus (batch 10 Orders) Delivery Q 20 Q π(20) 4% 4% Q 21 Q π(21) Q 22 Q π(22) OLAP Workload OLTP Workload (n ≥ 1 parallel Query Streams)

The contestants ◮ System X: Universal database system ◮ Popular, commercial DB ◮ Disk-based ◮ MonetDB: OLAP-focused ◮ In-memory column store ◮ “10-year Best Paper Award” + MonetDB, (VLDB 2009) HyPer, VectorWise, SAP NewDB T-REX (BWA), ◮ VoltDB: OLTP-focused OLAP Performance (SanssouciDB) ISAO (BLINK) ◮ H-Store (Stonebraker) successor ◮ In-memory TPC-H CH ◮ Private partitions with BenCHmark serial execution ◮ Java stored procedures DB2, VoltDB, TimesT en ◮ HyPer: Hybrid OLTP & OLAP Oracle 11g, SQL Server TPC-C - - OLTP Performance +

The contestants: HyPer ◮ Main-memory hybrid OLTP&OLAP database system ◮ Kemper&Neumann @ ICDE 2011 ◮ Hardware-/OS-based snapshots to reconcile OLTP & OLAP ◮ Ultra-efficient shadow paging (cf. Lorie ’77) ◮ No synchronization necessary between OLAP & OLTP OLAP Queries c d OLTP Requests /Tx c c d * b a Virtual Memory

The contestants: HyPer Pages OLTP Process Transactions able Page T

The contestants: HyPer Pages fork OLTP Process OLAP Process Transactions Queries able able Page T Page T

The contestants: HyPer Pages OLTP Process OLAP Process Transactions Queries able able Page T Page T Attempted Modification

The contestants: HyPer Pages OLTP Process OLAP Process Transactions Queries able able Page T Page T Copy on Write: 2µs

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A able able Page T Page T

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A able able Page T Page T

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A OLAP Process B able able able Page T Page T Page T

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A OLAP Process B able able able Page T Page T Page T

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A OLAP Process B able able able Page T Page T Page T

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A OLAP Process B OLAP Process C able able able able Page T Page T Page T Page T

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A OLAP Process B OLAP Process C able able able able Page T Page T Page T Page T

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A OLAP Process B OLAP Process C able able able able Page T Page T Page T Page T T erminate B

The contestants: HyPer Multiple Snapshots Pages OLTP Process OLAP Process A OLAP Process B OLAP Process C able able able able Page T Page T Page T Page T T erminated

Experiments ◮ Setup ◮ 2 × quad-core 2.93 GHz Xeon, 64GB memory, RHEL 5.4 ◮ Benchmark size: 12 warehouses ◮ System X ◮ 3 query sessions ◮ 25 OLTP sessions, group commit (5 transactions) ◮ MonetDB ◮ 3 query sessions ◮ no OLTP ◮ VoltDB ◮ No OLAP ◮ 12 “sites” ◮ No partition crossing transactions ◮ HyPer ◮ 3 OLAP sessions or 8 OLAP sessions ◮ 5 OLTP sessions or 1 OLTP session (incl. partition crossing Tx) ◮ Snapshot taken before 1 st transaction

Results System (# OLAP Sessions / # OLTP Sessions) System X (3/25) HyPer (8/1) HyPer (3/5) MonetDB (3/0) VoltDB (0/12) NO tps 222 tps 25 166 tps 112 217 tps – 16 274 tps Total tps 493 tps 55 924 tps 249 237 tps – 36 159 tps Q1 4221 76 70 72 – Q2 6555 282 156 218 – Q3 16410 112 72 112 – Q4 3830 348 227 8168 – Q5 15212 2489 1871 12028 – Q6 3895 24 15 163 – Q7 8285 2622 1559 2400 – Q8 1655 563 614 306 – Q9 3520 457 241 214 – Q10 15309 4288 2408 9239 – Q11 6006 48 32 42 – Q12 5689 324 182 214 – Q13 918 403 243 521 – Q14 6096 420 174 919 – Q15 6768 1407 822 587 – Q16 6088 2157 1523 7703 – Q17 5195 187 174 335 – Q18 14530 240 123 2917 – Q19 4417 292 134 4049 – Q20 3751 313 144 937 – Q21 9382 48 47 332 – Q22 8821 10 9 167 – Queries/s 0.38 q/s 10.49 q/s 5.96 q/s 1.21 q/s –