On Wed, Aug 29, 2012 at 10:17 PM, Craig Ringer <ringerc@xxxxxxxxxxxxx> wrote:
On 08/30/2012 03:37 AM, Gary Webster wrote:You need to:
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.
- 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/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:
This install came from EnterpriseDB package.
The hardware has 8 CPU cores, & 12GB RAM.
I am using autovacuum, with "autovacuum_vacuum_cost_limit = 500" .
