Performance Tuning

1
Performance Tuning

• Next, we focus on lock-based concurrency control,
  and look at optimising lock contention.
• The key is to combine the theory of concurrency
  control with practical DBMS knowledge
• Goal maximise DBMS throughput (not the response
  time of any single transaction)

2
Lock Tuning (I)

• Use special system facilities for long reads
• Create a version for reading purposes --
  multiversion read consistency
• Snapshot Isolation
• Eliminate locking when it is unnecessary
• Single transaction bulk loading
• Read only transaction statistical analysis

3
Lock Tuning (II)

• Take advantages of transactional context to chop
  transactions into small pieces
• Atomicity is only guaranteed on the small pieces!
• Longer transactions? more locks, longer wait
  (blocking others longer time)
• Weaken isolation guarantees when the application
  allows it
• SQL allows 4 level of consistency options

4
Lock Tuning (III)

• Select the appropriate locking granularity
• Table, record, field
• Do DDL statements when not busy
• Catalog is accessed by all transactions
• Avoid updates when system is busy
• Think about partitioning
• Use multiple physical disks, insertion points

5
Lock Tuning (IV)

• Circumventing hot spots
• Partitioning
• Delay the access till late stage of processing
• Use special database operations
• Tune the deadlock intervals
• How long to time-out a transaction?

6
Understand Your DBMS

• Each DBMS product may have different default
  locking behaviours
• Example SQL Server and Sybase
• Write locks are held to the end of a transaction
• Read locks are released immediately after use
• Not 2PL!

7
Understand Your Applications

• What is the requirement for your transaction?
• What will be the transactions that will run in
  parallel to your transaction?
• Where is the start and the end of your
  transaction?

8
Understand Your Run-Time Environment

• What are the concurrent transactions?
• What are their priorities?
• How your system has performed so far?

9
Snapshot Isolation

• Each transaction executes against the version of
  the data items that was committed when the
  transaction started
• No locks for read
• Costs space (old copy of data must be kept)
• Almost serialisable level
• T1 xy
• T2 y x
• Initially x3 and y 17
• Serial execution x,y17 or x,y3
• Snapshot isolation x17, y3 if both
  transactions start at the same time.

10
Isolation

• Correctness vs. Performance
• Number of locks held by each transaction
• Kind of locks
• Length of time a transaction holds locks
• Life is full of compromises
• High performance, at the cost of allowing some
  bad things happening
• Application programmer and DBA should make a
  decision
• An informed decision!

11
SQL Isolation Levels

• Degree 0
• Allow dirty read and lost update/nonrepeatable
  reads
• Write-locks released immediately after writing,
  and no read locks
• Degree 1 (read uncommitted)
• Degree 2 (read committed)
• Degree 3 (serialisable)

12
Isolation Levels

• Read Uncommitted (No lost update)
• Write-locks held for the duration of the
  transactions
• No read-locks
• Read Committed (No dirty retrieval)
• Read-locks released immediately after the read
  operation.
• SQL Server default option
• Repeatable Read (no unrepeatable reads for
  read/write )
• Two phase locking
• Serialisable (read/write/insert/delete model)
• Table locking or index locking to avoid phantoms

13
Value of Serializability Data

• Settings
• accounts( number, branchnum, balance)
• create clustered index c on accounts(number)
• 100000 rows
• Cold buffer same buffer size on all systems.
• Row level locking
• Isolation level (SERIALIZABLE or READ COMMITTED)
• SQL Server 7, DB2 v7.1 and Oracle 8i on Windows
  2000
• Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
  controller from Adaptec (80Mb), 4x18Gb drives
  (10000RPM), Windows 2000.

14
Value of Serializability Transactions

• Concurrent Transactions
• T1 summation query 1 thread
• select sum(balance) from accounts
• T2 swap balance between two account numbers (in
  order of scan to avoid deadlocks) N threads
• valXselect balance from accounts where
  numberXvalYselect balance from accounts
  where numberYupdate accounts set balancevalX
  where numberYupdate accounts set balancevalY
  where numberX

15
Value of Serializability Results

• With SQL Server and DB2 the scan returns
  incorrect answers if the read committed isolation
  level is used (default setting)

16
Cost of Serializability

• Because the update conflicts with the scan,
  correct answers are obtained at the cost of
  decreased concurrency and thus decreased
  throughput.

17
Logical Bottleneck Sequential Key Generation

• Consider an application that reuires a sequential
  number to act as a key in a table, e.g. invoice
  numbers for bills.
• Ad hoc approach a separate table holding the
  last invoice number. Fetch and update that number
  on each insert transaction.
• Counter approach use facility such as Sequence
  (Oracle)/Identity(MSSQL).

18
Counter Facility Data

• Settings
• default isolation level READ COMMITTED Empty
  tables
• Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
  controller from Adaptec (80Mb), 4x18Gb drives
  (10000RPM), Windows 2000.

accounts( number, branchnum, balance) create
clustered index c on accounts(number) counter
( nextkey ) insert into counter values (1)
19
Counter Facility Transactions

• No Concurrent Transactions
• System 100 000 inserts, N threads
• SQL Server 7 (uses Identity column)
• insert into accounts values (94496,2789)
• Oracle 8i
• insert into accounts values (seq.nextval,94496,278
  9)
• Ad-hoc 100 000 inserts, N threadsbegin
  transaction NextKeyselect nextkey from
  counter update counter set nextkey
  NextKey1commit transactionbegin transaction
  insert into accounts values(NextKey,?,?)commit
  transaction

20
Avoid Bottlenecks Counters

• System generated counter (system) much better
  than a counter managed as an attribute value
  within a table (ad hoc).

21
Insertion Points Transactions

• No Concurrent Transactions
• Sequential 100 000 inserts, N threads
• Insertions into account table with clustered
  index on ssnum
• Data is sorted on ssnum
• Single insertion point
• Non Sequential 100 000 inserts, N threads
• Insertions into account table with clustered
  index on ssnum
• Data is not sorted (uniform distribution)
• 100 000 insertion points
• Hashing Key 100 000 inserts, N threads
• Insertions into account table with extra
  attribute att with clustered index on (ssnum,
  att)
• Extra attribute att contains hash key (1021
  possible values)
• 1021 insertion points

22
Insertion Points

• Page locking single insertion point is a source
  of contention (sequential key with clustered
  index, or heap)
• Row locking No contention between successive
  insertions.
• DB2 v7.1 and Oracle 8i do not support page
  locking.

23
Summary

• In todays lecture, we have covered
• A review of concurrency control in DBMS
• How to optimise lock contention
• Concurrency control levels, their implications to
  the applications and their overheads in different
  systems