Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Kurt Wall updated an issue PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Change By: Kurt Wall QA Status: Reviewed QA Risk Assessment: Low Add Comment This message was sent by Atlassian JIRA (v6.4.13#64028-sha1:b7939e9) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Change By: Ryan Senior Fix Version/s: PDB 3.2.x Fix Version/s: PDB 4.0.0 Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Daniele Sluijters commented on PDB-2424 Re: DELETE FROM edges is extremely slow Thanks for figuring this out Ryan Senior! Sorry I couldn't get you a better description of what we were seeing. Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior commented on PDB-2424 Re: DELETE FROM edges is extremely slow I have figured out the issue and I have a fix for it. We're coercing the value in the column to something so that we can compare it with the value we're trying to delete. This means we'll decode every source and target hash for the certname before we compare it to the value we have in hand (the thing we're trying to delete). I have a PR up that switches it so we convert the source/target hash we're trying to delete to bytea, then run the query. This means we convert the value we have in hand, not each of the values in the column. This is not only WAY faster, it also hits the unique index that is already in place for the edges table. Daniele Sluijters with this patch you won't need your indexes anymore, they won't hurt anything, but you'll find they won't be used. Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior assigned an issue to Ryan Senior PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Change By: Ryan Senior Assignee: Ryan Senior Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Change By: Susan McNerney Sprint: PuppetDB 2016- 03-09, PuppetDB 2016- 02-24 Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior commented on PDB-2424 Re: DELETE FROM edges is extremely slow maybe it's the encode thing, we've switched to that recently, I'll investigate that side of it Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Daniele Sluijters commented on PDB-2424 Re: DELETE FROM edges is extremely slow Ryan Senior From what we could see the DELETE would call with the encode(source) in there and since the index created by PuppetDB doesn't take the end value produced by encode into account, but only the original source the index you mention doesn't get used. Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior commented on PDB-2424 Re: DELETE FROM edges is extremely slow Daniele Sluijters I looked at this and I don't see the issue. We have a unique index on (certname, source, target, type). Our current delete edges code, calls a delete using (certname, source, target). This still hits the unique index. There's only a few types of edges that we support, so it doesn't seem like that would ever be slow. One thing I do see is that we issue seperate deletes for each edge, which is really slow. Instead we should issue one delete statement that includes all the edges we need to delete. That doesn't seem to match up with what you're seeing. Do you have any more info on the issue? Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Russell Mull commented on PDB-2424 Re: DELETE FROM edges is extremely slow edges appears to be pretty seriously under-indexed at the moment; this fix does sound plausible. Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Change By: Ryan Senior Scope Change Category: Adopted Scope Change Reason: Had extra time Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Change By: Ryan Senior Story Points: 1 Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Change By: Ryan Senior Sprint: PuppetDB 2016- 03 02 - 09 24 Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Change By: Ryan Senior Sprint: PuppetDB 2016-03-09 Add Comment This message was sent by Atlassian JIRA (v6.4.12#64027-sha1:e3691cc) -- 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 post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at https://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-2424) DELETE FROM edges is extremely slow
Title: Message Title Daniele Sluijters created an issue PuppetDB / PDB-2424 DELETE FROM edges is extremely slow Issue Type: Bug Assignee: Unassigned Created: 2016/02/11 8:00 AM Priority: Normal Reporter: Daniele Sluijters We were seeing a lot of DELETE FROM edges queries taking 4-6s or more. We added some indices on edges which seems to have solved it: "edges_certname_idx" btree (certname) "edges_source_hex_idx" btree (encode(source, 'hex'::text)) "edges_target_hex_idx" btree (encode(target, 'hex'::text)) "edges_type_idx" btree (type)
