Improving the Performance of Read-only Transactions Through Asynchronous Speculation

T. Ragunathan and P. Krishna Reddy International Institute of Information Technology (IIIT H), Hyderabad, India E-mail: [email protected]

Outline

• • • • • • •

Introduction and issues

2PL and SI-based Protocols Overview of Speculative Locking Speculative Locking for Read-only Transactions Simulation results Related Work Conclusions and Future Work

Introduction..

• Modern web-based information systems should meet intensive information requirements from large number of users.

– Must exploit cost-effective processing power – Possibilities of parallel processing should be identified.

• In this paper we investigate the issue of improving the performance of read-only transactions by increasing parallelism and exploiting cost-effective processing power.

Introduction..

• Information systems frequently execute read-only transactions or queries.

• Two-phase locking – lacks the power of meeting growing throughput demand.

– performance degrades with data contention.

• Snapshot-isolation-based protocol improves performance over 2PL.

– Suffers from data currency issues – Correctness is compromised.

Introduction

• In the literature, speculative locking is proposed to improve the parallelism by exploiting the low cost processing power.

– A transaction carries out multiple executions under speculation – It has been shown that speculation improves performance over two phase locking for OLTP workloads.

• In this paper we have extended speculative locking protocol for ROTs (SLR) by carrying out executions in asynchronous mode – It improves the performance over ROTs.

– Trades performance with extra processing resources – Does not violate serializability criteria – No data currency related issues

Issues for Processing ROTs

• Performance – High throughput performance • Correctness – Serializability criteria: The execution of transactions should be equivalent to a serial execution.

• Data currency – “

The data currency of the data object provided to Ti is the value of “t” which is the time difference between the commit time of the transaction which created the latest version of the data object and the commit time of the transaction which created the version of that data object that was read by Ti. If “t” is less/more, it means that transactions are provided with high/low data currency. ”.

–

Example: cricket score

Outline

• • • • • • • Introduction and issues

2PL and SI-based Protocols

Overview of Speculative Locking Speculative Locking for Read-only Transactions Simulation results Related Work Conclusions and Future Work

Two-Phase locking

• Processes transactions at serializable-isolation level • No data currency related issues • Under 2PL, the performance of ROTs degrades with data contention .

– ROTs wait for UTs.

R W

Lock Request by T i Lock Held by T j

R W yes no no no

Snapshot-Isolation (SI) based protocol

• SI-based protocol is a popular protocol – An ROT reads snapshot of the (committed) data.

– Ignores the effect of concurrent UTs.

– An ROT running at SI is never blocked. • High performance • Data currency is compromised • Violates serializability criteria

Outline

• • • • • • • Introduction and issues 2PL and SI-based Protocols

Overview of Speculative Locking

Speculative Locking for Read-only Transactions Simulation results Related Work Conclusions and Future Work

Speculative Locking Protocol

• Normally, a transaction starts producing after-images at the earlier stage of its execution.

• Though these after-image values are not effected during remaining processing, the transaction releases locks only after commit processing.

• In speculative approach, the transaction releases locks on a data object whenever it produces corresponding after-image value.

• By accessing both before- and after-images, the waiting transaction carries out speculative executions. • A transaction commits only after receiving termination decisions of the preceding transactions.

Lock Request by T i

R EW

Lock Held by T j

R EW yes no sp_yes no SPW sp_yes sp_yes

• In case of 2PL, it can be observed that even though T 1 updates X at earlier stage of execution, it releases the lock only after commit processing.

T 1 completes work on X

Speculative locking: Example

T 1 releases lock on X • In case of speculative approach, on completion of final update operation on X by T 1 , T 2 reads both X and X´ and carries out speculative executions.

• T 2 commits only after the termination of T 1 . If T 1 commits , T 22 is retained; otherwise T 21 is retained.

