Jira (PDB-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Russell Mull updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Russell Mull Fix Version/s: PDB 4.2.3.2 Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Karen Van der Veer updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Karen Van der Veer Sprint: SE 2017-03-08 , SE 2017-03-22 Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Nick Walker Release Notes Summary: PostgreSQL CPU usage increaseAfter upgrading a split install to 2016.5.1 or 2016.5.2, PostgreSQL CPU usage increases by 50 percent. To workaround this issue, we've created the pdb_3323_workaround module.1. On the Puppet master, download the [module](https://forge.puppet.com/puppetlabs/pdb_3323_workaround).2. Classify the node hosting pe-postgresql with the `pdb_3323_workaround` class.3. On the node hosting pe-postgresql, run Puppet.After you install this module, it's safe to forget about. The index will be removed when you upgrade in the future. Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Nick Walker Release Notes Summary: PostgreSQL CPU usage increaseAfter upgrading a split install to 2016.5.1 or 2016.5.2, PostgreSQL CPU usage increases by 50 percent. To workaround this issue, we've created the puppetlabs- pdb_3323_workaround module.1. On the Puppet master, download the [module](https://forge.puppet.com/puppetlabs/pdb_3323_workaround).2. Classify the node hosting pe-postgresql with the `pdb_3323_workaround` class.3. On the node hosting pe-postgresql, run Puppet.After you install this module, it's safe to forget about. The index will be removed when you upgrade in the future. Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Nick Walker Release Notes Summary: PostgreSQL CPU usage increaseAfter upgrading a split install to 2016.5.1 or 2016.5.2, PostgreSQL CPU usage increases by 50 percent. To workaround this issue, we've created the puppetlabs-pdb_3323_workaround module.1. On the Puppet master, download the [module](https://forge.puppet.com/puppetlabs/pdb_3323_workaround).2. Classify the node hosting pe-postgresql with the `pdb_3323_workaround` class.3. On the Puppet master and the node hosting pe-postgresql, run Puppet.After you install this module, it's safe to ignore forget about . The class index will be removed when you upgrade in the future. Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Isaac Eldridge updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Isaac Eldridge Release Notes Summary: PostgreSQL CPU usage increase After upgrading a split install to 2016.5.1 or 2016.5.2, PostgreSQL related CPU usage increases by around 50 % percent . To workaround this issue, use we've created the puppetlabs- pdb_3323_workaround module to create and manage an index that provides a short-term fix: . 1. On the Puppet master, download the [module]( https://forge.puppet.com/puppetlabs/pdb_3323_workaround ).2. Classify the node hosting pe-postgresql with the `pdb_3323_workaround` class.3. On the Puppet master and the node hosting pe-postgresql, run Puppet.After you install this module, it's safe to ignore. The class will be removed when you upgrade in the future. Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3323
Checking if a report exists before inserting it is SLOW
Change By:
Nick Walker
h1. Suggested workaroundhttps:// github forge . puppet. com/puppetlabs/ puppetlabs- pdb_3323_workaroundh1. The ProblemWorking with a customer we noticed performance degradation after updating from PE 2016.4 to 2016.5.2. Using PgBadger to analyze the PostgreSQL logs we can see that checking to see if a report already exists is taking longer than 5 seconds much more frequently than expected. Researching on my own test install I can see that the query performs a Seq Scan on reports because the where clause doesn't match an index. Adding an index on the hash column that isn't wrapped in a function causes the query plan to switch to using a Index Only Scan. {code}pe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN-- Seq Scan on reports (cost=0.00..39.31 rows=1 width=0) (actual time=0.012..0.105 rows=1 loops=1) Filter: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Rows Removed by Filter: 186 Planning time: 0.142 ms Execution time: 0.133 ms(5 rows)pe-puppetdb=# create unique index test on reports(hash);CREATE INDEXpe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN --- Index Only Scan using test on reports (cost=0.14..8.16 rows=1 width=0) (actual time=0.033..0.035 rows=1 loops=1) Index Cond: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Heap Fetches: 1 Planning time: 0.346 ms Execution time: 0.062 ms(5 rows){code}h1. Customer Impact / How it manifestedWe saw that PuppetDB could only manage to work 5 commands / sec despite trying to bump command processing threads to 10 on a 8 core PuppetDB node. CPU usage on the PostgreSQL node showed steady 40% use on 2016.4 and immediately increased to 60% after the upgrade to 2016.5. Not perfect science here but that's a 50% degradation in CPU use. h1. Possible SolutionsAdding the index should make the query faster, {{CREATE UNIQUE INDEX idx_reports_hash on reports(hash)}}Otherwise we can simply stop checking if the report already exists before inserting it. h1. Additional ThoughtsI think we should research the current index on the hash column and determine if queries are being designed to hit it or if queries are being constructed like the one above. We hopefully shouldn't need two indexes on the same column but we should make it easy to construct queries without special knowledge of how to make the where clause.
Jira (PDB-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Nick Walker Release Notes Summary: After upgrading to 2016.5.1 or 2016.5.2, PostgreSQL related CPU usage increases by around 50%. To workaround this issue, add use the pdb_3323_workaround module to create and manage an index to the reports table that provides a short-term fix : su - pe-postgres -s https: / bin / bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"CREATE UNIQUE INDEX CONCURRENTLY _idx_reports_hash on reports(hash)\""This index can be removed prior to upgrading to versions newer than 2016 forge . 5 puppet . 2. The command to do so is:su - pe-postgres -s com / bin/bash -c "/opt/ puppetlabs/ server/bin/psql -d pe-puppetdb -c \"DROP INDEX CONCURRENTLY _idx_reports_hash\"" pdb_3323_workaround Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3323
Checking if a report exists before inserting it is SLOW
Change By:
Nick Walker
h1. Suggested workaroundhttps://github.com/puppetlabs/puppetlabs-pdb_3323_workaroundh1. The ProblemWorking with a customer we noticed performance degradation after updating from PE 2016.4 to 2016.5.2. Using PgBadger to analyze the PostgreSQL logs we can see that checking to see if a report already exists is taking longer than 5 seconds much more frequently than expected. Researching on my own test install I can see that the query performs a Seq Scan on reports because the where clause doesn't match an index. Adding an index on the hash column that isn't wrapped in a function causes the query plan to switch to using a Index Only Scan. {code}pe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN-- Seq Scan on reports (cost=0.00..39.31 rows=1 width=0) (actual time=0.012..0.105 rows=1 loops=1) Filter: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Rows Removed by Filter: 186 Planning time: 0.142 ms Execution time: 0.133 ms(5 rows)pe-puppetdb=# create unique index test on reports(hash);CREATE INDEXpe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN --- Index Only Scan using test on reports (cost=0.14..8.16 rows=1 width=0) (actual time=0.033..0.035 rows=1 loops=1) Index Cond: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Heap Fetches: 1 Planning time: 0.346 ms Execution time: 0.062 ms(5 rows){code}h1. Customer Impact / How it manifestedWe saw that PuppetDB could only manage to work 5 commands / sec despite trying to bump command processing threads to 10 on a 8 core PuppetDB node. CPU usage on the PostgreSQL node showed steady 40% use on 2016.4 and immediately increased to 60% after the upgrade to 2016.5. Not perfect science here but that's a 50% degradation in CPU use. h1. Possible SolutionsAdding the index should make the query faster, {{CREATE UNIQUE INDEX idx_reports_hash on reports(hash)}}Otherwise we can simply stop checking if the report already exists before inserting it. h1. Additional ThoughtsI think we should research the current index on the hash column and determine if queries are being designed to hit it or if queries are being constructed like the one above. We hopefully shouldn't need two indexes on the same column but we should make it easy to construct queries without special knowledge of how to make the where clause.
Jira (PDB-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Russell Mull updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Russell Mull Affects Version/s: PDB 4.3.1 Affects Version/s: PDB 4.3.0 Affects Version/s: PDB 4.2.5 Affects Version/s: PDB 4.2.4 Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Karen Van der Veer updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Karen Van der Veer Sprint: SE 2017-03-08 , SE 2017-03-22 Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Isaac Eldridge updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW here's the full text: PuppetDB node CPU usage increase After upgrading a split install to 2016.5.1 or 2016.5.2, CPU usage on the PuppetDB node increases by 50 percent. To workaround this issue, you can add an index to the reports table. On the PuppetDB node, run the following command: ~~~ su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"CREATE UNIQUE INDEX CONCURRENTLY _idx_reports_hash on reports(hash)\"" ~~~ PDB-3323 -> Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Isaac Eldridge updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Isaac Eldridge Comment: here's the full text:## PuppetDB node CPU usage increaseAfter upgrading a split install to 2016.5.1 or 2016.5.2, CPU usage on the PuppetDB node increases by 50 percent. To workaround this issue, you can add an index to the reports table.On the PuppetDB node, run the following command:~~~su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"CREATE UNIQUE INDEX CONCURRENTLY _idx_reports_hash on reports(hash)\""~~~ Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Isaac Eldridge commented on PDB-3323 Re: Checking if a report exists before inserting it is SLOW removing the known issue at the request of Nick Walker Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Adam Bottchen updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Adam Bottchen Release Notes Summary: After upgrading to 2016.5.1 or 2016.5.2, PostgreSQL related CPU usage increases by around 50%. To workaround this issue, add an index to the reports table:su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"CREATE UNIQUE INDEX CONCURRENTLY _idx_reports_hash on reports(hash)\""This index can be removed prior to upgrading to versions newer than 2016.5.2. The command to do so is:su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"DROP INDEX CONCURRENTLY _idx_reports_hash\"" Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Adam Bottchen updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Adam Bottchen Release Notes Summary: After upgrading to 2016.5.1 or 2016.5.2, PostgreSQL related CPU usage increases by around 50%. To workaround this issue, add an index to the reports table:su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"CREATE UNIQUE INDEX CONCURRENTLY _idx_reports_hash on reports(hash)\"" This index can be removed prior to upgrading to versions newer than 2016.5.2. Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Adam Bottchen updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Adam Bottchen Release Notes Summary: After upgrading to 2016.5.1 or 2016.5.2, PostgreSQL related CPU usage on the PuppetDB node of a split install increases by around 50%. To workaround this issue, add an index to the reports table:su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"CREATE UNIQUE INDEX CONCURRENTLY _idx_reports_hash on reports(hash)\"" Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Adam Bottchen updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Adam Bottchen Release Notes Summary: After upgrading to 2016.5.1 or 2016.5.2, CPU usage on the PuppetDB node of a split install increases by around 50%. To workaround this issue, add an index to the reports table:su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"CREATE UNIQUE INDEX CONCURRENTLY idx_reports_hash _idx_reports_hash on reports(hash)\"" Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Isaac Eldridge updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Isaac Eldridge Labels: docs_reviewed flanders-ss maintenance tcse Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Isaac Eldridge commented on PDB-3323 Re: Checking if a report exists before inserting it is SLOW Added known issue for 2016.5.2. Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Adam Bottchen updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Adam Bottchen Release Notes Summary: After upgrading to 2016.5.1 or 2016.5.2, CPU usage on the PostgreSQL PuppetDB node of a split install increases by around 50%. To workaround this issue, add an index to the reports table:su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/bin/psql -d pe-puppetdb -c \"CREATE UNIQUE INDEX CONCURRENTLY idx_reports_hash on reports(hash)\"" Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Russell Mull commented on PDB-3323 Re: Checking if a report exists before inserting it is SLOW Doug Rosser We have a real fix, it's actually pretty straightforward. Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Russell Mull commented on PDB-3323 Re: Checking if a report exists before inserting it is SLOW Adam Bottchen Yes, it should. (I don't know how to do that, do you?) Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Susan McNerney Fix Version/s: PDB 4.3.x Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3323
Checking if a report exists before inserting it is SLOW
Change By:
Nick Walker
h1. The ProblemWorking with a customer we noticed performance degradation after updating from PE 2016.4 to 2016.5.2. Using PgBadger to analyze the PostgreSQL logs we can see that checking to see if a report already exists is taking longer than 5 seconds much more frequently than expected. Researching on my own test install I can see that the query performs a Seq Scan on reports because the where clause doesn't match an index. Adding an index on the hash column that isn't wrapped in a function causes the query plan to switch to using a Index Only Scan. {code}pe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN-- Seq Scan on reports (cost=0.00..39.31 rows=1 width=0) (actual time=0.012..0.105 rows=1 loops=1) Filter: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Rows Removed by Filter: 186 Planning time: 0.142 ms Execution time: 0.133 ms(5 rows)pe-puppetdb=# create unique index test on reports(hash);CREATE INDEXpe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN --- Index Only Scan using test on reports (cost=0.14..8.16 rows=1 width=0) (actual time=0.033..0.035 rows=1 loops=1) Index Cond: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Heap Fetches: 1 Planning time: 0.346 ms Execution time: 0.062 ms(5 rows){code}h1. Customer Impact / How it manifestedWe saw that PuppetDB could only manage to work 5 commands / sec despite trying to bump command processing threads to 10 on a 8 core PuppetDB node. CPU usage on the PostgreSQL node showed steady 40% use on 2016.4 and immediately increased to 60% after the upgrade to 2016.5. Not perfect science here but that's a 50% degradation in CPU use. h1. Possible SolutionsAdding the index should make the query faster, {{CREATE UNIQUE INDEX idx_reports_hash on reports(hash)}}Otherwise we can simply stop checking if the report already exists before inserting it. h1. Additional ThoughtsI think we should research the current index on the hash column and determine if queries are being designed to hit it or if queries are being constructed like the one above. We hopefully shouldn't need two indexes on the same column but we should make it easy to construct queries without special knowledge of how to make the where clause.
Jira (PDB-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Adam Bottchen updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Adam Bottchen Release Notes: Known Issue Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3323
Checking if a report exists before inserting it is SLOW
Change By:
Nick Walker
h1. The ProblemWorking with a customer we noticed performance degradation after updating from PE 2016.4 to 2016.5.2. Using PgBadger to analyze the PostgreSQL logs we can see that checking to see if a report already exists is taking longer than 5 seconds much more frequently than expected. Researching on my own test install I can see that the query performs a Seq Scan on reports because the where clause doesn't match an index. Adding an index on the hash column that isn't wrapped in a function causes the query plan to switch to using a Index Only Scan. {code}pe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN-- Seq Scan on reports (cost=0.00..39.31 rows=1 width=0) (actual time=0.012..0.105 rows=1 loops=1) Filter: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Rows Removed by Filter: 186 Planning time: 0.142 ms Execution time: 0.133 ms(5 rows)pe-puppetdb=# create unique index test on reports(hash);CREATE INDEXpe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN --- Index Only Scan using test on reports (cost=0.14..8.16 rows=1 width=0) (actual time=0.033..0.035 rows=1 loops=1) Index Cond: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Heap Fetches: 1 Planning time: 0.346 ms Execution time: 0.062 ms(5 rows){code}h1. How it manifestedWe saw that PuppetDB could only manage to work 5 commands / sec despite trying to bump command processing threads to 10 on a 8 core PuppetDB node. CPU usage on the PostgreSQL node showed steady 40% use on 2016.4 and immediately increased to 60% after the upgrade to 2016.5. Not perfect science here but that's a 50% degradation in CPU use. h1. Possible SolutionsAdding the index should make the query faster, {{CREATE UNIQUE INDEX idx_reports_hash on reports(hash)}}Otherwise we can simply stop checking if the report already exists before inserting it. h1. Additional ThoughtsI think we should research the current index on the hash column and determine if queries are being designed to hit it or if queries are being constructed like the one above. We hopefully shouldn't need two indexes on the same column but we should make it easy to construct queries without special knowledge of how to make the where clause.
Jira (PDB-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Adam Bottchen commented on PDB-3323 Re: Checking if a report exists before inserting it is SLOW Should this be added as a known issue for 2016.5.2? Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Russell Mull updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Russell Mull Story Points: 2 Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Russell Mull updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Russell Mull Sprint: SE 2017-03-08 Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Russell Mull commented on PDB-3323 Re: Checking if a report exists before inserting it is SLOW I can easily repro this on puppetdb 4.3.1; looks like it's limited to the insert check, fortunately. We should probably change the query to use the index we already have, to avoid a costly reindex migration. Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Susan McNerney updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Susan McNerney Labels: flanders-ss maintenance tcse Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Russell Mull assigned an issue to Russell Mull PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Russell Mull Assignee: Russell Mull Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Nick Walker Priority: Major Critical Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3323
Checking if a report exists before inserting it is SLOW
Change By:
Nick Walker
h1. The ProblemWorking with a customer we noticed performance degradation after updating from PE 2016.4 to 2016.5.2. Using PgBadger to analyze the PostgreSQL logs we can see that checking to see if a report already exists is taking longer than 5 seconds much more frequently than expected. Researching on my own test install I can see that the query performs a Seq Scan on reports because the where clause doesn't match an index. Adding an index on the hash column that isn't wrapped in a function causes the query plan to switch to using a Index Only Scan. {code}pe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN-- Seq Scan on reports (cost=0.00..39.31 rows=1 width=0) (actual time=0.012..0.105 rows=1 loops=1) Filter: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Rows Removed by Filter: 186 Planning time: 0.142 ms Execution time: 0.133 ms(5 rows)pe-puppetdb=# create unique index test on reports(hash);CREATE INDEXpe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN --- Index Only Scan using test on reports (cost=0.14..8.16 rows=1 width=0) (actual time=0.033..0.035 rows=1 loops=1) Index Cond: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Heap Fetches: 1 Planning time: 0.346 ms Execution time: 0.062 ms(5 rows){code}h1. Possible SolutionsAdding the index should make the query faster, {{CREATE UNIQUE INDEX usaa_idx_reports_hash idx_reports_hash on reports(hash)}}Otherwise we can simply stop checking if the report already exists before inserting it. h1. Additional ThoughtsI think we should research the current index on the hash column and determine if queries are being designed to hit it or if queries are being constructed like the one above. We hopefully shouldn't need two indexes on the same column but we should make it easy to construct queries without special knowledge of how to make the where clause.
Add Comment
Jira (PDB-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Nick Walker Priority: Normal Major Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title
Nick Walker updated an issue
PuppetDB / PDB-3323
Checking if a report exists before inserting it is SLOW
Change By:
Nick Walker
h1. The ProblemWorking with a customer we noticed performance degradation after updating from PE 2016.4 to 2016.5.2. Using PgBadger to analyze the PostgreSQL logs we can see that checking to see if a report already exists is taking longer than 5 seconds much more frequently than expected. Researching on my own test install I can see that the query performs a Seq Scan on reports because the where clause doesn't match an index. Adding an index on the hash column that isn't wrapped in a function causes the query plan to switch to using a Index Only Scan. {code}pe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN-- Seq Scan on reports (cost=0.00..39.31 rows=1 width=0) (actual time=0.012..0.105 rows=1 loops=1) Filter: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Rows Removed by Filter: 186 Planning time: 0.142 ms Execution time: 0.133 ms(5 rows)pe-puppetdb=# create unique index test on reports(hash);CREATE INDEXpe-puppetdb=# explain analyze select 1 from reports where hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6';QUERY PLAN --- Index Only Scan using test on reports (cost=0.14..8.16 rows=1 width=0) (actual time=0.033..0.035 rows=1 loops=1) Index Cond: (hash = '\x7ad27615d5c7de439855b245e5eb6d7245e812f6'::bytea) Heap Fetches: 1 Planning time: 0.346 ms Execution time: 0.062 ms(5 rows){code}h1. Possible SolutionsAdding the index should make the query faster, {{CREATE UNIQUE INDEX usaa_idx_reports_hash on reports(hash)}}Otherwise we can simply stop checking if the report already exists before inserting it. h1. Additional ThoughtsI think we should research the current index on the hash column and determine if queries are being designed to hit it or if queries are being constructed like the one above. We hopefully shouldn't need two indexes on the same column but we should make it easy to construct queries without special knowledge of how to make the where clause.
Add Comment
Jira (PDB-3323) Checking if a report exists before inserting it is SLOW
Title: Message Title Nick Walker updated an issue PuppetDB / PDB-3323 Checking if a report exists before inserting it is SLOW Change By: Nick Walker Summary: Stop checking Checking if a report exists before inserting it is SLOW Add Comment This message was sent by Atlassian JIRA (v6.4.14#64029-sha1:ae256fe) -- 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.
