On Wed, Aug 29, 2012 at 10:17 PM, Craig Ringer <ring...@ringerc.id.au>wrote:
> On 08/30/2012 03:37 AM, Gary Webster wrote: > >> Hello. >> The subject says most of what I know at this point. >> >> We are still not getting along with Apache Jackrabbit. >> After a few hours of using Postgres as the Persistence Manager, the JCR >> gets stuck, apparently on a simple DB update statement. >> > > You need to: > > - Look at the PostgreSQL logs to see if there's anything informative > > - examine `iostat` and `vmstat` to see what the system activity is like > > - Look at `pg_stat_activity` to see what else is going on in the DB. > Look for "<IDLE> in transaction" entries, they might hold locks that > are preventing progress. > > - Check if queries run fine from psql > > - Check `pg_locks`, see if the blocked update is waiting on a lock > > - Possibly enable query logging for better diagnostics > > See also: > > https://wiki.postgresql.org/**wiki/Slow_Query_Questions<https://wiki.postgresql.org/wiki/Slow_Query_Questions> > > https://wiki.postgresql.org/**wiki/Guide_to_reporting_**problems<https://wiki.postgresql.org/wiki/Guide_to_reporting_problems> > > -- > Craig Ringer > Hello. Thanks for the suggestions. The Postgres log, with default settings, logs nothing during this problem. The system CPU & RAM are hardly being used. I'm not sure about disk I/O, though this is FC. Is there something in particular I should look for with iostat? pg_stat_activity shows several <IDLE> , and one: "update JOURNAL_LOCAL_REVISIONS set REVISION_ID = $1 where JOURNAL_ID = $2" I am also currently seeing one "<IDLE> in transaction" , but I'm not sure it's always there when it gets hung. Jackrabbit is the only client using this DB, so there's not much I can do about it anyway. The DB is still responding, answering queries. I don't know exactly what I'm looking for in pg_locks . If I enable query logging, I get over 300MB of logs per minute ! We are using JDBC, though I'm not sure where I should be getting that from... version: PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit This install came from EnterpriseDB package. The hardware has 8 CPU cores, & 12GB RAM. I am using autovacuum, with "autovacuum_vacuum_cost_limit = 500" .
