Jira (PDB-4386) Improve performance of catalog inserts/updates (by using an UPSERT)
Title: Message Title Jenna McCarthy updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates (by using an UPSERT) Change By: Jenna McCarthy Labels: 001G01pERe6IAG Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.84443.1626721860120%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates (by using an UPSERT)
Title: Message Title Nick Walker commented on PDB-4386 Re: Improve performance of catalog inserts/updates (by using an UPSERT) I'm a little skeptical of the reasoning here. However, even if we don't go with a full UPSERT it appears that we are updating, deleting, and maybe inserting a single row at a time instead of doing them as a single operation. It seems like at a minimum we could fetch all the records, do an UPSERT as one operation for insert and update ( instead of one operation per row that needs to be updated or inserted ), and then a delete as a single operation. I suspect it would still be better to write a single temp table that represents the new catalog and then an UPSERT followed by a delete but even without that I think removing all of the single row inserts and updates would be a boost. Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.42521.1622051100051%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates (by using an UPSERT)
Title: Message Title Rob Browning updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates (by using an UPSERT) Change By: Rob Browning Release Notes: Not Needed Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.38767.1621623540043%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates (by using an UPSERT)
Title: Message Title Rob Browning updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates (by using an UPSERT) Change By: Rob Browning Story Points: 3 0 Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.33624.1621029180113%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates (by using an UPSERT)
Title: Message Title Rob Browning commented on PDB-4386 Re: Improve performance of catalog inserts/updates (by using an UPSERT) After some investigation, and if our assumptions are correct, we think it's likely to be preferable to continue diffing the catalogs in PDB. One reason is that at the moment PDB CPU is expected to be a notably less constrained resource than postgres since we have the option of running multiple command processors, and trading PDB CPU for a decreased postgres write load is potentially useful. In addition, at least with plain SQL, while we could handle the new/changed rows via upsert, we'd still have to arrange for all the obsolete rows to be deleted. Furthermore, any unchanged rows, at least with the straightforward "on conflict update" solution, would still generate dead tuples (the only upsert that doesn't iirc is "do nothing"). Regarding the original problem, we noticed that the resource queries that were causing concern were likely running more slowly because of VM snapshot IO contention on the host. In any case, whatever we decide in the end, we've taken this opportunity to review some of the other storage code, and identified a number of places where we handle things more efficiently, via upsert, decreasing round trips, etc. cf. PDB-5128 Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit
Jira (PDB-4386) Improve performance of catalog inserts/updates (by using an UPSERT)
Title: Message Title Austin Blatt updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates (by using an UPSERT) Change By: Austin Blatt Sprint: HA 2020-05-05 , HA 2020-05-19 Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.26101.1620237900124%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates (by using an UPSERT)
Title: Message Title Rob Browning updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates (by using an UPSERT) Change By: Rob Browning Summary: Improve performance of catalog inserts/updates ( by using an UPSERT ) Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.23887.1620070980034%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates ( by using an UPSERT )
Title: Message Title Rob Browning updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates ( by using an UPSERT ) Change By: Rob Browning Team: PuppetDB HA Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.17255.1619196000193%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates ( by using an UPSERT )
Title: Message Title Rob Browning assigned an issue to Rob Browning PuppetDB / PDB-4386 Improve performance of catalog inserts/updates ( by using an UPSERT ) Change By: Rob Browning Assignee: Rob Browning Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.17206.1619194500040%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates ( by using an UPSERT )
Title: Message Title Zachary Kent updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates ( by using an UPSERT ) Change By: Zachary Kent Sprint: HAHA/Grooming HA 2020-05-05 Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.15592.1619030640089%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates ( by using an UPSERT )
Title: Message Title Zachary Kent updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates ( by using an UPSERT ) Change By: Zachary Kent Story Points: 3 Add Comment This message was sent by Atlassian Jira (v8.13.2#813002-sha1:c495a97) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.15588.1619030580125%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates ( by using an UPSERT )
Title: Message Title Zachary Kent updated an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates ( by using an UPSERT ) Change By: Zachary Kent Sprint: HAHA/Grooming Add Comment This message was sent by Atlassian Jira (v8.5.2#805002-sha1:a66f935) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/puppet-bugs/JIRA.310840.155925674.144103.1613584320103%40Atlassian.JIRA.
Jira (PDB-4386) Improve performance of catalog inserts/updates ( by using an UPSERT )
Title: Message Title Nick Walker created an issue PuppetDB / PDB-4386 Improve performance of catalog inserts/updates ( by using an UPSERT ) Issue Type: Improvement Assignee: Unassigned Attachments: pgbadger_catalog_resources_slow_query.png Created: 2019/05/30 3:52 PM Priority: Normal Reporter: Nick Walker The Problem After upgrade from PE 2019.0 to 2019.1 we're seeing a large uptick in the following query from running PGBadger on the postgresql logs. Where as before the upgrade we were only seeing a few hundred instances of that slow query. Suggested Solution Currently we query for the existing catalog_resources and compare to what we have in PDB to determine what we should update in the table. Then we send an update query with just those resources to update. https://github.com/puppetlabs/puppetdb/blob/efd35d761be93e4a7757f086ffb9030bb2b34133/src/puppetlabs/puppetdb/scf/storage.clj#L470 https://github.com/puppetlabs/puppetdb/blob/efd35d761be93e4a7757f086ffb9030bb2b34133/src/puppetlabs/puppetdb/scf/storage.clj#L636-L649 We can replace that logic with an INSERT INTO ... ON CONFLICT statement so that we send a single request to the database and it determines what to update instead of PuppetDB. Add Comment
