Обсуждение: vacuumdb in parallel
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.
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.
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
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.
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 > >
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
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
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.
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