Transcript Recovery Tuning

Recovery Tuning

Main techniques




Put the log on a dedicated disk
Delay writing updates to the database
disks as long as possible
Setting proper intervals for DB dumping
and checkpointing
Reduce the size of large update
transactions
1
Separate Disk for the Log

Throughput (tuples/sec)
350
Log on same disk
Log on separate disk
300

250
200
150
100
50

0
controller cache
no controller cache
DB2 UDB v7.1 on
Windows 2000
5 % performance
improvement if log is
located on a different
disk
Controller cache hides
negative impact

Figure 2.18 in the textbook shows a
30% improvement
mid-range server, with
Adaptec RAID controller
(80Mb RAM) and 2x18Gb
disk drives.
2
Tuning Database Writes

Database writes caused by transactions tend
to be random

Better to be delayed as much as possible


But eventually they need to be written to the disk


Sufficient info in the log for recovery
To reduce recovery time
When to write?



Forced: when the buffer is full (or nearly full)
Opportunistic: when no extra overhead for disk seeking
Checkpoint: force all committed writes to disk
3
Writing Dirty Pages to the Disk



When the number of dirty pages is greater
than a given parameter (Oracle 8)
When the number of dirty pages crosses a
given threshold (less than 3% of free pages
in the database buffer for SQL Server 7)
When the log is full, a checkpoint is forced.
This can have a significant impact on
performance.
4
Tune Checkpoint Intervals

Throughput Ratio
1.2

1
0.8
0.6
0.4
0.2
0
Oracle 8i on Windows
2000
A checkpoint (partial
flush of dirty pages to
disk) occurs at regular
intervals or when the
log is full:

0 checkpoint
4 checkpoints
+
+
Impacts the performance
of on-line processing
Reduces the size of log
Reduces time to recover
from a crash
5
Group Commit


Log-writing a bottleneck if every committing
transaction needs a write to the log
Group commit




Write the logs of multiple transactions in batch
Need to use a “log buffer” (another thing to tune!)
Better throughput if many concurrent short update
transactions
Longer response time for individual transactions


This is a problem if they hold lock
Early release of locks can cause problems, but the risk is
remote
6
Log IO - Data
Settings:
lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER
, L_QUANTITY, L_EXTENDEDPRICE ,
L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS ,
L_SHIPDATE, L_COMMITDATE,
L_RECEIPTDATE, L_SHIPINSTRUCT ,
L_SHIPMODE , L_COMMENT );



