From: Yudhvir Singh Sidhu <ysidhu@gmail.com>
To: Jim Nasby <decibel@decibel.org>
To: pgsql-performance@postgresql.org
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
Date: Mon, 07 May 2007 21:10:23 -0700
Message-ID: <463FF82F.9040002@gmail.com> (raw)
In-Reply-To: <A47620C3-8814-4952-8DC5-532CFB04897E@decibel.org>
References: <463D0BD5.3010404@gmail.com>
<A47620C3-8814-4952-8DC5-532CFB04897E@decibel.org>
Jim Nasby wrote:
> On May 5, 2007, at 5:57 PM, Yudhvir Singh Sidhu wrote:
>> Problem: Vacuum times jump up from 45 minutes, or 1:30 minutes to 6+
>> hours overnight, once every 1 to 3 months.
>> Solutions tried: db truncate - brings vacuum times down. Reindexing
>> brings vacuum times down.
>
> Does it jump up to 6+ hours just once and then come back down? Or once
> at 6+ hours does it stay there?
>
> Getting that kind of change in vacuum time sounds a lot like you
> suddenly didn't have enough maintenance_work_mem to remember all the
> dead tuples in one pass; increasing that setting might bring things
> back in line (you can increase it on a per-session basis, too).
>
> Also, have you considered vacuuming during the day, perhaps via
> autovacuum? If you can vacuum more often you'll probably get less
> bloat. You'll probably want to experiment with the vacuum_cost_delay
> settings to reduce the impact of vacuuming during the day (try setting
> vacuum_cost_delay to 20 as a starting point).
> --
> Jim Nasby jim@nasby.net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
>
It ramps up and I have to run a db truncate to bring it back down. On
some machines it creeps up, on others it spikes. I have seen it climb
from 6 to 12 to 21 in 3 consequtive days. Well, what's one to do? I have
maintenance_work_mem set to 32768 - Is that enough? I vacuum daily.
I just turned vacuum verbose on on one of the systems and will find out
tomorrow what it shows me. I plan on playing with Max_fsm_ settings
tomorrow. And I'll keep you guys up to date.
Yudhvir
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: ysidhu@gmail.com, decibel@decibel.org
Subject: Re: How to Find Cause of Long Vacuum Times - NOOB Question
In-Reply-To: <463FF82F.9040002@gmail.com>
* 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