An Experimental Analysis of Di ff erent Key-Value Stores and - - PowerPoint PPT Presentation

▶
an experimental analysis of di ff erent key value stores
SMART_READER_LITE
LIVE PREVIEW

An Experimental Analysis of Di ff erent Key-Value Stores and - - PowerPoint PPT Presentation

Dec 09, 2022 25 likes •857 views

BTW 2017 An Experimental Analysis of Di ff erent Key-Value Stores and Relational Databases David Gembalczyk Felix Martin Schuhknecht Jens Dittrich Information Systems Group, Saarland University infosys.uni-saarland.de BTW 2017 An

slide-1
SLIDE 1

An Experimental Analysis of Different Key-Value Stores and Relational Databases

David Gembalczyk Felix Martin Schuhknecht Jens Dittrich BTW 2017 Information Systems Group, Saarland University infosys.uni-saarland.de

slide-2
SLIDE 2

An Experimental Analysis of Different Key-Value Stores and Relational Databases

David Gembalczyk Felix Martin Schuhknecht Jens Dittrich BTW 2017 Information Systems Group, Saarland University infosys.uni-saarland.de

How different are they really?

slide-3
SLIDE 3

2/20

slide-4
SLIDE 4

OLTP OLAP

2/20

slide-5
SLIDE 5

Range Queries Point Queries OLTP OLAP

2/20

slide-6
SLIDE 6

Range Queries Point Queries Single Entry All Entries OLTP OLAP

2/20

slide-7
SLIDE 7

Range Queries Point Queries Single Entry All Entries Relational DBs OLTP OLAP

2/20

slide-8
SLIDE 8

Range Queries Point Queries Single Entry All Entries Relational DBs Relational DBs OLTP OLAP

2/20

slide-9
SLIDE 9

Range Queries Point Queries Single Entry All Entries Relational DBs Relational DBs OLTP OLAP Key-Value Stores

2/20

slide-10
SLIDE 10

Range Queries Point Queries Single Entry All Entries Relational DBs Relational DBs OLTP OLAP Key-Value Stores Key-Value Stores

2/20

slide-11
SLIDE 11

Systems

3/20

slide-12
SLIDE 12

Systems

3/20

slide-13
SLIDE 13

Systems

3/20

slide-14
SLIDE 14

Systems

3/20

slide-15
SLIDE 15

Systems

3/20

slide-16
SLIDE 16

HyPer (Demo 0.5)

( )

Systems

3/20

slide-17
SLIDE 17

Relational Systems

4/20

slide-18
SLIDE 18

Relational Systems

Primary Key A B C 1 a1 b1 c1 2 a2 b2 c2

Relational Table

4/20

slide-19
SLIDE 19

Relational Systems

Primary Key A B C 1 a1 b1 c1 2 a2 b2 c2

Relational Table PostgreSQL, MonetDB, Hyper

4/20

slide-20
SLIDE 20

Relational Systems

Primary Key A B C 1 a1 b1 c1 2 a2 b2 c2

Relational Table PostgreSQL, MonetDB, Hyper

N_NATIONKEY(integer) N_NAME(char(25)) N_REGIONKEY(integer) N_COMMENT(varchar(152))) 123 “Germany” 5 “some comment” ... ... ... ...

TPC-H NATION

4/20

slide-21
SLIDE 21

Redis

“any string”

Key Value

“any string”

5/20

slide-22
SLIDE 22

Redis

“any string”

Key

[ “a”, “b”, “c”, “d”]

Value 5/20

slide-23
SLIDE 23

Redis

“any string”

Key Value