READ COMMITTED isolation level
Empty table
Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID controller
from Adaptec (80Mb), 4x18Gb drives (10000RPM), Windows
2000.
7
Log IO - Transactions
No Concurrent Transactions:
Insertions [300 000 inserts, 10 threads], e.g.,
insert into lineitem values
(1,7760,401,1,17,28351.92,0.04,0.02,'N','O',
'1996-03-13','1996-02-12','1996-0322','DELIVER IN PERSON','TRUCK','blithely
regular ideas caj');
8
Group Commits
Throughput (tuples/sec)

350

300
250
200
150
100
50
DB2 UDB v7.1 on
Windows 2000
Log records of many
transactions are written
together

0
1
25
Size of Group Commit

Increases throughput by
reducing the number of
writes
At cost of increased
minimum response time.
9
Transaction Chopping

Some transactions, in particular batch
transactions, can be very long



A lot of log information
Very costly for recovery
Solution


Transaction chopping
An easy to understand concept

Formal work in appendix B of the textbook
10
Summary

In this module, we have covered:


The principles of recovery
How to optimise recovery-related options




Put the log on a dedicated disk
Delay writing updates
Using checkpoint and dump properly
Reduce the size of update transactions
11
CS5226 Week 6
Operating System &
Database Performance Tuning
Outline

Part 1: Operating systems and DBMS

Part 2: OS-related tuning
13
Operating System
• Operating system is an
interface between hardware
and other software,
supporting:
• Processes and threads;
hardware
• Paging, buffering and IO
scheduling
• Multi-tasking
• File system
• Other utilities such as timing,
networking and performing
monitoring
14
Scheduling

Process versus thread

Scheduling based on time-slicing, IO, priority etc


The cost of content switching


Different from transaction scheduling
When switch is desirable? And when is not?
The administrator can set priorities to
processes/threads



Case 1: The DBMS runs at a lower priority
Case 2: Different transactions run at different priority
Case 3: Online transactions with higher priority than offline
transactions
15
Priority Inversion

Let priorities T1 > T2s > T3
Request X
T1
T2s
T3
Lock x
… a solution: priority inheritance
16
Database Buffers
•An application can have its own inmemory buffers (e.g., variables in the
program; cursors);
•A logical read/write will be issued to
the DBMS if the data needs to be
read/written to the DBMS;
Application buffers
DBMS buffers
•A physical read/write is issued by the
DBMS using its systematic page
replacement algorithm. And such a
request is passed to the OS.
•OS may initiate IO operations to
support the virtual memory the DBMS
buffer is built on.
OS buffers
17
Database Buffer Size
DATABASE PROCESSES

Buffer too small, then hit
ratio too small
hit ratio =
(logical acc. - physical acc.) /
(logical acc.)
DATABASE
BUFFER
RAM


Paging
Disk
LOG
DATA
DATA
Buffer too large, paging
Recommended strategy:
monitor hit ratio and
increase buffer size until
hit ratio flattens out. If
there is still paging, then
buy memory.
18
Buffer Size - Data
Settings:
employees(ssnum, name, lat, long, hundreds1,
hundreds2);
clustered index c on employees(lat); (unused)




10 distinct values of lat and long, 100 distinct values of
hundreds1 and hundreds2
20000000 rows (630 Mb);
Warm Buffer
Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
controller from Adaptec (80Mb), 4x18Gb drives (10000
RPM), Windows 2000.
19
Buffer Size - Queries
Queries:
 Scan Query
select sum(long) from employees;

Multipoint query
select * from employees where lat = ?;
20
Database Buffer Size
Scan Query

Throughput
(Queries/sec)
0.1
0.08
0.06
0.04

0.02
SQL Server 7 on
Windows 2000
Scan query:

0
0
200
400
600
800
1000
Buffer Size (Mb)
Multipoint Query

Throughput
(Queries/sec)
160
120
Multipoint query:

80
40
LRU (least recently used)
does badly when table spills
to disk as Stonebraker
observed 20 years ago.
Throughput increases with
buffer size until all data is
accessed from RAM.
0
0
200
400
600
800
1000
Buffer Size (Mb)
21
Multiprogramming Levels

More concurrent users




Better utilization of CPU cycles (and other system
resources)
Risk of excessive page swapping
More lock conflicts
So how many exactly

Depends on transaction profiles



Experiments to find the best value
And this parameter may change when application
patterns change
Feedback control mechanism
22
Disk Layout and Access

Larger disk allocation chunks improves
write performance


Setting disk usage factor



At the cost of disk utilisation
Low when expecting updates/inserts
Higher for scan-type of queries
Prefetching within DBMS; not OS

For non-random accesses
23
Scan Performance - Data
Settings:
lineitem ( L_ORDERKEY, L_PARTKEY , L_SUPPKEY,
L_LINENUMBER
, L_QUANTITY, L_EXTENDEDPRICE ,
L_DISCOUNT, L_TAX , L_RETURNFLAG, L_LINESTATUS ,
L_SHIPDATE, L_COMMITDATE,
L_RECEIPTDATE, L_SHIPINSTRUCT ,
L_SHIPMODE , L_COMMENT );




600 000 rows
Lineitem tuples are ~ 160 bytes long
Cold Buffer
Dual Xeon (550MHz,512Kb), 1Gb RAM, Internal RAID
controller from Adaptec (80Mb), 4x18Gb drives (10000RPM),
Windows 2000.
24
Scan Performance - Queries
Queries:
select avg(l_discount) from lineitem;
25
Usage Factor
Throughput (Trans/sec)

0.2

0.15
0.1
scan
0.05
0
70
80
90
Usage Factor (%)
100

DB2 UDB v7.1 on
Windows 2000
Usage factor is the
percentage of the page
used by tuples and
auxiliary data structures
(the rest is reserved for
future)
Scan throughput
increases with usage
factor.
26
Prefetching
Throughput (Trans/sec)

0.2
DB2 UDB v7.1 on
Windows 2000
0.15

0.1
scan
0.05
0
32Kb
64Kb
128Kb
Prefetching
256Kb
Throughput
increases up to a
certain point when
prefetching size
increases.
27
Summary

In this module, we have covered:


A review of OS from the DBMS perspective
How to optimise OS-related parameters
and options



Thread
Buffer, and
File system
28