how to reduce Xidgenlock wait event

From: Ishan Joshi <Ishan(dot)Joshi(at)amdocs(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: how to reduce Xidgenlock wait event
Date: 2021-08-24 10:08:33
Message-ID: VE1PR06MB7119E200A63CB76E96D12C8786C59@VE1PR06MB7119.eurprd06.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 <https://www.amdocs.com/about/email-terms-of-service>

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2021-08-24 10:22:58 Re: Can not ALTER TEXT SEARCH DICTIONARY intdict which is default in dict_int
Previous Message Li EF Zhang 2021-08-24 06:21:08 Can not ALTER TEXT SEARCH DICTIONARY intdict which is default in dict_int