From: "marc.hsiao" <marc.hsiao@cobrasonic.com>
To: "'Scott Marlowe'" <scott.marlowe@gmail.com>
Cc: <pgsql-performance@postgresql.org>
References: <-121201471744211051@unknownmsgid>
	<AANLkTi=39eEqo-5j4DxGTLq4akygP2_Uh-+ZfAmf1MPJ@mail.gmail.com>
In-Reply-To: <AANLkTi=39eEqo-5j4DxGTLq4akygP2_Uh-+ZfAmf1MPJ@mail.gmail.com>
Subject: Re: How to turn autovacuum prevent wrap around run faster?
Date: Mon, 3 Jan 2011 10:01:38 +0800
Message-ID: <000601cbaaea$28a5e120$79f1a360$@hsiao@cobrasonic.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcumViSAZn9an7XiRMqWn2mo38ueAAEkzBww
Content-Language: zh-tw

Hi Scot Marlowe:

> Set an autovacuum max cost much higher than the vacuum max cost
> (100000 or so) and drop autovac cost delay to 0, then restart
> autovacuum.

I had set " autovacuum_vacuum_cost_delay=3D0".
BTW, how to set " autovacuum max cost" ?


Regards
Marc


-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Scott =
Marlowe
Sent: Tuesday, December 28, 2010 2:12 PM
To: marc.hsiao
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] How to turn autovacuum prevent wrap around run
faster?

On Mon, Dec 27, 2010 at 10:53 PM, marc.hsiao <marc.hsiao@cobrasonic.com>
wrote:
> Hi ALL:
>
> My Database is for logs only, I almost have 30G data growth in my
database.
>
> I Use partition table to store those data, those tables are =
partitioned by
> time column daily.
>
> My data only keep for three days.
>
> I will dump those data into dump file and drop the partition table =
after
> three days.
>
> My question is :
>
> The partition table that I have to backup and drop is running a long
> Prevent-Wraparound-Autovaccuum,
>
> Is any way to let the vacuum faster?
>
> The Prevent-Wraparound-Autovaccuum run very slow, almost 36 hours.
> My Disk IO is low.
> My Server config for vacuum list below:
> maintenance_work_mem =3D 192MB=A0=A0=A0 # min 1MB
>
> max_stack_depth =3D 4MB=A0=A0=A0=A0 # min 100kB
>
>
>
> vacuum_cost_delay =3D 50ms=A0=A0=A0 # 0-100 milliseconds

That's a pretty high regular vacuum cost delay.  Just sayin, autovac
doesn't use it.

> vacuum_cost_page_hit =3D 6=A0=A0=A0 # 0-10000 credits
>
> vacuum_cost_limit =3D 1000=A0=A0=A0 # 1-10000 credits

And that's pretty low.

> # AUTOVACUUM PARAMETERS
>
>
#------------------------------------------------------------------------=
---
---
> autovacuum =3D on=A0=A0=A0=A0 # Enable autovacuum subprocess?=A0 'on'
> autovacuum_max_workers =3D 3=A0=A0=A0 # max number of autovacuum =
subprocesses
> autovacuum_naptime =3D 1=A0=A0=A0 # time between autovacuum runs
> autovacuum_vacuum_scale_factor =3D 0.01 # fraction of table size =
before
vacuum
> autovacuum_vacuum_cost_delay =3D 10ms # default vacuum cost delay for

Set an autovacuum max cost much higher than the vacuum max cost
(100000 or so) and drop autovac cost delay to 0, then restart
autovacuum.

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