Re: slow "select count(*) from information_schema.tables;" in some cases

Поиск
Список
Период
Сортировка
От Vijaykumar Jain
Тема Re: slow "select count(*) from information_schema.tables;" in some cases
Дата
Msg-id CAM+6J941cquhkv=8VJz19VqCTTscRzyL8Uj7DZ11VBjErwh4dQ@mail.gmail.com
обсуждение исходный текст
Ответ на slow "select count(*) from information_schema.tables;" in some cases  (Lars Aksel Opsahl <Lars.Opsahl@nibio.no>)
Ответы Re: slow "select count(*) from information_schema.tables;" in some cases
Список pgsql-performance
Дерево обсуждения


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

В списке pgsql-performance по дате отправления: