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

On 08/07/2016 02:55 PM, Philippe Girolami wrote:
>> That is expected as template0 is read-only and so VACUUM will not work
>> on it.
> Isn’t template1 the same ? I’m not seeing that behavior on that one
>
>>> 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.
> “That database” = the database that’s been causing wrap-around problems since yesterday. It’s called “public”
>
>>    2) Show the actual numbers from your xid queries. Both the raw values
>>    and the age() transformed ones.
> backend> SELECT datname, datfrozenxid, age(datfrozenxid) FROM pg_database;
>          1: datname     (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid        (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>          1: datname = "template1"       (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid = "3814003766" (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age = "50000394"    (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>          1: datname = "postgres"        (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid = "3814003765" (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age = "50000395"    (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>          1: datname = "public"  (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid = "1717520404" (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age = "2146483756"  (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>          1: datname = "xxxx"  (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid = "3814003760" (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age = "50000400"    (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>          1: datname = "osmtest" (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid = "3814003762" (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age = "50000398"    (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>          1: datname = "template0"       (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid = "3732096533" (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age = "131907627"   (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>          1: datname = "drupal_prod"     (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid = "3814003758" (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age = "50000402"    (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>          1: datname = "anta"    (typeid = 19, len = 64, typmod = -1, byval = f)
>          2: datfrozenxid = "3814003756" (typeid = 28, len = 4, typmod = -1, byval = t)
>          3: age = "50000404"    (typeid = 23, len = 4, typmod = -1, byval = t)
>         ----
>
>>    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
> Should be the default values, I’ve never changed them.
>
> backend> show vacuum_freeze_min_age
>          1: vacuum_freeze_min_age       (typeid = 25, len = -1, typmod = -1, byval = f)
>         ----
>          1: vacuum_freeze_min_age = "50000000"  (typeid = 25, len = -1, typmod = -1, byval = f)
>
> backend> show vacuum_freeze_table_age
>          1: vacuum_freeze_table_age     (typeid = 25, len = -1, typmod = -1, byval = f)
>         ----
>          1: vacuum_freeze_table_age = "150000000"       (typeid = 25, len = -1, typmod = -1, byval = f)
>
> backend> show autovacuum_freeze_max_age
>          1: autovacuum_freeze_max_age   (typeid = 25, len = -1, typmod = -1, byval = f)
>         ----
>          1: autovacuum_freeze_max_age = "200000000"     (typeid = 25, len = -1, typmod = -1, byval = f)
>
> backend> show autovacuum_vacuum_threshold
>          1: autovacuum_vacuum_threshold (typeid = 25, len = -1, typmod = -1, byval = f)
>         ----
>          1: autovacuum_vacuum_threshold = "50"  (typeid = 25, len = -1, typmod = -1, byval = f)
>
> backend> show autovacuum_max_workers
>          1: autovacuum_max_workers      (typeid = 25, len = -1, typmod = -1, byval = f)
>         ----
>          1: autovacuum_max_workers = "3"        (typeid = 25, len = -1, typmod = -1, byval = f)
>
> backend> show autovacuum_vacuum_scale_factor
>          1: autovacuum_vacuum_scale_factor      (typeid = 25, len = -1, typmod = -1, byval = f)
>         ----
>          1: autovacuum_vacuum_scale_factor = "0.2"      (typeid = 25, len = -1, typmod = -1, byval = f)
>
> backend> show autovacuum_vacuum_cost_delay
>          1: autovacuum_vacuum_cost_delay        (typeid = 25, len = -1, typmod = -1, byval = f)
>         ----
>          1: autovacuum_vacuum_cost_delay = "20ms"       (typeid = 25, len = -1, typmod = -1, byval = f)
>
> backend> show autovacuum_vacuum_cost_limit
>          1: autovacuum_vacuum_cost_limit        (typeid = 25, len = -1, typmod = -1, byval = f)
>         ----
>          1: autovacuum_vacuum_cost_limit = "-1" (typeid = 25, len = -1, typmod = -1, byval = f)
>
>>    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.
> Well I’m now at a point where that’s not even possible, I have consistently run into the following
> • I vacuum enough table to get back a couple dozen transactions below the 1M mark

So you are VACUUMing the lesser 'younger' tables?

> • I exit singleuser mode and relaunch the server so I keep on vacuuming the oldest table first by copying
> • But I don’t even have time to launch my script : as soon as launch the server, the auto-vacuum daemon kicks in and
burnsthrough the transactions : I guess it’s not smart enough to start with the oldest tables ? 

Not understanding; 'the auto-vacuum daemon kicks in and burns through
the transactions'.

Are you saying it is reclaiming xids for you or using them?

If reclaiming that is what is supposed to do and is good thing.
Or am I misunderstanding?


>
> So it looks like I’m going to have to bite the bullet and really vacuum the whole database instead of just the oldest
tablesfirst which will impact our production pipelines. 

Or VACUUM the most heavily used tables in the database.

The next question to be asked is; what is creating the transactions and
is the transaction rate 'normal' or is there a possibility you have a
rogue process or rogue processes in action?

>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com