Re: Multixacts wraparound monitoring

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@aklaver.com]
> Sent: Wednesday, March 30, 2016 4:52 PM
> To: Pavlov Vladimir; 'Alvaro Herrera'
> Cc: 'pgsql-general@postgresql.org'
> Subject: Re: [GENERAL] 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@postgresql.org
>> Subject: RE: [GENERAL] 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@2ndquadrant.com]
>> Sent: Friday, March 25, 2016 12:25 AM
>> To: Pavlov Vladimir
>> Cc: 'Adrian Klaver'; pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] 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@aklaver.com
>


--
Adrian Klaver
adrian.klaver@aklaver.com