Lock during insert statement
Could any one please tell me why my system is waiting to get lock for an
INSERT statement?
2014-05-21 07:52:49.965 PDT [9-1]LOG: process 31407 acquired ExclusiveLock
on extension of relation 429298276 of database 21497 after 3219.963 ms
2014-05-21 07:52:49.965 PDT [10-1]STATEMENT: INSERT INTO table1
(end_id,account_id,create_time,event_type,details) VALUES($1,$2,$3,$4,$5)
2014-05-21 07:52:49.965 PDT [3-1]LOG: duration: 4590.048 ms execute
<unnamed>: INSERT INTO table1
(end_id,account_id,create_time,event_type,details) VALUES($1,$2,$3,$4,$5)
Thanks.
On Wed, May 21, 2014 at 3:14 PM, AI Rumman <rummandba@gmail.com> wrote:
Could any one please tell me why my system is waiting to get lock for an
INSERT statement?2014-05-21 07:52:49.965 PDT [9-1]LOG: process 31407 acquired
ExclusiveLock on extension of relation 429298276 of database 21497 after
3219.963 ms
"on extension of relation" means that it needs to add 8KB to the end of the
table. That it takes so long to obtain that locks suggests you have a some
serious IO congestion.
Cheers,
Jeff
Got it.
Thanks.
Any special parameter to tune it? Like wal_buffers or shared_buffers?
On Wed, May 21, 2014 at 3:28 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
Show quoted text
On Wed, May 21, 2014 at 3:14 PM, AI Rumman <rummandba@gmail.com> wrote:
Could any one please tell me why my system is waiting to get lock for an
INSERT statement?2014-05-21 07:52:49.965 PDT [9-1]LOG: process 31407 acquired
ExclusiveLock on extension of relation 429298276 of database 21497 after
3219.963 ms"on extension of relation" means that it needs to add 8KB to the end of
the table. That it takes so long to obtain that locks suggests you have a
some serious IO congestion.Cheers,
Jeff
On Wednesday, May 21, 2014, AI Rumman <rummandba@gmail.com> wrote:
Got it.
Thanks.
Any special parameter to tune it? Like wal_buffers or shared_buffers?
Possible but unlikely. You probably just need better hardware (or more
patience). How fast is the database growing? How much is actually being
written (sar, vmstat)? What is the expected theoretical write throughput
of your RAID?
Cheers,
Jeff