How databases guarantee durability.
WHAT REDO RECORDS
Changes, written before they reach the data files.
WHY
So a crash can be recovered by replaying them.
WHAT THAT MAKES POSSIBLE
Committing without waiting for the data files to be written.
WHAT UNDO RECORDS
The previous versions of changed rows.
WHAT IT ENABLES
Rolling back Showing other transactions a consistent earlier view
WHY THAT SECOND POINT MATTERS
It is how readers avoid blocking writers.
WHAT HAPPENS WHEN A LONG TRANSACTION STAYS OPEN
Undo must be retained for it, and grows.
WHAT THE SYMPTOM IS
Storage growing steadily with no matching data growth.
WHAT TO LOOK FOR
The oldest open transaction.
WHAT USUALLY CAUSES IT
An application that began a transaction and never committed A session left open by a person
WHAT TO DO
Find and end it.
WHAT LOG FILE SIZE AFFECTS
How much can be written before a checkpoint is forced.
WHAT TOO SMALL PRODUCES
Frequent checkpoints, and stalls under heavy writes.
WHAT TOO LARGE PRODUCES
Longer recovery after a crash.
WHAT TO SET
Large enough for your write rate, accepting recovery time.
WHAT FLUSH SETTINGS TRADE
Durability against speed.
WHAT TO NEVER REDUCE ON A SYSTEM HOLDING MONEY
Durability.
