On Thu, Nov 12, 2009 at 9:14 AM, Wayne Beaver <wayne@acedsl.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.
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
the ability to run > 1 thread (it defaults to 3) so you can still keep
it "detuned" to stay out of the way, but with enough threads it can
hopefully keep up.
Of course, eventually you reach the point where as the work load rises
the ability of autovac to keep up is lost, and then you need more IO
period. Whether pgsql or any other database, running out of io
bandwidth is only really solvable by more IO bandwidth.
So, what does iostat -x 10
say about utilization?
