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

[Stelios Mavromichalis <mstelios@xxxxxxxxx>]

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.g.johnston@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance