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

▶
benchmarking hybrid oltp amp olap database systems
SMART_READER_LITE
LIVE PREVIEW

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

May 15, 2023 309 likes •663 views

Benchmarking Hybrid OLTP&OLAP Database Systems Florian Funke Alfons Kemper Thomas Neumann TU Mnchen The Real-Time Business Intelligence Challenge OLTP and OLAP: very different workloads Separate systems Extract-Transform-Load

slide-1
SLIDE 1

Benchmarking Hybrid OLTP&OLAP Database Systems

Florian Funke Alfons Kemper Thomas Neumann

TU München

slide-2
SLIDE 2

The Real-Time Business Intelligence Challenge

++OLAP

  • -OLTP

Dedicated OLAP-Engines MonetDB, Vertica, SAP T-REX (BWA), IBM ISAO (BLINK) ++OLTP

  • -OLAP

Dedicated OLTP-Engines VoltDB, SAP P*Time, TimesT en, SolidDB, many Start-ups

◮ 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

slide-3
SLIDE 3

The Real-Time Business Intelligence Challenge

Hybrid OLTP&OLAP In-Memory DB-Systems SAP NewDB, SanssouciDB, HyPer

++OLAP

  • -OLTP

Dedicated OLAP-Engines MonetDB, Vertica, SAP T-REX (BWA), IBM ISAO (BLINK) ++OLTP

  • -OLAP

Dedicated OLTP-Engines VoltDB, SAP P*Time, TimesT en, SolidDB, many Start-ups

◮ 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

slide-4
SLIDE 4

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

slide-5
SLIDE 5

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

slide-6
SLIDE 6

Benchmark Design: Schema

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

slide-7
SLIDE 7

Benchmark Design: Schema

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

slide-8
SLIDE 8

Benchmark Design: Schema

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

Computed relationships:

◮ Stock ↔ Supplier ◮ Customer ↔ Nation

slide-9
SLIDE 9

Benchmark Design: OLTP Workload

◮ 5 TPC-C transactions:

◮ New-Order (44%) ◮ Payment (44%) ◮ Order-Status (4%) ◮ Delivery (4%) ◮ Stock-Level (4%)

◮ Distribution & semantics: TPC-C ◮ Differences to TPC-C

◮ No simulated terminals ◮ No think-time

StockLevel 4% O r d e r S t a t u s 4 % D e l i v e r y ( b a t c h 1 O r d e r s ) 4 % Payment 44% NewOrder (10 OrderLines) 44%

slide-10
SLIDE 10

Benchmark Design: OLAP Workload

Q2 Q1 Q3 ... Q20 Q21 Q22 Qπ(1) ... ... Qπ(2) Qπ(3) ... ... Qπ(20) Qπ(21) Qπ(22) ◮ 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 AND l_suppkey = s_suppkey AND c_nationkey = s_nationkey AND s_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name = ’[REGION]’ AND

  • _orderdate

>= DATE ’[DATE]’ AND

  • _orderdate

< DATE ’[DATE]’ + INTERVAL ’1’ YEAR 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 WHERE c_id=o_c_id AND c_w_id=o_w_id AND c_d_id=o_d_id AND

  • l_o_id=o_id

AND

  • l_w_id=o_w_id

AND

  • l_d_id=o_d_id

AND

  • l_w_id=s_w_id

AND

  • l_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 AND su_nationkey = n_nationkey AND n_regionkey = r_regionkey AND r_name=’[REGION]’ AND

  • _entry_d >=’[DATE]’

GROUP BY n_name ORDER BY revenue DESC

slide-11
SLIDE 11

Benchmark Parameters

S t

  • c

k L e v e l 4 % OrderStatus 4% Delivery (batch 10 Orders) 4% Payment 44%

OLAP Workload (n ≥ 1 parallel Query Streams) OLTP Workload

NewOrder (10 OrderLines) 44%

Q2 Q1 Q3 ... Q20 Q21 Q22 Qπ(1) ... ... Qπ(2) Qπ(3) ... ... Qπ(20) Qπ(21) Qπ(22)

◮ Size: Number of warehouses ◮ OLTP sessions: Random TPC-C transactions ◮ OLAP sessions: 22 TPC-H queries ◮ Isolation level ◮ Data freshness

slide-12
SLIDE 12

The contestants

◮ System X: Universal database system

◮ Popular, commercial DB ◮ Disk-based

◮ MonetDB: OLAP-focused

◮ In-memory column store ◮ “10-year Best Paper Award”

(VLDB 2009)

◮ VoltDB: OLTP-focused

◮ H-Store (Stonebraker) successor ◮ In-memory ◮ Private partitions with

serial execution

◮ Java stored procedures

◮ HyPer: Hybrid OLTP & OLAP

OLAP Performance OLTP Performance +

  • +
  • CH

BenCHmark

DB2, Oracle 11g, SQL Server VoltDB, TimesT en MonetDB, VectorWise, T-REX (BWA), ISAO (BLINK) HyPer, SAP NewDB (SanssouciDB)

TPC-C TPC-H

slide-13
SLIDE 13

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

c

OLTP Requests /Tx Virtual Memory

a b d c

OLAP Queries

d c *

slide-14
SLIDE 14

The contestants: HyPer

Page T able OLTP Process Pages Transactions

slide-15
SLIDE 15

The contestants: HyPer

Page T able Page T able OLTP Process OLAP Process Pages Transactions Queries fork

slide-16
SLIDE 16

The contestants: HyPer

Page T able Page T able OLTP Process OLAP Process Pages Transactions Queries Attempted Modification

slide-17
SLIDE 17

The contestants: HyPer

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

slide-18
SLIDE 18

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages

slide-19
SLIDE 19

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages

slide-20
SLIDE 20

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages Page T able OLAP Process B

slide-21
SLIDE 21

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages Page T able OLAP Process B

slide-22
SLIDE 22

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages Page T able OLAP Process B

slide-23
SLIDE 23

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages Page T able OLAP Process B Page T able OLAP Process C

slide-24
SLIDE 24

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages Page T able OLAP Process B Page T able OLAP Process C

slide-25
SLIDE 25

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages Page T able OLAP Process B Page T able OLAP Process C T erminate B

slide-26
SLIDE 26

The contestants: HyPer

Multiple Snapshots

Page T able Page T able OLTP Process OLAP Process A Pages Page T able OLAP Process B Page T able OLAP Process C T erminated

slide-27
SLIDE 27

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 1st transaction

slide-28
SLIDE 28

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 –

slide-29
SLIDE 29

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 –

slide-30
SLIDE 30

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 –

slide-31
SLIDE 31

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 –

slide-32
SLIDE 32

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 –

slide-33
SLIDE 33

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 –

slide-34
SLIDE 34

Summary

◮ CH-BenCHmark

◮ Hybrid OLTP & OLAP benchmark ◮ Easy & non-intrusive integration into existing TPC-C installations

◮ First results

◮ Universal DBMS ◮ OLTP-focused system ◮ OLAP-focused system ◮ Hybrid OLTP & OLAP system

Thank you for your attention