Too many waits on extension of relation

[avinash varma]

Hi Team,

Can someone please guide me how to improve/reduce these wait events.

Postgres Version:9.5

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

Thanks & Regards,
Avinash.

Re: Too many waits on extension of relation

[Michael Lewis]

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?

>

Re: Too many waits on extension of relation

[Sushant Pawar]

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.

https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/



Best Regards,

*Sushant Pawar *


On Mon, Oct 5, 2020 at 10:14 PM Michael Lewis  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?
>
>>

Re: Too many waits on extension of relation

[MichaelDBA]

Are you having locks where the type = extend?

If so, this is a symptom of slow insertsdue 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.


https://www.keithf4.com/a-large-database-does-not-mean-large-shared_buffers/



Best Regards,

*Sushant Pawar *



On Mon, Oct 5, 2020 at 10:14 PM Michael Lewis > 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?

Re: Too many waits on extension of relation

[Laurenz Albe]

On Mon, 2020-10-05 at 10:32 +0530, avinash varma wrote:
> Can someone please guide me how to improve/reduce these wait events.
> 
> Postgres Version:9.5
> 
> 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 [...]

Process 6423 is holding a lock on the table into which you'd like to INSERT
that blocks several other sessions.

Make sure that the transaction in this database session ends, e.g. by

  SELECT pg_cancel_backend(6423);

Either there is a session that did not close its transaction (coding bug),
or a database statement ran inordinately long.

Yours,
Laurenz Albe
-- 
+43-670-6056265
CYBERTEC PostgreSQL International GmbH
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Web: https://www.cybertec-postgresql.com

Re: Too many waits on extension of relation

[avinash varma]

Hi Michael,

Yes, All the locks are of type= extend.
Is there a way where we can improve the performance of concurrent inserts
on the same table.

Thanks,
Avinash