Log tuning
Download
1 / 20

Log Tuning - PowerPoint PPT Presentation


  • 123 Views
  • Uploaded on

Log Tuning. Every transaction either commits or aborts . It cannot change its mind Even in the face of failures: Effects of committed transactions should be permanent; Effects of aborted transactions should leave no trace. Atomicity and Durability. COMMITTED. COMMIT. Ø. ACTIVE

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Log Tuning' - kapono


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Atomicity and durability

Every transaction either commits or aborts. It cannot change its mind

Even in the face of failures:

Effects of committed transactions should be permanent;

Effects of aborted transactions should leave no trace.

Atomicity and Durability

COMMITTED

COMMIT

Ø

ACTIVE

(running, waiting)

BEGINTRANS

ABORTED

ROLLBACK

H. Galhardas


Outages

Environment

Fire in the machine room (Credit Lyonnais, 1996)

Operations

Problem during regular system administration, configuration and operation.

Maintenance

Problem during system repair and maintenance

Hardware

Fault in the physical devices: CPU, RAM, disks, network.

Software

99% are Heisenbugs: transient software error related to timing or overload.

Heisenbugs do not appear when the system is re-started.

Outages

H. Galhardas


Outages1

A fault tolerant system must provision for all causes of outages

Software is the problem

Hardware failures cause under 10% of outages

Heisenbugs stop the system without damaging the data.

Database systems protect integrity against single hardware failure and some software failures.

Outages

From J.Gray and A.ReutersTransaction Processing: Conceptsand Techniques

H. Galhardas


Db architecture
DB Architecture outages

Storage Subsystem

Concurrency Control

Recovery

Buffer Manager

Operating System

Hardware[Processor(s), Disk(s), Memory]

H. Galhardas


Buffer stealing frames and forcing pages
Buffer: Stealing frames and forcing pages outages

  • Steal approach: the changes made to an object O in the buffer pool by a transaction T are written to disk before T commits

  • No steal approach:pages updated by a transaction are only written to disk after the transaction commits

  • Force approach: when a transaction commits, all the changes it has made to objects in the buffer pool are immediately forced to disk

  • No force approach: the in-memory copy of a page that is being updated by several committed transactions is written to disk only when the page is eventually replaced in the buffer pool.

H. Galhardas


Handling the buffer pool
Handling the Buffer Pool outages

No Steal

Steal

  • No Steal/Force – simplest to use

    • changes of an aborted transaction do not have to be undone, because these

    • changes have not been written to disk

    • the changes of a committed transaction do not have to be redone if there is a subsequent crash, because all these changes are guaranteed to

    • have been written to disk at commit time.

  • But has drawbacks:

    • the no-steal app. Assumes that all pages modified by ongoing transactions can be accomodated in the buffer pool, but this is not realistic in the presence of

    • large transactions.

    • the force approach results in excessive

    • Page I/Os

Force

Trivial

Desired

No Force

  • Steal/No force usually used

    • if a page is dirty and chosen for replacement, the page it contains is written to disk even if the modifying transaction is still active

    • Pages in the buffer pool that are modified by a transaction are not forced to disk when the transaction commits

H. Galhardas


Handling the buffer pool1
Handling the Buffer Pool outages

No Steal

Steal

Undo

Force

UndoRedo

Redo

No Force

H. Galhardas


Logging
Logging outages

  • REDO and UNDO information in a log.

    • Sequential writes to log (put it on a separate disk).

    • Minimal info written to log, so multiple updates fit in a single log page.

  • Log: An ordered list of REDO/UNDO actions

    • Log record contains:

      <XID, pageID, offset, length, old data, new data>

    • and additional control info

Current database state = current state of data on disks + log

H. Galhardas


Write ahead logging wal
Write-Ahead Logging (WAL) outages

The Write-Ahead Logging Protocol:

  • Must force the log record for an update before the corresponding data page gets to disk.

    Guarantees Atomicity

  • Must write all log records for a Xact beforecommit.

    Guarantees Durability

    ARIES algorithms, developed by C.Mohan at IBM Almaden in the early 90’s

    http://www.almaden.ibm.com/u/mohan/ARIES_Impact.html

H. Galhardas


Log tuning

UNSTABLE STORAGE outages

DATABASE BUFFER

LOG BUFFER

lri

lrj

WRITE log records before commit

WRITE

modified pages after commit

LOG

DATA

DATA

DATA

RECOVERY

STABLE STORAGE

H. Galhardas


Tuning the recovery system
Tuning the recovery system outages

  • Put the log on a separate disk

  • Tuning database writes: delay writing updates to DB disks as long as possible

  • Setting intervals for database dumps and checkpoints

  • Reduce the size of large update transactions: from batch to mini-batch

H. Galhardas


Put the log on a separate disk
Put the Log on a Separate Disk outages

  • 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

H. Galhardas


Put the log on a separate disk1

300 000 transactions. Each contains an insert statement. outages

DB2 UDB v7.1

5 % performance improvement if log is located on a different disk

Controller cache hides negative impact

mid-range server, with Adaptec RAID controller (80Mb RAM) and 2x18Gb disk drives.

Put the Log on a Separate Disk

H. Galhardas


Group commits

300 000 transactions. Each contains an insert statement. outages

DB2 UDB v7.1

Log records of many transactions are written together

Increases throughput by reducing the number of writes

at the cost of increased mean response time.

Group Commits

H. Galhardas


Tuning database writes
Tuning Database Writes outages

  • Dirty data is written to disk

    • When the number of dirty pages is greater than a given parameter (Oracle 8)

    • When the number of pages in the free list crosses a given threshold (less than 3% of buffer size for SQL Server 7)

    • When a checkpoint is performed

      • At regular intervals

      • When the log is full (Oracle 8).

H. Galhardas


Tune checkpoint intervals

A checkpoint (partial flush of dirty pages to disk) occurs at regular intervals or when the log is full:

Impacts the performance of on-line processing

Reduces the size of log

Reduces time to recover from a crash

300 000 transactions. Each contains an insert statement.

Oracle 8i for Windows 2000

Tune Checkpoint Intervals

H. Galhardas


Reduce the size of large update transactions
Reduce the Size of at regular intervals or when the log is full: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 accounts and then updates a global counter.

H. Galhardas


Logging in sql server 2000
Logging in SQL Server 2000 at regular intervals or when the log is full:

Log entries:

- LSN- before and after images or logical log

DB2 UDB v7 uses a similar scheme

FreeLog caches

CurrentLog caches

FlushLog caches

DATABASE BUFFER

Waiting

processes

dbwriter

FlushLog caches

Lazy-writer

Flush queue

Synchronous I/O

Asynchronous I/O

LOG

DATA

H. Galhardas


Logging in oracle 8i
Logging in Oracle 8i at regular intervals or when the log is full:

Free list

Before images

Rollback segments(fixed size)

Log buffer (default 32 Kb)

DATABASE BUFFER

After images(redo entries)

LGWR(log writer)

DBWR(database writer)

RollbackSegments

LOG

DATA

Log File#1

Log File#2

H. Galhardas