On Wed, Jul 25, 2012 at 2:44 PM, Joshua D. Drake <j...@commandprompt.com>wrote:
> > 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. > > 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. 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 >
