Jira (PDB-3747) PDB holds summary query transactions open during long syncs

[Charlie Sharpsteen (JIRA)]

Charlie Sharpsteen updated an issue

PuppetDB / PDB-3747 PDB holds summary query transactions open during long syncs Change By: Charlie Sharpsteen Summary: PDB holds  summary  query transactions open during long syncs Add Comment

This message was sent by Atlassian JIRA (v7.0.2#70111-sha1:88534db)

[Owen Rodabaugh (JIRA)]

Owen Rodabaugh updated an issue

PuppetDB / PDB-3747 PDB holds summary query transactions open during long syncs

Change By: Owen Rodabaugh CS Priority: Needs Priority Major CS Impact: When you reconnect a partitioned HA setup this can cause the postgresql nodes to slow to a crawl a few hours later as these long running transactions prevent maintenance. This can take hours to resolve CS Severity: 4 - Major CS Business Value: 4 - $$$$$ CS Frequency: 2 - 5-25% of Customers

Add Comment

[Nick Walker (JIRA)]

Nick Walker commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs Russell Mull thoughts on this? Seems like a cascading failure when sync fails and you need to re-sync you don't want to get into this situation.

Add Comment

This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93)

[Russell Mull (JIRA)]

Russell Mull commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs

Agree, I think this is a bug.

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt assigned an issue to Austin Blatt

PuppetDB / PDB-3747

PDB holds summary query transactions open during long syncs

Change By: Austin Blatt Assignee: Austin Blatt

Add Comment

[Austin Blatt (JIRA)]

[Austin Blatt (JIRA)]

Austin Blatt assigned an issue to Unassigned

[Adam Bottchen (JIRA)]

Adam Bottchen updated an issue

PuppetDB / PDB-3747 PDB holds summary query transactions open during long syncs

Change By: Adam Bottchen Labels: cstop10

Add Comment

[Yasmin Rajabi (JIRA)]

Yasmin Rajabi commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs This is currently on the prioritized backlog according to Rob Browning

Add Comment

[Yasmin Rajabi (JIRA)]

Yasmin Rajabi commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs

Rob Browning when we last chatted you mentioned this was on the backlog, do you have an idea where it sits?

Add Comment

[Charlie Sharpsteen (JIRA)]

Charlie Sharpsteen commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs

Since the description is fairly technical, a higher level overview is: The primary and replica become de-synced and copying the data required to sync back up will take several hours. After a couple hours, the DB queries that are running for sync start to severely impact the performance of the database that is providing the data. At this point, the PE administrator has to start periodically interrupting the connection between the two PuppetDB instances in order to force the sync to avoid queries that run long enough to avoid a performance impact.

Add Comment

[Nick Walker (JIRA)]

Nick Walker commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs

It seems like when we're looking for commands to sync we should do a select columns from table LIMIT <some reasonable number> and then sync those commands and commit the change and keep looping until we don't have sync work left to do. That way we commit changes in small batches and don't leave a long running transaction. Austin Blatt is that reasonable?

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs

Yes, we already batch the transfer in sets of 2,000, but we leave the summary query open and start the next set. My general plan is to end that query entirely after the first batch and start over. Unfortunately that'll mean we re-run the summary query every 2,000 items transferred, but those are fairly lightweight queries afaik (we should verify that during this work and optimize where necessary if that is not the case).

Add Comment

[Nick Walker (JIRA)]

Nick Walker commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs

Can we just add a limit 2000 to the summary query? That should make it even more lightweight

Add Comment

[Austin Blatt (JIRA)]

Austin Blatt commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs

Yeah, that sounds like a reasonable change.

Add Comment

[Rob Browning (JIRA)]

Rob Browning commented on PDB-3747

Re: PDB holds summary query transactions open during long syncs

Yasmin Rajabi It's on our team's list of priorities, and it sounds like it's on others' lists as well, so given that, and what it sounds like may be a more general emphasis on HA, I assume it's likely to be addressed in our next rounds of work – we definitely want to fix it.

Add Comment

[Austin Blatt (Jira)]

Austin Blatt updated an issue

PuppetDB / PDB-3747

PDB holds summary query transactions open during long syncs

Change By: Austin Blatt Release Notes: Not Needed

Add Comment

This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935)