Re: [pgsql-admin] JCR (Apache Jackrabbit) locking when using Postgres, not with MySQL

On Wed, Aug 29, 2012 at 10:17 PM, Craig Ringer <ringerc(at)ringerc(dot)id(dot)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" .