From: AJ Weber <aweber@comcast.net>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: autovacuum fringe case?
Date: Wed, 23 Jan 2013 17:48:10 -0500
Message-ID: <510068AA.1000001@comcast.net> (raw)
In-Reply-To: <CAMkU=1x1c5fJs=O533i9+fNvbaF41Ug82OOMARPmXGdWPRqqrQ@mail.gmail.com>
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: aweber@comcast.net, jeff.janes@gmail.com
Subject: Re: autovacuum fringe case?
In-Reply-To: <510068AA.1000001@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox