Thread: Autovacuum different in 9.2.4?
Hello, I seem to recall autovacuum changes landing for 9.2.4. Can someone please describe what those changes were and how they could affect usage? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats
On Mon, Aug 5, 2013 at 11:16 AM, Joshua D. Drake <jd@commandprompt.com> wrote: > > Hello, > > I seem to recall autovacuum changes landing for 9.2.4. Can someone please > describe what those changes were and how they could affect usage? Those landed in 9.2.3, see release notes for that version: Fix performance problems with autovacuum truncation in busy workloads (Jan Wieck) Fix error in vacuum_freeze_table_age implementation (Andres Freund) There should be no change in usage, unless you were taking some heroic methods to overcome the problems and can now discontinue them. Cheers, Jeff
Jeff Janes <jeff.janes@gmail.com> wrote: > On Mon, Aug 5, 2013 at 11:16 AM, Joshua D. Drake <jd@commandprompt.com> > wrote: >> I seem to recall autovacuum changes landing for 9.2.4. Can someone please >> describe what those changes were and how they could affect usage? > > Those landed in 9.2.3, see release notes for that version: > Fix performance problems with autovacuum truncation in busy workloads > (Jan Wieck) > Fix error in vacuum_freeze_table_age implementation (Andres Freund) > > There should be no change in usage, unless you were taking some heroic > methods to overcome the problems and can now discontinue them. Well, that was the intent, but there was an unintended increase in the frequency with which an autovacuum which attempts to truncate a heap may fail to set new statistics, and the logging around truncation got a bit too "chatty". These issues will be corrected with the next minor release, but until then some users may need to run ANALYZE commands in some cases to prevent tables with large swings in size from developing stale statistics, and there may be some new LOG entries which users wonder about -- they can safely be ignored. Overall, the autovacuum changes in 9.2.3 put an end to some debilitating problems with blocking and load related to overly aggressive and eager autovacuum runs. Jan's fix addressed problems with tables used for queues, as in slony and some JMS implementations. Andres fixed a bug which caused wraparound prevention autovacuum runs to occur too frequently. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 08/05/2013 12:13 PM, Jeff Janes wrote: > > On Mon, Aug 5, 2013 at 11:16 AM, Joshua D. Drake <jd@commandprompt.com> wrote: >> >> Hello, >> >> I seem to recall autovacuum changes landing for 9.2.4. Can someone please >> describe what those changes were and how they could affect usage? > > Those landed in 9.2.3, see release notes for that version: > Fix performance problems with autovacuum truncation in busy workloads > (Jan Wieck) > Fix error in vacuum_freeze_table_age implementation (Andres Freund) > > There should be no change in usage, unless you were taking some heroic > methods to overcome the problems and can now discontinue them. That is what is confusing me, I could be cracked but messages like these: automatic vacuum of table "pg_catalog.pg_attribute": could not (re)acquire exclusive lock for truncate scan Seem to be new? JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats
Joshua D. Drake escribió: > On 08/05/2013 12:13 PM, Jeff Janes wrote: > > >There should be no change in usage, unless you were taking some heroic > >methods to overcome the problems and can now discontinue them. > > That is what is confusing me, I could be cracked but messages like these: > > automatic vacuum of table "pg_catalog.pg_attribute": could not > (re)acquire exclusive lock for truncate scan > > Seem to be new? Yeah, those are new. In the old code, trying to truncate the free pages at the end of a table (which requires an ACCESS EXCLUSIVE lock on the table) could lock other processes out of that table. The new code instead tries to handle this gracefully by giving up the lock if some other process is trying to access the table. The result is that a few free pages might be left over after vacuuming the table. Not a big deal, normally, unless you're really short on disk space. There was discussion about autovacuum being tweaked so that it would reattempt to truncate those free pages in a future pass. I don't know if this was done or not. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Joshua D. Drake escribió: >> That is what is confusing me, I could be cracked but messages like these: >> >> automatic vacuum of table "pg_catalog.pg_attribute": could not >> (re)acquire exclusive lock for truncate scan >> >> Seem to be new? > > Yeah, those are new. Yeah, they started appearing in 9.2.3 and concurrent releases; but since they seem to be causing more confusion than enlightenment, I removed them in a subsequent patch. They should be gone again in 9.2.5. > In the old code, trying to truncate the free pages > at the end of a table (which requires an ACCESS EXCLUSIVE lock on the > table) could lock other processes out of that table. Right, prior to 9.2.3 all other access to the table could be blocked by an autovacuum truncation attempt for the duration that deadlock_timeout was set to. Those who set deadlock_timeout to something longer than the default 1 second could experience a lot of pain. > The new code > instead tries to handle this gracefully by giving up the lock if some > other process is trying to access the table. The result is that a few > free pages might be left over after vacuuming the table. Not a big > deal, normally, unless you're really short on disk space. Actually, the old code wound up not truncating anything when the autovacuum process was killed by the deadlock checker when deadlock_timeout expired, and needed to start all over for each attempt; with each attempt coming quickly on the heals of the last. So you could have an indefinitely repeating series of AccessExclusiveLocks on the table, with heavy load making no progress. In 9.2.3 and 9.2.4, it will truncate to the point it has checked and release the AccessExclusiveLock if any other process is waiting for 20 ms. It will attempt to reacquire the lock to continue the truncation every 50 ms for 5 seconds, at which point it will give up without the drama of the deadlock checker killing it. Either way, an incomplete truncation attempt would result in no statistics update, which would cause another autovacuum on that table to be attempted soon. Significantly, the old code *would* update statistics if the truncation attempt was not able to *begin* due to lock contention, while that behavior was lost in 9.2.3 and 9.2.4. This is where people with certain types of workloads are seeing an increase in stale statistics with the new code. This will be fixed in 9.2.5. We will then update statistics regardless of any problems in the truncation phase. > There was discussion about autovacuum being tweaked so that it would > reattempt to truncate those free pages in a future pass. I don't know > if this was done or not. Well, the old code did not reattempt the truncation very quickly if it was not able to acquire the initial AccessExclusiveLock, but would fire again soon if killed by the deadlock checker after it started. The new code has a cycle of quick detection of blocked processes, incremental truncate and sleep, and retry up to 100 times before giving up. In 9.2.3 and 9.2.4 it *also* reschedules quickly like the old aborted truncation; in 9.2.5 it will just try again if it seems needed at the next normally scheduled autovacuum. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company