Re: autovacuum fringe case?

On 1/23/2013 2:13 PM, Jeff Janes wrote:
> On Wed, Jan 23, 2013 at 8:53 AM, AJ Weber<aweber@comcast.net>  wrote:
>> I have a server that is IO-bound right now (it's 4 cores, and top indicates
>> the use rarely hits 25%, but the Wait spikes above 25-40% regularly).
> How long do the spikes last?
 From what I can gather, a few seconds to a few minutes.
>
>> The
>> server is running postgresql 9.0 and tomcat 6.  As I have mentioned in a
>> previous thread, I can't alter the hardware to add disks unfortunately, so
>> I'm going to try and move postgresql off this application server to its own
>> host, but this is a production environment, so in the meantime...
>>
>> Is it possible that some spikes in IO could be attributable to the
>> autovacuum process?  Is there a way to check this theory?
> set log_autovacuum_min_duration to 0 or some positive number, and see
> if the vacuums correlate with periods of io stress (from sar or
> vmstat, for example--the problem is that sar only takes snapshots
> every 10 minutes, which is too coarse if the spikes are short).
I used iotop last time it was going crazy, and there were 5 postgres
procs at the top of the list (and virtually nothing else) all doing a
SELECT.  So I'm also going to restart the DB this weekend with
log-min-duration enabled.  Could also be some misbehaving queries...

Is there a skinny set of instructions on loading pg_stat_statements?  Or
should I just log them and review them from there?

>
>> Would it be advisable (or even permissible to try/test) to disable
>> autovacuum, and schedule a manual vacuumdb in the middle of the night, when
>> this server is mostly-idle?
> Scheduling a manual vacuum should be fine (but keep in mind that
> vacuum has very different default cost_delay settings than autovacuum
> does.  If the server is completely idle that shouldn't matter, but if
> it is only mostly idle, you might want to throttle the IO a bit).  But
> I certainly would not disable autovacuum without further evidence.  If
> a table only needs to be vacuumed once a day and you preemptively do
> it at 3a.m., then autovac won't bother to do it itself during the day.
>   So there is no point, but much risk, in also turning autovac off.
If I set autovacuum_max_workers = 1, will that effectively single-thread
it so I don't have two running at once?  Maybe that'll mitigate disk
contention a little at least?
>
> Cheers,
>
> Jeff