Message-ID: <5012D9D6.8080002@commandprompt.com>
Date: Fri, 27 Jul 2012 11:11:34 -0700
From: "Joshua D. Drake" <jd@commandprompt.com>
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 <webster@lexmark.com>
CC: pgsql-admin@postgresql.org
Subject: Re: db size growing out of control when using clustered Jackrabbit
References: 
 <CAEHjwJ6pu1D+nQEbdg=bimY0gKVFvBTVq6JoMQjt3v3_ChMncA@mail.gmail.com>
	<500DC4BB.2090102@commandprompt.com>
	<CAEHjwJ75MB1jm0W516w1YJt7NJ35a=pHaPPtMf1z+SX=G+9qLA@mail.gmail.com>
	<500EDE55.9060409@commandprompt.com>
	<CAEHjwJ4qf2emMPKadyEkXyFVBbeMBNKjNo0ssTPfVGbxXfRazw@mail.gmail.com>
	<50103E9C.2050301@commandprompt.com>
	<CAEHjwJ44sJqAGpZeZZX+h=xLWw3Pi94AveX2DcNH3qEci=7eBA@mail.gmail.com>
In-Reply-To: 
 <CAEHjwJ44sJqAGpZeZZX+h=xLWw3Pi94AveX2DcNH3qEci=7eBA@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


On 07/26/2012 12:31 PM, Gary Webster wrote:

> OK, I set "log_statement = "all""
> The log grew to 1GB in ~minute!  It is dominated by this one statement,
> which occurs every ~1.4 sec:
> "update WS_BUNDLE set BUNDLE_DATA = $1 where NODE_ID_HI = $2 and
> NODE_ID_LO = $3"
> parameter $1 is hex, over 6million characters long !!   Surely this is
> the root of my problem.

It definitely is. Every time you update, you are creating a dead tuple 
(unless it is HOT capable). If autovacuum can't come in behind and clean 
that up due to idle in transaction, you are going to have serious problems.

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