I am running Postgres 8.1.9 on an 8 core Xeon 5430 box that is showing
single digit CPU and IO utilization. the database size is 820G .
Vacuum_cost_delay=0 and maintenance_mem = 900M

Is there an option to vacuumdb or a way to make it run parallel threads.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Tue, Oct 13, 2009 at 7:29 PM, Anj Adu <fotographs [at] gmail.com> wrote:
> I am running Postgres 8.1.9 on an 8 core Xeon 5430 box that is showing
> single digit CPU and IO utilization. the database size is 820G .
> Vacuum_cost_delay=0 and maintenance_mem = 900M
>
> Is there an option to vacuumdb or a way to make it run parallel threads.

I think that option showed up in 8.2 or 8.3.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Tue, 2009-10-13 at 19:40 -0600, Scott Marlowe wrote:
> On Tue, Oct 13, 2009 at 7:29 PM, Anj Adu <fotographs [at] gmail.com> wrote:
> > I am running Postgres 8.1.9 on an 8 core Xeon 5430 box that is showing
> > single digit CPU and IO utilization. the database size is 820G .
> > Vacuum_cost_delay=0 and maintenance_mem = 900M
> >
> > Is there an option to vacuumdb or a way to make it run parallel threads.
>
> I think that option showed up in 8.2 or 8.3.

There is no option to do that yet at any release.

We have parallel pg_restore, but that doesn't apply to vacuumdb,
reindexdb, clusterdb or any custom written jobs.

You need to break out your top few tables into separate jobs, e.g.
vacuumdb -t big1
vacuumdb -t big2
....

--
Simon Riggs www.2ndQuadrant.com


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Wed, Oct 14, 2009 at 1:34 AM, Simon Riggs <simon [at] 2ndquadrant.com> wrote:
> On Tue, 2009-10-13 at 19:40 -0600, Scott Marlowe wrote:
>> On Tue, Oct 13, 2009 at 7:29 PM, Anj Adu <fotographs [at] gmail.com> wrote:
>> > I am running Postgres 8.1.9 on an 8 core Xeon 5430 box that is showing
>> > single digit CPU and IO utilization. the database size is 820G .
>> > Vacuum_cost_delay=0 and maintenance_mem = 900M
>> >
>> > Is there an option to vacuumdb or a way to make it run parallel threads.
>>
>> I think that option showed up in 8.2 or 8.3.
>
> There is no option to do that yet at any release.
>
> We have parallel pg_restore, but that doesn't apply to vacuumdb,
> reindexdb, clusterdb or any custom written jobs.
>
> You need to break out your top few tables into separate jobs, e.g.
> vacuumdb -t big1
> vacuumdb -t big2

I was thinking of autovacuum, which does indeed have that option.
Much simpler to me, just set the autovac thresholds right and it's
automatic.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Thanks


I have several "daily" tables that get dropped every day..Is there a
wildcard that I can use to tell vacuumdb NOT to vacuum those
tables...i.e my goal is to ensure that vacuumdb vacuums the entire
database (minus the daily tables that get dropped) so that the XID
wraparound value gets reset correctly once vacuumdb is done. The
daily tables have suffixes like 2009_01_01, 2009_01_02 etc.

On Wed, Oct 14, 2009 at 12:34 AM, Simon Riggs <simon [at] 2ndquadrant.com> wrote:
> On Tue, 2009-10-13 at 19:40 -0600, Scott Marlowe wrote:
>> On Tue, Oct 13, 2009 at 7:29 PM, Anj Adu <fotographs [at] gmail.com> wrote:
>> > I am running Postgres 8.1.9 on an 8 core Xeon 5430 box that is showing
>> > single digit CPU and IO utilization. the database size is 820G .
>> > Vacuum_cost_delay=3D0 and maintenance_mem =3D 900M
>> >
>> > Is there an option to vacuumdb or a way to make it run parallel thread=
s.
>>
>> I think that option showed up in 8.2 or 8.3.
>
> There is no option to do that yet at any release.
>
> We have parallel pg_restore, but that doesn't apply to vacuumdb,
> reindexdb, clusterdb or any custom written jobs.
>
> You need to break out your top few tables into separate jobs, e.g.
> vacuumdb -t big1
> vacuumdb -t big2
> ...
>
> --
> =A0Simon Riggs =A0 =A0 =A0 =A0 =A0 www.2ndQuadrant.com
>
>

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Anj Adu escribi=F3:

> I have several "daily" tables that get dropped every day..Is there a
> wildcard that I can use to tell vacuumdb NOT to vacuum those
> tables...

No. You need to do "INSERT INTO pg_autovacuum" (or ALTER TABLE/SET in 8.=
4)
just after you've created the table.

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Wed, 2009-10-14 at 13:57 -0300, Alvaro Herrera wrote:
> Anj Adu escribi=C3=B3:
>
> > I have several "daily" tables that get dropped every day..Is there a
> > wildcard that I can use to tell vacuumdb NOT to vacuum those
> > tables...
>
> No. You need to do "INSERT INTO pg_autovacuum" (or ALTER TABLE/SET in =
8.4)
> just after you've created the table.

This doesn't work with vacuumdb, only with autovacuum.

--
Simon Riggs www.2ndQuadrant.com


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Simon Riggs escribi=F3:
> On Wed, 2009-10-14 at 13:57 -0300, Alvaro Herrera wrote:
> > Anj Adu escribi=F3:
> >
> > > I have several "daily" tables that get dropped every day..Is there =
a
> > > wildcard that I can use to tell vacuumdb NOT to vacuum those
> > > tables...
> >
> > No. You need to do "INSERT INTO pg_autovacuum" (or ALTER TABLE/SET i=
n 8.4)
> > just after you've created the table.
>
> This doesn't work with vacuumdb, only with autovacuum.

Ah, that's right. But then I see no reason why autovacuum wouldn't work
for him with this (he doesn't seem to have tried it).

--
Alvaro Herrera http://www.CommandPrompt.co=
m/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

On Thu, 2009-10-15 at 09:24 -0300, Alvaro Herrera wrote:
> Simon Riggs escribi=C3=B3:
> > On Wed, 2009-10-14 at 13:57 -0300, Alvaro Herrera wrote:
> > > Anj Adu escribi=C3=B3:
> > >
> > > > I have several "daily" tables that get dropped every day..Is ther=
e a
> > > > wildcard that I can use to tell vacuumdb NOT to vacuum those
> > > > tables...
> > >
> > > No. You need to do "INSERT INTO pg_autovacuum" (or ALTER TABLE/SET=
in 8.4)
> > > just after you've created the table.
> >
> > This doesn't work with vacuumdb, only with autovacuum.
>
> Ah, that's right. But then I see no reason why autovacuum wouldn't wor=
k
> for him with this (he doesn't seem to have tried it).

IIRC he wanted to do vacuum in parallel using 8.1

That's before you did your fine work to improve autovacuum.

--
Simon Riggs www.2ndQuadrant.com


--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin