Making Database Applications Perform Using Program Analysis

1. Making Database Applications Perform Using Program Analysis Alvin Cheung Samuel MaddenArmando Solar-Lezama MIT Owen Arden Andrew C. Myers Cornell

2. Developing Database Applications Query SQL Application Logic Java SQL Database Application Server NEDB '13

3. Developing Database Applications Application Logic Query SQL StoredProcedures PL/SQL Java SQL Database Application Server NEDB '13

4. Developing Database Applications Language Choice for Application Logic Application Logic Query SQL StoredProcedures PL/SQL Java Program analysis to the rescue! Application Distribution SQL Database Application Server NEDB '13

5. StatusQuo • Express application logic in ways that programmers are comfortable with • Job of compiler & runtime to determine the most efficient implementation NEDB '13

6. Two Key Technologies • Infer queries from imperative code • Migrate computation between servers for optimal performance NEDB '13

7. Relational Operations in Imperative Code 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”;} convert to NEDB '13

8. Relational Operations in Imperative Code 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; } GoalFind a variable that we can rewrite into a SQL expression results output variable List getUsersWithRoles () {   return executeQuery( “SELECT u FROM users u, roles r WHERE u.roleId == r.id ORDER BY u.roleId, r.id”;} convert to NEDB '13

9. 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! NEDB '13

10. Search for Output Expressions 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 Relations involved:users, rolesPossible expressions to consider for results: σf(users) topf(users) πf(users ⨝g roles) πf(σg(users) ⨝hroles) other expressions involving users, roles Infinite search space size! NEDB '13

11. Constraints for Output Expressions 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 outer loop invariant results = πuser( users[0 .. i] ⨝roleId = id roles ) results = πuser( users ⨝roleId = id roles ) output expression results is true and outer loop terminates outer loop invariant If then output expression is true Hoare-style program verification Still need a smarter way to search NEDB '13

12. Search for Output Expressionsand Invariants • Use program synthesis as search engine Symbolic desc. of search space Expression thatsatisfies all theconstraints Program synthesizer Solutionconstraints Symbolic manipulation Counter-example driven search NEDB '13

13. Experiments NEDB '13

14. Real-world Evaluation NEDB '13

15. Performance Evaluation:Join Query Nested-loop join  Hash join! O(n2) O(n) NEDB '13

16. Developing Database Applications Application Logic Query SQL StoredProcedures PL/SQL Java Application Distribution SQL Database Application Server NEDB '13

17. Running Example 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); NEDB '13

18. 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); DB APP DB APP DB NEDB '13

19. 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); DB network communication APP network communication DB network communication APP network communication DB NEDB '13

20. 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); DB APP DB NEDB '13

21. 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); data dependency DB control dependency APP DB NEDB '13

22. 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); data dependency DB DB Server control dependency APP DB NEDB '13

23. Introducing Pyxis • “Store-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 NEDB '13

24. Using Pyxis NEDB '13

25. How Pyxis Works Java Java SQL SQL Java Java Partition SQL SQL Java Java SQL SQL Java Java Instrument control transfer Java SQL Deploy Java SQL Java SQL Java Monitor App Server DB Server NEDB '13

26. How Pyxis Works Java Java SQL SQL Java Java Partition SQL SQL Java Java SQL SQL Java Java Instrument control transfer Java SQL Deploy Java SQL Java SQL Java Monitor App Server DB Server NEDB '13

27. Generating Program Partitions • Deploy and profile application as-is • Construct a dependence graph of program statements • captures both control and data flow • Formulate linear program from profile data and dependence graph • solution gives a partitioning of the source code NEDB '13

28. Experiments NEDB '13

29. TPC-C on 16-core DB machine Pyxis generated implementation:3x latency reduction1.7x thruput increase NEDB '13

30. 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 NEDB '13

31. Backup NEDB '13