Missing indexes for srvid in tables? High CPU usage

[ikalafat]

Hi,

I'm not sure if this report should be in the powa-collector repo. Please accept my apologies if I missed the repo.

I am seeing a lot of CPU pressure on local machine (where powa-collector) is running, for example on following query

SELECT public.powa_take_snapshot(5) with comments/application name such as (powa_all_tables_snapshot, all_indexes_snapshot)

I was also seeing timeouts on query like this one

SELECT 1 FROM public.powa_catalog_class_src_tmp WHERE srvid = 2 LIMIT 1

Based on that, I think that some tables should have indexes, at least on srvid column.

For example, these are ones that I have executed on my machine:

CREATE index concurrently if not exists powa_catalog_class_src_tmp_srvid_idx ON public.powa_catalog_class_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_proc_src_tmp_srvid_idx ON public.powa_catalog_proc_src_tmp (srvid);
CREATE index concurrently if not exists powa_all_indexes_src_tmp_srvid_idx ON public.powa_all_indexes_src_tmp (srvid);
CREATE index concurrently if not exists powa_all_tables_src_tmp_srvid_idx ON public.powa_all_tables_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_attribute_srvid_idx ON public.powa_catalog_attribute (srvid);
CREATE index concurrently if not exists powa_statements_srvid_idx ON public.powa_statements (srvid);
CREATE index concurrently if not exists powa_statements_src_tmp_srvid_idx ON public.powa_statements_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_attribute_src_tmp_srvid_idx ON public.powa_catalog_attribute_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_proc_srvid_idx ON public.powa_catalog_proc (srvid);
CREATE index concurrently if not exists powa_catalog_collation_src_tmp_srvid_idx ON public.powa_catalog_collation_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_type_src_tmp_srvid_idx ON public.powa_catalog_type_src_tmp (srvid);

I am seeing consistent increase in table size for these tables (which absolutely makes sense) however it looks like that due to indexes not being here, PostgreSQL uses sequential scan and a lot of CPU

• remote setup
• 8 remote machines (all PostgreSQL 16)
• 3 day retention, 2 minute frequency

For clarity, remote machines (4 clusters with master and replica servers) are beefy machines (8-16vCPU with 64 to 128GB RAM) with quite a lot of load.

If I have only one cluster (2 machines) registered to POWA, then I'm not seeing CPU issues and timeouts.

Local machine where the powa-collector is running and PostgreSQL has 16vCPU and 64GB of RAM

Any hints how can I reduce the CPU load?

Thank you,

[ikalafat]

After some time, i received following errors in PoWA Web

