Re: AutoVacuum Behaviour Question

Alvaro Herrera wrote:
> Bruce McAlister wrote:
>> Alvaro Herrera wrote:
>>> Bruce McAlister wrote:
>>>
>>> Ok now this is interesting:
>>>
>>>> select datname, age(datfrozenxid) from pg_database;
>>>> datname | age
>>>> -------------------------+-----------
>>>> blueface-crm | 441746613
>>> Note this value is 440 million, and you said in your original report that
>>>
>>>> autovacuum_freeze_max_age = 200000000
>>> 200 million. So this database is being selected each time because of
>>> this.
>>>
>> Ahhh okay, I didnt know how to extract the age for a database. Learnt
>> something new here.
>>
>>> However, what should happen is that after the vacuum the age of the
>>> database is decreased after the vacuuming. What's your
>>> vacuum_freeze_min_age setting?
>> My *_freeze_* values are:
>>
>> autovacuum_freeze_max_age = 200000000
>> #vacuum_freeze_min_age = 100000000
>>
>> The vacuum_freeze_min_age is the default at 100 million (I assume).
>
> What do you get from a SHOW vacuum_freeze_min_age? That would tell you
> what's the actual value in use. Most likely it's those 100 million but
> if you change it, reload, then comment it back in the file and reload
> again, the value in use will be the one to which you first changed it.
>
Before Change
~~~~~~~~~~~~~

blueface-crm=# SHOW vacuum_freeze_min_age ;
vacuum_freeze_min_age
-----------------------
100000000
(1 row)

Now I edited postgresql.conf and changed vacuum_freeze_min_age to 150
million and reloaded ("pg_ctl -D `pwd` reload")

Change to 150 million
~~~~~~~~~~~~~~~~~~~~~

blueface-crm=# SHOW vacuum_freeze_min_age ;
vacuum_freeze_min_age
-----------------------
150000000
(1 row)

Now I commented out the vacuum_freeze_min_age value and reloaded

Commented Out
~~~~~~~~~~~~~

blueface-crm=# SHOW vacuum_freeze_min_age ;
vacuum_freeze_min_age
-----------------------
150000000
(1 row)

Now I changed postgresql.conf back to the original value and reloaded

Back to original
~~~~~~~~~~~~~~~~

blueface-crm=# SHOW vacuum_freeze_min_age ;
vacuum_freeze_min_age
-----------------------
100000000
(1 row)

>> How much is the age decremented by on a vacuum run then?
>
> It should be decremented to the vacuum_freeze_min_age. However, I'm
> running some experiments with your settings and apparently it's not
> working as it should.
>