Thread: Is regular vacuuming with autovacuum needed?
Hi all, I have an database server that is experiencing some lock contention and deadlock. It's infrequent, maybe once every two months, but time-consuming to deal with. The issue was that a routine VACUUM process (vacuumdb -az, called nightly via cron) was locking a table and wasn't completing. This server is also running autovacuum. This wasn't the source of the deadlock, but I'm wondering if regular vacuuming is necessary or even desirable with autovacuum running. Is there any reason for me to not disable the vacuum cron job and just tweak autovacuum parameters (or even just to leave the defaults)? Details: PostgreSQL version 8.4.4 Default vacuum/autovacuum parameters in postgresql.conf: #vacuum_cost_delay = 0ms #vacuum_cost_page_hit = 1 #vacuum_cost_page_miss = 10 #vacuum_cost_page_dirty = 20 #vacuum_cost_limit = 200 ... #autovacuum = on #log_autovacuum_min_duration = -1 #autovacuum_max_workers = 3 #autovacuum_naptime = 1min #autovacuum_vacuum_threshold = 50 #autovacuum_analyze_threshold = 50 #autovacuum_vacuum_scale_factor = 0.2 #autovacuum_analyze_scale_factor = 0.1 #autovacuum_freeze_max_age = 200000000 #autovacuum_vacuum_cost_delay = 20ms #autovacuum_vacuum_cost_limit = -1 ... #vacuum_freeze_min_age = 50000000 #vacuum_freeze_table_age = 150000000 Cheers, Peter
On Mon, Aug 16, 2010 at 12:08 PM, Peter Koczan <pjkoczan@gmail.com> wrote: > Hi all, > > I have an database server that is experiencing some lock contention > and deadlock. It's infrequent, maybe once every two months, but > time-consuming to deal with. > > The issue was that a routine VACUUM process (vacuumdb -az, called > nightly via cron) was locking a table and wasn't completing. This > server is also running autovacuum. This wasn't the source of the > deadlock, but I'm wondering if regular vacuuming is necessary or even > desirable with autovacuum running. Is there any reason for me to not > disable the vacuum cron job and just tweak autovacuum parameters (or > even just to leave the defaults)? If autovac is properly configured, very few, if any, PostgreSQL databases need routine vacuuming jobs. However, other than sleep states making it run slower, autovacuum is no different than a regular old vacuum. Are you sure this wasn't a vacuum full, which is almost never a desired operation to be regularly scheduled? -- To understand recursion, one must first understand recursion.
Peter Koczan <pjkoczan@gmail.com> writes:
> The issue was that a routine VACUUM process (vacuumdb -az, called
> nightly via cron) was locking a table and wasn't completing. This
> server is also running autovacuum. This wasn't the source of the
> deadlock, but I'm wondering if regular vacuuming is necessary or even
> desirable with autovacuum running. Is there any reason for me to not
> disable the vacuum cron job and just tweak autovacuum parameters (or
> even just to leave the defaults)?
On versions where autovacuum is on by default, I would certainly
recommend trying to use only autovacuum. cron-driven vacuum still
has some uses but they are corner cases.
regards, tom lane
Tom Lane wrote: > On versions where autovacuum is on by default, I would certainly > recommend trying to use only autovacuum. cron-driven vacuum still > has some uses but they are corner cases. > Corner cases implies something a bit more rare than I'd consider the case here. Consider a server where you know you have a large table that ends up with 5% dead rows each day. This will cause autovacuum to kick in to clean up about every 4 days, at the defaults where autovacuum_vacuum_scale_factor = 0.2. When it does finally get to that table, it's going to have a fairly large amount of work to do. If that happens during peak load time on your server, you may find that a painful shock. In that situation, it's completely reasonable to manually vacuum that table each day during a known slow period, late at night for example. Then it will never get to where it's so bloated that a hefty autovacuum kicks in at an unpredictable time. The other alternative here is to just tune autovacuum so it runs really slowly, so it won't kill responsiveness during any peak period. While in theory that's the right thing to do, this is much harder to get working well than what I just described. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support greg@2ndQuadrant.com www.2ndQuadrant.us
Greg Smith <greg@2ndquadrant.com> writes:
> Tom Lane wrote:
>> On versions where autovacuum is on by default, I would certainly
>> recommend trying to use only autovacuum. cron-driven vacuum still
>> has some uses but they are corner cases.
> Corner cases implies something a bit more rare than I'd consider the
> case here.
Well, it certainly has some uses, but I still think it's good advice to
first see if autovac alone will keep you happy.
> The other alternative here is to just tune autovacuum so it runs really
> slowly, so it won't kill responsiveness during any peak period. While
> in theory that's the right thing to do, this is much harder to get
> working well than what I just described.
But you really have to do that *anyway*, if you're not going to turn
autovac off.
I think the case where you want to combine cron-driven vacuum with
autovac is where, having made sure autovac is dialed down enough to not
present performance issues, you find that it can't keep up with the
required vacuuming. Then you need to do some not-hobbled vacuuming
during your maintenance windows. Eventually probably autovac will have
some understanding of maintenance windows built-in, but it doesn't yet.
regards, tom lane
On Mon, Aug 16, 2010 at 4:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Greg Smith <greg@2ndquadrant.com> writes: >> The other alternative here is to just tune autovacuum so it runs really >> slowly, so it won't kill responsiveness during any peak period. While >> in theory that's the right thing to do, this is much harder to get >> working well than what I just described. > > But you really have to do that *anyway*, if you're not going to turn > autovac off. > > I think the case where you want to combine cron-driven vacuum with > autovac is where, having made sure autovac is dialed down enough to not > present performance issues, you find that it can't keep up with the > required vacuuming. Then you need to do some not-hobbled vacuuming > during your maintenance windows. Eventually probably autovac will have > some understanding of maintenance windows built-in, but it doesn't yet. If the global values aren't vacuuming that table enough, then it seems apropos to change the autovacuum_vacuum_threshold value to some lower value for that table. (And it seems to me that if autovac never kicks in until 10% of a table's "dead," that's not nearly aggressive enough, possibly even with the global value! Given the 8.4 visibility map changes, shouldn't autovac be a tad more aggressive, when it should be spending little time on non-dead material? If the visibility map is doing its job, then the global threshold can be set pretty low, no?) I'm not quite sure what to think maintenance windows would look like... I see them having at least two distinct forms: a) A maintenance that is particularly for vacuuming, where factors would get dialed down to encourage vacuuming tables that mayn't have been hit lately; b) A maintenance where it is expected that Things Are Being Changed, where it might be pretty well necessary to shut off autovac so it doesn't interfere with DDL work. -- http://linuxfinances.info/info/postgresql.html
On Mon, Aug 16, 2010 at 1:34 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > If autovac is properly configured, very few, if any, PostgreSQL > databases need routine vacuuming jobs. However, other than sleep > states making it run slower, autovacuum is no different than a regular > old vacuum. Are you sure this wasn't a vacuum full, which is almost > never a desired operation to be regularly scheduled? I'm sure it wasn't a full vacuum. I almost never do those and when I do, I have to schedule downtime. I think another process got hung up somewhere and couldn't release its lock on the table in question, and there were several other processes waiting. It's possible that it was just a symptom of a larger problem at the time. I didn't have time to do a thorough analysis (and the problem state is lost now), and what was cause vs. effect is probably immaterial at this point. Peter
On Mon, Aug 16, 2010 at 3:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Greg Smith <greg@2ndquadrant.com> writes: >> Tom Lane wrote: >>> On versions where autovacuum is on by default, I would certainly >>> recommend trying to use only autovacuum. cron-driven vacuum still >>> has some uses but they are corner cases. > >> Corner cases implies something a bit more rare than I'd consider the >> case here. > > Well, it certainly has some uses, but I still think it's good advice to > first see if autovac alone will keep you happy. > >> The other alternative here is to just tune autovacuum so it runs really >> slowly, so it won't kill responsiveness during any peak period. While >> in theory that's the right thing to do, this is much harder to get >> working well than what I just described. > > But you really have to do that *anyway*, if you're not going to turn > autovac off. > > I think the case where you want to combine cron-driven vacuum with > autovac is where, having made sure autovac is dialed down enough to not > present performance issues, you find that it can't keep up with the > required vacuuming. Then you need to do some not-hobbled vacuuming > during your maintenance windows. Eventually probably autovac will have > some understanding of maintenance windows built-in, but it doesn't yet. For this application (and most of my databases), I'm fairly certain that autovacuum will work fine on its own. I'm going to disable the cron-vacuuming and try running with autovacuum alone. Thanks for the help, Peter
On Mon, Aug 16, 2010 at 2:47 PM, Peter Koczan <pjkoczan@gmail.com> wrote: > On Mon, Aug 16, 2010 at 1:34 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: >> If autovac is properly configured, very few, if any, PostgreSQL >> databases need routine vacuuming jobs. However, other than sleep >> states making it run slower, autovacuum is no different than a regular >> old vacuum. Are you sure this wasn't a vacuum full, which is almost >> never a desired operation to be regularly scheduled? > > I'm sure it wasn't a full vacuum. I almost never do those and when I > do, I have to schedule downtime. > > I think another process got hung up somewhere and couldn't release its > lock on the table in question, and there were several other processes > waiting. It's possible that it was just a symptom of a larger problem > at the time. I didn't have time to do a thorough analysis (and the > problem state is lost now), and what was cause vs. effect is probably > immaterial at this point. OK then. It's important to understand that regular vacuum and autovacuum perform the same functions and the only difference is the default nap time. If you had a problem with vacuum, you can expect it to crop up with autovacuum eventually too. Note that I have had priority inversion issues with autovacuum, slony, and the application where I work that meant I had to turn off autovacuum to perform maintenance operations with slony. You might be seeing the same kind of thing. -- To understand recursion, one must first understand recursion.