{ ( 0.5, „a“ ), { ( 1.0, „c“ ), { ( 1.5, „b“ ), { ( 2.0, „d“ ) }

5/20

slide-24
SLIDE 24

Redis

“any string”

Key Value

[ “1” ➞ “a“ , [ “2” ➞ “b“, [ “3” ➞ “c“, [ “4” ➞ “d“ ]

5/20

slide-25
SLIDE 25

Redis

“any string”

Key Value

[ “1” ➞ “a“ , [ “2” ➞ “b“, [ “3” ➞ “c“, [ “4” ➞ “d“ ] “NATION_123”

Key Value

[ N_NAME ➞ “Germany“ , [ N_REGIONKEY ➞ “5“, [ N_COMMENT ➞ “some comment“ ] TPC-H NATION

5/20

slide-26
SLIDE 26

Aerospike

6/20

slide-27
SLIDE 27

Aerospike

“any string 1”

Key 6/20

slide-28
SLIDE 28

Aerospike

“any string 1”

Key Value Bin

“binA”: “a”

Bin

“binB”: 42

Bin

“binC”: [“a”, “b”, “c”]

6/20

slide-29
SLIDE 29

Aerospike

“any string 1”

Key Value Bin

“binA”: “a”

Bin

“binB”: 42

Bin

“binC”: [“a”, “b”, “c”]

Value Bin

“binB”: “b”

Bin

“sub”: {“binD”: “d”, “binE”: “e”} “any string 2”

Key 6/20

slide-30
SLIDE 30

Aerospike

“any string 1”

Key Value Bin

“binA”: “a”

Bin

“binB”: 42

Bin

“binC”: [“a”, “b”, “c”] Column?

Value Bin

“binB”: “b”

Bin

“sub”: {“binD”: “d”, “binE”: “e”} “any string 2”

Key 6/20

slide-31
SLIDE 31

Aerospike

“any string 1”

Key Value Bin

“binA”: “a”

Bin

“binB”: 42

Bin

“binC”: [“a”, “b”, “c”] Column?

Value Bin

“binB”: “b”

Bin

“sub”: {“binD”: “d”, “binE”: “e”} “any string 2”

Key 6/20

slide-32
SLIDE 32

Aerospike

“any string 1”

Key Value Bin

“binA”: “a”

Bin

“binB”: 42

Bin

“binC”: [“a”, “b”, “c”]

Set

Column?

Value Bin

“binB”: “b”

Bin

“sub”: {“binD”: “d”, “binE”: “e”} “any string 2”

Key 6/20

slide-33
SLIDE 33

Aerospike

“any string 1”

Key Value Bin

“binA”: “a”

Bin

“binB”: 42

Bin

“binC”: [“a”, “b”, “c”]

Set

Column? Table?

Value Bin

“binB”: “b”

Bin

“sub”: {“binD”: “d”, “binE”: “e”} “any string 2”

Key 6/20

slide-34
SLIDE 34

Aerospike

“any string 1”

Key Value Bin

“binA”: “a”

Bin

“binB”: 42

Bin

“binC”: [“a”, “b”, “c”]

Set

Column? Table?

Value Bin

“binB”: “b”

Bin

“sub”: {“binD”: “d”, “binE”: “e”} “any string 2”

Key

123

Key Value Bin

N_NAME: “Germany”

Bin

N_REGIONKEY: 5

Bin

N_COMMENT: “some comment” TPC-H NATION

Set 6/20

slide-35
SLIDE 35

PostgreSQL

7/20

slide-36
SLIDE 36

PostgreSQL

HSTORE

Key Value 1 [ “A” ➞ “a1”, “B” ➞ “b1”, “C” ➞ “c1” ] 7/20

slide-37
SLIDE 37

PostgreSQL

HSTORE

Key Value 1 [ “A” ➞ “a1”, “B” ➞ “b1”, “C” ➞ “c1” ]

JSONB

Key Value 1 { “A”: “a1”, “B”: “b1”, “C”: “c1” } 2 { “A”: “a2”, “B”: “b2”, “C”: “c2”, “sub”: { “D”: “d” “E”: “e” } } 7/20

slide-38
SLIDE 38

PostgreSQL

HSTORE

Key Value 1 [ “A” ➞ “a1”, “B” ➞ “b1”, “C” ➞ “c1” ]

JSONB

Key Value 1 { “A”: “a1”, “B”: “b1”, “C”: “c1” } 2 { “A”: “a2”, “B”: “b2”, “C”: “c2”, “sub”: { “D”: “d” “E”: “e” } } Key Value 123 [ “N_NAME” ➞ “Germany”, “N_REGIONKEY” ➞ “5”, “N_COMMENT” ➞ “some comment” ] Key Value 123 [ “N_NAME”: “Germany”, “N_REGIONKEY”: 5, “N_COMMENT”: “some comment” ] 7/20

slide-39
SLIDE 39

Comparing Apples and Oranges

8/20

slide-40
SLIDE 40

Comparing Apples and Oranges

vs

8/20

slide-41
SLIDE 41

Comparing Apples and Oranges

vs

RAM-disk enlarge caches warm-up run

8/20

slide-42
SLIDE 42

Comparing Apples and Oranges

vs

RAM-disk enlarge caches warm-up run

vs

Custom Interface(s)

8/20

slide-43
SLIDE 43

Comparing Apples and Oranges

vs

RAM-disk enlarge caches warm-up run

vs

Custom Interface(s)

8/20

slide-44
SLIDE 44

Comparing Apples and Oranges

vs

RAM-disk enlarge caches warm-up run

vs

Custom Interface(s)

vs

1 2 3 4 1 2 3 4

8/20

slide-45
SLIDE 45

Experimental Setup

9/20

slide-46
SLIDE 46

Experimental Setup

LINEITEM LINEITEMKEY ORDERKEY PARTKEY SUPPKEY LINENUMBER QUANTITY EXTENDEDPRICE DISCOUNT TAX RETURNFLAG LINESTATUS SHIPDATE COMMITDATE RECEIPTDATE SHIPINSTRUCT SHIPMODE COMMENT PARTSUPP PARTSUPPKEY PARTKEY SUPPKEY AVAILQTY SUPPLYCOST COMMENT

modified TPC-H

9/20

slide-47
SLIDE 47

Experimental Setup

LINEITEM LINEITEMKEY ORDERKEY PARTKEY SUPPKEY LINENUMBER QUANTITY EXTENDEDPRICE DISCOUNT TAX RETURNFLAG LINESTATUS SHIPDATE COMMITDATE RECEIPTDATE SHIPINSTRUCT SHIPMODE COMMENT PARTSUPP PARTSUPPKEY PARTKEY SUPPKEY AVAILQTY SUPPLYCOST COMMENT

modified TPC-H Scale Factor 1

9/20

slide-48
SLIDE 48

Experimental Setup

LINEITEM LINEITEMKEY ORDERKEY PARTKEY SUPPKEY LINENUMBER QUANTITY EXTENDEDPRICE DISCOUNT TAX RETURNFLAG LINESTATUS SHIPDATE COMMITDATE RECEIPTDATE SHIPINSTRUCT SHIPMODE COMMENT PARTSUPP PARTSUPPKEY PARTKEY SUPPKEY AVAILQTY SUPPLYCOST COMMENT

modified TPC-H Scale Factor 1 warm-up run 3 measurement runs 50,000 ops/run

9/20

slide-49
SLIDE 49

Experimental Setup

LINEITEM LINEITEMKEY ORDERKEY PARTKEY SUPPKEY LINENUMBER QUANTITY EXTENDEDPRICE DISCOUNT TAX RETURNFLAG LINESTATUS SHIPDATE COMMITDATE RECEIPTDATE SHIPINSTRUCT SHIPMODE COMMENT PARTSUPP PARTSUPPKEY PARTKEY SUPPKEY AVAILQTY SUPPLYCOST COMMENT

modified TPC-H Scale Factor 1 warm-up run 3 measurement runs 50,000 ops/run

Intel Xeon X5690 Intel Xeon X5690 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 3.47 GHz 96GB RAM 96GB RAM

9/20

slide-50
SLIDE 50

Round-Trip Time

jdbc.executeQuery( "SELECT 1;" ); // Relational DBs redis.echo( "1" ); // Redis aerospike.exists( non_existing_key ); // Aerospike

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 400 000 500 000 Clients Performance [Q/sec]

PostgreSQL MonetDB HyPer (Demo) Aerospike Redis

10/20

slide-51
SLIDE 51

Round-Trip Time

jdbc.executeQuery( "SELECT 1;" ); // Relational DBs redis.echo( "1" ); // Redis aerospike.exists( non_existing_key ); // Aerospike

PostgreSQL MonetDB HyPer (Demo) Aerospike Redis

10/

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 400 000 500 000 Clients Performance [Q/sec]

YCSB

NO-DB CUSTOM

20

slide-52
SLIDE 52

Modifying the Store

slide-53
SLIDE 53

Inserts

Batch Size 1 50,000 Inserts into LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 Clients Performance [ops/sec]

12/20

slide-54
SLIDE 54

Inserts

Batch Size 1 50,000 Inserts into LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 Clients Performance [ops/sec]

12/20

slide-55
SLIDE 55

Inserts

Batch Size 1 50,000 Inserts into LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 Clients Performance [ops/sec]

12/20

slide-56
SLIDE 56

Inserts

Batch Size 1 50,000 Inserts into LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 Clients Performance [ops/sec]

12/20

slide-57
SLIDE 57

Inserts

50,000 Inserts into LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis HyPer (Demo) b MonetDB

Single Client

1 10 100 1 000 100 1 000 10 000 100 000 Batch Size Performance [ops/sec]

13/20

slide-58
SLIDE 58

Inserts

50,000 Inserts into LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis HyPer (Demo) b MonetDB

Single Client

1 10 100 1 000 100 1 000 10 000 100 000 Batch Size Performance [ops/sec]

13/20

slide-59
SLIDE 59

Deletes

50,000 Deletes in LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

Batch Size 1

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 400 000 Clients Performance [ops/sec]

14/20

slide-60
SLIDE 60

Deletes

50,000 Deletes in LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

Batch Size 1

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 400 000 Clients Performance [ops/sec]

14/20

slide-61
SLIDE 61

Deletes

50,000 Deletes in LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

Batch Size 1

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 400 000 Clients Performance [ops/sec]

14/20

slide-62
SLIDE 62

Reading from the Store

slide-63
SLIDE 63

Queries on Primary Key

50,000 Point-Queries in LINEITEM and ORDERS

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 400 000 Clients Performance [ops/sec]

PG-row PG-hstore PG-jsonb Aerospike Redis

Batch Size 1

16/20

slide-64
SLIDE 64

Queries on Primary Key

50,000 Point-Queries in LINEITEM and ORDERS

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 400 000 Clients Performance [ops/sec]

PG-row PG-hstore PG-jsonb Aerospike Redis

Batch Size 1

16/20

slide-65
SLIDE 65

Queries on Primary Key

50,000 Point-Queries in LINEITEM and ORDERS

2 4 6 8 10 12 14 16 18 20 100 000 200 000 300 000 400 000 Clients Performance [ops/sec]

PG-row PG-hstore PG-jsonb Aerospike Redis

Batch Size 1

16/20

slide-66
SLIDE 66

Queries on Primary Key

50,000 Point-Queries in LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

Single Client

1 10 100 1 000 10 000 100 000 Batch Size Performance [ops/sec]

17/20

slide-67
SLIDE 67

Queries on Primary Key

50,000 Point-Queries in LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

Single Client

1 10 100 1 000 10 000 100 000 Batch Size Performance [ops/sec]

17/20

slide-68
SLIDE 68

Queries on Primary Key

50,000 Point-Queries in LINEITEM and ORDERS

PG-row PG-hstore PG-jsonb Aerospike Redis

Single Client

1 10 100 1 000 10 000 100 000 Batch Size Performance [ops/sec]

17/20

slide-69
SLIDE 69

Queries on Secondary Index

1 10 100 1 000 20 000 40 000 60 000 80 000 100 000 Range Size Performance [ops/sec]

PG-row PG-hstore PG-jsonb Aerospike Redis

SELECT MAX([ ps_supplycost | o_totalprice | l_discount ]) FROM [PARTSUPP | ORDERS | LINEITEM]

8 Clients

18/20

slide-70
SLIDE 70

Queries on Secondary Index

1 10 100 1 000 20 000 40 000 60 000 80 000 100 000 Range Size Performance [ops/sec]

PG-row PG-hstore PG-jsonb Aerospike Redis

SELECT MAX([ ps_supplycost | o_totalprice | l_discount ]) FROM [PARTSUPP | ORDERS | LINEITEM] WHERE [ ps_partkey | o_custkey | l_orderkey ] = value

if Range Size = 1

8 Clients

18/20

slide-71
SLIDE 71

Queries on Secondary Index

1 10 100 1 000 20 000 40 000 60 000 80 000 100 000 Range Size Performance [ops/sec]

PG-row PG-hstore PG-jsonb Aerospike Redis

SELECT MAX([ ps_supplycost | o_totalprice | l_discount ]) FROM [PARTSUPP | ORDERS | LINEITEM] WHERE [ ps_partkey | o_custkey | l_orderkey ] = value

if Range Size = 1

WHERE start <= [ ps_partkey | o_custkey | l_orderkey ] AND [ ps_partkey | o_custkey | l_orderkey ] <= end

if Range Size ≥ 1

8 Clients

18/20

slide-72
SLIDE 72

Queries on Secondary Index

1 10 100 1 000 20 000 40 000 60 000 80 000 100 000 Range Size Performance [ops/sec]

PG-row PG-hstore PG-jsonb Aerospike Redis

SELECT MAX([ ps_supplycost | o_totalprice | l_discount ]) FROM [PARTSUPP | ORDERS | LINEITEM] WHERE [ ps_partkey | o_custkey | l_orderkey ] = value

if Range Size = 1

WHERE start <= [ ps_partkey | o_custkey | l_orderkey ] AND [ ps_partkey | o_custkey | l_orderkey ] <= end

if Range Size ≥ 1

8 Clients

18/20

slide-73
SLIDE 73

Complex Analytical Queries

PG-row PG-hstore PG-jsonb Aerospike Redis HyPer (Demo) b MonetDB

Q01 Q06 0.01 0.1 1 10 100 TPC-H Query Duration [sec]

19/20

slide-74
SLIDE 74

Complex Analytical Queries

PG-row PG-hstore PG-jsonb Aerospike Redis HyPer (Demo) b MonetDB

Q01 Q06 0.01 0.1 1 10 100 TPC-H Query Duration [sec]

19/20

slide-75
SLIDE 75

Complex Analytical Queries

PG-row PG-hstore PG-jsonb Aerospike Redis HyPer (Demo) b MonetDB

Q01 Q06 0.01 0.1 1 10 100 TPC-H Query Duration [sec]

19/20

slide-76
SLIDE 76

Complex Analytical Queries

PG-row PG-hstore PG-jsonb Aerospike Redis HyPer (Demo) b MonetDB

Q01 Q06 0.01 0.1 1 10 100 TPC-H Query Duration [sec]

19/20

slide-77
SLIDE 77

Conclusion

20/20

slide-78
SLIDE 78

Conclusion

UDF support

20/20

slide-79
SLIDE 79

Conclusion

UDF support secondary index support

20/20

slide-80
SLIDE 80

Conclusion

UDF support secondary index support build-in data structures

20/20

slide-81
SLIDE 81

Conclusion

UDF support secondary index support bulk operations build-in data structures

20/20

slide-82
SLIDE 82

Conclusion

UDF support secondary index support bulk operations build-in data structures HSTORE

20/20

slide-83
SLIDE 83

Conclusion

UDF support secondary index support bulk operations build-in data structures HSTORE JSONB

20/20