Skip site navigation (1) Skip section navigation (2)

Re: transaction delays to apply

From: Nickolay <nitro(at)zhukcity(dot)ru>
To:
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: transaction delays to apply
Date: 2009-08-13 13:31:29
Message-ID: 4A8415B1.4020203@zhukcity.ru (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Nickolay <nitro(at)zhukcity(dot)ru> writes:
>   
>> BUT it seems that rarely this transaction is being delayed to apply and 
>> log entry is being inserted in wrong order:
>> ID   timestamp
>> 1     2009-08-08 00:00:00.111
>> 2     2009-08-08 00:00:30.311
>> 3     2009-08-08 00:00:00.211
>> Yep, that's right - sometimes for 30 seconds or even more.
>>     
>
> You haven't provided enough information to let anyone guess at the
> problem.  Have you checked to see if one of the processes is blocking
> on a lock, or perhaps there's a sudden spike in system load, or what?
> Watching pg_stat_activity, pg_locks, and/or "vmstat 1" output during
> one of these events might help narrow down what's happening.
>
> 			regards, tom lane
>
>   
Thank you, guys. Problem's solved. I'm guilty and stupid :-)
One of the SELECT's in the transaction was wrong. Its job was to select 
messages from archive by several conditions, including:
date_time::date = now()::date
(i.e. timestamp field "date_time" was being converted to date type). 
After first run, postgresql seems to fix my mistake by cache or 
something else and futher SELECT's are being executed in a matter of 
milliseconds.
Fixed the statement to:
date_time >= now()::date
and now everything seems to work just fine even at first run.

Best regards, Nick.

In response to

pgsql-performance by date

Next:From: Matthew WakelingDate: 2009-08-13 14:16:13
Subject: How to run this in reasonable time:
Previous:From: NickolayDate: 2009-08-13 11:54:47
Subject: Re: transaction delays to apply

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group