pe-puppetdb - 6.7.3-1
This case is raised after seeing continuous performance issues for the customer.
All nodes appeared "not reporting" section in the console
Console reporting compilers are not responding
Grafana unable to get the metrics from compilers
- In all the above cases, ` netstat -pan | grep -i 5432` shows `TIME_WAIT` status ( Attached ports.txt from support script)
- Slowest query from support script:
2020-09-06 23:28:14.185 GMT [db:pe-puppetdb,sess:5f55631a.52a9,pid:21161,vtid:76/42777,tid:108303837] STATEMENT: with recursive live_paths(path, value) as (select key as path, value from (select (jsonb_each(stable||volatile)).* from factsets) as base_case union select path||'#~'||sub_level.key as path, sub_level.value from live_paths, lateral (select * from (select (jsonb_each(value)).* where jsonb_typeof(value) = 'object') as sub_fields union (select generate_series(0, jsonb_array_length(value - 1))::text as key, jsonb_array_elements(value) as value where jsonb_typeof(value) = 'array')) as sub_level) select path into temp tmp_live_paths from live_paths
- Autovacuum unable to clean up the table "pe-puppetdb.public.catalog_inputs". It's active for a few days.
2020-09-07 00:09:03.448 GMT [db:,sess:5f5570ae.3f9d,pid:16285,vtid:112/62735,tid:108349218] LOG: automatic vacuum of table "pe-puppetdb.public.catalog_inputs": index scans: 1
pages: 4038 removed, 2787159 remain, 0 skipped due to pins, 2763439 skipped frozen
tuples: 190119 removed, 2424275 remain, 30905 are dead but not yet removable, oldest xmin: 108303837
buffer usage: 1076888 hits, 891738 misses, 31950 dirtied
avg read rate: 2.882 MB/s, avg write rate: 0.103 MB/s
system usage: CPU: user: 9.96 s, system: 12.37 s, elapsed: 2417.08 s
The size of the table "pe-puppetdb.public.catalog_inputs":
pe-puppetdb | public.catalog_inputs | 23 GB
pe-puppetdb | public.catalog_inputs | 157 MB
- All nodes appeared "not reporting" section in the console
su - pe-postgres -s /bin/bash -c "/opt/puppetlabs/server/apps/postgresql/11/bin/pg_repack -d pe-puppetdb -t certnames -t factsets -t fact_paths"
- Console reporting compilers are not responding
- Grafana unable to get the metrics from compilers
=> Restarting the PSQL & puppetdb resolve it for a short period of time.
- relates to
PDB-4881 Add primary key to catalog_inputs