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

>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
• 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 ?
 

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.