Обсуждение: Vacuum looping?

От:
"Steven Flatt"
Дата:

Postgres 8.2.4.
 
We have a large table, let's call it "foo", whereby an automated process periodically inserts many (hundreds of thousands or millions) rows into it at a time.  It's essentially INSERT INTO foo SELECT FROM <another table> WHERE <some conditions>.  Recently, for whatever reason, the query started to run out of memory.  This happened on the order of 50 times before it was noticed and the process was stopped.  (Admittedly, more investigation needs to go into the OOM problem... )
 
Now autovacuum promptly kicked in trying to clean up this mess, however it couldn't keep up at the rate that dead tuples were being generated.  I'm not sure if it got into a weird state.  After a few days, long after the inserting process was stopped, we decided to abort the vacuum (which we weren't convinced was doing anything), then start a manual vacuum with a higher vacuum_cost_limit to get things cleaned up quicker.
 
After 28 hours, here was the output of vacuum verbose:
 
# VACUUM VERBOSE foo;
INFO:  vacuuming "public.foo"
INFO:  scanned index "foo_pkey" to remove 44739062 row versions
DETAIL:  CPU 5.74s/26.09u sec elapsed 529.57 sec.
INFO:  scanned index "foo_1" to remove 44739062 row versions
DETAIL:  CPU 760.09s/619.83u sec elapsed 56929.54 sec.
INFO:  scanned index "foo_2" to remove 44739062 row versions
DETAIL:  CPU 49.35s/99.57u sec elapsed 4410.74 sec.
INFO:  "foo": removed 44739062 row versions in 508399 pages
DETAIL:  CPU 47.35s/12.88u sec elapsed 3985.92 sec.
INFO:  scanned index "foo_pkey" to remove 32534234 row versions
DETAIL:  CPU 22.05s/32.51u sec elapsed 2259.05 sec.
 
The vacuum then just sat there.  What I can't understand is why it went back for a second pass of the pkey index?  There was nothing writing to the table once the vacuum began.  Is this behaviour expected?  Are these times reasonable for a vacuum (on a busy system, mind you)?
 
We have since aborted the vacuum and truncated the table.  We're now working on the root OOM problem, which is easier said than done...
 
Steve
 
От:
Tom Lane
Дата:

"Steven Flatt" <> writes:
> The vacuum then just sat there.  What I can't understand is why it went back
> for a second pass of the pkey index?  There was nothing writing to the table
> once the vacuum began.  Is this behaviour expected?

Yes (hint: the numbers tell me what your maintenance_work_mem setting is).
You should have left it alone, probably, though there seems to be
something funny about your foo_1 index --- why was that so much slower
than the others for the first pass?

            regards, tom lane

От:
"Jim C. Nasby"
Дата:

On Fri, Jul 27, 2007 at 05:32:11PM -0400, Steven Flatt wrote:
> weren't convinced was doing anything), then start a manual vacuum with a
> higher vacuum_cost_limit to get things cleaned up quicker.

What are your vacuum_cost_* settings? If you set those too aggressively
you'll be in big trouble.

The second pass on the vacuum means that maintenance_work_memory isn't
large enough.
--
Jim Nasby                                      
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

От:
"Steven Flatt"
Дата:

On 7/28/07, Jim C. Nasby <> wrote:
What are your vacuum_cost_* settings? If you set those too aggressively
you'll be in big trouble.
 
autovacuum_vacuum_cost_delay = 100
autovacuum_vacuum_cost_limit = 200
 
These are generally fine, autovacuum keeps up, and there is minimal impact on the system.
 
vacuum_cost_delay = 100
vacuum_cost_limit = 1000
 
We set this cost_limit a little higher so that, in the few cases where we have to intervene manually, vacuum runs faster.
 

The second pass on the vacuum means that maintenance_work_memory isn't
large enough.
 
maintenance_work_mem is set to 256MB and I don't think we want to make this any bigger by default.  Like I say above, generally autovacuum runs fine.  If we do run into this situation again (lots of OOM queries and lots to cleanup), we'll probably increase maintenance_work_mem locally and run a vacuum in that session.
 
Good to know that vacuum was doing the right thing.
 
Thanks,
Steve
 
От:
Decibel!
Дата:

On Jul 30, 2007, at 9:04 AM, Steven Flatt wrote:
> On 7/28/07, Jim C. Nasby <> wrote: What are your
> vacuum_cost_* settings? If you set those too aggressively
> you'll be in big trouble.
>
> autovacuum_vacuum_cost_delay = 100

Wow, that's *really* high. I don't think I've ever set it higher than
25. I'd cut it way back.

> autovacuum_vacuum_cost_limit = 200
>
> These are generally fine, autovacuum keeps up, and there is minimal
> impact on the system.
>
> vacuum_cost_delay = 100
> vacuum_cost_limit = 1000
>
> We set this cost_limit a little higher so that, in the few cases
> where we have to intervene manually, vacuum runs faster.

IIRC, when the cost delay was initially introduced (8.0), someone did
testing and decided that the cost limit of 200 was optimal, so I
wouldn't go changing it like that without good reason.

Normally, I'll use a delay of 10ms on good disk hardware, and 20ms on
slower hardware.
--
Decibel!, aka Jim Nasby                        
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)