From: Rihad <grihad@gmail.com>
To: Adrian Klaver <adrian.klaver@aklaver.com>
To: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
Date: Mon, 21 Aug 2023 09:31:21 +0400
Message-ID: <db75baff-dbc7-ee53-6b06-73455428358d@gmail.com> (raw)
In-Reply-To: <b0bb3ee4-e26c-941a-b326-68b515a09006@aklaver.com>
References: <69eacc0a-7e9b-4a35-278b-110b93d72882@mail.ru>
<18167fbd-815f-9b43-7016-5e5bbb8fb255@gmail.com>
<9440ab9c-4ee1-7668-5476-dc28e8086524@aklaver.com>
<c1d7097c-710a-9513-7831-c765a2b622bf@gmail.com>
<b0bb3ee4-e26c-941a-b326-68b515a09006@aklaver.com>
On 8/21/23 00:15, Adrian Klaver wrote:
> On 8/20/23 12:10, Rihad wrote:
>> On 8/20/23 20:22, Adrian Klaver wrote:
>>> On 8/18/23 22:35, Rihad wrote:
>>>> On 8/17/23 13:01, rihad wrote:
>>>>>
>>>>> Hi, all. After calling pg_stat_reset all statistics used by
>>>>> autovacuum got zeroed, and started accumulating from scratch. Some
>>>>> tables get acted upon properly, some don't.
>>>>>
>>>>>
>>>> Self-replying: yup, it seems there's an arbitrary limit of 100K of
>>>> n_live_tup after which autovac/analyze kicks in, or it seems so.
>>>
>>> To know rather then guess read:
>>>
>>> https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM
>>>
>>
>>
>> Sure, I read it before asking.
>>
>> Taking the first table in the list as an example:
>>
>>
>> relname | n_live_tup | n_dead_tup | left |
>> n_mod_since_analyze | left
>> --------------------------+------------+------------+----------+---------------------+--------
>>
>> fooooooooooo | 32781 | 240663 | -234057 |
>> 513265 | -509937
>>
>>
>> n_dead_tup (not the actual value, but some time after calling
>> pg_stat_reset) is much larger than 20% of n_live_tup 32781, and
>> n_mod_since_analyze is much larger than 10% of it.
>>
>> Yet it is kept unvacuumed and unanalyzed for a long time.
>>
>> autovacuum_(vacuum|analyze)_threshold is 50.
>>
>> What am I missing?
>
> Hard to say without seeing the actual settings in postgresql.conf that
> match:
>
> https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-F...
>
>
>
> Most importantly:
>
> autovacuum
>
> and
>
> track_counts
>
> https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS
>
>
They are both on and set as per default. Autovac/analyze continue
running on some tables after pg_stat_reset. Just not on all of them,
even thought they should judging by live/dead tuples calculation.
foo=> show track_counts;
track_counts
--------------
on
(1 row)
foo=> show autovacuum;
autovacuum
------------
on
(1 row)
>>
>>
>>>
>>>>
>>>> There are still many tables waiting for their turn, which is long due.
>>>>
>>>> Although there are some tables having only 60-70 (not 60-70K)
>>>> n_live_tup that have had autovacuum run on them. Weird.
>>>>
>>>>
>>>>
>>>
>>>
>>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-general@postgresql.org
Cc: grihad@gmail.com, adrian.klaver@aklaver.com
Subject: Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset?
In-Reply-To: <db75baff-dbc7-ee53-6b06-73455428358d@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox