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

Поиск
Список
Период
Сортировка
Искать
От
Lars Aksel Opsahl
Тема
slow "select count(*) from information_schema.tables;" in some cases
Дата
Msg-id
AM7P189MB1028F684ADED9D89722C85C19D2C9@AM7P189MB1028.EURP189.PROD.OUTLOOK.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 Justin Pryzby <pryzby@telsasoft.com>
Re: 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 Imre Samu <pella.samu@gmail.com>
Re: 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 Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Re: 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 Tom Lane <tgl@sss.pgh.pa.us>
Re: slow "select count(*) from information_schema.tables;" in some cases Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
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

В списке pgsql-performance по дате отправления
От: Pavel Stehule
Дата:
От: Justin Pryzby
Дата:
FAQ