Обсуждение: [pgsql-admin] JCR (Apache Jackrabbit) locking when using Postgres, not with MySQL
On Wed, Aug 29, 2012 at 10:17 PM, Craig Ringer <ringerc@ringerc.id.au> 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:
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-bitThis install came from EnterpriseDB package.
The hardware has 8 CPU cores, & 12GB RAM.
I am using autovacuum, with "autovacuum_vacuum_cost_limit = 500" .
On Thu, Aug 30, 2012 at 3:33 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
If you want to know if there are locking issuesGary Webster wrote:
> 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.
>
> This problem does not occur at all if we substitute MySQL!
>
> This is Postgres v9.1.3 on RHEL5, 64-bit.
in the database, examine pg_locks and pg_stat_activity.
If you use MyISAM in MySQL, it is not surprising that
you have no locking issues, since no effort is made to
ascertain data integrity.
Yours,
Laurenz Albe
Hello.
Thanks for the info.
I responded about pg_stat_activity in my other post.
The only interesting activity I see is this:
"update JOURNAL_LOCAL_REVISIONS set REVISION_ID = $1 where JOURNAL_ID = $2"
I don't know exactly what I'm looking for in pg_locks .
MySQL works OK with both MyISAM & InnoDB.
Here is some info from the JCR side:
I see these two log statements with Postgres and MySQL in which case they both halt...
INFO - http-8080-35 - org.apache.jackrabbit.core.journal.AbstractJournal - Record with revision '20942' created by this journal, skipped.
INFO - http-8080-35 - org.apache.jackrabbit.core.journal.AbstractJournal - Synchronized to revision: 20942
Then, MySQL 'breaks free' but Postgres does not.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-bitThis install came from EnterpriseDB package.
The hardware has 8 CPU cores, & 12GB RAM.
I am using autovacuum, with "autovacuum_vacuum_cost_limit = 500" .