On 27/09/2011, at 2:21 PM, Tom Lane wrote: > Royce Ausburn <royce.ml@xxxxxxxxxxx> writes: >> I have a problem with autovacuum apparently not doing the job I need it to do. > > Hm, I wonder whether you're getting bit by bug #5759, which was fixed > after 8.3.12. If this were the case, would I see lots of auto vacuum worker processes in ps that are essentially doing nothing because they're sleeping all the time? If so, then I think perhaps not. > >> I have a table named datasession that is frequently inserted, updated and deleted from. Typically the table will have a few thousand rows in it. Each row typically survives a few days and is updated every 5 - 10 mins. The application receives unreliable, potentially duplicate data from its source, so this table is heavily used for synchronising application threads as well. A typical access pattern is: > >> - tx begin >> - SELECT FOR UPDATE on a single row >> - Do some application processing (1 - 100 ms) >> - Possibly UPDATE the row >> - tx commit > > Transactions of that form would not interfere with autovacuum. You'd > need something that wants exclusive lock, like a schema change. > >> I've read some recent threads and found a discussion (below) on auto vacuum that mentions auto vacuum will be cancelled when a client requests a lock that auto vacuum is using∑ My questions: >> 1) Does it look like I'm affected by the same problem as in the below discussion? > > Not unless you're seeing a lot of "canceling autovacuum task" messages > in the postmaster log. Okay - This is not the case. Since sending this first email I've up'd the autovacuum log level and I've noticed that the same tables seem to be auto vacuum'd over and over again… Some of the tables are a bit surprising in that they're updated semi-regularly, but not enough (I'd think) to warrant an autovacuum every few minutes… Is this unusual? Perhaps unrelated: I've done some digging around and happened across a nightly task doing: select pg_stat_reset() on each of the databases in the cluster…. I've no idea why we're doing that (and our usual sysadmin / DBA has resigned, so I doubt I'll ever know). There must have been a reason at the time, but I wonder if this might be interfering with things? At any rate, I think the logs might glean some more interesting information, I'll let it alone for a few hours and hopefully I'll have some more useful information. --Royce -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
