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 <mle...@entrata.com
<mailto:mle...@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?