AHM cannot lag behind
Hello All,
A very strange issue coming up when trying to drop projections.
I am trying to drop unwanted projections which are also super projections, but there is a exact copy of these projections segmented on lesser nodes.
So I go ahead and drop projections this way
drop projection xx.xxxx_b0, xx.xxxx_b1
After doing this I get such a message
ERROR 2154: AHM must lag behind the create epoch of unrefreshed projection string (Create epoch: value)
also checked the timing for epochs
select * from vs_epochs where epoch_number in(145135,143745,145134,143746);
143745 - 2014-04-01 19:41:14
143746 -2014-04-03 11:40:47
145134 -2014-04-09 10:40:03
145135 -2014-04-09 11:53:17
Why is the ahm not advancing further, Also to add I had just deployed new projection and done refresh operation on database for many projections one day before.
Thanks
A very strange issue coming up when trying to drop projections.
I am trying to drop unwanted projections which are also super projections, but there is a exact copy of these projections segmented on lesser nodes.
So I go ahead and drop projections this way
drop projection xx.xxxx_b0, xx.xxxx_b1
After doing this I get such a message
ROLLBACK 4470: Projection cannot be dropped because history after AHM would be lostI checked the epochs:
DETAIL: Current AHM epoch = 143745
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)
dbadmin=> select current_epoch,ahm_epoch,last_good_epoch,refresh_epoch from system; current_epoch | ahm_epoch | last_good_epoch | refresh_epochNow when I try using make_ahm_now() as in the hint.
---------------+-----------+-----------------+---------------
145135 | 143745 | 145134 | 143746
dbadmin=> select make_ahm_now();ERROR 2154: AHM must lag behind the create epoch of unrefreshed projection xx.xxxx_v1_b0 (Create epoch: 143746)Checked in the docs
ERROR 2154: AHM must lag behind the create epoch of unrefreshed projection string (Create epoch: value)
also checked the timing for epochs
select * from vs_epochs where epoch_number in(145135,143745,145134,143746);
143745 - 2014-04-01 19:41:14
143746 -2014-04-03 11:40:47
145134 -2014-04-09 10:40:03
145135 -2014-04-09 11:53:17
Why is the ahm not advancing further, Also to add I had just deployed new projection and done refresh operation on database for many projections one day before.
Thanks
0
Comments
There can be any reason for AHM not advancing, which can be clear from logs. It can be because of "too many ros containers" or delete vectors present in
system or some other reason, whcih can be more clear from vertica.log
You could use a query as below to find all projections with a large % of deletions.
Deletes cause query performance issues and can hold back the AHM/LGE as you saw.
=> select dv.projection_name,deleted_row_count*100/row_count as PctDeleted from delete_vectors dv,projection_storage ps
where ps.projection_schema = dv.schema_name and ps.projection_name = dv.projection_name order by projection_name;
Try to do following:
1. Either, refresh the projection xx.xxxx_v1_b0 and then advance AHM using "select make_ahm_now();
2. OR, drop projection xx.xxxx_v1_b0 and then advance AHM using "select make_ahm_now();
Regards'
Abhishek
I figured it out.
There were few projection/tables which were not refreshed.
Refreshed them and make_ahm_now started working.
Any good document where I can read about epochs other then Vertica documentations ?