Re: checking the trigger values for autovacuum tresholds

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: checking the trigger values for autovacuum tresholds
Дата
Msg-id CAMkU=1yxUBoPSMYMKHwXUJ0qmHGHNQMKdKKSBLKVObMnnz_Hrw@mail.gmail.com
обсуждение исходный текст
Ответ на checking the trigger values for autovacuum tresholds  (Dirk Krautschick <Dirk.Krautschick@trivadis.com>)
Список pgsql-admin
On Tue, May 24, 2022 at 6:17 PM Dirk Krautschick <Dirk.Krautschick@trivadis.com> wrote:

Hi,

 

I am actually doing some test with pglogical and a trivial bi-directional replication setup (no BDR, just cross subscriptions, no conflicts because UUID a prim key).

During the tests I am stressing the autovacuum results to simulate a customer situation having trouble with files growing way different on two replica nodes.

 

I am checking for the count of dead tuples with pg_stat_user_tables or the extension pgstattuple and I am not sure if any is somehow accurate.

 

What is the most accurate way to check for this information except using pageinspect and/or freespacemap and count for myself and

what exact value is using by the autovacuum process related to the configured thresholds?


pg_stat_user_tables is not transactional.  The values are reset to zero upon crash/unclean shutdown, and can also be reset manually.  Also, they are updated over UDP messages, which might get lost under high load.  So it is not necessarily accurate.  But it is what autovacuum uses, so if your question is about what the autovacuum worker is thinking, this is what it is thinking, even if that is wrong.  

pgstattuple actually inspects every tuple, so it is slow but as accurate as anything that runs on an unlocked table over a non-zero amount of time can be.  It basically is doing the pageinspect for you.  The freespacemap only shows you space that is free.  Space occupied by dead tuples is not free.  It is freeable, but not yet free.

Cheers,

Jeff

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

Предыдущее
От: Dirk Krautschick
Дата:
Сообщение: checking the trigger values for autovacuum tresholds
Следующее
От: Dennis
Дата:
Сообщение: postgresql database use a case insensitive collation