Обсуждение: deadlock under load
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
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
On 30 November 2012 15:57, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bob Jolliffe <bobjolliffe@gmail.com> writes:Current release in that branch is 8.4.14. (By this time next week
> We am running a web application on ubuntu 10.10 using postgres 8.4.3.
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 isPossibly. Looking into the pg_locks view would tell you more.
> holding a lock which is required tn order to complete the select
> query?
Ok. I guess I will have to wait for it to lock up again to do this.
regards, tom lane