Обсуждение: Cancelling of autovacuums considered harmful
As some of you folks know I work on some pretty beefy machines. Some of these machines don't see rest for months or days on end. And autovacuum keeps cancelling. I set it more aggressive, it still never finishes. We've now had to schedule manual vacuums because autovac never finishes on large heavily updated tables. Is there some way to turn off the cancelling of autovacuums?
Scott Marlowe escribió: > As some of you folks know I work on some pretty beefy machines. Some > of these machines don't see rest for months or days on end. And > autovacuum keeps cancelling. I set it more aggressive, it still never > finishes. No matter how heavily updated, regular activity should not cause autovacuum kills. Only heavier operations would do that (say ALTER TABLE, etc). > We've now had to schedule manual vacuums because autovac never > finishes on large heavily updated tables. > Is there some way to turn off the cancelling of autovacuums? I don't think we have a setting for that. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Feb 26, 2014 at 8:15 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
As some of you folks know I work on some pretty beefy machines. Some
of these machines don't see rest for months or days on end. And
autovacuum keeps cancelling. I set it more aggressive, it still never
finishes.
We've now had to schedule manual vacuums because autovac never
finishes on large heavily updated tables.
Is there some way to turn off the cancelling of autovacuums?
I don't think so, not without recompiling or hacking shared memory.
Perhaps you could set vacuum_freeze_table_age to a ridiculously low value, such that most vacuums become anti-wraparound ones.
You could have a frequent cron job look in pg_stat_activity for autovacs, and then automatically fire up a regular vacuum to replace them. But if they already got cancelled by someone else, you wouldn't notice that.
Are you sure you would want the currently cancelling processes to instead block for hours or days? If I had regularly scheduled jobs that cancelled autovac, I'd probably make that job also do a vacuum, that way I could control it.
Cheers,
Jeff
On Wed, Feb 26, 2014 at 9:56 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Scott Marlowe escribió: >> As some of you folks know I work on some pretty beefy machines. Some >> of these machines don't see rest for months or days on end. And >> autovacuum keeps cancelling. I set it more aggressive, it still never >> finishes. > > No matter how heavily updated, regular activity should not cause > autovacuum kills. Only heavier operations would do that (say ALTER > TABLE, etc). Is there any logging of what killed the autovac available? I've looked and not seen anything (currently on 8.4 moving to 9.2 soon.) >> We've now had to schedule manual vacuums because autovac never >> finishes on large heavily updated tables. > >> Is there some way to turn off the cancelling of autovacuums? > > I don't think we have a setting for that. Well it looks like it's slony 1.2 doing it. I'm gonna turn off autovac on the slony tables and see if it stops.
On Wed, Feb 26, 2014 at 1:34 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Feb 26, 2014 at 9:56 AM, Alvaro HerreraIs there any logging of what killed the autovac available? I've looked
<alvherre@2ndquadrant.com> wrote:
> Scott Marlowe escribió:
>> As some of you folks know I work on some pretty beefy machines. Some
>> of these machines don't see rest for months or days on end. And
>> autovacuum keeps cancelling. I set it more aggressive, it still never
>> finishes.
>
> No matter how heavily updated, regular activity should not cause
> autovacuum kills. Only heavier operations would do that (say ALTER
> TABLE, etc).
and not seen anything (currently on 8.4 moving to 9.2 soon.)
Better logging for that was introduced in 8.4.13 and 9.2.0.
Cheers,
Jeff
On 02/26/2014 08:56 AM, Alvaro Herrera wrote: > ... > No matter how heavily updated, regular activity should not cause > autovacuum kills. Only heavier operations would do that (say ALTER > TABLE, etc). "Considered harmful" got my attention. What, if any, known harm is caused? We have many errors of this type but in our case most are due to batch processes that have a vacuum embedded at appropriate points in the string of commands in order to avoid excessive bloat and to ensure the tables are analyzed for the following steps. Occasionally the autovacuum triggers before the manual but gets canceled. Any harm? Cheers, Steve
On 02/26/2014 04:40 PM, Steve Crawford wrote: > On 02/26/2014 08:56 AM, Alvaro Herrera wrote: >> ... >> No matter how heavily updated, regular activity should not cause >> autovacuum kills. Only heavier operations would do that (say ALTER >> TABLE, etc). > > "Considered harmful" got my attention. What, if any, known harm is > caused? > > We have many errors of this type but in our case most are due to batch > processes that have a vacuum embedded at appropriate points in the > string of commands in order to avoid excessive bloat and to ensure the > tables are analyzed for the following steps. Occasionally the > autovacuum triggers before the manual but gets canceled. Oops, I meant to say the processes have an ANALYZE embedded in them but this kills the vacuum. Typically they are bulk-loads into an empty table followed by analysis and distribution of the incoming data to other tables. The ANALYZE immediately follows the data load to ensure the planner has updated stats for the analysis and other following stages. Cheers, Steve
On Wed, Feb 26, 2014 at 5:40 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote: > On 02/26/2014 08:56 AM, Alvaro Herrera wrote: >> >> ... >> >> No matter how heavily updated, regular activity should not cause >> autovacuum kills. Only heavier operations would do that (say ALTER >> TABLE, etc). > > > "Considered harmful" got my attention. What, if any, known harm is caused? > > We have many errors of this type but in our case most are due to batch > processes that have a vacuum embedded at appropriate points in the string of > commands in order to avoid excessive bloat and to ensure the tables are > analyzed for the following steps. Occasionally the autovacuum triggers > before the manual but gets canceled. > > Any harm? We have some rather large tables that have never been autovacuumed. At first I was thinking it was due to pgsql cancelling them due to load etc. But if it's slony getting in the way then cancelling them is still harmful, it's just not postgres' fault.
On 02/27/14 10:43, Scott Marlowe wrote: > On Wed, Feb 26, 2014 at 5:40 PM, Steve Crawford > <scrawford@pinpointresearch.com> wrote: >> On 02/26/2014 08:56 AM, Alvaro Herrera wrote: >>> >>> ... >>> >>> No matter how heavily updated, regular activity should not cause >>> autovacuum kills. Only heavier operations would do that (say ALTER >>> TABLE, etc). >> >> >> "Considered harmful" got my attention. What, if any, known harm is caused? >> >> We have many errors of this type but in our case most are due to batch >> processes that have a vacuum embedded at appropriate points in the string of >> commands in order to avoid excessive bloat and to ensure the tables are >> analyzed for the following steps. Occasionally the autovacuum triggers >> before the manual but gets canceled. >> >> Any harm? > > We have some rather large tables that have never been autovacuumed. At > first I was thinking it was due to pgsql cancelling them due to load > etc. But if it's slony getting in the way then cancelling them is > still harmful, it's just not postgres' fault. Slony (even the very old 1.2) does not cancel anything explicitly. Jan -- Jan Wieck Senior Software Engineer http://slony.info