1 / 20

Log Tuning

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

kapono
Download Presentation

Log Tuning

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Log Tuning

  2. 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

  3. 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

  4. 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

  5. DB Architecture Storage Subsystem Concurrency Control Recovery Buffer Manager Operating System Hardware[Processor(s), Disk(s), Memory] H. Galhardas

  6. Buffer: Stealing frames and forcing pages • 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

  7. Handling the Buffer Pool 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

  8. Handling the Buffer Pool No Steal Steal Undo Force UndoRedo Redo No Force H. Galhardas

  9. Logging • 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

  10. Write-Ahead Logging (WAL) 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

  11. UNSTABLE STORAGE 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

  12. Tuning the recovery system • 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

  13. 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 H. Galhardas

  14. 300 000 transactions. Each contains an insert statement. 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

  15. 300 000 transactions. Each contains an insert statement. 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

  16. 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 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

  17. 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

  18. 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 accounts and then updates a global counter. H. Galhardas

  19. Logging in SQL Server 2000 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

  20. Logging in Oracle 8i 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

More Related