PSQL performance issue - Autovacuum unable to clean up "pe-puppetdb.public.catalog_inputs"

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.