Post by Gary WebsterHello.
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.
- 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
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" .