Making Database Applications Perform Using Program Analysis

grrrgrapeInternet και Εφαρμογές Web

31 Οκτ 2013 (πριν από 3 χρόνια και 9 μήνες)

65 εμφανίσεις

Making Database Applications Perform
Using
Program
Analysis

Alvin Cheung

Samuel Madden

Armando Solar
-
Lezama

MIT


Owen Arden

Andrew C. Myers


Cornell


Developing Database
Applications

2/1/13

NEDB '13

2

Application Server

SQL Database

Java

Application

Logic

SQL

Query

Developing Database
Applications

2/1/13

NEDB '13

3

SQL Database

Java

Application

Logic

PL/SQL

Stored

Procedures

SQL

Query

Application Server

Developing Database
Applications

2/1/13

NEDB '13

4

SQL Database

Java

Application

Logic

PL/SQL

Stored

Procedures

SQL

Query

Application Server

Language Choice for Application Logic

Application Distribution

Program
analysis
to the rescue!

StatusQuo



Express application logic in ways that
programmers are comfortable with



Job of compiler & runtime to determine the
most efficient implementation

2/1/13

NEDB '13

5

Two Key Technologies



Infer queries from imperative code



Migrate computation between servers for
optimal performance

2/1/13

NEDB '13

6

Relational
Operations
in
Imperative
Code

2/1/13

NEDB '13

7

List getUsersWithRoles () {


List users = getUsersFromDB();


List roles = getRolesFromDB();


List results =
new
ArrayList
();


for (User u : users)
{


for (Role r : roles)
{


if (
u.roleId

==
r.id
)


results.add(
u);

}}



return
results
; }


SELECT * FROM
user

SELECT * FROM
role

