[Bucardo-general] Bad Plan for Bucardo Query

[David E. Wheeler]

On Sep 24, 2019, at 12:25, David Christensen <david at endpoint.com> wrote:

> This particular sentence stands out to me; if the delta tables are not getting cleaned out then I would expect some sort of eventual performance penalty as records just stacked up.  Bucardo is supposed to clean these up itself, but it will keep delta rows around if there are multiple targets that it thinks it still needs to replicate to; I would assume you’ve already checked if there are old targets in-place in the bucardo_delta_targets table?

Right, this is key. We’ve added and removed a bunch of syncs over the years, and it looks like Bucardo doesn’t clean everything up in the targets when syncs are removed. We cleaned out some bucardo_delta_targets records, and that has helped, but there are a bunch more, many of them dating from 2013-2016. We’re looking to clear those out next. Hopefully this solves the problem.

> The cleanup function runs from bucardo_purge_delta_oid(), which has the logic for cleanup of the delta tables based on track tables and dbgroups.  I’d try running some of those queries manually and seeing if you can find out why it’s not purging the records in question; not sure if it could have to do with some of the custom conflict handling logic changing records in such a way that the matching conditions aren’t working, but that or some old data that makes bucardo think it needs to hang onto seem like possible suspects here.


No, we haven’t deployed the conflict handler yet. Some of the more recent records, though, may well be due to conflicts that killed syncs. We cleaned up the conflicts manually and restarted the syncs, but I think it means a bunch of records remain.

Tempted to shut down Bucardo kill one of the targets, drop the bucardo database and the bucardo schema from the other target, then start things up again by creating our syncs anew, dumping the database and reloading it on the killed target and then starting Bucardo again to catch up. Clean start. PITA, though. Will start with deleting bucardo_delta_targets records and see how things look.

Thanks for the reply, useful insight.

Best,

David


-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: Message signed with OpenPGP
URL: <https://bucardo.org/pipermail/bucardo-general/attachments/20191005/1902ecfb/attachment.sig>