drop projections, update query performance

Katrin_Pfistere

Hallo Stephen, We have a problem. We have a large table oaslt.pts_raw_data_delivery_detail with two projections. Because of our hourly updates on this table, I created two new projections. After the refresh of these two new projections, we wanted to drop the old ones. But the drop was not successful: The result: vsql:/home/vertica/dba/drop_projections.sql:6: ROLLBACK 4470: Projection cannot be dropped because history after AHM would be lost DETAIL: Current AHM epoch = 632121 HINT: Use make_ahm_now() to move the AHM epoch to the present. Use set_ahm_epoch() only if you rely on historical queries (AT EPOCH/TIME) So we executed: select make_ahm_now(); And tried to drop the projections again. But no success, them same output message. How can we drop the old projections? We also saw, that our update on this table takes up to 10 times longer than before. Is this, because we have now 4 projections with 2 different sort orders for this table? Please help. Thank you. Best regards Katrin

Daniel_Leybovic

Try to define HistoryRetentionTime.
Im not sure that it is  issue of HistoryRetentionTime, but looks very similar.

Important note:

In Vertica 4.1, the default for the HistoryRetentionTime configuration parameter changed to 0, which means that Vertica only keeps historical data when nodes are down. This new setting effectively prevents the use of the Administration Tools 'Roll Back Database to Last Good Epoch' option because the AHM remains close to the current epoch and a rollback is not permitted to an epoch prior to the AHM. If you rely on the Roll Back option to remove recently loaded data, consider setting a day-wide window for removing loaded data; for example:

=> SELECT SET_CONFIG_PARAMETER ('HistoryRetentionTime', '86400');

[DUPLICATE]
https://community.vertica.com/vertica/topics/get_last_good_epoch_not_advancing_so_cant_drop_old_proj...

Prasanta_Pal

Try the below:

Verify the output refresh_epoch with current_epoch, they should be uptodate - rather same

dbadmin=> select current_epoch,last_good_epoch, ahm_epoch, refresh_epoch from system;

if not then run
dbadmin=>select start_refresh();

probably projections are not refreshed properly.

Sharon_Cutter

Daniel - I think you have misinterpreted the purpose of HistoryRententionTime.  That configuration parameter controls how far back data is kept before it is eligible for being purged.  By default, AHM stays pretty current, so that a ROS container that is merged is also likely to purge recently-deleted records in the merged containers.  If you set HistoryRetentionTime to be longer - let's say 6 hours, then AHM will stay 6 hours back, maintaining 6 hours of history, and deleted records won't be purged for at least 6 hours.  A stale AHM also means that deleted records won't be removed when a table or projection is purged.  I wouldn't change this setting without fully understanding the consequences since large numbers of deleted records can negatively impact performance.

Prasanta pointed towards unrefreshed projections, which is the likeliest cause of AHM being held back.  Check the projections table for projections that are not is_up_to_date.  You may want to drop them, or refresh them, once you see the list.

Katrin_Pfistere

Thank you for your help.
Our DBA team worked with your support.
After a few hours they were able to set the ahm_time to now
and then the drop of the projections was no problem.