Last Good Epoch/Ancient History Mark not advancing

You have either data or delete vectors stuck in the WOS.

- Check the STORAGE_CONTAINERS table where storage_type like '%WOS' ordered by start_epoch to see if there's any data at an epoch right around your current AHM epoch.

- Check the DELETE_VECTORS table where storage_type like '%WOS' ordered by start_epoch to see if there are any DVs at an eopch right around your current AHM epoch.

That will help you identify the problematic table.  Then look for locks on that table using the LOCKs table and checking TUPLE_MOVER_OPERATIONS where is_executing.   

It could be a long-running tuple mover operation that is preventing the data from getting moved out, or a "too many ROS containers" condition.

  --Sharon

Hi Sharon,

Thank you for your reply.

I followed your advise with the following results:

The LGE is 26549170

Current epoch is 27505478

select * from storage_containers where storage_type = 'WOS' order by start_epoch; shows me the earliest start_epoch in WOS is 27504665.

select * from DELETE_VECTORS where storage_type like '%WOS%' order by start_epoch; shows an earliest start_epoch of 27504667

Both results are close to the current epoch, which leads me to believe there isn't actually any data stuck in WOS?

select * from TUPLE_MOVER_OPERATIONS where is_executing; does not show any long running Moveout operations.

We did have issues with too many ROS containers around the time the LGE stopped advancing, but not since.

You could look at the PROJECTION_CHECKPOINT_EPOCHS table.  Look for a projection on that node with the checkpoint_epoch value is the same as your current LGE.   When I just looked at that table it was slow to query so you probably want to dump it to a regular table or a temp table before you start poking around.

If that doesn't help, open a support case.

  --Sharon

Hi Sharon,

This actually solved the problem. Thank you very much.

I followed the steps as you advised:

Found Last Good Epoch using: SELECT get_last_good_epoch(); . LGE was 26549170.

Looked for projections that had a checkpoint_epoch corresponding to LGE:

SELECT * FROM PROJECTION_CHECKPOINT_EPOCHS WHERE checkpoint_epoch = 26549170;

This resulted in one table, which we dropped (cascade) and recreated. Straight away the LGE advanced to 27135640, which is still not close to the current epoch of 27593331, but we can repeat the procedure looking at the next table that stops the LGE from advancing.

Thanks again.

Yay!  

Though if you are seeing the problem on multiple tables, it would be worth understanding how/why you're getting into this situation.

You are on a relatively old version of 7.2.  There's this fix in a 7.2.3 hotfix if this sounds plausible:

  --Sharon

Looking back at your original post and the original error:

2016-11-07 15:01:20.923 TM Moveout:0x7f6a5cd6cd90 <ERROR> @v_byk_dwh_node0002: {threadShim} 55V03/5157: Unavailable: [Txn 0xb000002e05f0a3] Moveout of mart_sales_assistant.snowplow_events_b0 - timeout error Timed out T locking Table:mart_sales_assistant.snowplow_events. X held by [user etl_user 

That lock was no longer present? 

Looking through the release notes again, there are also bugs related to LGE for table creation during recovery and dropping storage locations.  Definitely worth an upgrade to the most recent 7.2 anyway.

Hi Sharon,

Yes, the lock is no longer present. The issue seemed to only affect two tables, one of which was definitely created during recovery, so I'm assuming that's where the problem originated. The hotfix seems to describe the issue exactly.

We're planning to upgrade to the latest 7.2.x release soon, so hopefully that will prevent this issue from occuring in the future.

Again, thanks a lot for your help.

Tim

During AHM syncing activity my [vertica](During AHM syncing activity my vertica nodes went down several time and I started the nodes manually. Can you explain why it happens. I used the steps explained below. https://www.orahow.com/2018/06/fix-ahm-not-advancing-to-last-epoch.html "vertica") nodes went down several time and I started the nodes manually. Can you explain why it happens. I used the steps explained below.

Hi,

Are you asking why did your nodes go down? I'm guess a network issue? Have you looked at the vertica.log for some sort of indication of a problem?

Check the "Spread Debugging" Knowledge Base article here:

https://my.vertica.com/kb/Spread-Debugging/Content/BestPractices/Spread-Debugging.htm

Note: If Spread on one node stops communicating with the other Spreads in the cluster, the Spread daemon removes that node from the cluster membership. The Spread daemon waits for 8 seconds before removing nodes from the membership.

HI Jim,

Is this interval (8 seconds) configurable ? I am considering a scenario where there is a network disconnect for lets say 15-20 seconds. If we know this beforehand, we can increase this value to avoid the node outage.

In a non-Vertica deployment of Spread you can modify the token timeout.

See: http://www.spread.org/docs/guide/users_guide.pdf

However, in a Vertica deployment, we do not support any change of the spread timeout settings, as this would require a custom build of spread, and would likely have unseen consequences and could destabilize the system.

Fyi ... Here is link to a great read titled "Spread Configuration Best Practices":
https://my.vertica.com/kb/SpreadConfigurationBestPractices/Content/SpreadConfigurationBestPractices.htm