Skip site navigation (1) Skip section navigation (2)

Re: db size growing out of control when using clustered Jackrabbit

From: Gary Webster <webster(at)lexmark(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
Subject: Re: db size growing out of control when using clustered Jackrabbit
Date: 2012-07-24 15:58:53
Message-ID: CAEHjwJ75MB1jm0W516w1YJt7NJ35a=pHaPPtMf1z+SX=G+9qLA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
Hello.
Thanks for the response.

There are several 'idle in transaction' on this server/app, but to a
different db/schema.
The "repository" (JCR) schema has only a few 'idle', none 'in transaction' .

By "routine maintenance", do you mean autovacuum, or something else?
Autovacuum does appear to usually get 'auto-canceled' by a lock.  However,
even when it runs successfully, it doesn't seem to help with this ws_bundle
Toast table size.
I am rather looking for a root cause here.  Surely this table is not
supposed to grow so much (100s of GB).  It is even bigger than the data
store!

I agree that this may not be an 'error' in Postgres, but somehow it is not
playing well with Jackrabbit clustering.


On Mon, Jul 23, 2012 at 5:40 PM, Joshua D. Drake <jd(at)commandprompt(dot)com>wrote:

>
> On 07/23/2012 02:13 PM, Gary Webster wrote:
>
>> Hello. I'm hoping someone has seen this before.
>>
>> We are trying to use Postgres Plus v9.1.3 as the Persistence Manager in
>> Jackrabbit (Apache JCR) clustering
>> (http://wiki.apache.org/**jackrabbit/Clustering<http://wiki.apache.org/jackrabbit/Clustering>
>> ).
>> Whenever the JCR is under load, the ws_bundle TOAST table in the
>> repository  schema, grows out of control !
>>
>> Some of my team members maintain that this problem doesn't occur with
>> MySQL, but I would rather stay with Postgres if possible...
>>
>
> I don't really know anything about jackrabbit but generally this problem
> presents when you have a lot of transactions that are idle. Meaning, you
> have transactions that are just open, doing nothing. This will present a
> problem with routine maintenance.
>
> Under load you can check your process list to see if you have long running
> transactions that are idle (  idle in transaction ). If you do, you have a
> code problem not a postgres problem and it is presenting itself through
> bloat.
>
> Note: IDLE is fine. It is specifically IDLE IN TRANSACTION that is a
> problem.
>
>
> Sincerely,
>
> Joshua D. Drake
>
>
>
>> Thanks.
>>
>>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/
> PostgreSQL Support, Training, Professional Services and Development
> High Availability, Oracle Conversion, Postgres-XC
> @cmdpromptinc - 509-416-6579
>

In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2012-07-24 17:08:35
Subject: Re: db size growing out of control when using clustered Jackrabbit
Previous:From: Gary WebsterDate: 2012-07-24 15:34:57
Subject: Re: db size growing out of control when using clustered Jackrabbit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group