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

Поиск
Список
Период
Сортировка
От Gary Webster
Тема Re: [pgsql-admin] JCR (Apache Jackrabbit) locking when using Postgres, not with MySQL
Дата
Msg-id CAEHjwJ7L=c8=bmb8HT+JiRVyQNAwAxsDb=pg3PYpLmC4a4oHmw@mail.gmail.com
обсуждение исходный текст
Ответ на [pgsql-admin] JCR (Apache Jackrabbit) locking when using Postgres, not with MySQL  (Gary Webster <gary.webster@perceptivesoftware.com>)
Список pgsql-admin
Дерево обсуждения
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:
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/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" .

В списке pgsql-admin по дате отправления:

Предыдущее
От: Devrim GÜNDÜZ
Дата:
Сообщение: [Fwd: Re: symbol lookup error: /usr/pgsql-9.1/bin/pg_ctl: undefined symbol: PQping]
Следующее
От: Gary Webster
Дата:
Сообщение: Re: [pgsql-admin] JCR (Apache Jackrabbit) locking when using Postgres, not with MySQL