Tables with tombstone blocks
RStoolKit Result: Tables with tombstone blocks
Tombstone blocks are generated when a WRITE transaction to an Amazon Redshift table occurs and there is a concurrent Read. Amazon Redshift keeps the blocks before the write operation to keep a concurrent Read operation consistent. Amazon Redshift blocks can’t be changed. Every Insert, Update or Delete action creates a new set of blocks, marking the old blocks as tombstoned. – AWS Doc
Pro Tip: Here is the more detailed Blog post about the tombstone block with visual explanation.
Find the tombstone blocks for all the tables:
-- Credits: AWS: Find the number of tombstones blocks SELECT Trim(name) AS tablename, Count(CASE WHEN tombstone > 0 THEN 1 ELSE NULL END) AS tombstones FROM svv_diskusage GROUP BY 1 HAVING Count(CASE WHEN tombstone > 0 THEN 1 ELSE NULL END) > 0 ORDER BY 2 DESC; -- Credits: AWS: Find when these blocks are added SELECT node, Date_trunc('h', endtime) AS endtime, Min(tombstonedblocks) min_tombstonedblocks, Max(tombstonedblocks) AS max_tombstonedblocks FROM stl_commit_stats WHERE tombstonedblocks > (SELECT 0.1 * SUM(capacity) AS disksize FROM stv_partitions WHERE host = owner AND host = 0) GROUP BY 1, 2 ORDER BY 2, 1;
How to fix this problem:
Tombstone blocks can be removed by running the vacuum FULL or DELETE ONLY. It always recommended to schedule the vacuum process, so you don’t need to worry about this. Even RedShift also will do auto vacuum, when your cluster’s load is less.
VACUUM [ FULL | SORT ONLY | DELETE ONLY | REINDEX ] [ [ table_name ] [ TO threshold PERCENT ] [ BOOST ] ]
- Vacuum options in RedShift
- RedShift vacuum Utility - Python Based
- Automate Vacuum Analyze Utility - Shell based with more control
- Tombstone Blocks - A visual explanation