Re: AutoVacuum and growing transaction XID's

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Thu, May 7, 2020 at 4:18 PM github kran <githubkran@xxxxxxxxx> wrote:


On Thu, May 7, 2020 at 1:33 PM Michael Lewis <mlewis@xxxxxxxxxxx> wrote:
It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any settings changed from default related to autovacuum?

Read 24.1.5. Preventing Transaction ID Wraparound Failures


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)  The 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
        
 
 

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux