Transcript What if the system crashes during recovery

Log Tuning
© Dennis Shasha, Philippe Bonnet 2001
overview
What if the system crashes
during recovery
• The recovery algorithm should be run again, however,it
could be run more efficiently.
• Crash during the REDO phase:
– Some updates need not be redone if they are written to disk at
the last REDO phase.
– Note: pageLSN stored at each page indicates the most recent
log record for an update to that page
• Crash during the UNDO phase:
–
–
–
–
– Similar to crash during Xact aborts
– All the actions of the last UNDO phase should be reapplied
– the REDO phase will redo the CLRs
– then UNDO phase will continue with the undo tasks left
UNSTABLE STORAGE
DATABASE BUFFER
LOG BUFFER
lri lrj
Pi
WRITE
log records before commit
LOG
DATA
RECOVERY
Pj
WRITE
modified pages after commit
DATA
DATA
STABLE STORAGE
© Dennis Shasha, Philippe Bonnet 2001
Logging in SQL Server 2000
Log entries:
- LSN
- before and after images or
logical log
DB2 UDB v7 uses a similar scheme
Free
Current
Flush
Log caches Log caches Log caches
DATABASE BUFFER
Waiting
processes
db
writer
Flush
Log caches
free Pi
Lazywriter
Flush queue
Synchronous I/O
free Pj
Asynchronous I/O
DATA
LOG
© Dennis Shasha, Philippe Bonnet 2001
Logging in Oracle 8i
Before images
Free list
Rollback segments
(fixed size)
Log buffer (default 32 Kb)
After images
(redo entries)
DATABASE BUFFER
Pi
Pj
LGWR
(log writer)
DBWR
(database writer)
Log File
#1
Log File
#2
LOG
Rollback
Segments
© Dennis Shasha, Philippe Bonnet 2001
DATA
Put the Log on a Separate Disk
• Writes to log occur sequentially
• Writes to disk occur (at least) 100 times
faster when they occur sequentially than
when they occur randomly
A disk that has the log should have no other data
+ sequential I/O
+ log failure independent of database failure
© Dennis Shasha, Philippe Bonnet 2001
Put the Log on a Separate Disk
• 300 000 transactions.
Each contains an insert
or update statement.
– Oracle 9i on linux
• 30 % performance
improvement if log is
located on a different disk
© Dennis Shasha, Philippe Bonnet 2001
Group Commits
Throughput (tuples/sec)
• 300 000 transactions.
Each contains an insert
statement.
– DB2 UDB v7.1
350
• Group Commits: Log
300
250
200
150
100
50
0
1
25
Size of Group Commit
records of many transactions
are written together
– Increases throughput by
reducing the number of
writes
– at the cost of increased
mean response time, since
locks will be held longer.
© Dennis Shasha, Philippe Bonnet 2001
Tuning Database Writes
• Dirty data is written to 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 a checkpoint is performed
• Trade-off between online performance and
recoverability
• At regular intervals
• When the log is full (Oracle 8).
2 - Tuning the Guts
© Dennis Shasha, Philippe Bonnet 2001
10
Tune Checkpoint Intervals
• A checkpoint (partial flush
of dirty pages to disk)
occurs at regular intervals
or when the log is full:
Throughput Ratio
1.2
1
0.8
– Impacts the performance of
on-line processing
+ Reduces time to recover
from a crash
0.6
0.4
0.2
0
0 checkpoint
4 checkpoints
• 300 000 transactions.
Each contains an insert
statement.
– Oracle 8i for Windows
2000
© Dennis Shasha, Philippe Bonnet 2001
Reduce the Size of
Large Update Transactions
• Consider an update-intensive batch
transaction (concurrent access is not an
issue):
It can be broken up in short transactions
(mini-batch):
+ Easy to recover
+ Does not overfill the log buffers
Example: Transaction that updates, in sorted order, all accounts
that had activity on them, in a given day.
Break-up to mini-batches each of which access 10,000
© Dennis Shasha,
Philippe Bonnet
2001
accounts and
then
updates
a global
counter.
summary