On Mon, Feb 7, 2022, 10:26 PM Lars Aksel Opsahl <lars.ops...@nibio.no> 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 >
