On 03/30/2016 08:03 AM, Pavlov, Vladimir wrote:
Yes, VACUUM helps to solve the problem and the WARNING gone away.
Okay, so now we are on a different problem.
But, the problem is that the VACUUM for the entire database (2.4T) takes over 7 hours, and it has to run every 15-20 hours (about 300 millions transactions), otherwise:
ERROR: multixact "members" limit exceeded - and server stops working.
The question is how to start the VACUUM at least once in three days.
That is the purpose of autovacuum:
http://www.postgresql.org/docs/9.5/interactive/routine-vacuuming.html#AUTOVACUUM
http://www.postgresql.org/docs/9.5/interactive/runtime-config-autovacuum.html
Which also has a per table feature:
http://www.postgresql.org/docs/9.5/interactive/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
So how is your autovacuum set up?
Do really need to vacuum the whole database or selected heavily updated
table?
Kind regards,
Vladimir Pavlov
-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@xxxxxxxxxxx]
Sent: Wednesday, March 30, 2016 4:52 PM
To: Pavlov Vladimir; 'Alvaro Herrera'
Cc: 'pgsql-general@xxxxxxxxxxxxxx'
Subject: Re: Multixacts wraparound monitoring
On 03/30/2016 06:24 AM, Pavlov, Vladimir wrote:
Hello,
There is no news?
Now I have to do VACUUM every night, so that the server worked.
So has the WARNING gone away?:
WARNING: oldest multixact is far in the past
HINT: Close open transactions with multixacts soon to avoid wraparound problems.
Or to put it another way, define worked.
Maybe run VACUUM FREEZE?
Kind regards,
Vladimir Pavlov
-----Original Message-----
From: Pavlov Vladimir
Sent: Friday, March 25, 2016 9:55 AM
To: 'Alvaro Herrera'
Cc: 'Adrian Klaver'; pgsql-general@xxxxxxxxxxxxxx
Subject: RE: Multixacts wraparound monitoring
Hi, thank you very much for your help.
Pg_control out in the attachment.
Kind regards,
Vladimir Pavlov
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@xxxxxxxxxxxxxxx]
Sent: Friday, March 25, 2016 12:25 AM
To: Pavlov Vladimir
Cc: 'Adrian Klaver'; pgsql-general@xxxxxxxxxxxxxx
Subject: Re: Multixacts wraparound monitoring
Pavlov, Vladimir wrote:
There is nothing:
select * from pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+-----+----------+-------+----------
(0 rows)
It is also noticed that a lot of files in a directory main/pg_multixact/members/, now - 69640.
Can you attach pg_controldata output?
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
