Re: autovacuum fringe case? - Mailing list pgsql-performance
From | AJ Weber |
---|---|
Subject | Re: autovacuum fringe case? |
Date | |
Msg-id | 510068AA.1000001@comcast.net Whole thread Raw |
In response to | Re: autovacuum fringe case? (Jeff Janes <jeff.janes@gmail.com>) |
Responses |
Re: autovacuum fringe case?
(Alvaro Herrera <alvherre@2ndquadrant.com>)
Re: autovacuum fringe case? (Jeff Janes <jeff.janes@gmail.com>) |
List | pgsql-performance |
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
pgsql-performance by date: