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.
wb
> Quoting Scott Marlowe <scott.marlowe@gmail.com>:
>
> Autovac running slow is (generally) a good thing. It reduces the load
> on your IO subsystem so that other queries can still run fast. What
> resources are your long running autovacs eating up. If top shows
> 500Mres and 499Mshr, then don't worry, it's not actually eating up
> resources.
> Quoting Tom Lane <tgl@sss.pgh.pa.us>:
>
> autovacuum_vacuum_cost_delay. Is the slow autovac *really* eating
> a noticeable amount of system resources? I would think that a full
> speed manual vacuum would be a lot worse.
>> Wayne Beaver <wayne@acedsl.com> writes:
>>
>> Running Pg 8.3RC2, Linux server, w/8GB RAM, OpenSuSE 10.2 OS (yes, I
>> know that's old). I have seen *really* long-running autovacs eating up
>> system resources. While the below is not an example of *really* long,
>> it shows how I killed an autovac which had been running for more than
>> 10 minutes, then ran a VAC FULL ANALYZE on same exact table in about
>> ~2 min. Any wisdom here? Attributable to autovac_worker settings?