The bottleneck of database systems has always been accessing the data. As soon as the data is in the 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.
What is the Teradata Write-Ahead Logic (WAL)?
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 improves the performance of DML statements.
How does the Teradata WAL work?
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 modified 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 the main memory.
At a later time, the data block is finally written back to the disk. This is done in the background.
If the data block’s size 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. The block in the main memory is lost, but the block on the disk could be corrupt.
The copy in WAL Depot is not necessary if the data block’s size has changed, because then it will be written to another place on the disk anyway.
How does Teradata WAL affect performance?
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 written to the WAL Log before and after a row are 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.
Where are the Teradata WAL Depot and the WAL Log stored?
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.