powa@<redacted>:5432/powa:
Error while taking snapshot for server 1: canceling statement due to statement timeout CONTEXT: SQL statement "SELECT 1 FROM public.powa_catalog_collation_src_tmp WHERE srvid = 1 LIMIT 1" PL/pgSQL function public.powa_catalog_generic_snapshot(integer,text) line 22 at EXECUTE SQL statement "SELECT public.powa_catalog_generic_snapshot(_srvid, v_catname)" PL/pgSQL function public.powa_take_snapshot(integer) line 224 at PERFORM
powa@<redacted>:5432/powa:
Error while taking snapshot for server 3: canceling statement due to statement timeout CONTEXT: SQL statement "WITH rel AS ( SELECT * FROM public.powa_all_tables_src_tmp ), by_relation AS ( INSERT INTO public.powa_all_tables_history_current (srvid, dbid, relid, record) SELECT _srvid, dbid, relid, ROW(ts, tbl_size, seq_scan, last_seq_scan, seq_tup_read, idx_scan, last_idx_scan, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_liv_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit )::public.powa_all_tables_history_record AS record FROM rel ), by_database AS ( INSERT INTO public.powa_all_tables_history_current_db (srvid, dbid, record) SELECT _srvid AS srvid, dbid, ROW(ts, sum(tbl_size), sum(seq_scan), sum(seq_tup_read), sum(idx_scan), sum(n_tup_ins), sum(n_tup_upd), sum(n_tup_del), sum(n_tup_hot_upd), sum(n_tup_newpage_upd), sum(n_liv_tup), sum(n_dead_tup), sum(n_mod_since_analyze), sum(n_ins_since_vacuum), sum(vacuum_count), sum(autovacuum_count), sum(analyze_count), sum(autoanalyze_count), sum(heap_blks_read), sum(heap_blks_hit), sum(idx_blks_read), sum(idx_blks_hit), sum(toast_blks_read), sum(toast_blks_hit), sum(tidx_blks_read), sum(tidx_blks_hit) )::public.powa_all_tables_history_db_record FROM rel GROUP BY srvid, dbid, ts ) SELECT COUNT(*) FROM rel" PL/pgSQL function public.powa_all_tables_snapshot(integer) line 15 at SQL statement SQL statement "SELECT public.powa_all_tables_snapshot(3)" PL/pgSQL function public.powa_take_snapshot(integer) line 79 at EXECUTE
powa@<redacted>:5432/powa:
Error while taking snapshot for server 4: canceling statement due to statement timeout CONTEXT: SQL statement "WITH rel AS ( SELECT * FROM public.powa_all_tables_src_tmp ), by_relation AS ( INSERT INTO public.powa_all_tables_history_current (srvid, dbid, relid, record) SELECT _srvid, dbid, relid, ROW(ts, tbl_size, seq_scan, last_seq_scan, seq_tup_read, idx_scan, last_idx_scan, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_tup_newpage_upd, n_liv_tup, n_dead_tup, n_mod_since_analyze, n_ins_since_vacuum, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count, heap_blks_read, heap_blks_hit, idx_blks_read, idx_blks_hit, toast_blks_read, toast_blks_hit, tidx_blks_read, tidx_blks_hit )::public.powa_all_tables_history_record AS record FROM rel ), by_database AS ( INSERT INTO public.powa_all_tables_history_current_db (srvid, dbid, record) SELECT _srvid AS srvid, dbid, ROW(ts, sum(tbl_size), sum(seq_scan), sum(seq_tup_read), sum(idx_scan), sum(n_tup_ins), sum(n_tup_upd), sum(n_tup_del), sum(n_tup_hot_upd), sum(n_tup_newpage_upd), sum(n_liv_tup), sum(n_dead_tup), sum(n_mod_since_analyze), sum(n_ins_since_vacuum), sum(vacuum_count), sum(autovacuum_count), sum(analyze_count), sum(autoanalyze_count), sum(heap_blks_read), sum(heap_blks_hit), sum(idx_blks_read), sum(idx_blks_hit), sum(toast_blks_read), sum(toast_blks_hit), sum(tidx_blks_read), sum(tidx_blks_hit) )::public.powa_all_tables_history_db_record FROM rel GROUP BY srvid, dbid, ts ) SELECT COUNT(*) FROM rel" PL/pgSQL function public.powa_all_tables_snapshot(integer) line 15 at SQL statement SQL statement "SELECT public.powa_all_tables_snapshot(4)" PL/pgSQL function public.powa_take_snapshot(integer) line 79 at EXECUTE
powa@<redacted>:5432/powa:
Error while taking snapshot for server 5: canceling statement due to statement timeout CONTEXT: SQL statement "SELECT 1 FROM public.powa_catalog_collation_src_tmp WHERE srvid = 5 LIMIT 1" PL/pgSQL function public.powa_catalog_generic_snapshot(integer,text) line 22 at EXECUTE SQL statement "SELECT public.powa_catalog_generic_snapshot(_srvid, v_catname)" PL/pgSQL function public.powa_take_snapshot(integer) line 224 at PERFORM
powa@<redacted>:5432/powa:
Error while taking snapshot for server 6: canceling statement due to statement timeout CONTEXT: SQL statement "SELECT 1 FROM public.powa_catalog_collation_src_tmp WHERE srvid = 6 LIMIT 1" PL/pgSQL function public.powa_catalog_generic_snapshot(integer,text) line 22 at EXECUTE SQL statement "SELECT public.powa_catalog_generic_snapshot(_srvid, v_catname)" PL/pgSQL function public.powa_take_snapshot(integer) line 224 at PERFORM
powa@<redacted>:5432/powa:
Error while taking snapshot for server 7: canceling statement due to statement timeout CONTEXT: SQL statement "WITH rel AS ( SELECT * FROM public.powa_all_indexes_src_tmp ), by_relation AS ( INSERT INTO public.powa_all_indexes_history_current (srvid, dbid, relid, indexrelid, record) SELECT _srvid, dbid, relid, indexrelid, ROW(ts, idx_size, idx_scan, last_idx_scan, idx_tup_read, idx_tup_fetch, idx_blks_read, idx_blks_hit )::public.powa_all_indexes_history_record AS record FROM rel ), by_database AS ( INSERT INTO public.powa_all_indexes_history_current_db (srvid, dbid, record) SELECT _srvid AS srvid, dbid, ROW(ts, sum(idx_size), sum(idx_scan), sum(idx_tup_read), sum(idx_tup_fetch), sum(idx_blks_read), sum(idx_blks_hit) )::public.powa_all_indexes_history_db_record FROM rel GROUP BY srvid, dbid, ts ) SELECT COUNT(*) FROM rel" PL/pgSQL function public.powa_all_indexes_snapshot(integer) line 15 at SQL statement SQL statement "SELECT public.powa_all_indexes_snapshot(7)" PL/pgSQL function public.powa_take_snapshot(integer) line 79 at EXECUTE
powa@<redacted>:5432/powa:
Error while taking snapshot for server 8: canceling statement due to statement timeout CONTEXT: SQL statement "SELECT 1 FROM public.powa_catalog_type_src_tmp WHERE srvid = 8 LIMIT 1" PL/pgSQL function public.powa_catalog_generic_snapshot(integer,text) line 22 at EXECUTE SQL statement "SELECT public.powa_catalog_generic_snapshot(_srvid, v_catname)" PL/pgSQL function public.powa_take_snapshot(integer) line 224 at PERFORM

