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

>So you are VACUUMing the lesser 'younger' tables?
I VACUUM those with the highest age : 
SELECT age,array_agg(table_name) FROM (SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid))as age FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHEREc.relkind IN ('r', 'm') order by 2 desc limit 1000) tt group by age order by age desc
 

  
>> • 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?
Here is  what the logs show when I do what I described above

1) I got 7 transactions back in single user mode
Aug  7 23:40:57 p2 postgres[30376]: [5-1] 2016-08-07 23:40:57 CEST WARNING:  database "public" must be vacuumed within
999893transactions
 
Aug  7 23:40:57 p2 postgres[30376]: [5-2] 2016-08-07 23:40:57 CEST HINT:  To avoid a database shutdown, execute a
database-wideVACUUM in that database.
 
 
2) I exit single user mode and restart the database
Aug  7 23:41:40 p2 postgres[15457]: [1-1] 2016-08-07 23:41:40 CEST LOG:  database system was shut down at 2016-08-07
23:41:32CEST
 
Aug  7 23:41:40 p2 postgres[15458]: [1-1] 2016-08-07 23:41:40 CEST LOG:  incomplete startup packet
Aug  7 23:41:40 p2 postgres[15459]: [1-1] 2016-08-07 23:41:40 CEST FATAL:  the database system is starting up
Aug  7 23:41:40 p2 postgres[15457]: [2-1] 2016-08-07 23:41:40 CEST WARNING:  database with OID 16385 must be vacuumed
within999892 transactions
 
Aug  7 23:41:40 p2 postgres[15457]: [2-2] 2016-08-07 23:41:40 CEST HINT:  To avoid a database shutdown, execute a
database-wideVACUUM in that database.
 
Aug  7 23:41:40 p2 postgres[15457]: [2-3] #011You might also need to commit or roll back old prepared transactions.
Aug  7 23:41:40 p2 postgres[15462]: [1-1] 2016-08-07 23:41:40 CEST LOG:  autovacuum launcher started
Aug  7 23:41:40 p2 postgres[15447]: [1-1] 2016-08-07 23:41:40 CEST LOG:  database system is ready to accept
connections

3) but I don’t even have time to run a query to see the state of the databases
Aug  7 23:41:52 p2 postgres[15487]: [2-1] 2016-08-07 23:41:52 CEST ERROR:  database is not accepting commands to avoid
wraparounddata loss in database "public"
 
Aug  7 23:41:52 p2 postgres[15487]: [2-2] 2016-08-07 23:41:52 CEST HINT:  Stop the postmaster and use a standalone
backendto vacuum that database.
 
Aug  7 23:41:52 p2 postgres[15487]: [2-3] #011You might also need to commit or roll back old prepared transactions.
Aug  7 23:41:52 p2 postgres[15487]: [2-4] 2016-08-07 23:41:52 CEST STATEMENT:  SELECT datname, age(datfrozenxid) FROM
pg_database
Aug  7 23:41:52 p2 postgres[15497]: [2-1] 2016-08-07 23:41:52 CEST ERROR:  database is not accepting commands to avoid
wraparounddata loss in database "public"
 
Aug  7 23:41:52 p2 postgres[15497]: [2-2] 2016-08-07 23:41:52 CEST HINT:  Stop the postmaster and use a standalone
backendto vacuum that database.
 
Aug  7 23:41:52 p2 postgres[15497]: [2-3] #011You might also need to commit or roll back old prepared transactions.
Aug  7 23:41:52 p2 postgres[15497]: [2-4] 2016-08-07 23:41:52 CEST STATEMENT:  COPY  ( SELECT 'VACUUM VERBOSE '
||c.oid::regclass||';--' as _command, greatest ( age ( c.relfrozenxid ) ,age ( t.relfrozenxid ) ) as age FROM pg_class
cLEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ( 'r' , 'm' ) order by 2 desc limit 1000 ) TO
STDOUT(FORMAT text)
 