T 1 : T 2 : s 1 r 1 [X] w 1 [X'] r 1 [Y] w 1 [Y'] e 1 s 2 c 1 r 2 [X] w 2 [X'] r 2 [Z] w 2 [Z'] e 2 (i) 2PL T 1 : T 2 : s 2 s 1 r 1 [X] w 1 [X'] r 1 [Y] w 1 [Y'] T 21 e 1 r 2 [X] w 2 [X"] r 2 [Z] w 2 [Z'] T 22 r 2 [X'] w 2 [X"'] r 2 [Z] w 2 [Z"] c 1 e 2 c 2 time c 2 time (ii) SL

SL family of protocols

• In the literature, it has been shown that SL can improve the performance by trading extra resources in distributed environments.

• A family of SL protocols, SL(n), SL(1), and SL(2) are proposed.

• In SL(1) the number of speculative execution increases linearly with data contention.

• SL protocol produces serializable executions.

• The number of speculative executions explode with data contention.

Outline

• Introduction • 2PL and SI-based Protocols • • Basic Speculative Locking Protocol

Asynchronous Speculative Locking protocol for Read-only Transactions

• Performance Evaluation • Related Work • Conclusions and Future Work

Speculative Locking for ROTs (SLR): Basic Idea

• In the literature, speculative locking is proposed to process update transactions (UTs). – UTs contain both read and write operations.

• In SL, whenever a UT carries out write operation, it produces new uncommitted versions which are added to the data object trees.

• Observation:

Write operations are the cause for the generation of new uncommitted versions which in turn increase speculative executions

.

• Regarding ROTs – ROTs only read data.

– ROTs can commit at any time – So, if we process only ROTs with speculation, performance can be improved with less number of additional resources.

Basic idea to improve performance of ROTs with speculation

• Process update transactions with two-phase locking (2PL) – Update transactions release the locks whenever it completes execution.

• ROTs are processed with speculation.

Straightforward Extension of Speculation to ROT

• Synchronous SL for ROT – Update Transactions are processed with 2PL. – Whenever UT produces after-images, ROT reads both before- and after-images and carries out speculative executions.

– An ROT waits till the preceding transaction produces after-image to carry out speculative executions.

Synchronous Speculation: Example

(T 1 and T 3 are UTs and T 2 is ROT)

RR RU EW

Lock Request by T i

RR yes yes no

Lock Held by T j

RU EW yes no yes no no no SPW sp_yes no no

Proposed Method: Asynchronous Speculation to Process ROTs

• The speculative executions of ROTs can be processed in an asynchronous fashion or independent manner. – An ROT does not wait for after-image.

– The ROT is allowed to access the available data object versions and carry out speculative executions. – Whenever preceding transaction produces after-image, further speculative executions can be started in a dynamic manner. • In SLR, an ROT commits whenever it completes execution by retaining appropriate execution without waiting for the preceding transactions.

– Parallelism improves as compared to synchronous SL and 2PL

Asynchronous Speculation: Example

T 1 : r r 1 [x 0 ] w 1 [x 1 ] r 1 [p 0 ] w 1 [p 1 ] r 1 [q 0 ] w 1 [q 1 ] T 2 : T 21 r r 2 [x 0 ]r 2 [y 0 ]r 2 [z 0 ] T 22 r r 2 [x 1 ] T 3 : r r 3 [x 1 ] w 3 [x 2 ] time

Asynchronous Speculation Lock compatibility matrix

Lock Request

by T i

RR RU EW RR yes

Lock Held by T j

RU EW yes

sp_yes

yes no yes no no no SPW sp_yes no no

ASLR – correctness

• Three types of conflicts can occur: - RW conflict - WW conflict - WR conflict.

• Regarding RW conflict, a conflicting UT has to wait for the commitment of ROT to obtain the lock as per 2PL rule. So, all the RW conflicts are captured with ASLR.

• WW conflicts happen among UTs only. UTs follow 2PL. • WR conflicts force ROT to do speculative executions. The ROT commits with the execution which has taken only committed values of UTs. So WR conflict is captured.

• So, ASLR captures all the conflicts and ensures that serialization graph is acyclic. So, the history produced by ASLR is serializable.

Outline

• Introduction • 2PL and SI-based Protocols • Basic Speculative Locking Protocol • Speculative Locking protocol for Read-only Transactions •

Performance Evaluation

• Related Work • Conclusions and Future Work

Simulation Model

• Discrete event simulator based on a closed queuing model is developed.

• DB SIZE= 1000 • Transactions – ROTs (15-20) – UTs (5-15) • CPU time- 5 ms, IO Time – 10ms • We have compared 2PL, SL, ASLR, SI-2PL and FCWR protocols.

Parameter dbSize

Table 1.

Simulation Parameters, Meaning and Values Meaning Number of objects in the database Values 1000 cpuTime ioTime rotMaxTranSize rotMinTranSize Time to carry out CPU request Time to carry out I/O request Size of largest ROT transaction Size of smallest ROT transaction 5 ms 10 ms 20 objects 15 objects utMaxTranSize utMinTranSize noResUnits MPL % of UTs Size of largest UT transaction Size of smallest UT transaction Number of RUs( 1 CPU, 2 I/O ) Multiprogramming Level(10 – 100) Percentage of UTs ( 10 - 90) 15 objects 5 objects 8 Simulation Variable Simulation Variable

Performance Metrics

• Throughput # of transactions completed per second • CPU Utilization Let ‘c’ and ‘t’ denote CPU idle time and total simulation time respectively. Then, CPU utilization = 100(1 – (c/t)).

Performance results under unlimited resources

MPL vs Throughput

• • • Performance of ASLR is significantly higher than that of 2PL and FCWR, SI2PL. FCWR and SI2PL suffers from correctness ASLR performance is superior to SL.

80 70 60 50 40 30 20 10 0 0

# UTs = 30%, # RUs = 8

20 40 60

MPL

80 100 2PL FCWR SI-2PL SL ASLR

Details of Speculative Executions

# RUs = 8, MPL = 20, # UTs= 30% ASLR SL

80 60 40 20 0 1 2 4 8 >8

Number of speculative executions

• The average number of speculative executions: SL= 4.2 and ASLR=1.5

MPL versus CPU Utilization

ASLR 2PL FCWR 100 80 60 40 20 0 0 20 40 60

MPL

80 100

Performance results under limited resources

Throughput Performance

# RUs = 8, MPL = 20, # UTs= 30%

50 40 30 20 10 0 1 1.2

1.4

1.6

1.8

2

Total Memory Units (in multiples of MPL)

2PL SL ASLR

Implementation issues

• •

Pre-compiling.

A software module is required to put lock conversion marker.

- During execution, when the lock conversion marker is encountered, the EW-lock on the data object is converted into the SPW-lock.

Speculative executions.

- We have assumed that speculative executions of a transaction are carried out in parallel by considering multi processor (multi-core) environment.

Comparison of ASLR,SL, 2PL and FCWR

Parameter

Throughput Performance Device utilization

ASLR

High High

SL

High High

2PL

Low Low

FCWR

Medium High Performance variation with additional computing resources Additional resource requirement Data Currency Performance increases Manage-able (0.2 times) Maximum Correctness Serializable Performance increases slowly Very High Maximum Serializable Performance cannot be improved Not required Maximum Serializable Performance cannot be improved Not required Minimum Not Serializ able

Outline

•

Introduction

• 2PL and SI-based Protocols • Basic Speculative Locking Protocol • Asynchronous speculative locking protocol for read-only transactions.

• Performance Evaluation •

Related Work

• Conclusions and Future Work

Related Work

• Four isolation levels are specified in ANSI/ISO SQL 92 standard – read uncommitted, read committed, repeatable read, and serializable.

• Snapshot Isolation – Serializability Violation – Low Data Currency Problem • Recently

Alan Fekete

(ACM June 2005) has made an effort to make SI serilaizable by modifying the program logic. Also he works on automating the task of modifying the program (VLDB 2007).

• Speculation has been extended to improve the performance of distributed database systems

Outline

• • Introduction • 2PL and SI-based Protocols • Basic Speculative Locking Protocol • Synchronous speculative locking protocol for read only transactions • Asynchronous speculative locking protocol for read only transactions.

• Performance Evaluation • Related Work

Conclusions and Future Work

Conclusions and Future work

• Proposed improved protocol for ROTs based on asynchronous speculation.

– Improves the performance significantly over 2PL and SI based protocols. – Improves data currency over SI-based protocols. – Requires manageable extra processing resources.

• Future work – Investigation in distributed database environment – Investigating for real-time read-only transactions.