Re: [HACKERS] Allowing multi -t and adding -n to vacuumdb ?

2012-03-02 Thread Jehan-Guillaume (ioguix) de Rorthais
On 01/03/2012 23:13, Tom Lane wrote:
 Jehan-Guillaume (ioguix) de Rorthais j...@dalibo.com writes:
 One of our customer send us a patch he wrote for his needs (on
 src/bin/scripts/vacuumdb.c, no doc were included).
 
 He's using one schema per application and would like to be able to run
 vacuumdb on each of them independently so he added the --schema|-n
 option and send us the patch.
 
 Reviewing his patch, I thought it would be more useful to allow multi
 --table|-t options on the command line first. It might be possible to
 pass an array of tables to vacuum_one_database function instead of
 just one.
 
 Then, we could add the --schema|-n option which would fetch and build
 the table list and call vacuum_one_database.
 
 But before I start writing this patch, I would like some opinion, pros /
 cons. Do you think such a feature could be accepted in official
 PostgreSQL code or should we keep this as an external script ?
 
 I think most of us see vacuumdb as a historical leftover.  We keep it
 around in case anyone is still relying on it, but improving it seems
 like misdirected effort.
 
 Why isn't your customer using autovacuum?  If there are concrete
 reasons why that doesn't get the job done for him, it would be more
 useful in the long run to work on fixing that.

I usually advice to use both of them: vacuumdb and autovacuum (with
tuning if needed).

Autovacuum is launching its workers whenever tables need some
maintenance. It is usually pretty discrete in most case, but not all of
them (cf. Kevin Grittner email) and you cannot control when these
workers are launched.

As a DBA you know when you can afford some maintenances during the
day/week/month WRT your db activity and plan them accordingly. Hence,
running vacuumdb from crontab will just do autovacuum's job in some
situation avoiding a random worker during the day.

   regards, tom lane

-- 
Jehan-Guillaume (ioguix) de Rorthais
http://www.dalibo.com

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


Re: [HACKERS] Allowing multi -t and adding -n to vacuumdb ?

2012-03-01 Thread Tom Lane
Jehan-Guillaume (ioguix) de Rorthais j...@dalibo.com writes:
 One of our customer send us a patch he wrote for his needs (on
 src/bin/scripts/vacuumdb.c, no doc were included).

 He's using one schema per application and would like to be able to run
 vacuumdb on each of them independently so he added the --schema|-n
 option and send us the patch.

 Reviewing his patch, I thought it would be more useful to allow multi
 --table|-t options on the command line first. It might be possible to
 pass an array of tables to vacuum_one_database function instead of
 just one.

 Then, we could add the --schema|-n option which would fetch and build
 the table list and call vacuum_one_database.

 But before I start writing this patch, I would like some opinion, pros /
 cons. Do you think such a feature could be accepted in official
 PostgreSQL code or should we keep this as an external script ?

I think most of us see vacuumdb as a historical leftover.  We keep it
around in case anyone is still relying on it, but improving it seems
like misdirected effort.

Why isn't your customer using autovacuum?  If there are concrete
reasons why that doesn't get the job done for him, it would be more
useful in the long run to work on fixing that.

regards, tom lane

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


Re: [HACKERS] Allowing multi -t and adding -n to vacuumdb ?

2012-03-01 Thread Kevin Grittner
Tom Lane t...@sss.pgh.pa.us wrote:
 
 Why isn't your customer using autovacuum?  If there are concrete
 reasons why that doesn't get the job done for him, it would be
 more useful in the long run to work on fixing that.
 
FWIW, we're using autovacuum here, at slightly more aggressive
settings from the default, and we still rely on manual vacuums for
two main reasons:
 
(1)  VACUUM FREEZE ANALYZE after a bulk load to avoid the hint bit
rewrite costs for end users and the unpredictable anti-wraparound
autovacuum when all the loaded data suddenly hits the freeze
threshold.
 
(2)  For base backups of databases across a slow WAN, we do a diff
rsync against a copy of the hot standby here.  (Well, actually, to
save space we do it against a hard-link copy of the previous base
backup against which we have run a diff rsync from the hot
standby.)  If we don't do a VACUUM FREEZE ANALYZE before each base
backup, it at least doubles the size of base backups, due to the
hint bit and xmin freeze changes that occur after the initial copy
of a tuple is backed up.
 
Simon's recent work, if it makes it in, will deal with (1), and it
may be possible to deal with (2) using much more aggressive
configurations for autovacuum, although I suspect it might take
another tweak or two to the back end to really cover that without
manual vacuums.
 
-Kevin

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