Teradata WAL – The Write-Ahead Logic
The bottleneck of database systems has always been accessing the data. As soon as the data is in main memory, it can be processed.
Even though mainly SSDs are now used and less and less hard disks, copying the data into the main memory is still the slowest process.
Teradata WAL is a feature that serves to improve the performance of DML operations and simultaneously increases the security of the data.
The Teradata WAL is used to increase the reliability of DML statements and to ensure that changes can be recovered in the event of errors. The WAL also increases the performance of DML statements.
When a DML statement is executed, Teradata copies the affected data blocks from the disk into the main memory.
A copy of the rows to be changed is stored in the WAL log.
The data block is changed in the main memory and marked as changed but not written back to the disk.
The changed rows are written to the WAL log.
All locks are released. Other sessions can now make changes to this data block which is still in main memory.
At a later time, the data block is finally written back to the disk. This is done in the background.
If the size of the data block has not changed, it is written back to the same place.
To prevent a complete loss of the data block a copy is written into the WAL depot.
A complete loss could happen if the system fails while writing the data block. Then the block in main memory is lost but also the block on the disk could be corrupt.
The copy in WAL Depot is not necessary if the size of the data block has changed, because then it will be written to another place on the disk anyway.
Since several transactions can be executed on the same data block in memory, performance is improved because fewer IOs have to be executed.
The possible copy in the WAL repository is an additional IO but is necessary if an existing data block is overwritten.
The copies that are written to the WAL Log before and after a row is changed are single row writes and therefore much more efficient than writing a data block.
Overall WAL has a positive effect on performance. The additional IO into the WAL depot cannot be prevented, but collecting changes in main memory reduces the number of IOs.
The WAL Depot is a fixed number of cylinders on each AMP. If possible several blocks are written at once.
The WAL Log requires a dynamic number of cylinders on each AMP.