Обсуждение: Occasional timeouts on TRUNCATE and simple INSERTs

Hello,

I am seeing some strange performance on a new pg9.1 instance.  We are seeing occasional statement timeouts on some
TRUNCATEsand INSERTs.  In both cases, the statements are quite simple: 
   - TRUNCATE schema.table;
  -  INSERT  INTO schema.table VALUES  ($1,2,$2,'');

Sometimes these will succeed.  Occasionally I see timeouts.  The statement_timeout is set to 60 seconds.  These tables
arenot particularly large; in the case of the insert, the table only has three rows.   

Our previous Postgresql 8.2 instance did not have this problem.  Any ideas about how to track down the issue?

Thanks,

--Jeff O

On Tue, Dec 11, 2012 at 1:19 PM, Osborn, Jeff <jeosborn@akamai.com> wrote:
> I am seeing some strange performance on a new pg9.1 instance.  We are seeing occasional statement timeouts on some
TRUNCATEsand INSERTs.  In both cases, the statements are quite simple: 
>    - TRUNCATE schema.table;
>   -  INSERT  INTO schema.table VALUES  ($1,2,$2,'');
>
> Sometimes these will succeed.  Occasionally I see timeouts.  The statement_timeout is set to 60 seconds.  These
tablesare not particularly large; in the case of the insert, the table only has three rows. 

A most common case is when backup (pg_dump*) is running TRUNCATE has
to wait for it because it acquires an access exclusive lock on a table
and all other queries including INSERT have to wait for the TRUNCATE.
Check the backup case first.

> Our previous Postgresql 8.2 instance did not have this problem.

This is strange for me.

--
Sergey Konoplev
Database and Software Architect
http://www.linkedin.com/in/grayhemp

Phones:
USA +1 415 867 9984
Russia, Moscow +7 901 903 0499
Russia, Krasnodar +7 988 888 1979

Skype: gray-hemp
Jabber: gray.ru@gmail.com

On Tue, Dec 11, 2012 at 3:38 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
> On Tue, Dec 11, 2012 at 1:19 PM, Osborn, Jeff <jeosborn@akamai.com> wrote:
>> I am seeing some strange performance on a new pg9.1 instance.  We are seeing occasional statement timeouts on some
TRUNCATEsand INSERTs.  In both cases, the statements are quite simple: 
>>    - TRUNCATE schema.table;
>>   -  INSERT  INTO schema.table VALUES  ($1,2,$2,'');
>>
>> Sometimes these will succeed.  Occasionally I see timeouts.  The statement_timeout is set to 60 seconds.  These
tablesare not particularly large; in the case of the insert, the table only has three rows. 
>
> A most common case is when backup (pg_dump*) is running TRUNCATE has
> to wait for it because it acquires an access exclusive lock on a table
> and all other queries including INSERT have to wait for the TRUNCATE.
> Check the backup case first.

Yeah: absolute first thing to check is if your statements are being
blocked -- you can get that via pg_stat_activity from another session.
 It's a completely different beast if that's the case.

merlin

Yeah I've been running a cron pulling relevant info from pg_stat_activity.  Haven't seen anything yet.  Currently
lookinginto the pg_dump situation. 

--Jeff O

On Dec 11, 2012, at 5:16 PM, Merlin Moncure wrote:

> On Tue, Dec 11, 2012 at 3:38 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
>
> Yeah: absolute first thing to check is if your statements are being
> blocked -- you can get that via pg_stat_activity from another session.
> It's a completely different beast if that's the case.
>
> merlin

You all were right.  The time-outs for TRUNCATE were due to a rogue pg_dump.  And the issue with the inserts was due to
anunrelated code change.   

Thanks for your help!

--Jeff O

On Dec 11, 2012, at 5:34 PM, Osborn, Jeff wrote:

> Yeah I've been running a cron pulling relevant info from pg_stat_activity.  Haven't seen anything yet.  Currently
lookinginto the pg_dump situation. 
>
> --Jeff O
>
> On Dec 11, 2012, at 5:16 PM, Merlin Moncure wrote:
>
>> On Tue, Dec 11, 2012 at 3:38 PM, Sergey Konoplev <gray.ru@gmail.com> wrote:
>>
>> Yeah: absolute first thing to check is if your statements are being
>> blocked -- you can get that via pg_stat_activity from another session.
>> It's a completely different beast if that's the case.
>>
>> merlin
>