Thread: Simple thing to make pg_autovacuum more useful
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hello, Table "pg_catalog.pg_autovacuum" Column | Type | Modifiers - ------------------+---------+-----------vacrelid | oid | not nullenabled | boolean | not nullvac_base_thresh | integer | not nullvac_scale_factor | real | not nullanl_base_thresh | integer | not nullanl_scale_factor| real | not nullvac_cost_delay | integer | not nullvac_cost_limit | integer | not nullfreeze_min_age | integer | not nullfreeze_max_age | integer | not null Can we by default set vac_cost_limit and vac_cost_delay have a DEFAULT - -1? That way by default it will use the settings in postgresql.conf? Secondly can we set the default for freeze_min_age to 100mil (I think that is the default in the docs) and freeize_max_age to the default 200000000 . In the environments we manage we are using pg_autovacuum alot in order to manage individual relations. This is probably too late for 8.3 :( but it should would make our lives easier. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHj8ayATb/zqfZUUQRAjHqAJ0bkc/ik4mZ0JldO6NLLjDKDDvLogCgqCK6 er+zFTHiFO1Fbi/BXzkWCqA= =7swT -----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes: > Can we by default set vac_cost_limit and vac_cost_delay have a DEFAULT > - -1? That way by default it will use the settings in postgresql.conf? Surely we're not going to force initdb for that. > Secondly can we set the default for freeze_min_age to 100mil (I think > that is the default in the docs) and freeize_max_age to the default > 200000000 . This is an outright bad idea, because those defaults aren't hard-wired. Why would you not use -1 if you want a default value? regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 17 Jan 2008 16:54:47 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > > - -1? That way by default it will use the settings in > > postgresql.conf? > > Surely we're not going to force initdb for that. I didn't realize it would take that so sure lets do it for 8.4. > > > Secondly can we set the default for freeze_min_age to 100mil (I > > think that is the default in the docs) and freeize_max_age to the > > default 200000000 . > > This is an outright bad idea, because those defaults aren't > hard-wired. Why would you not use -1 if you want a default value? There are two things here. One having a default value 8.2 currently doesn't (8.3 I don't know). Two what the default value should be. I will happily defer to you on that determination. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHj9FuATb/zqfZUUQRAlt3AKCWo+v26U2iyD2wpoRAQxFlmHwfWwCePyYf qQcaopPJ1ocYe+kcKIwEWYM= =AtNk -----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes: > Tom Lane <tgl@sss.pgh.pa.us> wrote: > There are two things here. One having a default value 8.2 currently > doesn't I'm not really convinced by this argument. pg_autovacuum was never designed to be user-friendly; it is designed to be the back end storage for something that *is* user-friendly (which we have not got yet). I would say that smarts about defaults ought to go into whatever code you are creating that inserts into pg_autovacuum. regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 17 Jan 2008 17:13:52 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: > > Tom Lane <tgl@sss.pgh.pa.us> wrote: > > There are two things here. One having a default value 8.2 currently > > doesn't > > I'm not really convinced by this argument. pg_autovacuum was never > designed to be user-friendly; it is designed to be the back end > storage for something that *is* user-friendly (which we have not got > yet). I would say that smarts about defaults ought to go into > whatever code you are creating that inserts into pg_autovacuum. My insert statements don't agree ;). I can type it, I can script it. It's not that big of a deal but consider what I am asking provides only a positive return that isn't out of line with the scope of autovacuum configuration in general. Your objection is let's keep it as difficult as possible within the existing paradigm because nobody thought pg_autovacuum could be useful in the first place. That seems incorrect to me as we continue to have people that are going to need to use pg_autovacuum. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHj9csATb/zqfZUUQRAlpAAJ4lJcj2pqqaAVz+x8SD6DpKH3cWUgCgiAfL edr2JA2sl72rzH2ogGHxpy8= =9FO8 -----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes: > Your objection is let's keep it as difficult as possible within the > existing paradigm because nobody thought pg_autovacuum could be useful > in the first place. No, my point is that there's no value in putting band-aids on an object that was never designed to be user-friendly. The extra ease of use from putting defaults on that table's columns is insignificant compared to what we'd get by fixing its *real* problems: * superuser-only, no mechanism to let users admin their own tables (nor any way to reconcile user-set values with a DBA'spossible wish to override them) * no support for dumping and restoring settings I don't think we should be encouraging direct manual insertions into pg_autovacuum in any case. So I'd rather see some effort spent on figuring out what the API really *should* look like. I don't know, other than that it should hard-wire as little as possible because we are likely to be changing the set of available parameters in future. Maybe we need a concept like per-table settings for GUC variables? regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 17 Jan 2008 17:38:57 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Joshua D. Drake" <jd@commandprompt.com> writes: > > Your objection is let's keep it as difficult as possible within the > > existing paradigm because nobody thought pg_autovacuum could be > > useful in the first place. > > No, my point is that there's no value in putting band-aids on an > object that was never designed to be user-friendly. The extra ease > of use from putting defaults on that table's columns is insignificant > compared to what we'd get by fixing its *real* problems: > > * superuser-only, no mechanism to let users admin their own tables > (nor any way to reconcile user-set values with a DBA's possible > wish to override them) > * no support for dumping and restoring settings > > I don't think we should be encouraging direct manual insertions into > pg_autovacuum in any case. > > So I'd rather see some effort spent on figuring out what the API > really *should* look like. I don't know, other than that it should > hard-wire as little as possible because we are likely to be changing > the set of available parameters in future. Maybe we need a concept > like per-table settings for GUC variables? Tom I don't understand this. Your arguments above are great but let's be realistic. I am offering something that even *I* could do with code that is simple and useful. You are offering what appears to be a "solution". A perfectly valid one in fact. Which one is going to get done first? Which one is going to provide immediate benefit? I can't realistically code change the code for the first problem. I might be able to hack my way through the second, I guarantee you I could do my solution. So why is it such a bad thing to implement something incrementally useful? Especially considering my incremental solution doesn't conflict with your todo for pg_autovacuum? Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHj9wwATb/zqfZUUQRArj4AJ0e2ln+tul3Z7tUHMWuwSVfBC8q6ACgocP3 j5dKNnHaoClMJgJRV2mHFTA= =j3NJ -----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes: > You are offering what appears to be a "solution". A perfectly valid one > in fact. Which one is going to get done first? Which one is going to > provide immediate benefit? The problem is that your "immediate benefit" is to encourage people to do direct manual insertions into pg_autovacuum, which is something that we shouldn't be encouraging, because it's not the correct long-term solution. Or even short-term --- it seems reasonably likely to me that something could be done about building a decent API in the 8.4 cycle, which is the soonest we could entertain a proposal to put defaults on pg_autovacuum anyway. regards, tom lane
All this thread is a waste of time. We've previously agreed that we're going to store autovacuum per-table settings in pg_class.reloptions. That automatically gives it pg_dump support, and moreover it means the user needs not set the options that he/she doesn't want to change from defaults. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera <alvherre@commandprompt.com> writes: > All this thread is a waste of time. We've previously agreed that we're > going to store autovacuum per-table settings in pg_class.reloptions. I had forgotten that discussion. So the plan is for the pg_autovacuum catalog to go away entirely, correct? regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > All this thread is a waste of time. We've previously agreed that we're > > going to store autovacuum per-table settings in pg_class.reloptions. > > I had forgotten that discussion. So the plan is for the pg_autovacuum > catalog to go away entirely, correct? Yup. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > "Joshua D. Drake" <jd@commandprompt.com> writes: >> You are offering what appears to be a "solution". A perfectly valid one >> in fact. Which one is going to get done first? Which one is going to >> provide immediate benefit? > > The problem is that your "immediate benefit" is to encourage people > to do direct manual insertions into pg_autovacuum, which is something > that we shouldn't be encouraging, because it's not the correct long-term > solution. Or even short-term --- it seems reasonably likely to me that > something could be done about building a decent API in the 8.4 cycle, > which is the soonest we could entertain a proposal to put defaults on > pg_autovacuum anyway. Are you picturing adding ALTER TABLE commands to set autovacuum parameters? Or do you mean for tools like pgadmin to control this? Because the latter could happen even during the 8.3 cycle (though I perhaps not with pgadmin itself which I think follows the Postgres release cycle). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning
Gregory Stark <stark@enterprisedb.com> writes: > Are you picturing adding ALTER TABLE commands to set autovacuum > parameters? Well, as I said, I was trying to think of an appropriate user-visible API, which I didn't think that pg_autovacuum itself could become. Further downthread Alvaro points out that we've already had that discussion, and exploiting the reloptions infrastructure was agreed to be a reasonable solution. It took a bit of searching to find that thread, but here it is: http://archives.postgresql.org/pgsql-hackers/2007-02/msg01440.php regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 17 Jan 2008 21:43:46 -0300 Alvaro Herrera <alvherre@commandprompt.com> wrote: > Tom Lane wrote: > > Alvaro Herrera <alvherre@commandprompt.com> writes: > > > All this thread is a waste of time. We've previously agreed that > > > we're going to store autovacuum per-table settings in > > > pg_class.reloptions. > > > > I had forgotten that discussion. So the plan is for the > > pg_autovacuum catalog to go away entirely, correct? > > Yup. > Wait .reloptions is a column... how are we going to populate it and array? Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHkBosATb/zqfZUUQRAru/AJ90HXXRHP17GmfzGa1I5nc34VCNGQCeM5Gb 6CeL1LyVZuOvYgG/OAoz7rw= =EEuY -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Thu, 17 Jan 2008 20:34:07 -0500 Tom Lane <tgl@sss.pgh.pa.us> wrote: > Gregory Stark <stark@enterprisedb.com> writes: > > Are you picturing adding ALTER TABLE commands to set autovacuum > > parameters? > > Well, as I said, I was trying to think of an appropriate user-visible > API, which I didn't think that pg_autovacuum itself could become. > > Further downthread Alvaro points out that we've already had that > discussion, and exploiting the reloptions infrastructure was agreed > to be a reasonable solution. It took a bit of searching to find > that thread, but here it is: > http://archives.postgresql.org/pgsql-hackers/2007-02/msg01440.php Interesting. O.k. I must admit that I would sure rather use ALTER TABLE to do this than INSERT, SELECT. Let's not lose sight that we still need to be able to manage large sets of relations though. We need to make sure there is a readily available way to retrieve the data as well. Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHkBrAATb/zqfZUUQRApUHAJ9MvS3pcZAa2ix+8pCrmGQ4G1VdiQCaA+44 YwbncnAmby4pzCZqnRn6ANM= =cbaq -----END PGP SIGNATURE-----
On Fri, Jan 18, 2008 at 01:07:27AM +0000, Gregory Stark wrote: > "Tom Lane" <tgl@sss.pgh.pa.us> writes: > > > "Joshua D. Drake" <jd@commandprompt.com> writes: > >> You are offering what appears to be a "solution". A perfectly valid one > >> in fact. Which one is going to get done first? Which one is going to > >> provide immediate benefit? > > > > The problem is that your "immediate benefit" is to encourage people > > to do direct manual insertions into pg_autovacuum, which is something > > that we shouldn't be encouraging, because it's not the correct long-term > > solution. Or even short-term --- it seems reasonably likely to me that > > something could be done about building a decent API in the 8.4 cycle, > > which is the soonest we could entertain a proposal to put defaults on > > pg_autovacuum anyway. > > Are you picturing adding ALTER TABLE commands to set autovacuum parameters? Or > do you mean for tools like pgadmin to control this? Because the latter could > happen even during the 8.3 cycle (though I perhaps not with pgadmin itself > which I think follows the Postgres release cycle). Of course, pgadmin already does this :-P Ask that tall guy sitting next to you for a demo... What it doesn't do is help you figure out what values to put in... //Magnus
On 18/01/2008, Gregory Stark <stark@enterprisedb.com> wrote: > Are you picturing adding ALTER TABLE commands to set autovacuum parameters? Or > do you mean for tools like pgadmin to control this? Because the latter could > happen even during the 8.3 cycle (though I perhaps not with pgadmin itself > which I think follows the Postgres release cycle). pgAdmin already has an interface to pg_autovacuum. /D
On Thursday 17 January 2008 19:17:00 Joshua D. Drake wrote: > On Thu, 17 Jan 2008 21:43:46 -0300 > > Alvaro Herrera <alvherre@commandprompt.com> wrote: > > Tom Lane wrote: > > > Alvaro Herrera <alvherre@commandprompt.com> writes: > > > > All this thread is a waste of time. We've previously agreed that > > > > we're going to store autovacuum per-table settings in > > > > pg_class.reloptions. > > > > > > I had forgotten that discussion. So the plan is for the > > > pg_autovacuum catalog to go away entirely, correct? > > > > Yup. > > Wait .reloptions is a column... how are we going to populate it and > array? ALTER TABLE foo SET autovacuum to true; ALTER TABLE foo SET autovacuum_naptime to '1min'; (or there about) I suppose we could also have pg_autovacuum become a view, with rules to manage relopts. > > Sincerely, > > Joshua D. Drake -- Darcy Buskermolen Command Prompt, Inc. +1.503.667.4564 X 102 http://www.commandprompt.com/ PostgreSQL solutions since 1997
Added to TODO: o Store per-table autovacuum settings in pg_class.reloptions. http://archives.postgresql.org/pgsql-hackers/2007-02/msg01440.php http://archives.postgresql.org/pgsql-hackers/2008-01/msg00724.php --------------------------------------------------------------------------- Alvaro Herrera wrote: > All this thread is a waste of time. We've previously agreed that we're > going to store autovacuum per-table settings in pg_class.reloptions. > That automatically gives it pg_dump support, and moreover it means the > user needs not set the options that he/she doesn't want to change from > defaults. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +