Message-ID: <510068AA.1000001@comcast.net>
Date: Wed, 23 Jan 2013 17:48:10 -0500
From: AJ Weber <aweber@comcast.net>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:7.0.1) Gecko/20110929 Thunderbird/7.0.1
MIME-Version: 1.0
To: Jeff Janes <jeff.janes@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: autovacuum fringe case?
References: <50E4AAB1.9040902@optionshouse.com>
	<B994348460014EC7BA65F310E9D30EBD@BERNICE>
	<50EC6549.3090503@optionshouse.com>
	<CAOR=d=2g4iH+4U2Dkn2EZtTnZrescxp+MW10zehVUzX6a5GXSA@mail.gmail.com>
	<50EC6742.4090303@optionshouse.com>
	<CAOR=d=0R1GPVfhhy9w2+j=czNvgtbA519rY6-Or6sNegvQmdFQ@mail.gmail.com>
	<50EC743E.1040209@optionshouse.com>
	<CABvEAQs7cRUH9PLHyTo-F+0t8=iKgX8N3ZSfCuzeSWXBJ9hM3w@mail.gmail.com>
	<510015A5.8060601@comcast.net>
	<CAMkU=1x1c5fJs=O533i9+fNvbaF41Ug82OOMARPmXGdWPRqqrQ@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1x1c5fJs=O533i9+fNvbaF41Ug82OOMARPmXGdWPRqqrQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance