FASTER @andy_pavlo Yale University Columbia University April 2012 - - PowerPoint PPT Presentation

▶
faster
SMART_READER_LITE
LIVE PREVIEW

FASTER @andy_pavlo Yale University Columbia University April 2012 - - PowerPoint PPT Presentation

Jul 10, 2023 506 likes •845 views

Making Fast Databases FASTER @andy_pavlo Yale University Columbia University April 2012 Fast + Cheap Legacy Systems TPC-C NewOrder 100% 12.3% Real Work 80% 29.6% Buffer Pool 60% Latching 10.2% CPU Cycles Locking 18.7% 40%

slide-1
SLIDE 1

@andy_pavlo

FASTER

Making Fast Databases

Yale University Columbia University

April 2012
slide-2
SLIDE 2
slide-3
SLIDE 3

Fast Cheap

+

slide-4
SLIDE 4

OLTP Through the Looking Glass, and What We Found There

SIGMOD 2008

0% 20% 40% 60% 80% 100%

Real Work Buffer Pool Latching Locking Logging B-Tree Keys CPU Cycles

TPC-C NewOrder

8.1% 21.1% 18.7% 10.2% 29.6% 12.3%

Legacy Systems

slide-5
SLIDE 5

Fast Repetitive Small

OLTP Transactions

slide-6
SLIDE 6

Main Memory • Parallel • Shared-Nothing Transaction Processing

H-Store: A High-Performance, Distributed Main Memory Transaction Processing System

VLDB vol. 1, issue 2, 2008

slide-7
SLIDE 7

Client Application

Database Cluster

Procedure Name Input Parameters Stored Procedure Execution Database Cluster

slide-8
SLIDE 8
slide-9
SLIDE 9 25,000 50,000 75,000 100,000 125,000 150,000 4 8 12 12 16 16 20 20 24 24 28 28 32 32 36 36 40 40 44 44 48 48 52 52 56 56 60 60 64 64

txn/s /s Parti titi tion

  • ns

No Distributed Txns 20% Distributed Txns

TPC-C NewOrder

slide-10
SLIDE 10

Skew-Aware Automatic Database Partitioning in Shared-Nothing, Parallel OLTP Systems

SIGMOD 2012

Partition database to reduce the number of distributed txns.

Optimization #1:

slide-11
SLIDE 11
  • _id
  • _c_id
id
  • _w_id
… 78703 1004 5
  • 78704
1002 3
  • 78705
1006 7
  • 78706
1005 6
  • 78707
1005 6
  • 78708
1003 12
  • c_id
c_w_id id c_last st … 1001 5 RZA
  • 1002
3 GZA
  • 1003
12 Raekwon
  • 1004
5 Deck
  • 1005
6 Killah
  • 1006
7 ODB
  • CUSTOMER

ORDERS

CUSTOMER ORDERS CUSTOMER ORDERS CUSTOMER ORDERS

slide-12
SLIDE 12

CUSTOMER ORDERS CUSTOMER ORDERS CUSTOMER ORDERS

ITEM

i_id i_name i_pric ice … 603514 XXX 23.99
  • 267923
XXX 19.99
  • 475386
XXX 14.99
  • 578945
XXX 9.98
  • 476348
XXX 103.49
  • 784285
XXX 69.99
  • ITEM

ITEM ITEM

slide-13
SLIDE 13

CUSTOMER ORDERS CUSTOMER ORDERS CUSTOMER ORDERS ITEM ITEM ITEM

CUSTOMER

c_id c_w_id id c_last st … 1001 5 RZA
  • 1002
3 GZA
  • 1003
12 Raekwon
  • 1004
5 Deck
  • 1005
6 Killah
  • 1006
7 ODB
slide-14
SLIDE 14

CUSTOMER ORDERS CUSTOMER ORDERS CUSTOMER ORDERS ITEM ITEM ITEM

Client Application NewOrder(5, “Method Man”, 1234)

slide-15
SLIDE 15 CUSTOMER ORDERS ITEM CUSTOMER ORDERS ITEM CUSTOMER ORDERS ITEM CUSTOMER ORDERS ITEM

…

Large-NeighorhoodSearch Algorithm

Skew Estimator DTxn Estimator

Schema Workload

  • DDL

DDL

slide-16
SLIDE 16

Workload

  • Schema

DDL DDL

Initial Design Relaxation Local Search Restart

Large-Neighborhood Search

slide-17
SLIDE 17

Workload

  • Schema

DDL DDL

Initial Design Relaxation Local Search Restart

Large-Neighborhood Search

slide-18
SLIDE 18

TATP TPC-C TPC-C Skewed

(txn/s)

+88% +16% +183%

Horticulture State-of-the-Art

Throughput

10,000 20,000 30,000 40,000 50,000 60,000 4 8 16 16 32 32 64 64 2,000 4,000 6,000 8,000 10,000 12,000 14,000 4 8 16 16 32 32 64 64 10,000 20,000 30,000 40,000 50,000 60,000 70,000 80,000 4 8 16 16 32 32 64 64
slide-19
SLIDE 19

TATP SEATS TPC-C TPC-C Skewed AuctionMark TPC-E

Search Times

% Single-Partitioned Transactions

slide-20
SLIDE 20
slide-21
SLIDE 21

Database Cluster

Database Cluster Client Application Undo Log

slide-22
SLIDE 22

On Predictive Modeling for Optimizing Transaction Execution in Parallel OLTP Systems

VLDB, vol 5. issue 2, October 2011

Predict what txnswill do beforethey execute.

Optimization #2:

slide-23
SLIDE 23

Database Cluster

Database Cluster Client Application

» Partitions Touched? » Undo Log? » Done with Partitions?

slide-24
SLIDE 24
slide-25
SLIDE 25

w_id=0 i_w_ids=[0,1] i_ids=[1001,1002]

Input Parameters:

CurrentState:

SELECT * FROM WAREHOUSE WHERE W_ID = ?

GetWarehouse:

slide-26
SLIDE 26

Confidence Coefficient: 0.96 Best Partition: Partitions Accessed: { 0 } Use Undo Logging: Yes

Transaction Estimate:

Estimated Execution Path

w_id=0 i_w_ids=[0,1] i_ids=[1001,1002]

Input Parameters:

slide-27
SLIDE 27

TATP TPC-C AuctionMark

(txn/s)

2,000 4,000 6,000 8,000 10,000 12,000 14,000 4 8 16 32 64 2,000 4,000 6,000 8,000 10,000 12,000 14,000 16,000 4 8 16 32 64 2,000 4,000 6,000 8,000 10,000 12,000 14,000 16,000 4 8 16 32 64

+57% +126% +117%

Houdini Assume Single-Partitioned

Throughput

slide-28
SLIDE 28

TATP TPC-C AuctionMark

Prediction Overhead

slide-29
SLIDE 29
slide-30
SLIDE 30

Future Work:

Reduce distributed txnoverhead through creative scheduling.

Conclusion:

Achieving fast performance is more than just using only RAM.

slide-31
SLIDE 31

h-store

hstore.cs.brown.edu

github.com/apavlo/h-store

slide-32
SLIDE 32

Help is Available

Graduate Student Abuse Hotline

Available24/7 Collect Calls Accepted

+1-212-939-7064