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