Jira (PDB-3882) garbage collection performance problem

[Marius Rieder (JIRA)]

Title: Message Title

Marius Rieder created an issue   PuppetDB / PDB-3882 garbage collection performance problem Issue Type: Bug Affects Versions: PDB 5.2.0 Assignee: Unassigned Components: PuppetDB Created: 2018/03/25 12:45 AM Environment: Operating System: Ubuntu Xenial PuppetDB: 5.2.0-1xenial PuppetServer: 5.2.0-1xenial PuppetAgent: 5.4.0-1xenial   Priority: Normal Reporter: Marius Rieder   The database garbage collection of PuppetDB which is run each 60 minutes by default can lead to performance issues. We see spikes in the average compile time of catalogues from 2s to 10s each time the database gc is running. In the log of PuppetDB is obvious that "database garbage collection" takes quiet some time.       2018-03-25 06:34:17,608 INFO [p.p.c.services] Starting purge deactivated and expired nodes (threshold: 7 days) 2018-03-25 06:34:17,610 INFO [p.p.c.services] Finished purge deactivated and expired nodes (threshold: 7 days) 2018-03-25 06:34:17,610 INFO [p.p.c.services] Starting sweep of stale reports (threshold: 3 days) 2018-03-25 06:34:19,161 INFO [p.p.c.services] Finished sweep of stale reports (threshold: 3 days) 2018-03-25 06:34:19,162 INFO [p.p.c.services] Starting gc packages 2018-03-25 06:34:19,163 INFO [p.p.c.services] Finished gc packages 2018-03-25 06:34:19,163 INFO [p.p.c.services] Starting database garbage collection 2018-03-25 06:34:44,475 INFO [p.p.c.services] Finished database garbage collection 2018-03-25 07:34:44,475 INFO [p.p.c.services] Starting purge deactivated and expired nodes (threshold: 7 days) 2018-03-25 07:34:44,477 INFO [p.p.c.services] Finished purge deactivated and expired nodes (threshold: 7 days) 2018-03-25 07:34:44,477 INFO [p.p.c.services] Starting sweep of stale reports (threshold: 3 days) 2018-03-25 07:34:45,685 INFO [p.p.c.services] Finished sweep of stale reports (threshold: 3 days) 2018-03-25 07:34:45,685 INFO [p.p.c.services] Starting gc packages 2018-03-25 07:34:45,686 INFO [p.p.c.services] Finished gc packages 2018-03-25 07:34:45,687 INFO [p.p.c.services] Starting database garbage collection 2018-03-25 07:35:33,661 INFO [p.p.c.services] Finished database garbage collection 2018-03-25 08:35:33,661 INFO [p.p.c.services] Starting purge deactivated and expired nodes (threshold: 7 days) 2018-03-25 08:35:33,663 INFO [p.p.c.services] Finished purge deactivated and expired nodes (threshold: 7 days) 2018-03-25 08:35:33,663 INFO [p.p.c.services] Starting sweep of stale reports (threshold: 3 days) 2018-03-25 08:35:35,492 INFO [p.p.c.services] Finished sweep of stale reports (threshold: 3 days) 2018-03-25 08:35:35,492 INFO [p.p.c.services] Starting gc packages 2018-03-25 08:35:35,493 INFO [p.p.c.services] Finished gc packages 2018-03-25 08:35:35,493 INFO [p.p.c.services] Starting database garbage collection 2018-03-25 08:35:48,070 INFO [p.p.c.services] Finished database garbage collection At the same time the creation of temp-tables is spiking in PostgreSQL. The Querie responsible is the following one. 2018-03-25 06:34:39 CEST LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp2376.1832", size 38169987 2018-03-25 06:34:39 CEST STATEMENT: with recursive live_paths(key, path, value) as (select key, key as path, value from (select (jsonb_each(stable||volatile)).* from factsets) as base_case union all select sub_path as key, sub_paths.path||'#~'||sub_path as path, sub_value as value from (select * from (select path, case jsonb_typeof(value) when 'object' then (jsonb_each(value)).key when 'array' then generate_series(0, jsonb_array_length(value - 1))::text end as sub_path, case jsonb_typeof(value) when 'object' then (jsonb_each(value)).value when 'array' then jsonb_array_elements(value) end as sub_value from live_paths) as candidates where candidates.sub_path is not null) as sub_paths) delete from fact_paths fp where not exists (select 1 from live_paths where live_paths.path = fp.path) According to https://www.postgresql.org/docs/9.6/static/queries-with.html  UNION ALL does not discard duplicate rows. So live_paths ends up containing the full path of each fact in the facts set and not only each unique path in the facts set. For our environment this is a difference of 900000 fact paths to 300000 fact paths. puppetdb=# with recursive live_paths(key, path, value) as ( select key, key as path, value from ( select (jsonb_each(stable||volatile)).* from factsets) as base_case union all select sub_path as key, sub_paths.path||'#~'||sub_path as path, sub_value as value from ( select * from ( select path, case jsonb_typeof(value) when 'object' then (jsonb_each(value)).key when 'array' then generate_series(0, jsonb_array_length(value - 1))::text end as sub_path, case jsonb_typeof(value) when 'object' then (jsonb_each(value)).value when 'array' then jsonb_array_elements(value) end as sub_value from live_paths) as candidates where candidates.sub_path is not null) as sub_paths) select COUNT(*) from live_paths; count -------- 980463 (1 row)puppetdb=# puppetdb=# with recursive live_paths(key, path, value) as ( select key, key as path, value from ( select (jsonb_each(stable||volatile)).* from factsets) as base_case union select sub_path as key, sub_paths.path||'#~'||sub_path as path, sub_value as value from ( select * from ( select path, case jsonb_typeof(value) when 'object' then (jsonb_each(value)).key when 'array' then generate_series(0, jsonb_array_length(value - 1))::text end as sub_path, case jsonb_typeof(value) when 'object' then (jsonb_each(value)).value when 'array' then jsonb_array_elements(value) end as sub_value from live_paths) as candidates where candidates.sub_path is not null) as sub_paths) select COUNT(*) from live_paths; count -------- 307840 (1 row)   Add Comment   This message was sent by Atlassian JIRA (v7.7.1#77002-sha1:e75ca93) -- 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.