Bulk Update in Table

Vertica_User

Hi,
Bulk update has been done to one of the Vertica table without following any best practices. Nearly 500 ROS containers are created and no of delete vectors are close to 400. Could you please guide me how to clean this up.

emoreno

You could purge the table to remove all the delete vectors

1- First move the ahm : SELECT make_ahm_now();
2- Purge the table : SELECT PURGE_TABLE('

'); but if the table is big it may take time, because it will be rewriting all the ROS that have delete vectors. You could wait to the merge_out to do its job. There is a checklist that gives you some steps of what to do : https://my.vertica.com/hpe-vertica-troubleshooting-checklists/manage-delete-vectors/ maybe you get more ideas how to best do it based on your table size. Hope this helps Eugenia

Vertica_User

Thanks Emoreno. AHM_EPOCH() is not advancing.
Current epoch is 5900345 and AHM is 5830810

Currently, MergeOut is running for replay Delete.
1. Shall I wait for the merge out to complete by itself?
2. Once the merge out is complete, will AHM advance automatically? or should we do it manually?

Vertica_User

Hi,
Looks like all WOS are moved to ROS during move out tuple operation.But, I can see around 20K delete vectors created for this table for storage type DVROS.
Should I purge the table to recover the space occupied for the deleted records in ROS?

Vertica_User

Please ignore my previous comment. I have gone through the content which was shared before.
https://my.vertica.com/hpe-vertica-troubleshooting-checklists/manage-delete-vectors/
Will take care. Thanks

drlove

Delete vectors should be purged with Mergeout but you can, as Eugenia (emoreno) mentioned, purge manually with the two steps Eugenia mentioned above. Getting rid of the delete vectors will help query performance and will gain you disk space.

You mentioned your AHM is not advancing. Are all your nodes up? AHM will not advance when you have a node down.

You asked if the AHM will advance automatically. Yes, it advances automatically according to the configuration parameter AdvanceAHMInterval, which is 180 seconds by default if you haven't changed that parameter. Note, AHM will not advance past the LGE. That is the epoch to look at when comparing with the AHM not the CE. Hope this helps.

Vertica_User

AHM is advancing this week as one of the node was down last week. I will try to purge the table manually. Thanks for the information.