pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedVacuum looping?
5+ messages / 4 participants
[nested] [flat]
* Vacuum looping?
@ 2007-07-27 21:32 Steven Flatt <steven.flatt@gmail.com>
2007-07-28 14:37 ` Re: Vacuum looping? Tom Lane <tgl@sss.pgh.pa.us>
2007-07-28 16:36 ` Re: Vacuum looping? Jim C. Nasby <decibel@decibel.org>
0 siblings, 2 replies; 5+ messages in thread
From: Steven Flatt @ 2007-07-27 21:32 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum looping?
2007-07-27 21:32 Vacuum looping? Steven Flatt <steven.flatt@gmail.com>
@ 2007-07-28 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-07-28 14:37 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: pgsql-performance
"Steven Flatt" <steven.flatt@gmail.com> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum looping?
2007-07-27 21:32 Vacuum looping? Steven Flatt <steven.flatt@gmail.com>
@ 2007-07-28 16:36 ` Jim C. Nasby <decibel@decibel.org>
2007-07-30 16:04 ` Re: Vacuum looping? Steven Flatt <steven.flatt@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Jim C. Nasby @ 2007-07-28 16:36 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: pgsql-performance
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 decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum looping?
2007-07-27 21:32 Vacuum looping? Steven Flatt <steven.flatt@gmail.com>
2007-07-28 16:36 ` Re: Vacuum looping? Jim C. Nasby <decibel@decibel.org>
@ 2007-07-30 16:04 ` Steven Flatt <steven.flatt@gmail.com>
2007-07-31 01:46 ` Re: Vacuum looping? Decibel! <decibel@decibel.org>
0 siblings, 1 reply; 5+ messages in thread
From: Steven Flatt @ 2007-07-30 16:04 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-performance
On 7/28/07, Jim C. Nasby <decibel@decibel.org> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Vacuum looping?
2007-07-27 21:32 Vacuum looping? Steven Flatt <steven.flatt@gmail.com>
2007-07-28 16:36 ` Re: Vacuum looping? Jim C. Nasby <decibel@decibel.org>
2007-07-30 16:04 ` Re: Vacuum looping? Steven Flatt <steven.flatt@gmail.com>
@ 2007-07-31 01:46 ` Decibel! <decibel@decibel.org>
0 siblings, 0 replies; 5+ messages in thread
From: Decibel! @ 2007-07-31 01:46 UTC (permalink / raw)
To: Steven Flatt <steven.flatt@gmail.com>; +Cc: pgsql-performance
On Jul 30, 2007, at 9:04 AM, Steven Flatt wrote:
> On 7/28/07, Jim C. Nasby <decibel@decibel.org> 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 decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-07-31 01:46 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-07-27 21:32 Vacuum looping? Steven Flatt <steven.flatt@gmail.com>
2007-07-28 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-07-28 16:36 ` Jim C. Nasby <decibel@decibel.org>
2007-07-30 16:04 ` Steven Flatt <steven.flatt@gmail.com>
2007-07-31 01:46 ` Decibel! <decibel@decibel.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox