Thread: Allowing multi "-t" and adding "-n" to vacuumdb ?
Hi, 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 ? Thank you, -- Jehan-Guillaume (ioguix) de Rorthais http://www.dalibo.com
"Jehan-Guillaume (ioguix) de Rorthais" <jgdr@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
Tom Lane <tgl@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
Re: Allowing multi "-t" and adding "-n" to vacuumdb ?
From
"Jehan-Guillaume (ioguix) de Rorthais"
Date:
On 01/03/2012 23:13, Tom Lane wrote: > "Jehan-Guillaume (ioguix) de Rorthais" <jgdr@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