how to reduce Xidgenlock wait event - Mailing list pgsql-general

From Ishan Joshi
Subject how to reduce Xidgenlock wait event
Date
Msg-id VE1PR06MB7119E200A63CB76E96D12C8786C59@VE1PR06MB7119.eurprd06.prod.outlook.com
Whole thread Raw
List pgsql-general

Hi Team,

 

We have been observing XidGenlock wait in our transaction. This leeads some perofmrance issue in our product. We have heavyly commit product but still we are not getting such wait during the time. We have increase our load to double and then it start getting it. I have check the transaction id is not getting reached to the max size.

 

I am using AWS RDS instance and we have autovacuum on on this RDS. Plese find  parameter as follows.

Please let me know who to reduce such wait event with tunning of some parameters.

 

autovacuum

on

  autovacuum_analyze_scale_factor        

0

  autovacuum_analyze_threshold           

1000

  autovacuum_freeze_max_age              

200000000

  autovacuum_max_workers                 

20

  autovacuum_multixact_freeze_max_age    

10000000

  autovacuum_naptime                     

10

  autovacuum_vacuum_cost_delay           

0

  autovacuum_vacuum_cost_limit           

-1

  autovacuum_vacuum_scale_factor         

0.1

  autovacuum_vacuum_threshold            

1000

  checkpoint_completion_target           

0.9

  cpu_tuple_cost                         

0.03

  effective_cache_size                   

23592960 (180 GB)

  seq_page_cost                          

0.7

  shared_buffers                         

64GB

  temp_buffers                           

16384

  vacuum_cost_delay                      

0

  vacuum_cost_limit                      

6000

  vacuum_cost_page_dirty                 

20

  vacuum_cost_page_miss                  

5

  vacuum_defer_cleanup_age               

0

  vacuum_freeze_min_age                  

10000

  vacuum_freeze_table_age                

20000

  vacuum_multixact_freeze_min_age        

10000

  vacuum_multixact_freeze_table_age      

20000

  wal_buffers                            

262143

  wal_keep_segments                      

400

  work_mem                               

18432

 

 

Thanks & Regards

Ishan Joshi

 

This email and the information contained herein is proprietary and confidential and subject to the Amdocs Email Terms of Service, which you may review at https://www.amdocs.com/about/email-terms-of-service

pgsql-general by date:

Previous
From: Bhanu Priya
Date:
Subject: RE: Multiple Postgres process are running in background
Next
From: Laurenz Albe
Date:
Subject: Re: Can not ALTER TEXT SEARCH DICTIONARY intdict which is default in dict_int