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?
> 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).
> 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.
Cheers,
Jeff