Re: Should a DB vacuum use up a lot of space ? - Mailing list pgsql-general

On 08/07/2016 10:26 AM, Philippe Girolami wrote:
>>> I am seeing something weird though (again, this is v9.1): after my database became usable again, I started getting
the10M warning on template0. So I made it connectable and ran VACUUM 
>>> FREEZE on it and made it unconnectable again. That resolve the warning.
>>>
>>> However, I see the “age” keeps increasing on that database as I ran queries on my own db. Yesterday the age was 32
andnow it’s already 77933902 
>> Just to be sure you are talking about template0?
> Yes, I am
>
>>> Is that to be expected ? I didn’t expect it
>> As I understand it;
>>
>>    1) xid's are global to the cluster.
>>    2) age(xid) measures the difference between the latest global xid to
>>    whatever xid you supply it.
>>    3) age(datfrozenxid) measures the difference between the minimum value
>>    for the table frozen ids in a particular database and the latest global xid.
>>    4) template0 has a datfrozenxid so there is something for age(xid) to
>>    compute, it just does not mean anything as long as template0 is really a
>>    read-only database. In other words template0 is not actually
>>    contributing any transactions to the consumption of the global store of
>>    xids.
> Yes, I understand. I’m just worried that if I see the WARNING for the 100M mark, I’m afraid when it gets to the 1M
markon that database it will shut down the cluster. 
>
> More weirdness this afternoon : the wraparound ERROR showed up again even though I have trouble believing I burned
throughso many transactions in under a day. But let’s assume I did, here is what I noticed 
>
> 1) I vacuumed all other databases. For everyone of those, the age went down to 50M instead of zero. Is that normal ?
> 2) The only database that didn’t work on was template0 (the age did not change). It did work on template1

That is expected as template0 is read-only and so VACUUM will not work
on it.

>
> Should I suspect something fishy going on ?

Not sure without more information.

1) Can you be specific about your database references? 'That database'
is open-ended.

2) Show the actual numbers from your xid queries. Both the raw values
and the age() transformed ones.

3) What are your configuration parameters for the variables mentioned in
the section below?:

https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

4) If you want to get an idea of fast xid's are being created a quick
and dirty way is from here:

https://www.postgresql.org/docs/9.1/static/functions-info.html

txid_current()     bigint     get current transaction ID

Now if you do select txid_current() outside a transaction it will create
an xid on its own, still if you repeat it over some interval of time you
will get an idea of how fast the server is going through xid's.


>
>
> Thanks
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com