Re: Re: recently and selectively slow, but very simple, update query....

hello David,

thank you! for the reply.

right, to this regard i did some research following the recommendation here
<http://wiki.postgresql.org/wiki/Lock_Monitoring>

however, it showed that nothing was wrong. no deadlocks, no pending locks
no nothing.

have to mention that this update is run in serial (no other thread/process
is trying to update that balance -or any other-, only one. well not 100%
true, except the topup mechanism that happens relatively rare).

also have to mention that his exact same mechanic is there some time now,
life a few years, and it never had similar problem.

also the fact that if i use another usid, with great many updates, will
roll normally leads me to think that it might be a corruption or something,
thus the dump/restore hope :)

as a prior step to dump/restore i am thinking of deleting and re-inserting
that particular row. that might share some light you think?

best regards,

/mstelios

Stelios Mavromichalis
Cytech Ltd. - http://www.cytech.gr/
Science & Technology Park of Crete
fax: +30 2810 31 1045
tel.: +30 2810 31 4127
mob.: +30 697 7078013
skype: mstelios

On Tue, May 6, 2014 at 12:51 AM, David G Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> Stelios Mavromichalis wrote
> >> the load of the machine is also low (like 0.2).
>
> Which means little if the update is waiting for a lock to be released by
> one
> other process; which is more likely the situation (or some other
> concurrency
> contention) especially as you said that this particular user generates
> significant transaction/query volume (implied by the fact the user has the
> most balance updates).
>
> During slow-update executions you want to look at:
> pg_stat_activity
> pg_locks
>
> to see what other concurrent activity is taking place.
>
> It is doubtful that dump/restore would have any effect given that the
> symptoms are sporadic and we are only talking about a select statement that
> returns a single row; and an update that does not hit any indexed column
> and
> therefore benefits from "HOT" optimization.
>
> HTH
>
> David J.
>
>
>
>
>
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Re-recently-and-selectively-slow-but-very-simple-update-query-tp5802553p5802555.html
> Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>