Aug  7 23:41:52 p2 postgres[15518]: [2-1] 2016-08-07 23:41:52 CEST ERROR:  database is not accepting commands to avoid
wraparounddata loss in database "public"
 
Aug  7 23:41:52 p2 postgres[15518]: [2-2] 2016-08-07 23:41:52 CEST HINT:  Stop the postmaster and use a standalone
backendto vacuum that database.
 
Aug  7 23:41:52 p2 postgres[15518]: [2-3] #011You might also need to commit or roll back old prepared transactions.
Aug  7 23:41:52 p2 postgres[15518]: [2-4] 2016-08-07 23:41:52 CEST STATEMENT:  SELECT datname, age(datfrozenxid) FROM
pg_database
Aug  7 23:41:53 p2 rsyslogd-2177: imuxsock lost 3512 messages from pid 15476 due to rate-limiting
Aug  7 23:41:53 p2 postgres[15476]: [2-1] 2016-08-07 23:41:53 CEST ERROR:  database is not accepting commands to avoid
wraparounddata loss in database "public"
 
Aug  7 23:41:53 p2 postgres[15476]: [2-2] 2016-08-07 23:41:53 CEST HINT:  Stop the postmaster and use a standalone
backendto vacuum that database.
 
Aug  7 23:41:53 p2 postgres[15476]: [2-3] #011You might also need to commit or roll back old prepared transactions.
Aug  7 23:41:53 p2 postgres[15476]: [2-4] 2016-08-07 23:41:53 CEST CONTEXT:  automatic analyze of table
"public.public.aaaaaa"


4) lots of autovacuum failures
Aug  7 23:41:55 p2 postgres[15476]: [3-1] 2016-08-07 23:41:55 CEST ERROR:  database is not accepting commands to avoid
wraparounddata loss in database "public"
 
Aug  7 23:41:55 p2 postgres[15476]: [3-2] 2016-08-07 23:41:55 CEST HINT:  Stop the postmaster and use a standalone
backendto vacuum that database.
 
Aug  7 23:41:55 p2 postgres[15476]: [3-3] #011You might also need to commit or roll back old prepared transactions.
Aug  7 23:41:55 p2 postgres[15476]: [3-4] 2016-08-07 23:41:55 CEST CONTEXT:  automatic analyze of table
"public.public.aaaaab"
Aug  7 23:41:56 p2 postgres[15476]: [4-1] 2016-08-07 23:41:56 CEST ERROR:  database is not accepting commands to avoid
wraparounddata loss in database "public"
 
Aug  7 23:41:56 p2 postgres[15476]: [4-2] 2016-08-07 23:41:56 CEST HINT:  Stop the postmaster and use a standalone
backendto vacuum that database.
 
Aug  7 23:41:56 p2 postgres[15476]: [4-3] #011You might also need to commit or roll back old prepared transactions.
Aug  7 23:41:56 p2 postgres[15476]: [4-4] 2016-08-07 23:41:56 CEST CONTEXT:  automatic analyze of table
"public.public.aaaaac"
Aug  7 23:41:56 p2 postgres[15476]: [5-1] 2016-08-07 23:41:56 CEST ERROR:  database is not accepting commands to avoid
wraparounddata loss in database "public"
 
Aug  7 23:41:56 p2 postgres[15476]: [5-2] 2016-08-07 23:41:56 CEST HINT:  Stop the postmaster and use a standalone
backendto vacuum that database.
 
Aug  7 23:41:56 p2 postgres[15476]: [5-3] #011You might also need to commit or roll back old prepared transactions.
(…)

>> So it looks like I’m going to have to bite the bullet and really vacuum the whole database instead of just the
oldesttables first which will impact our production pipelines.  
 
> Or VACUUM the most heavily used tables in the database.
Why would that help ? Aren’t the tables with the highest age the problem ?

>    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?
That’s always a possibility but I can’t think of what that would be.
I was guessing that the auto-vacuum wasn’t vacuuming the tables with the highest age first. Isn’t that a possibility ?

Thanks