pg.ddx.io pgsql-general@postgresql.org mailing list archive help / color / mirror / Atom feed 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 20:09:03 +0400
Message-ID: <918f94be-41b9-e684-7679-b6bd819f693c@gmail.com> (raw )
In-Reply-To: <b020c3de-4399-4d35-8dd7-cb64a3a77627@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 >
<db75baff-dbc7-ee53-6b06-73455428358d@gmail.com >
<b020c3de-4399-4d35-8dd7-cb64a3a77627@aklaver.com >
On 8/21/23 20:00, Adrian Klaver wrote:
> On 8/20/23 22:31, Rihad wrote:
>> 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:
>>>>>>>
>
>>>
>>> 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)
>
> How about the rest of the settings at?:
>
> https://www.postgresql.org/docs/current/runtime-config-autovacuum.html
>
> Have the storage parameters for the tables been changed per?:
>
> https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
>
>
> In psql you can do:
>
> \d+ <table_name>
>
> The setting if changed will show up as Options: <setting>
>
> Also are there include directives in use per?:
>
> https://www.postgresql.org/docs/current/config-setting.html#CONFIG-INCLUDES
>
>
> You can see by looking at the sourcefile field in pg_settings:
>
> https://www.postgresql.org/docs/current/view-pg-settings.html
>
Thanks for the detailed reply, no tables have custom settings.
I need to make it clear once again that all autovac/analyze work as
expected when n_live_tup matches reality, i.e. when analyze has been run
on them since last reset.
A way to fix this is to simply analyze the whole database. Before doing
that, while n_live_tup starts from basically 0 and grows based on DB
activity, these usual calculations of 10-20% table size for
vacuum/analyze don't work. They don't trigger autovac for most tables,
or do it much much later.
>>
>>
>>>>
>>>>
>>>>>
>>>>>>
>>>>>> 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.
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
view thread (13+ messages) latest in thread
Message-ID: <918f94be-41b9-e684-7679-b6bd819f693c@gmail.com>
Permalink: ../918f94be-41b9-e684-7679-b6bd819f693c@gmail.com/
Also on: postgresql.org/message-id/918f94be-41b9-e684-7679-b6bd819f693c@gmail.com
copy link · copy postgr.es
reply 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: <918f94be-41b9-e684-7679-b6bd819f693c@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