[ikalafat]

With adding of the indexes from the initial post, I was able to have a functional gathering of data from 8 remote servers. However, the high CPU issue still remains.

Everytime when the data gathering is initiated and when I see parallel queries running SELECT public.powa_take_snapshot I have high CPU.

I will try to increase frequency from 2 minutes back to default 5 minutes.

[ikalafat]

Additional update, it looks like reducing the interval to 5 minutes has given enough time for syncing, and I'm no longer seeing high CPU (80-90%), only some spikes up to 10-20%

[rjuju]

hi,

thanks for the report. The original reason why we avoid creating indexes on the *_src_tmp tables is because they're used as temporary tables mostly, so adding indexes makes the snapshot operation more expensive.

in general you shouldn't have too many rows in those tables since they're only used temporarily and the data should be removed almost immediately. if reducing the snapshot frequency solves the problem it's likely that the actual problem is auto vacuum not being aggressive enough. Maybe you could try that to see if it can lower the cpu usage enough even with more frequent snapshots?

[ikalafat]

Hi,

I did see that autovacuum processes were running in parallel with SELECT public.powa_take_snapshot(x) queries and I have temporarily disabled the autovacuum on following tables

ALTER TABLE public.powa_statements SET (autovacuum_enabled = off);
ALTER TABLE public.powa_statements_src_tmp SET (autovacuum_enabled = off);
ALTER TABLE public.powa_all_indexes_src_tmp SET (autovacuum_enabled = off);
ALTER TABLE public.powa_all_tables_src_tmp SET (autovacuum_enabled = off);
ALTER TABLE public.powa_catalog_attribute_src_tmp SET (autovacuum_enabled = off);
ALTER TABLE public.powa_catalog_class_src_tmp SET (autovacuum_enabled = off);
ALTER TABLE public.powa_catalog_collation_src_tmp SET (autovacuum_enabled = off);
ALTER TABLE public.powa_catalog_proc_src_tmp SET (autovacuum_enabled = off);
ALTER TABLE public.powa_catalog_type_src_tmp SET (autovacuum_enabled = off);
ALTER TABLE public.powa_all_indexes_history_current SET (autovacuum_enabled = off);
ALTER TABLE public.powa_all_tables_history_current SET (autovacuum_enabled = off);

Below are autovacuum settings that I have on local machine where collector is running

autovacuum	on
autovacuum_analyze_scale_factor	0.1
autovacuum_analyze_threshold	50
autovacuum_freeze_max_age	200000000
autovacuum_max_workers	10
autovacuum_multixact_freeze_max_age	400000000
autovacuum_naptime	60
autovacuum_vacuum_cost_delay	20
autovacuum_vacuum_cost_limit	-1
autovacuum_vacuum_insert_scale_factor	0.2
autovacuum_vacuum_insert_threshold	1000
autovacuum_vacuum_scale_factor	0.06
autovacuum_vacuum_threshold	50
autovacuum_work_mem	-1

I will try to re-enable autovacuum and see if there is difference

[ikalafat]

Well, disabling the auto vacuum was a horrible idea (although I was very aware of the consequences). powa_all_tables_history_current table size went up to ~800GB.

