pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
deadlock under load 3+ messages / 2 participants [nested] [flat]
* deadlock under load @ 2012-11-30 14:34 Bob Jolliffe <bobjolliffe@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Bob Jolliffe @ 2012-11-30 14:34 UTC (permalink / raw) To: pgsql-performance Hello We am running a web application on ubuntu 10.10 using postgres 8.4.3. We are experiencing regular problems (each morning as the users come in) which seem to be caused by deadlocks in the postgres database. I am seeing messages like: 2012-11-30 10:24:36 GMT LOG: sending cancel to blocking autovacuum PID 16951 at character 62 2012-11-30 10:24:36 GMT DETAIL: Process 3368 waits for AccessShareLock on relation 36183 of database 33864. 2012-11-30 10:24:36 GMT STATEMENT: SELECT indicatorid, periodid, organisationunitid, value FROM aggregatedindicatorvalue WHERE indicatorid I N (41471, 46324, 41481, 41487) AND periodid IN (46422, 46423, 46424) AND organisationunitid IN (67) Almost all of the postgres processes seem to be stuck in the "PARSE WAITING" state and the application ceases to respond as it becomes starved of database connections. The only way to get things moving again seems to be to restart postgres. Trying to interpret this, does this mean that the autovacuum process is holding a lock which is required tn order to complete the select query? Is it possible that the autovacuum process is ignoring that 'cancel' request so everything stays blocked? Sorry if these seem like basic questions. I am not too sure where to look to start resolving this. Any suggestions would be appreciated. Bob ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: deadlock under load @ 2012-11-30 15:57 Tom Lane <tgl@sss.pgh.pa.us> parent: Bob Jolliffe <bobjolliffe@gmail.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2012-11-30 15:57 UTC (permalink / raw) To: Bob Jolliffe <bobjolliffe@gmail.com>; +Cc: pgsql-performance Bob Jolliffe <bobjolliffe@gmail.com> writes: > We am running a web application on ubuntu 10.10 using postgres 8.4.3. Current release in that branch is 8.4.14. (By this time next week it'll be 8.4.15.) You are missing a lot of bug fixes: http://www.postgresql.org/docs/8.4/static/release.html > Trying to interpret this, does this mean that the autovacuum process is > holding a lock which is required tn order to complete the select > query? Possibly. Looking into the pg_locks view would tell you more. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: deadlock under load @ 2012-11-30 16:13 Bob Jolliffe <bobjolliffe@gmail.com> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 3+ messages in thread From: Bob Jolliffe @ 2012-11-30 16:13 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance On 30 November 2012 15:57, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bob Jolliffe <bobjolliffe@gmail.com> writes: > > We am running a web application on ubuntu 10.10 using postgres 8.4.3. > > Current release in that branch is 8.4.14. (By this time next week > it'll be 8.4.15.) You are missing a lot of bug fixes: > http://www.postgresql.org/docs/8.4/static/release.html > > Sorry I reported that incorrectly. 8.4.3 was initially installed but the package system has kept it up to date. Currently it is in fact 8.4.14. > > Trying to interpret this, does this mean that the autovacuum process is > > holding a lock which is required tn order to complete the select > > query? > > Possibly. Looking into the pg_locks view would tell you more. > Ok. I guess I will have to wait for it to lock up again to do this. > > regards, tom lane > ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-11-30 16:13 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2012-11-30 14:34 deadlock under load Bob Jolliffe <bobjolliffe@gmail.com> 2012-11-30 15:57 ` Tom Lane <tgl@sss.pgh.pa.us> 2012-11-30 16:13 ` Bob Jolliffe <bobjolliffe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox