Re: Manual Vaccum very slow with Autovaccum enabled

"Andy Dale" <andy(dot)dale(at)gmail(dot)com> writes:

> Hi,
>
> I am working with a 3 Postgresql databases, 1 is configured with autovaccum
> enabled with the following settings:
>
> vacuum_cost_delay = 200 # 0-1000 milliseconds
> vacuum_cost_limit = 100 # 0-10000 credits
...
> on the 2 machines with autovaccum not enabled this analyze takes only a few
> minutes at most, but on the machine with it enabled it takes roughly 40 - 50
> mins (2882336 ms.), i do not know why ?? does the autovaccum slow down a
> manually invoked vaccum ?

These say to wait 200ms every few pages. That makes sense if you want to run
vacuum without having it slow down the production system but, yes, it will
make it take a lot longer than if you don't have these set so high.

You can set these locally in your session before running vacuum if you want to
run vacuum manually faster than the normal autovacuum times.

http://www.postgresql.org/docs/8.2/interactive/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-VACUUM-COST

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com