Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title David McTavish assigned an issue to Rob Browning PuppetDB / PDB-5450 Slow fact queries after PuppetDB 7.9.0 upgrade Change By: David McTavish Assignee: Rob Browning Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.434144.1643897486000.8816.1643982240125%40Atlassian.JIRA.
Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title David McTavish updated an issue PuppetDB / PDB-5450 Slow fact queries after PuppetDB 7.9.0 upgrade Change By: David McTavish Sprint: HA 2022-03-02 HAHA/Grooming Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.434144.1643897486000.8814.1643982240106%40Atlassian.JIRA.
Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title David McTavish updated an issue PuppetDB / PDB-5450 Slow fact queries after PuppetDB 7.9.0 upgrade Change By: David McTavish Sprint: HAHA/Grooming HA 2022-02-16 Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.434144.1643897486000.8815.1643982240114%40Atlassian.JIRA.
Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title David McTavish updated an issue PuppetDB / PDB-5450 Slow fact queries after PuppetDB 7.9.0 upgrade Change By: David McTavish Sprint: HA 2022-03-02 Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.434144.1643897486000.8813.1643982240080%40Atlassian.JIRA.
Jira (PUP-11451) Make collecting exported resources optional
Title: Message Title Nacho Barrientos created an issue Puppet / PUP-11451 Make collecting exported resources optional Issue Type: New Feature Assignee: Unassigned Created: 2022/02/04 6:08 AM Priority: Normal Reporter: Nacho Barrientos According to the documentation, storing catalogs and facts in PuppetDB and using exported resources are two actions that are governed by the same configuration option (storeconfigs): Whether to store each client's configuration, including catalogs, facts, and related data. This also enables the import and export of resources in the Puppet language - a mechanism for exchange resources between nodes. https://puppet.com/docs/puppet/7/configuration.html#storeconfigs It'd be useful for us in our deployment if those two things were not tied to each other. In other words, if it was possible to keep "storing configs" but exported resource collectors were ignored. This is rather cheap to achieve by introducing a new configuration option and patching the exported resources collector, something like: @@ -18,7 +18,7 @@ class Puppet::Pops::Evaluator::Collectors::ExportedCollector < Puppet::Pops::Eva # Ensures that storeconfigs is present before calling AbstractCollector's # evaluate method
Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title
Elof Ofel commented on PDB-5450
Re: Slow fact queries after PuppetDB 7.9.0 upgrade
Ok, so I exec:ed into the container and added 'log_min_duration_statement = 2' to postgresql.conf and restarted. Now the postgres-container logs slow queries. Here is a spot sample. This query is generated by a PuppetExplorer dashboard cell showing how many Debian 11 nodes I have, i.e. the query: operatingsystemrelease~"^11." and operatingsystem="Debian" This generates this log while the loading of the dashboard page timeout: postgres_1 | 2022-02-04 16:40:58.161 CET [75] LOG: duration: 23246.062 ms execute /C_2: SELECT COUNT AS result_count FROM (WITH inactive_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL AND deactivated > '2022-01-21T15:40:33.691Z') OR (expired IS NOT NULL and expired > '2022-01-21T15:40:33.691Z')), not_active_nodes AS (SELECT certname FROM certnames WHERE (deactivated IS NOT NULL OR expired IS NOT NULL)) SELECT reports.corrective_change AS "latest_report_corrective_change", certnames.deactivated AS "deactivated", certnames.expired AS "expired", reports_environment.environment AS "report_environment", certnames.certname AS "certname", fs.timestamp AS "facts_timestamp", facts_environment.environment AS "facts_environment", reports.cached_catalog_status AS "cached_catalog_status", report_statuses.status AS "latest_report_status", encode(reports.hash::bytea, 'hex') AS "latest_report_hash", catalogs.timestamp AS "catalog_timestamp", reports.noop_pending AS "latest_report_noop_pending", reports.end_time AS "report_timestamp", reports.noop AS "latest_report_noop", catalog_environment.environment AS "catalog_environment", reports.job_id AS "latest_report_job_id" FROM certnames LEFT JOIN catalogs ON catalogs.certname = certnames.certname LEFT JOIN factsets fs ON certnames.certname = fs.certname LEFT JOIN reports ON (certnames.certname = reports.certname AND certnames.latest_report_id = reports.id) LEFT JOIN environments catalog_environment ON catalog_environment.id = catalogs.environment_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id LEFT JOIN environments facts_environment ON facts_environment.id = fs.environment_id LEFT JOIN environments reports_environment ON reports_environment.id = reports.environment_id WHERE certnames.certname) in (SELECT certname FROM ( (SELECT fc.certname AS "certname" FROM (select certname, flattened.* from factsets fs left join lateral ( with recursive flattened_one (parent_path, parent_types, key, value, type) as ( select array[]::text[], '', (jsonb_each(fs.stable||fs.volatile)).*, 's' union all select parent_path || flattened_one.key, parent_types || flattened_one.type, sub_paths.key, sub_paths.value, sub_paths.type from flattened_one inner join lateral ( select (jsonb_each(value)).*, 's' as type where jsonb_typeof(value) = 'object' union all select generate_series::text as key, value->generate_series as value, 'i' as type from generate_series(0, jsonb_array_length(value) - 1) where jsonb_typeof(value) = 'array' ) as sub_paths on true ) select environment_id, parent_path || key as path, parent_types || type as types, coalesce(parent_path[1], key) as name, value from flattened_one where not jsonb_typeof(value) = any(' {"array", "object"}') ) as flattened on true) fc LEFT JOIN environments env ON fc.environment_id = env.id WHERE ((fc.path = (array['operatingsystemrelease']) and fc.types = 's') AND (value#>>'{}')::text ~ $1)) ) sub)) AND ((certnames.certname) in (SELECT certname FROM ( (SELECT fc.certname AS "certname" FROM (select certname, flattened.* from factsets fs left join lateral ( with recursive flattened_one (parent_pa
Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title Elof Ofel commented on PDB-5450 Re: Slow fact queries after PuppetDB 7.9.0 upgrade I now add "EXPLAIN (ANALYZE, BUFFERS)" to the query. time psql -f /tmp/q2.txt puppetdb puppetdb > /tmp/idle.txt real 0m1.036s user 0m0.035s sys 0m0.004s Works fine. I now generate some background load by reloading the dashboard and immediately re-run the command: time psql -f /tmp/q2.txt puppetdb puppetdb > /tmp/load.txt real 0m18.966s user 0m0.049s sys 0m0.013s Still works fine, only this query takes 18 seconds. Meanwhile the dashboard webpage keeps loading for ca 20 seconds more, and then display values in all cells. If I diff idle.txt and load.txt, both show the same 128 rows, just different timings. So my guess is that it is not the query(s) itself that need debugging but why a PuppetDB 7.9.0 under load start having the 40 second problems when none of the previous versions had this problem. # cat /tmp/idle.txt QUERY PLAN - - Aggregate (cost=76757873.32..76757873.33 rows=1 width=8) (actual time=924.199..924.215 rows=1 loops=1) Buffers: shared hit=3037 -> Nested Loop Semi Join (cost=1493156.08..76757872.92 rows=32 width=299) (actual time=881.713..924.189 rows=63 loops=1) Join Filter: (certnames.certname
Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title Elof Ofel commented on PDB-5450 Re: Slow fact queries after PuppetDB 7.9.0 upgrade # cat /tmp/load.txt QUERY PLAN - - Aggregate (cost=76757874.40..76757874.41 rows=1 width=8) (actual time=17384.424..17384.445 rows=1 loops=1) Buffers: shared hit=3038 -> Nested Loop Semi Join (cost=1493157.16..76757874.00 rows=32 width=299) (actual time=16595.373..17384.369 rows=63 loops=1) Join Filter: (certnames.certname = fs.certname) Buffers: shared hit=3038 -> Nested Loop Semi Join (cost=747602.50..38819975.28 rows=32 width=50) (actual time=16594.563..16840.309 rows=63 loops=1)
Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title David McTavish updated an issue PuppetDB / PDB-5450 Slow fact queries after PuppetDB 7.9.0 upgrade Change By: David McTavish Method Found: Needs Assessment Issue Type: Task Bug Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.434144.1643897486000.9212.1644000480105%40Atlassian.JIRA.
Jira (PUP-10891) 17. Update Public-facing Puppet docs to include RedHat 8 Power9
Title: Message Title Josh Cooper updated an issue Puppet / PUP-10891 17. Update Public-facing Puppet docs to include RedHat 8 Power9 Change By: Josh Cooper Summary: 17. Update Public-facing Puppet docs to include RedHat 8 Power9 Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.386554.1612775622000.9531.1644023160396%40Atlassian.JIRA.
Jira (PUP-11442) 17. Update Public-facing Puppet docs to include macOS 12 (x86_64)
Title: Message Title Josh Cooper updated an issue Puppet / PUP-11442 17. Update Public-facing Puppet docs to include macOS 12 (x86_64) Change By: Josh Cooper Summary: 17. Update Public-facing Puppet docs to include macOS 12 (x86_64) Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.433576.1643678327000.9529.1644023160306%40Atlassian.JIRA.
Jira (PUP-11444) 17. Update Public-facing Puppet docs to include Windows 11 Enterprise (x86_64)
Title: Message Title Josh Cooper updated an issue Puppet / PUP-11444 17. Update Public-facing Puppet docs to include Windows 11 Enterprise (x86_64) Change By: Josh Cooper Summary: 17. Update Public-facing Puppet docs to include Windows 11 Enterprise (x86_64) Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.433650.1643678919000.9533.1644023160489%40Atlassian.JIRA.
Jira (PUP-11443) 17. Update Public-facing Puppet docs to include Ubuntu 22.04 (x86_64)
Title: Message Title Josh Cooper updated an issue Puppet / PUP-11443 17. Update Public-facing Puppet docs to include Ubuntu 22.04 (x86_64) Change By: Josh Cooper Summary: 17. Update Public-facing Puppet docs to include Ubuntu 22.04 (x86_64) Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.433613.1643678593000.9528.1644023160259%40Atlassian.JIRA.
Jira (PUP-11233) 17. Update Public-facing Puppet docs to include CentOS Stream 9(x86_64)
Title: Message Title Josh Cooper updated an issue Puppet / PUP-11233 17. Update Public-facing Puppet docs to include CentOS Stream 9(x86_64) Change By: Josh Cooper Summary: 17. Update Public-facing Puppet docs to include CentOS Stream 9(x86_64) Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.413240.1629802481000.9532.1644023160441%40Atlassian.JIRA.
Jira (PUP-11197) 17. Update Public-facing Puppet docs to include macOS 11 (arm64)
Title: Message Title Josh Cooper updated an issue Puppet / PUP-11197 17. Update Public-facing Puppet docs to include macOS 11 (arm64) Change By: Josh Cooper Summary: 17. Update Public-facing Puppet docs to include macOS 11 (arm64) Add Comment This message was sent by Atlassian Jira (v8.20.2#820002-sha1:829506d) -- 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.409849.1628064871000.9530.1644023160354%40Atlassian.JIRA.
Jira (PDB-5450) Slow fact queries after PuppetDB 7.9.0 upgrade
Title: Message Title
Austin Blatt commented on PDB-5450
Re: Slow fact queries after PuppetDB 7.9.0 upgrade
I think the facts queries are creating the significant load due to the JIT optimization that looks to be enabled on your Postgres, and that load is slowing the Postgres down and creating the poor query performance. I spun up a totally empty database and ran a similar query. With JIT disabled, it takes less than a millisecond.
Planning Time: 1.312 ms
Execution Time: 0.278 ms
and then with JIT enabled it takes over 700ms
Planning Time: 1.224 ms
JIT:
Functions: 56
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 12.209 ms, Inlining 81.108 ms, Optimization 268.355 ms, Emission 160.117 ms, Total 521.789 ms
Execution Time: 735.923 ms
It appears that the Postgres query planner is generating a huge cost estimate {{cost=745615.49..90016708.23 }} and then Postgres attempts as much JIT optimization as possible against the query because it expects it to be slow (even though it is not). This is in line with the explain analyzes that you provides, where most of the time for both queries seems to be spent in attempting the various JIT optimizations rath
