Re: AutoVacuum and growing transaction XID's

On Thu, May 7, 2020 at 4:18 PM github kran <githubkran(at)gmail(dot)com> wrote:

>
>
> On Thu, May 7, 2020 at 1:33 PM Michael Lewis <mlewis(at)entrata(dot)com> wrote:
>
>> It is trying to do a vacuum freeze. Do you have autovacuum turned off?
>> Any settings changed from default related to autovacuum?
>>
>> https://www.postgresql.org/docs/9.6/routine-vacuuming.html
>> Read 24.1.5. Preventing Transaction ID Wraparound Failures
>>
>> These may also be of help-
>>
>> https://info.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
>> https://www.2ndquadrant.com/en/blog/managing-freezing/
>>
>> Note that you need to ensure the server gets caught up, or you risk being
>> locked out to prevent data corruption.
>>
>
> Thanks Mike.
> 1) We haven't changed anything related to autovacuum except a work_mem
> parameter which was increased to 4 GB which I believe is not related to
> autovacuum
> 2) The vacuum was not turned off and few parameters we had on vacuum are
> *autovacuum_analyze_scale_factor = 0.02* and *autovacuum_vacuum_scale_factor
> = 0.05*
> *3) *The database curently we are running is 2 years old for now and we
> have around close to 40 partitions and the *datfrozenxid on the table is
> 343 million whereas the default is 200 million*. I would try doing a
> manual auto vacuum on those tables
> where the *autovacuum_freeze_max_age > 200 million*. Do you think It's a
> right thing to do ?.
>
> I will also go through this documents.
>

* Few more things 5/7 - 8:40 PM CDT*
1) I see there are *8 Vacuum workers* ( Not sure what changed) running
in the background and the concern I have is all of these vacuum processes
are running with wrap around and while they are running
I can't either DROP or ALTER any other tables ( REMOVE Inheritance
for any of old tables where the WRITES are not getting written to).* Any of
the ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I
WAITED FOR SEVERAL MINUTES , so I have terminated those queries as I didn't
have luck.*
2) T*he VACUUM Process wrap around is running for last 1 day and
several hrs on other tables. *
3) *Can I increase the autovacuum_freeze_max_age on the tables
on production system* ?

>
> Thanks
>

>
>