Обсуждение: Too many waits on extension of relation
- LOG: process 3718 still waiting for ExclusiveLock on extension of relation 266775 of database 196511 after 1000.057 ms
Detail: Process holding the lock: 6423. Wait queue: 3718, 4600, 2670, 4046.
Context: SQL statement "INSERT INTO cms_c207c1e2_0ce7_422c_aafb_77d43f61e563.cms_item (display_name,ancestor_ids,content_size,case_node_id,case_model_id,case_instance_id,properties,mime_type,linked_ancestor_ids,linked_parent_folder_ids,payload_id,category,owner_id,version_no,latest,lock_time,lock_owner_id,version_label,chronicle_id,acl_id,trait_names,tags,parent_folder_id,updated_by,created_by,update_time,create_time,description,type,name,etag,id) VALUES (new.display_name,new.ancestor_ids,new.content_size,new.case_node_id,new.case_model_id,new.case_instance_id,json,new.mime_type,new.linked_ancestor_ids,new.linked_parent_folder_ids,new.payload_id,new.category,new.owner_id,new.version_no,new.latest,new.lock_time,new.lock_owner_id,new.version_label,new.chronicle_id,new.acl_id,new.trait_names,new.tags,new.parent_folder_id,new.updated_by,new.created_by,new.update_time,new.create_time,new.description,new.type,new.name,new.etag,new.id)"
When I search google for "ExclusiveLock on extension of relation" I find one thread about shared_buffers being very high but not big enough to fit the entire data in the cluster. How much ram, what is shared buffers and what is the total size of the database(s) on that Postgres instance?
Thanks for the response.
Yes, CMS_ITEM is the relname for id 266775 .
Overall DB's Size is 17GB and the size of shared_buffers is 1GB whereas the RAM size is around 32G.
Thanks,
Avinash
Вложения
Sushant Pawar
What is relation 266775 of database 196511? Is it cms_c207c1e2_0ce7_422c_aafb_77d43f61e563.cms_item or some system catalog table?
When I search google for "ExclusiveLock on extension of relation" I find one thread about shared_buffers being very high but not big enough to fit the entire data in the cluster. How much ram, what is shared buffers and what is the total size of the database(s) on that Postgres instance?
If so, this is a symptom of slow inserts due to many concurrent connections trying to insert into the same table at the same time. Each insert request may result in an extend lock (8k extension), which blocks other writers. What normally happens is that these extend locks happen so fast that you hardly ever see them in the pg_locks table, except in the case where many concurrent connections are trying to do inserts into the same table.
Regards,
Michael Vitale
Sushant Pawar wrote on 10/5/2020 1:38 PM:
We are also getting similar warning messages in the log file, for Insert operation as it is blocking concurrent inserts on the same table. As per the online documents, I have come across, suggest is because the Postgres process takes time to search for the relevant buffer in the shared_buffer area if shared_buffer is too big.In the highly transactional system, there may not be enough free buffers to allocate for incoming transactions. In our case allocated shared buffer is 24GB and has RAM 120GB, not sure whether we can call it too big but while querying pg_buffercache has always given indication that 12-13GB shared_buffers would be appropriate in our case. I have used the below URL to evaluate the shared buffer sizing.Sushant Pawar
On Mon, Oct 5, 2020 at 10:14 PM Michael Lewis <mlewis@entrata.com> wrote:What is relation 266775 of database 196511? Is it cms_c207c1e2_0ce7_422c_aafb_77d43f61e563.cms_item or some system catalog table?
When I search google for "ExclusiveLock on extension of relation" I find one thread about shared_buffers being very high but not big enough to fit the entire data in the cluster. How much ram, what is shared buffers and what is the total size of the database(s) on that Postgres instance?
Is there a way where we can improve the performance of concurrent inserts on the same table.
Thanks,