List getUsersWithRoles ()
{



return
executeQuery
(



SELECT u FROM users u, roles r

WHERE
u.roleId

== r.id


ORDER BY u.roleId, r.id”;

}


c
onvert to

Relational Operations in
Imperative Code

2/1/13

NEDB '13

8

List getUsersWithRoles () {


List users = getUsersFromDB();


List roles = getRolesFromDB();


List results =
new
ArrayList
();


for (User u : users)
{


for (Role r : roles)
{


if
(
u.roleId

==
r.id
)


results.add(
u);

}}



return
results
; }


List getUsersWithRoles ()
{



return
executeQuery
(



SELECT u FROM users u, roles r

WHERE
u.roleId

== r.id


ORDER BY u.roleId, r.id”;

}


c
onvert to


Goal


Find a variable that

we can rewrite into a

SQL expression


output variable

results

Query By Synthesis (QBS)


Identify potential code fragments


i.e., regions
of code that fetches
persistent
data and return values



Find SQL expressions for output variables



Try to prove that those expressions
preserve program semantics


if so, convert the code!

2/1/13

NEDB '13

9

Search for
Output Expressions

2/1/13

NEDB '13

11

List getUsersWithRoles () {


List users =
query(select * from users);


List roles =
query(select * from roles);


List results =
[];


for (User u : users)
{


for (Role r : roles)
{


if
(
u.roleId

==
r.id
)


results = results : [] }}



return
results
; }


Infinite search space size!

users

roles

results

Constraints for

Output Expressions

2/1/13

NEDB '13

12

List getUsersWithRoles () {


List users =
query(select * from users);


List roles =
query(select * from roles);


List results =
[];


for (User u : users)
{


for (Role r : roles)
{


if
(
u.roleId

==
r.id
)


results = results : [] }}



return
results
; }


users

roles

results

If

output expression

outer loop invariant

outer loop invariant

outer loop terminates

then

output expression

is true and

is true

Still need a smarter

way to search

results = π
user
( users

roleId

= id
roles )


results = π
user
( users[0 ..
i
]

roleId

= id
roles )

Search for Output Expressions

and
Invariants


Use
program synthesis

as search engine

2/1/13

NEDB '13

13

Symbolic
desc
.

of
search space

Solution

constraints

Expression that

satisfies all the

constraints

Symbolic manipulation

Counter
-
example driven search

Experiments

2/1/13

NEDB '13

14

Real
-
world Evaluation

2/1/13

NEDB '13

15

Wilos


(
project management application)


62k

LOC

Operation type

# Fragments

found

# Fragments converted

Projection

1

1

Selection

13

10

Join

7

7

Aggregation

11

10

Total

33

28

100
1K
10K
100K
1000K
0
20K
40K
60K
80K
100K
Execution time
(
ms
)

Number of roles / users in DB

original (lazy)
inferred (lazy)
Performance Evaluation:

Join Query

2/1/13

NEDB '13

16

Nested
-
loop join



Hash join!



O(n
2
)
O(n)

Developing Database
Applications

2/1/13

NEDB '13

17

SQL Database

Java

Application

Logic

PL/SQL

Stored

Procedures

SQL

Query

Application Server

Application Distribution

Running
E
xample

discount =
executeQuery("select

discount from
customers







where id = " + cid);


totalAmount

=
orderTotal

* (1


discount);


credit =
executeQuery
("select credit from customers








where id = " + cid);


if (credit <
totalAmount
)


printToConsole
("Only " + credit + " in account!");

else


executeUpdate("update

customer set credit = " +










(credit


totalAmount
) + "
where id = " + cid);

2/1/13

18

NEDB '13

Actual Execution

discount =
executeQuery("select

discount from
customers







where id = " + cid);


totalAmount

=
orderTotal

* (1


discount);


credit =
executeQuery
("select credit from customers








where id = " + cid);


if (credit <
totalAmount
)


printToConsole
("Only " + credit + " in account!");

else


executeUpdate("update

customer set credit = " +







(credit


totalAmount
) + "
where id = " + cid);

2/1/13

19

NEDB '13

DB

APP

APP

DB

DB

Actual Execution

discount =
executeQuery("select

discount from
customers







where id = " + cid);


totalAmount

=
orderTotal

* (1


discount);


credit =
executeQuery
("select credit from customers








where id = " + cid);


if (credit <
totalAmount
)


printToConsole
("Only " + credit + " in account!");

else


executeUpdate("update

customer set credit = " +






(credit


totalAmount
) + " where
id = " + cid);

2/1/13

20

NEDB '13

n
etwork communication

network communication

network communication

network communication

DB

APP

APP

DB

DB

Speeding up Execution

discount =
executeQuery("select

discount from
customers







where id = " + cid);


totalAmount

=
orderTotal

* (1


discount);


credit =
executeQuery
("select credit from customers








where id = " + cid);


if (credit <
totalAmount
)


printToConsole
("Only " + credit + " in account!");

else


executeUpdate("update

customer set credit = " +






(credit


totalAmount
) + " where
id = " + cid);

2/1/13

21

NEDB '13

DB

APP

DB

Speeding up Execution

discount =
executeQuery("select

discount from
customers







where id = " + cid);


totalAmount

=
orderTotal

* (1


discount);


credit =
executeQuery
("select credit from customers








where id = " + cid);


if (credit <
totalAmount
)


printToConsole
("Only " + credit + " in account!");

else


executeUpdate("update

customer set credit = " +








(credit


totalAmount
) + "
where id = " + cid);

2/1/13

22

NEDB '13

data dependency

DB

APP

DB

control dependency

Speeding
up Execution

discount =
executeQuery("select

discount from
customers







where id = " + cid);


totalAmount

=
orderTotal

* (1


discount);


credit =
executeQuery
("select credit from customers








where id = " +
cid
);


if (credit <
totalAmount
)


printToConsole
("Only " + credit + " in account!");

else


executeUpdate("update

customer set credit = " +







(credit


totalAmount
) + " where
id = " + cid);

2/1/13

23

NEDB '13

DB Server

DB

APP

DB

control dependency

data dependency

Introducing Pyxis



S
tore
-
procedurizes
” DB apps and pushes
computation to the DB



Adaptively controls the amount of
computation pushed to DB for optimal
performance



No programmer intervention required


2/1/13

24

NEDB '13

Using
Pyxis

2/1/13

NEDB '13

25

How Pyxis Works

2/1/13

NEDB '13

26

Instrument

Partition

Monitor

App Server

DB Server

Deploy

Java

SQL

Java

SQL

Java

Java

Java

SQL

Java

SQL

SQL

Java

Java

SQL

Java

SQL

Java

SQL

Java

SQL

Java

control transfer

How Pyxis Works

2/1/13

NEDB '13

27

Monitor

App Server

DB Server

Deploy

Java

Java

SQL

Java

SQL

SQL

Java

control transfer

Instrument

Partition

Java

SQL

Java

SQL

Java

SQL

Java

Java

SQL

Java

SQL

Java

SQL

Java

Generating Program Partitions


Deploy and profile application as
-
is


Construct a dependence graph of program
statements


c
aptures both control and data flow


Formulate linear program from profile data
and dependence graph


s
olution gives a partitioning of the source
code

2/1/13

NEDB '13

28

Experiments

2/1/13

NEDB '13

30

5
10
15
20
25
100
300
500
700
900
1100
1300
Average Latency (ms)

Average Thruput (xact / s)

JDBC
Manual
Pyxis
TPC
-
C on 16
-
core DB machine

2/1/13

32

NEDB '13

Pyxis

generated implementation:

3x latency reduction

1.7x
thruput

increase

StatusQuo

Ease
DB application development


Convert imperative program statements into
declarative SQL


Fully automatic code partitioning using
application and server characteristics


db.csail.mit.edu/statusquo

2/1/13

NEDB '13

33

Backup

2/1/13

NEDB '13

34