Thread: vacuumdb in parallel

vacuumdb in parallel

From
Anj Adu
Date:
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.

Re: vacuumdb in parallel

From
Scott Marlowe
Date:
On Tue, Oct 13, 2009 at 7:29 PM, Anj Adu <fotographs@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.

Re: vacuumdb in parallel

From
Simon Riggs
Date:
On Tue, 2009-10-13 at 19:40 -0600, Scott Marlowe wrote:
> On Tue, Oct 13, 2009 at 7:29 PM, Anj Adu <fotographs@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


Re: vacuumdb in parallel

From
Scott Marlowe
Date:
On Wed, Oct 14, 2009 at 1:34 AM, Simon Riggs <simon@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@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.

Re: vacuumdb in parallel

From
Anj Adu
Date:
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@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@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
>
>

Re: vacuumdb in parallel

From
Alvaro Herrera
Date:
Anj Adu escribió:

> 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.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: vacuumdb in parallel

From
Simon Riggs
Date:
On Wed, 2009-10-14 at 13:57 -0300, Alvaro Herrera wrote:
> Anj Adu escribió:
>
> > 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


Re: vacuumdb in parallel

From
Alvaro Herrera
Date:
Simon Riggs escribió:
> On Wed, 2009-10-14 at 13:57 -0300, Alvaro Herrera wrote:
> > Anj Adu escribió:
> >
> > > 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.

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.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: vacuumdb in parallel

From
Simon Riggs
Date:
On Thu, 2009-10-15 at 09:24 -0300, Alvaro Herrera wrote:
> Simon Riggs escribió:
> > On Wed, 2009-10-14 at 13:57 -0300, Alvaro Herrera wrote:
> > > Anj Adu escribió:
> > >
> > > > 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.
>
> 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).

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