Re: Vacuum wait time problem

On Fri, Feb 13, 2009 at 5:02 PM, Michael Monnerie
<michael(dot)monnerie(at)is(dot)it-management(dot)at> wrote:
> On Freitag 13 Februar 2009 Roger Ging wrote:
>> I'm running vacuum full analyze verbose on a table with 20million
>> rows and 11 indexes. In top, I'm seeing [pdflush] and postgres:
>> writer process each using diferent cpu cores, with wait time well
>> above 90% on each of them. The vacuum has been running for several
>> hours
>
> Roger, I've had the same issue some time ago and wondered why it was so
> slow. I did "iostat -kx 5 555" and saw that I/O was also quite low.
>
> vacuum_cost_delay = 0
> That was the trick for me. It was set to 250(ms), where it took 5 hours
> for a vacuum to run. Now it takes 5-15 minutes.

Wow!!! 250 ms is HUGE in the scheme of vacuum cost delay. even 10ms
is usually plenty to slow down vacuum enough to keep it out of your
way and double to quadruple your vacuum times.

250 is like taking a nap every 5 feet while running a mile. :)