Data-oriented Transaction Execution

1. Ippokratis Pandis†‡ Ryan Johnson†‡ Nikos Hardavellas Anastasia Ailamaki †‡ †Carnegie Mellon University ‡École Polytechnique Fédérale de Lausanne  NorthwesternUniversity Data-oriented Transaction Execution VLDB 2010

2. Scaling OLTP on multicores/sockets CPU CPU Core Core Core Core Core Core Core Core Core Core Core Core Core Core Core Core Multicoremultisocket machine • Shared Everything • Hard to scale

3. Scaling OLTP on multicores/sockets CPU CPU Core Core Core Core Core Core Core Core Core Core Core Core Core Core Core Core • Two approachescomplementary • Focus on scalability of a single (shared everything) instance Multicoremultisocket machine • Shared Everything • Hard to scale • Shared Nothing • Multiple processes, physically separated data • Explicit contention control • Perfectly partition-able workload • Memory pressure: redundant data/structures

4. Shore-MT Sun Niagara T1 Insert-only mbench • Multithreaded version of SHORE • State-of-the-art DBMS features • Two-phase row-level locking • ARIES-style logging/recovery • Similar at instruction-level with commercial DBMSs • High-performing, scalable conventional engine • Available at: http://www.cs.wisc.edu/shore-mt/

5. Problems on Even Higher Parallelism Sun Niagara T2 TPC-C Payment Linear • Lock manager overhead dominant • Contention for acquiring locks in compatible mode

6. Data-oriented Transaction Execution • New transaction execution model • Reduce overhead of locking and data accesses • It is not the transaction which dictates what data the transaction-executing thread will access • Break each transaction into smaller actions • Depending on the data they touch • Execute actions by “data-owning” threads • Distribute and privatize locking, data accesses across threads

7. DORA vs. Conventional – At 100% CPU Sun Niagara T2 64 HW contexts • Eliminate contention on the centr. lock manager • Significantly reduced work (lightweight locks)

8. Roadmap • Introduction • Conventional execution • Data-oriented execution • Evaluation • Conclusions

9. Example Transaction: I D u(wh) u(cust) u(ord) CPU-1 CPU-2 CPU CPU-0 D D D I I I L1 L2 I = Instruction D = Data MEM I/O CUST ORD WH

10. Conventional - Example Transaction: I D u(wh) u(cust) u(ord) CPU-1 CPU-2 CPU CPU-0 u(wh) L1 u(cust) u(ord) L2 MEM I/O CUST ORD WH

11. Typical Lock Manager Lock Hash Table T1 Lock Head Xct’s Lock Requests L1 EX L2 EX EX Queue Lock Requests • The higher the HW parallelism Longer Queues of Requests  Longer CSs  Higher Contention

12. Time Inside the lock manager Sun Niagara T2 TPC-B • Latch contention on the lock manager

13. Thread-to-transaction – Access Pattern • Unpredictable access pattern • Source of contention

14. Roadmap • Introduction • Conventional execution • Data-oriented execution • Evaluation • Conclusions

15. Thread-to-data – Access Pattern • Predictable access patterns • Optimizations possible (e.g. no centralized locks)

16. Transaction Flow Graph TPC-C Payment Upd(WH) Upd(DI) Upd(CU) Phase 1 Phase 2 Ins(HI) • Each transaction input is a graph of Actions & RVPs • Actions • Table/Index it is accessing • Subset of routing fields • Rendezvous Points • Decision points (commit/abort) • Separate different phases • Counter of the # of actions to report • Last to report initiates next phase • Enqueue the actions of the next phase

17. Partitions & Executors Routing fields: {WH_ID, D_ID} Completed Local Lock Table Own A LM Wait Pref {1,0} EX A A {1,3} EX B B A A Input • Routing table at each table • {Routing fields  executor} • Executor thread • Local lock table • {RoutingFields + partof(PK), LockMode} • List of blocked actions • Input queue • New actions • Completed queue • On xct commit/abort • Remove from local lock table • Loop completed/input queue • Execute requests in serial order

18. DORA - Example Transaction: I D u(wh) u(cust) u(ord) CPU-1 CPU-2 CPU CPU-0 u(ord) u(wh) u(cust) L1 L2 MEM I/O CUST ORD WH • Centralized lock free • Improved data reuse

19. Non-partition aligned accesses Secondary index probes without part of the primary key in their definition Each entry stores the part of the primary key needed to decide routing, along with the RID Do the probe with the RVP-executing thread Enqueue a new action to the corresponding thread Do the record retrieve with the RID

20. Roadmap • Introduction • Conventional execution • Data-oriented execution • Evaluation • Conclusions

21. Eliminating contention on the lock mgr Sun Niagara T2 TM1 DORA BASELINE Baseline DORA • Eliminates contention on the lock manager • Linear scalability to 64 HW ctxs • Immune to oversaturation

22. Response time for single client better • Exploits intra-xctparallelism • Lower response times on low loads

23. Peak Performance better

24. Peak Performance better • Higher peak performance • Always close to 100% CPU utilization

25. Roadmap • Introduction • Conventional execution • Data-oriented execution • Evaluation • Conclusions

26. DORA vs. Conventional Avoid expensive (centralized) lock manager operations 20% 40+% Immune to centr. lock manager 2x Intra-xaction parallelism on light loads • Higher performance in the entire load spectrum

27. Reducing overheads of locking • Rdb/VMS • Distributed DBMS • Lock “carry-over” reduces network traffic • DB2 “keep table lock” setting • Connection holds all table locks until close • Leads to “poor concurrency” • H-Store • Single-threaded, non-interleaved execution model • No locking or latching at all

28. Summary Large number of active threads stress scalability of database system Data-oriented transaction execution (DORA) Decentralize locking & data accesses Benefits of shared-nothing w/o physical data partitioning Small modifications on a conventional engine Higher performance on the entire load spectrum