Re: Manual vacs 5x faster than autovacs?

On Thu, Nov 12, 2009 at 9:33 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne(at)acedsl(dot)com> wrote:
>> Hmm, looks like I've been myth-busted here.
>>
>> $ top | grep -E '29343|31924|29840|PID'; echo
>>  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
>> 29840 postgres  15   0 2150m 203m 194m S    0  2.5   0:00.59 postmaster
>> 29343 postgres  15   0 2137m 360m 356m S    1  4.5   0:00.92 postmaster
>> 31924 postgres  15   0 2135m  73m  70m S    1  0.9   0:00.44 postmaster
>>
>> So my claims of resource-usage appear incorrect.
>>
>> I'd seen autovacs running for hours and had mis-attributed this to growing
>> query times on those tables  - my thought was that "shrinking" the tables
>> "more quickly" could make them "more-optimized", more often. Sounds like I
>> could be chasing the wrong symptoms, though.
>
> Now it is quite possible that a slow autovac is causing your queries
> to run slower.  And it's that autovac isn't keeping up.  One of the
> verious serious shortcomings of autovac in 8.1 (or was it 8.0?  I
> think it was 8.1 as well) was that it only had one worker thread.  So,
> if it has a moderate to high cost delay, then it might be able to keep
> up with the job and your tables will become bloated.

might NOT be able to keep up

>
> The problem isn't that autovac is stealing too many resources, it's
> that it's not stealing enough.
>
> The first quick fix is 8.3 which has more efficient vacuuming code and

Whoops I see you're technically running 8.3, but you're running RC2
for some reason? I don't usually run 8.x.0 in production. Let alone
RCs. You should really update before some nasty bug that's been
squashed in later releases bites you.