that is exactly what I am seeing, one process, no change, always in idle while the others are constantly changing their state. looks like someone opened a tx then is blocking on a queue lock or something. dang. On Wednesday 21 June 2006 13:36, Csaba Nagy wrote: > On Wed, 2006-06-21 at 18:21, jody brownell wrote: > > That is interesting. > > > > There is one thread keeping a transaction open it appears from ps > > > > postgres: app app xxx(42644) idle in transaction > > That shouldn't be a problem on itself, "idle in transaction" happens all > the time between 2 commands in the same transaction... you only have a > problem if you see the same PID always "idle", that means somebody left > an open transaction and left for lunch. > > [snip] > > this was with the "Idle in transaction" though..... > > This probably means you don't have long running transactions currently. > However, if you happen to have just one such long transaction, the dead > space accumulates and normal vacuum will not be able to clean that > anymore. But I guess if you didn't find one now then you should take a > look at Tom's suggestion and bump up debug level to see if autovacuum > picks your table at all... > > > Ah HA! Wondering, my autovacuum naptime is 60 seconds, that is also the interval which I wake up and begin persistence. > > Wondering if I am simply locking autovacuum out of the tables b/c they are on a similar timeline. > > > > I will try a 30 second naptime, if this is it, that should increase the likely hood of falling on the right side of the TX more often. > > > > make sense? > > I don't think that's your problem... vacuum wouldn't be locked out by > any activity which doesn't lock exclusively the table (and I guess > you're not doing that). If your persistence finishes quickly then that's > not the problem. > > Oh, just occured to me... in order to use autovacuum you also need to > enable the statistics collector on row level: > > stats_start_collector = on > stats_row_level = on > > See also: > http://www.postgresql.org/docs/8.1/static/maintenance.html#AUTOVACUUM > > This was not mentioned in the settings in your original post, so I guess > you didn't touch that, and I think they are disabled by default. > > If this is disabled, you should enable it and "pg_ctl reload ....", that > should fix the problem. > > Cheers, > Csaba. > > >
