Re: AutoVacuum Behaviour Question - Mailing list pgsql-general

Alvaro Herrera wrote:
> Alvaro Herrera wrote:
>
>>> 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.
>

Okay, if it's decremented by the vacuum runs and the decrement is
vacuum_freeze_min_age, how is the age incremented? Is it a reflection of
the number of transactions performed on the database?

> Nah, false alarm, it's working as expected for me.  And I see the age of
> databases being correctly decreased to the freeze min age (plus however
> many transactions it took to do the vacuuming work).  So I'm still at a
> loss on why is it failing to advance the datfrozenxid of your database.
>
> Please let me have a look at this query result while connected to that
> database:
>
> select relname, relfrozenxid from pg_class where relkind in ('r', 't');
>

In the interest of safety, we used the oid value :)

blueface-crm=# select oid, relfrozenxid from pg_class where relkind in
('r', 't');
   oid   | relfrozenxid
---------+--------------
   10762 |   3291686808
   10769 |   3291686812
   10767 |   3291686811
   10739 |   3291686815
   10737 |   3291686814
   10744 |   3291686961
    2830 |   3291686990
    2832 |   3291686993
    2834 |   3291687121
    2836 |   3291686983
    2838 |   3291687029
    2840 |   3291687676
    2842 |   3291686964
    2844 |   3291687147
    2846 |   3291687144
 2570051 |   2947120794
 2580908 |   3291686819
 1950204 |   3291686821
 1950243 |   3291686825
 1950245 |   3291686827
 1950102 |   3291686829
 1950157 |   3291686836
 1950168 |   3291686896
 1950177 |   3291686900
 1950179 |   3291686902
 1950181 |   3291686904
 1950183 |   3291686906
 1950185 |   3291686908
 1950193 |   3291686910
 1950195 |   3291686912
 1950198 |   3291686914
 1950096 |   3291686916
 1950099 |   3291686918
 2449300 |   3291686920
 1950128 |   3291686922
 1950141 |   3291686925
 1950143 |   3291686927
 1950145 |   3291686933
 1950025 |   3291686935
 1950031 |   3291686944
 1950036 |   3291686946
 2410339 |   3291686948
 2443096 |   3291686951
 1950112 |   3291686958
   10742 |   3291686960
   10749 |   3291686967
    1260 |   3291686963
   10747 |   3291686966
   10754 |   3291686970
   10752 |   3291686969
   10759 |   3291686973
   10757 |   3291686972
   10764 |   3291686809
    2619 |   3291687675
    1247 |   3291686978
    1249 |   3291686980
    1255 |   3291686982
    1259 |   3291686985
    1248 |   3291686987
    2604 |   3291686989
    2606 |   3291686992
    2611 |   3291686995
 2449290 |   3291686998
 2449288 |   3291686997
 1950022 |   3291687215
 1950201 |   3291687008
    2610 |   3291687010
    2617 |   3291687012
    2616 |   3291687014
    2601 |   3291687016
    2602 |   3291687018
    2603 |   3291687020
    2612 |   3291687022
    2613 |   3291687024
    2600 |   3291687026
    2618 |   3291687028
    2620 |   3291687031
    2614 |   3291687118
    2609 |   3291687120
    2605 |   3291687125
    2615 |   3291687127
    2607 |   3291687129
    2608 |   3291687133
    1213 |   3291687137
    1136 |   3291687139
    1214 |   3291687141
    2396 |   3291687143
    1262 |   3291687146
    1261 |   3291687149
 1950028 |   3291686942
 1950212 |   3291687151
 1950046 |   3291687170
 1950214 |   3291687153
 1950057 |   3291687230
 1950217 |   3291687155
 1950062 |   3291687237
 1950222 |   3291687158
 1950076 |   3291687221
 1950237 |   3291687161
 1950093 |   3291687224
 1950240 |   3291687163
 2449510 |   3291687175
 1950109 |   3291686830
 1950118 |   3291687251
 1950148 |   3291687165
 1950152 |   3291687167
 2410336 |   3291687200
 1950138 |   3291686923
 1950042 |   3291687169
 2449508 |   3291687174
 2410341 |   3291686949
 2449775 |   3291687195
 1950160 |   3291686848
 1950049 |   3291687197
 1950165 |   3291687218
 2410334 |   3291687199
 1950174 |   3291686898
 1950079 |   3291687202
 1950083 |   3291687206
 1950007 |   3291687208
 1950012 |   3291687210
 1950015 |   3291687212
 1950018 |   3291687214
 1950163 |   3291687217
 1950219 |   3291687156
 1950234 |   3291687159
 2608570 |   3291687227
 2443093 |   3291687248
 1950069 |   3291687220
 2443098 |   3291686952
 1950086 |   3291687223
 2608567 |   3291687226
 1950054 |   3291687229
 1950060 |   3291687236
 1950065 |   3291687239
 1950038 |   3291687245
 2443091 |   3291687247
 1950114 |   3291687250
(138 rows)


> You can change the relname to oid if showing the table names is
> problematic for you.
>