On Mon, Feb 7, 2022, 10:26 PM Lars Aksel Opsahl <Lars.Opsahl@xxxxxxxx> wrote:
Hi
Sometimes simple sql's like this takes a very long time "select count(*) from information_schema.tables;"
Other sql's not including system tables may work ok but login also takes a very long time.
The CPU load on the server is around 25%. There is no iowait.
This happens typically when we are running many functions in parallel creating many temp tables and unlogged tables I think.
Here is a slow one:
https://explain.depesz.com/s/tUt5
and here is fast one :
https://explain.depesz.com/s/yYG4
Here are my settings (the server has around 256 GB og memory) :
max_connections = 500
work_mem = 20MB
effective_cache_size = 96GB
effective_io_concurrency = 256
shared_buffers = 96GB
temp_buffers = 80MB
Any hints ?
Thanks .
Lars
Can you share the output of the below query?
From the past threads I have learnt that too many templates objects may add to bloat of system catalogs and may in start resulting in impacting performance.
Make a note especially around
pg_attribute
pg_depends
and check for bloat, if required, vacuum full? these objects to speed up.
SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname = 'pg_catalog' ORDER BY 2 DESC LIMIT 20; can you show the output of this query