I deleted the whole DB to release the disk space, recreated it and registered the servers again. This time I kept the autovacuum enabled per tables (default settings) and reduced the retention interval and frequency from 5 to 10 minutes.

I have also recreated all indexes from initial post.

Will let you know the results.

[ikalafat]

Hi,

After all of mentioned changes, CPU is more-or less idle, disk usage is around ~20GB with retention interval of 6 hours.

I assume that initially, I have encountered wild scenarios with highly active remote servers and intense polling frequency, which caused CPU issues.

Nevertheless, I think that these indexes can be of use in these temp tables, mostly depending on activity on remote servers.

[BornTKill]

Hello,

Same for me. Very high CPU usage in POWA5. Was not the case in POWA4.

[rjuju]

thanks @ikalafat for all the details. We could of course add those indexes but if we can avoid it it's good as it will make the whole process way more expensive.

@BornTKill do you have more details on your use case so we can try to investigate? What would be useful would be at least:

• local or remote mode
• if remote, number of remote servers and how they were added (all at the same time, one at a time over a few days...)
• list of extensions enabled
• any other details like things you might have changes

[BornTKill]

Hi @rjuju

Thank you for you answer.

• I am on remote mode. Same configuration when i was on powa4. 8vCPU/16Go RAM (was 4vCPU in powa4)
• 80 remotes servers. All added at the same time.
• Extensions list on all remote :

pg_qualstats | ✓ | ✓ | ✓ | 2.1.0
pg_stat_kcache | ✓ | ✓ | ✓ | 2.2.3
pg_stat_statements | ✓ | ✓ | ✓ | 1.10
pg_track_settings | ✓ | ✓ | ✓ | 2.1.2
pg_wait_sampling | ✓ | ✓ | ✓ | 1.1

• Same issue with or without indexes :

CREATE index concurrently if not exists powa_catalog_class_src_tmp_srvid_idx ON public.powa_catalog_class_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_proc_src_tmp_srvid_idx ON public.powa_catalog_proc_src_tmp (srvid);
CREATE index concurrently if not exists powa_all_indexes_src_tmp_srvid_idx ON public.powa_all_indexes_src_tmp (srvid);
CREATE index concurrently if not exists powa_all_tables_src_tmp_srvid_idx ON public.powa_all_tables_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_attribute_srvid_idx ON public.powa_catalog_attribute (srvid);
CREATE index concurrently if not exists powa_statements_srvid_idx ON public.powa_statements (srvid);
CREATE index concurrently if not exists powa_statements_src_tmp_srvid_idx ON public.powa_statements_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_attribute_src_tmp_srvid_idx ON public.powa_catalog_attribute_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_proc_srvid_idx ON public.powa_catalog_proc (srvid);
CREATE index concurrently if not exists powa_catalog_collation_src_tmp_srvid_idx ON public.powa_catalog_collation_src_tmp (srvid);
CREATE index concurrently if not exists powa_catalog_type_src_tmp_srvid_idx ON public.powa_catalog_type_src_tmp (srvid);

• Change autovacuum config like this

autovacuum	on		Autovacuum
autovacuum_analyze_scale_factor	0.1		Autovacuum
autovacuum_analyze_threshold	50		Autovacuum
autovacuum_freeze_max_age	200000000		Autovacuum
autovacuum_max_workers	3		Autovacuum
autovacuum_multixact_freeze_max_age	400000000		Autovacuum
autovacuum_naptime	60	s	Autovacuum
autovacuum_vacuum_cost_delay	20	ms	Autovacuum
autovacuum_vacuum_cost_limit	-1		Autovacuum
autovacuum_vacuum_insert_scale_factor	0.2		Autovacuum
autovacuum_vacuum_insert_threshold	1000		Autovacuum
autovacuum_vacuum_scale_factor	0.06		Autovacuum
autovacuum_vacuum_threshold	50		Autovacuum
autovacuum_work_mem	-1	kB	Resource Usage / Memory
log_autovacuum_min_duration	600000	ms	Reporting and Logging / What to Log

[ikalafat]

two more cents from my side - I only have the pg_qualstats and pg_stat_statements extensions, and all remote servers were added simultaneously.

@BornTKill what is sampling rate/frequency on your end? can you try to add for example 5 by 5 servers with some pause in between?

[rjuju]

thanks for the extra details. I think that the common denominator here is a large number of remote serves all added at the same time. it's something not well tested for now, and that can indeed have some side effects. @frost242 already started some discussions about it in various issues.

