From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Rihad <grihad(at)gmail(dot)com>, pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Why doesn't autovacuum/analyze run in due time after calling pg_stat_reset? |
Date: | 2023-08-21 16:00:53 |
Message-ID: | b020c3de-4399-4d35-8dd7-cb64a3a77627@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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-FACTOR
>>
>>
>> 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
>
>
>>>
>>>
>>>>
>>>>>
>>>>> 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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Mostafa Fathy | 2023-08-21 16:02:46 | Will PostgreSQL 16 supports native transparent data encryption ? |
Previous Message | David G. Johnston | 2023-08-21 15:50:21 | Re: The same prepared query yield "-1" the first six times and then "-1.0" |