Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 55EA6BEAE5C for ; Wed, 25 Jul 2012 15:45:11 -0300 (ADT) Received: from 30.55.colo.spiretech.net ([69.168.55.30] helo=lists.commandprompt.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Su6a6-0003Bf-Ne for pgsql-admin@postgresql.org; Wed, 25 Jul 2012 18:45:11 +0000 Received: from [192.168.1.5] (spinlock.commandprompt.com [69.168.55.30]) by lists.commandprompt.com (Postfix) with ESMTPA id 8C4BF34D; Wed, 25 Jul 2012 11:44:57 -0700 (PDT) Received: from [192.168.1.5] ([68.118.161.194] helo=[192.168.1.5]) by assp.commandprompt.com with ESMTPS(AES256-SHA) (2.1.1); 25 Jul 2012 11:44:57 -0700 Message-ID: <50103E9C.2050301@commandprompt.com> Date: Wed, 25 Jul 2012 11:44:44 -0700 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120714 Thunderbird/14.0 MIME-Version: 1.0 To: Gary Webster CC: pgsql-admin@postgresql.org Subject: Re: db size growing out of control when using clustered Jackrabbit References: <500DC4BB.2090102@commandprompt.com> <500EDE55.9060409@commandprompt.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Assp-Version: 2.1.1(11364) on assp.commandprompt.com X-Assp-Client-SSL: yes X-Assp-ID: assp.commandprompt.com m1-41897-10488 X-Assp-Envelope-From: jd@commandprompt.com X-Assp-Intended-For: webster@lexmark.com X-Assp-Original-Subject: Re: [ADMIN] db size growing out of control when using clustered Jackrabbit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/78 X-Sequence-Number: 39795 On 07/25/2012 11:37 AM, Gary Webster wrote: > This is a cluster issue, not a database issue. So if you have an > idnle in transaction, then it is affecting your JCR schema as well. > > OK, how do I track/debug/stop the "idle in transaction"s ? Well idle in transaction is ALWAYS a code issue. You have code that is executing that is starting a transaction, leaving the connection open while not closing (committing/rollingback) the transaction. You could turn on query logging and make sure pid and timestamp is in the log_line_prefix. They you can see what pids are idle in transaction and trace to what the last query was. o you mean autovacuum, or something else? > > > I mean autovacuum. > > I was hoping to find more of a 'root cause' (eg. jackrabbit config) for > this issue. > I can't believe that this table is supposed to be getting so big, to > even require much vacuuming. Any update/delete to that table is going to cause bloat, autovacuum cleans that up. If it can. If it can't, it will just continously grow. Sincerely, jD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579