the main problem with adding a lot of servers at the same time is that almost everything will happen concurrently. we added some mechanism to try to spread the snapshots themselves (I think it should kick in in that case too, you could confirm by comparing the snapshot time for each remote servers), but then there are other problems, like all the catalog snapshot will still happen mostly at the same time (for now first snapshot and then every month), same for the coalesce and the purge.

you could try to apply locally the patch merged at powa-team/powa-archivist#92 to see if this helps, and if yes if there are still some other problems after that.

[BornTKill]

Hello,

Thank you for you answer. Will try the patch today and let you know.

[BornTKill]

@rjuju

Can you explain me how to load this SQL ?
when i check the header i am confused :)

-- complain if script is sourced in psql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION powa" to load this file. \quit

I have to put the file here ?

/usr/share/postgresql/16/extension/powa--5.0.0.sql
/usr/share/postgresql/16/extension/powa--5.0.1.sql
/usr/share/postgresql/16/extension/powa--5.0.0--5.0.1.sql

[BornTKill]

OK I put the file here /usr/share/postgresql/16/extension/
I drop the extension and recreate it.
I add remote servers per batch of 10 servers and will wait before adding 10 more.

[rjuju]

Sorry I should have been clearer. The idea was to copy the new CREATE OR REPLACE FUNCTION command and apply it on your powa-repository, ie this part https://github.com/powa-team/powa-archivist/blob/master/powa--5.0.2.sql#L3345-L3605.

The mentioned changes has been merged as version 5.0.2, so if you only took the powa--5.0.2.sql file and you copied it in /usr/sare/postgresql/16/extension you will have to manually use CREATE EXTENSION powa WITH VERSION '5.0.2' to use that script.

[BornTKill]

@rjuju After one day, i still have high CPU finally.

SELECT
  pid,
  user,
  pg_stat_activity.query_start,
  now() - pg_stat_activity.query_start AS query_time,
  query,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity;

[marco44]

Sorry to hijack a bit, but autovacuum_vacuum_cost_delay to 20ms is extremely high… The default is 2ms on recent PostgreSQL versions (and it's already tuned for smallish installations). Mine is at 1ms (so my autovacuum is tuned to cleanup blocks 20 times faster than yours, as you seem to not have touched the vacuum_cost_limit). So it's very likely your autovacuum can't keep up, and your _tmp tables are not cleaned fast enough. Which then would make all the rest pile up, as you'll have to scan very bloated tables. Which will make the scans even slower, and everything would snowball from here. If you try changing the tuning, also put more autovacuum workers (like 8), so you're sure that smaller tables get vacuumed in a timely fashion

[ikalafat]

@marco44 thanks for the headsup. will give it a shot today

[ikalafat]

Just a quick info. I have applied new settings related to the autovacuum

autovacuum	on
autovacuum_analyze_scale_factor	0.02
autovacuum_analyze_threshold	50
autovacuum_freeze_max_age	200000000
autovacuum_max_workers	10
autovacuum_multixact_freeze_max_age	400000000
autovacuum_naptime	60
autovacuum_vacuum_cost_delay	1
autovacuum_vacuum_cost_limit	10000
autovacuum_vacuum_insert_scale_factor	0.05
autovacuum_vacuum_insert_threshold	1000
autovacuum_vacuum_scale_factor	0.01
autovacuum_vacuum_threshold	50

to be more precise

autovacuum_vacuum_insert_scale_factor	
autovacuum_vacuum_scale_factor	
autovacuum_analyze_scale_factor	

have been reduced significantly

autovacuum_vacuum_cost_delay has also been reduced per suggestion @marco44

I have also (re)created extension with version 5.0.2 (copied the files to /usr/share/postgresql/extension/, added indexes from my initial post and added 8 servers at once, and now I'm monitoring the CPU usage.

If this goes well, I can try to drop the indexes to see if there is a difference.

I am also considering reducing the autovacuum_naptime from default 60s, maybe to 10-20s

[BornTKill]

Dear,

Same on my side after changing autovaccum config.

[ikalafat]

Similar thing on my end.

I am additionally seeing weird behavior related to the disk usage, it looks like the retention isn't working well and then disk usage goes to 800GB for 8 servers with 6hrs retention

CPU graph

Disk graph

1st of February around 11:45, I have deleted the database and recreated it and added the servers back (disk was depleted)
2nd of February around 4AM something went wild and CPU went nuts
2nd of February around 8-9AM disk usage rapidly increases