Jira (PDB-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Eric Thompson updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Eric Thompson QA Status: Reviewed 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Andrew Roetker assigned an issue to Andrew Roetker PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Andrew Roetker Assignee: Ryan Senior Andrew Roetker 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Andrew Roetker assigned an issue to Unassigned PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Andrew Roetker Assignee: Andrew Roetker 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Ryan Senior Sprint: PuppetDB 2016-03-09, PuppetDB 2016-04-20 Funnel 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Ryan Senior Sprint: PuppetDB 2016-03-09, PuppetDB 2016- 05- 04 -20 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Ryan Senior Sprint: PuppetDB 2016-03-09, PuppetDB 2016-04- 06 20 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Susan McNerney Sprint: PuppetDB 2016-03-09, PuppetDB 2016- 03 04 - 23 06 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Susan McNerney Sprint: PuppetDB 2016-03-09 , PuppetDB 2016-03-23 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Susan McNerney Fix Version/s: PDB 4.0.0 Fix Version/s: PDB 4.1.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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Ryan Senior assigned an issue to Ryan Senior PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Nick Walker commented on PDB-2415 Re: PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL I have some notes about how I implemented a different set of queries for the report-ttl. https://docs.google.com/document/d/17_WGVPBsjWkquQl1EE3v6nnM_RL0zYY2fLKawhpdqeo/edit#heading=h.js9qux1ovj3t 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Susan McNerney 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Susan McNerney Story Points: 3 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-2415
PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Change By:
Nick Walker
h1. The ProblemWhile using the following to clean up some nodes that I had terminated I ran into an extreme slowdown in performance.{noformat}node-ttl = 12h node-purge-ttl = 4h {noformat} I traced it back to multiple delete statements that were running and I believe they were coming from foreign-key constraints on the certnames table. {noformat}pe-puppetdb=# \d+ certnames Table "public.certnames" Column | Type | Modifiers | Storage | Stats target | Description--+--+---+--+--+- id | bigint | not null default nextval('certname_id_seq'::regclass) | plain| | certname | text | not null | extended | | latest_report_id | bigint | | plain| | deactivated | timestamp with time zone | | plain| | expired | timestamp with time zone | | plain| |Indexes:"certnames_transform_pkey" PRIMARY KEY, btree (id)"certnames_transform_certname_key" UNIQUE CONSTRAINT, btree (certname)Foreign-key constraints:"certnames_reports_id_fkey" FOREIGN KEY (latest_report_id) REFERENCES reports(id) ON DELETE SET NULLReferenced by:TABLE "catalogs" CONSTRAINT "catalogs_certname_fkey" FOREIGN KEY (certname) REFERENCES certnames(certname) ON DELETE CASCADETABLE "factsets" CONSTRAINT "factsets_certname_fk" FOREIGN KEY (certname) REFERENCES certnames(certname) ON UPDATE CASCADE ON DELETE CASCADETABLE "reports" CONSTRAINT "reports_certname_fkey" FOREIGN KEY (certname) REFERENCES certnames(certname) ON DELETE CASCADEOptions: autovacuum_vacuum_scale_factor=0.20, autovacuum_analyze_scale_factor=0.10{noformat}My understanding is that instead of a bulk deletions of reports that are for certnames that are no longer active. We’re simply deleting reports, catalogs, and factsets using a cascading delete from certnames. This means that if I have 200 certnames to delete I think that postgresql will simply shoot out 200 queries to delete the reports that look something like {noformat}DELETE from reports where certname = $1;And another 200 queries to delete catalogs and another 200 to delete fact_sets {noformat}I think I proved that to myself by getting the query plan for deleting two certnames{noformat}pe-puppetdb=# EXPLAIN ANALYZE DELETE from certnames where certname IN ( 'mono-scale-agent-61.us-west-2.compute.internal', 'mono-scale-agent-60.us-west-2.compute.internal'); QUERY
Jira (PDB-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Susan McNerney Sprint: PuppetDB 2016-02- 10 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Nick Walker created an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Issue Type: Improvement Affects Versions: PDB 3.2.2 Assignee: Unassigned Created: 2016/02/10 9:17 AM Labels: tcse Priority: Normal Reporter: Nick Walker The Problem While using the following to clean up some nodes that I had terminated I ran into an extreme slowdown in performance. node-ttl = 12h node-purge-ttl = 4h
Jira (PDB-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-2415 PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL Change By: Susan McNerney Sprint: PuppetDB 2016-02-10 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-2415) PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL
Title: Message Title Russell Mull commented on PDB-2415 Re: PuppetDB Should Reduce Use of Cascading Deletes and Updates In PostgreSQL certnames_reports_id_fkey also appears to be the culprit for the generation of dead tuples in certnames; with out that constraint, pg should be able to HOT-update the rows when updating latest_report_id and avoid the garbage altogether. 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.
