On 1/23/2013 2:13 PM, Jeff Janes wrote:
On Wed, Jan 23, 2013 at 8:53 AM, AJ Weber<aweber@xxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
