Feedback-ID: i76984098:Fastmail
Message-ID: <b0bb3ee4-e26c-941a-b326-68b515a09006@aklaver.com>
Date: Sun, 20 Aug 2023 13:15:27 -0700
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.14.0
Subject: Re: Why doesn't autovacuum/analyze run in due time after calling
 pg_stat_reset?
To: Rihad <grihad@gmail.com>,
 pgsql-general General <pgsql-general@postgresql.org>
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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <c1d7097c-710a-9513-7831-c765a2b622bf@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/b0bb3ee4-e26c-941a-b326-68b515a09006%40aklaver.com>
Precedence: bulk

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-FACTOR


Most importantly:

autovacuum

and

track_counts

https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS

> 
> 
>>
>>>
>>> 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.
>>>
>>>
>>>
>>
>>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com