| This case is raised after seeing continuous performance issues for the customer. Issues description: All nodes appeared "not reporting" section in the console or Console reporting compilers are not responding or Grafana unable to get the metrics from compilers Log analysis:
- 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": Master: pe-puppetdb | public.catalog_inputs | 23 GB Replica: pe-puppetdb | public.catalog_inputs | 157 MB Workaround:
- 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. |
