Bob Jolliffe
2012-11-30 14:34:15 UTC
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
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