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 Experimental Analysis of Di ff erent Key-Value Stores and Relational Databases How di ff erent are they really? David Gembalczyk Felix Martin Schuhknecht Jens Dittrich Information Systems Group, Saarland University infosys.uni-saarland.de
2/20
OLTP OLAP 2/20
OLTP OLAP Point Queries Range Queries 2/20
OLTP OLAP Point Queries Range Queries Single Entry All Entries 2/20
OLTP OLAP Point Queries Range Queries Single Entry All Entries Relational DBs 2/20
OLTP OLAP Point Queries Range Queries Single Entry All Entries Relational DBs Relational DBs 2/20
OLTP OLAP Point Queries Range Queries Single Entry All Entries Relational DBs Relational DBs Key-Value Stores 2/20
OLTP OLAP Point Queries Range Queries Single Entry All Entries Relational DBs Relational DBs Key-Value Stores Key-Value Stores 2/20
Systems 3/20
Systems 3/20
Systems 3/20
Systems 3/20
Systems 3/20
Systems ( ) HyPer (Demo 0.5) 3/20
Relational Systems 4/20
Relational Systems Relational Table Primary A B C Key 1 a1 b1 c1 2 a2 b2 c2 4/20
Relational Systems Relational Table Primary A B C PostgreSQL, Key MonetDB, 1 a1 b1 c1 Hyper 2 a2 b2 c2 4/20
Relational Systems Relational Table Primary A B C PostgreSQL, Key MonetDB, 1 a1 b1 c1 Hyper 2 a2 b2 c2 TPC-H NATION N_NATIONKEY(integer) N_NAME(char(25)) N_REGIONKEY(integer) N_COMMENT(varchar(152))) 123 “Germany” 5 “some comment” ... ... ... ... 4/20
Redis Key Value “any string” “any string” 5/20
Redis Key Value “any string” [ “a”, “b”, “c”, “d”] 5/20
Redis Key Value “any string” { ( 0.5, „a“ ), { ( 1.0, „c“ ), { ( 1.5, „b“ ), { ( 2.0, „d“ ) } 5/20
Redis Key Value “any string” [ “1” ➞ “a“ , [ “2” ➞ “b“, [ “3” ➞ “c“, [ “4” ➞ “d“ ] 5/20
Redis Key Value “any string” [ “1” ➞ “a“ , [ “2” ➞ “b“, [ “3” ➞ “c“, [ “4” ➞ “d“ ] TPC-H NATION Value [ N_NAME ➞ “Germany“ , Key [ N_REGIONKEY ➞ “5“, “NATION_123” [ N_COMMENT ➞ “some comment“ ] 5/20
Aerospike 6/20
Aerospike Key “any string 1” 6/20
Aerospike Value Bin Bin Bin Key “binA”: “a” “binB”: 42 “binC”: [“a”, “b”, “c”] “any string 1” 6/20
Aerospike Value Bin Bin Bin Key “binA”: “a” “binB”: 42 “binC”: [“a”, “b”, “c”] “any string 1” Value Key Bin Bin “any string 2” “binB”: “b” “sub”: {“binD”: “d”, “binE”: “e”} 6/20
Aerospike Column? Value Bin Bin Bin Key “binA”: “a” “binB”: 42 “binC”: [“a”, “b”, “c”] “any string 1” Value Key Bin Bin “any string 2” “binB”: “b” “sub”: {“binD”: “d”, “binE”: “e”} 6/20
Aerospike Column? Value Bin Bin Bin Key “binA”: “a” “binB”: 42 “binC”: [“a”, “b”, “c”] “any string 1” Value Key Bin Bin “any string 2” “binB”: “b” “sub”: {“binD”: “d”, “binE”: “e”} 6/20
Aerospike Set Column? Value Bin Bin Bin Key “binA”: “a” “binB”: 42 “binC”: [“a”, “b”, “c”] “any string 1” Value Key Bin Bin “any string 2” “binB”: “b” “sub”: {“binD”: “d”, “binE”: “e”} 6/20
Aerospike Table? Set Column? Value Bin Bin Bin Key “binA”: “a” “binB”: 42 “binC”: [“a”, “b”, “c”] “any string 1” Value Key Bin Bin “any string 2” “binB”: “b” “sub”: {“binD”: “d”, “binE”: “e”} 6/20
Aerospike Table? Set Column? Value Bin Bin Bin Key “binA”: “a” “binB”: 42 “binC”: [“a”, “b”, “c”] “any string 1” Value Key Bin Bin “any string 2” “binB”: “b” “sub”: {“binD”: “d”, “binE”: “e”} TPC-H NATION Set Value Bin Bin Bin Key N_NAME: N_REGIONKEY: N_COMMENT: 123 “Germany” 5 “some comment” 6/20
PostgreSQL 7/20
PostgreSQL HSTORE Key Value [ “A” ➞ “a1”, “B” ➞ “b1”, 1 “C” ➞ “c1” ] 7/20
PostgreSQL JSONB HSTORE Key Value Key Value { [ “A” ➞ “a1”, “A”: “a1”, “B” ➞ “b1”, 1 “B”: “b1”, 1 “C” ➞ “c1” “C”: “c1” } ] { “A”: “a2”, “B”: “b2”, “C”: “c2”, 2 “sub”: { “D”: “d” “E”: “e” } } 7/20
PostgreSQL JSONB HSTORE Key Value Key Value { [ “A” ➞ “a1”, “A”: “a1”, “B” ➞ “b1”, 1 “B”: “b1”, 1 “C” ➞ “c1” “C”: “c1” } ] { “A”: “a2”, “B”: “b2”, “C”: “c2”, 2 “sub”: { “D”: “d” “E”: “e” } } Key Value Key Value [ [ “N_NAME” ➞ “Germany”, “N_NAME”: “Germany”, “N_REGIONKEY” ➞ “5”, 123 123 “N_REGIONKEY”: 5, “N_COMMENT” ➞ “some comment” “N_COMMENT”: “some comment” ] ] 7/20
Comparing Apples and Oranges 8/20
Comparing Apples and Oranges vs 8/20
Comparing Apples and Oranges RAM-disk vs enlarge caches warm-up run 8/20
Comparing Apples and Oranges RAM-disk vs enlarge caches warm-up run vs Custom Interface(s) 8/20
Comparing Apples and Oranges RAM-disk vs enlarge caches warm-up run vs Custom Interface(s) 8/20
Comparing Apples and Oranges RAM-disk vs enlarge caches warm-up run vs Custom Interface(s) 1 3 vs 1 2 3 4 2 4 8/20
Experimental Setup 9/20
Experimental Setup modified TPC-H LINEITEM PARTSUPP LINEITEMKEY PARTSUPPKEY ORDERKEY PARTKEY PARTKEY SUPPKEY SUPPKEY AVAILQTY LINENUMBER SUPPLYCOST QUANTITY COMMENT EXTENDEDPRICE DISCOUNT TAX RETURNFLAG LINESTATUS SHIPDATE COMMITDATE RECEIPTDATE SHIPINSTRUCT SHIPMODE COMMENT 9/20
Experimental Setup modified TPC-H LINEITEM PARTSUPP LINEITEMKEY PARTSUPPKEY ORDERKEY PARTKEY PARTKEY SUPPKEY SUPPKEY AVAILQTY LINENUMBER SUPPLYCOST QUANTITY COMMENT EXTENDEDPRICE DISCOUNT TAX RETURNFLAG LINESTATUS SHIPDATE COMMITDATE RECEIPTDATE SHIPINSTRUCT SHIPMODE COMMENT Scale Factor 1 9/20
Experimental Setup modified TPC-H LINEITEM PARTSUPP 50,000 ops/run LINEITEMKEY PARTSUPPKEY ORDERKEY PARTKEY PARTKEY SUPPKEY SUPPKEY AVAILQTY LINENUMBER SUPPLYCOST QUANTITY COMMENT warm-up 3 measurement run runs EXTENDEDPRICE DISCOUNT TAX RETURNFLAG LINESTATUS SHIPDATE COMMITDATE RECEIPTDATE SHIPINSTRUCT SHIPMODE COMMENT Scale Factor 1 9/20
Experimental Setup modified TPC-H LINEITEM PARTSUPP 50,000 ops/run LINEITEMKEY PARTSUPPKEY ORDERKEY PARTKEY PARTKEY SUPPKEY SUPPKEY AVAILQTY LINENUMBER SUPPLYCOST QUANTITY COMMENT warm-up 3 measurement run runs EXTENDEDPRICE DISCOUNT TAX RETURNFLAG Intel Xeon X5690 Intel Xeon X5690 LINESTATUS 3.47 3.47 3.47 3.47 SHIPDATE GHz GHz GHz GHz COMMITDATE 96GB 96GB 3.47 3.47 3.47 3.47 RAM RAM GHz GHz GHz GHz RECEIPTDATE 3.47 3.47 3.47 3.47 SHIPINSTRUCT GHz GHz GHz GHz SHIPMODE COMMENT Scale Factor 1 9/20
Round-Trip Time jdbc . executeQuery ( "SELECT 1;" ); // Relational DBs redis . echo ( "1" ); // Redis aerospike . exists ( non_existing_key ); // Aerospike 500 000 Performance [Q / sec] 400 000 300 000 200 000 100 000 0 2 4 6 8 10 12 14 16 18 20 Clients PostgreSQL MonetDB Aerospike HyPer (Demo) Redis 10/20
Round-Trip Time jdbc . executeQuery ( "SELECT 1;" ); // Relational DBs redis . echo ( "1" ); // Redis aerospike . exists ( non_existing_key ); // Aerospike 500 000 Performance [Q / sec] 400 000 NO-DB CUSTOM 300 000 YCSB 200 000 100 000 0 2 4 6 8 10 12 14 16 18 20 Clients PostgreSQL MonetDB Aerospike HyPer (Demo) Redis 10/ 20
Modifying the Store
Inserts 50,000 Inserts into LINEITEM and ORDERS Batch Size 1 300 000 Performance [ops / sec] 200 000 100 000 0 0 2 4 6 8 10 12 14 16 18 20 Clients PG-row PG-hstore PG-jsonb Aerospike Redis 12/20
Inserts 50,000 Inserts into LINEITEM and ORDERS Batch Size 1 300 000 Performance [ops / sec] 200 000 100 000 0 0 2 4 6 8 10 12 14 16 18 20 Clients PG-row PG-hstore PG-jsonb Aerospike Redis 12/20
Inserts 50,000 Inserts into LINEITEM and ORDERS Batch Size 1 300 000 Performance [ops / sec] 200 000 100 000 0 0 2 4 6 8 10 12 14 16 18 20 Clients PG-row PG-hstore PG-jsonb Aerospike Redis 12/20
Inserts 50,000 Inserts into LINEITEM and ORDERS Batch Size 1 300 000 Performance [ops / sec] 200 000 100 000 0 0 2 4 6 8 10 12 14 16 18 20 Clients PG-row PG-hstore PG-jsonb Aerospike Redis 12/20
Recommend
More recommend
