Thread: autovacuum?
Hello all, I usually run "vaccumdb --all --analyze --quiet" as a part of my daily maintenance. While reading up a little on vacuumdb, I saw a reference to "autovacuum", which seemed interesting. Apparently, by enabling autovacuum in postgresql.conf, there's a deamon that will check if vacuuming is required or not, and run the vacuuming process as needed. Quick questions: Do I simply need to enable autovacuum in the postgresql.conf file, or is there more to it? Will it vacuum all databases, or do I need to specify which ones? If so, how? -- Kind Regards, Jan Danielsson Te audire non possum. Musa sapientum fixa est in aure.
Attachment
Jan Danielsson wrote: [---] > Quick questions: Do I simply need to enable autovacuum in the > postgresql.conf file, or is there more to it? Will it vacuum all > databases, or do I need to specify which ones? If so, how? Ok, I finally found the relevant documentation. Seems I had to enable two other keywords in the postgresql.conf file. However, I still haven't figured out how I can actually verify that it *is* running the vacuum procedure. Any hints? -- Kind Regards, Jan Danielsson Te audire non possum. Musa sapientum fixa est in aure.
Attachment
Jan Danielsson wrote: > Hello all, > > I usually run "vaccumdb --all --analyze --quiet" as a part of my > daily maintenance. While reading up a little on vacuumdb, I saw a > reference to "autovacuum", which seemed interesting. > > Apparently, by enabling autovacuum in postgresql.conf, there's a > deamon that will check if vacuuming is required or not, and run the > vacuuming process as needed. > > Quick questions: Do I simply need to enable autovacuum in the > postgresql.conf file, or is there more to it? Will it vacuum all > databases, or do I need to specify which ones? If so, how? > Yes and no. Setting autovacuum=on will enable the autovacuum to work but you may want to adjust some other options to suit your needs. You will find about 7 other options (starting with autovacuum_) in postgresql.conf that you can use to fine tune the behaviour. http://www.postgresql.org/docs/8.1/interactive/runtime-config-autovacuum.html Normally it will be applied to every database but you can use the pg_autovacuum system catalog to limit it's use. http://www.postgresql.org/docs/current/interactive/catalog-pg-autovacuum.html
Jan Danielsson wrote: > Jan Danielsson wrote: > [---] >> Quick questions: Do I simply need to enable autovacuum in the >> postgresql.conf file, or is there more to it? Will it vacuum all >> databases, or do I need to specify which ones? If so, how? > > Ok, I finally found the relevant documentation. Seems I had to enable > two other keywords in the postgresql.conf file. However, I still haven't > figured out how I can actually verify that it *is* running the vacuum > procedure. Any hints? > > I'm not sure if there is a way. I would think detailed enough logs should show it up but that may have to be at debug level I am guessing. The only one I would think of is to check the stats - I would use pgAdmin and check the row counts on a table (these show up after a vacuum) before and after you know a number of rows have been inserted and see if that updates after a couple of minutes.
Shane Ambler wrote: [---] >> Ok, I finally found the relevant documentation. Seems I had to enable >> two other keywords in the postgresql.conf file. However, I still haven't >> figured out how I can actually verify that it *is* running the vacuum >> procedure. Any hints? >> >> > I'm not sure if there is a way. > > I would think detailed enough logs should show it up but that may have > to be at debug level I am guessing. > > The only one I would think of is to check the stats - I would use > pgAdmin and check the row counts on a table (these show up after a > vacuum) before and after you know a number of rows have been inserted > and see if that updates after a couple of minutes. Thanks for the tips. I'll try to crank up the level of logging a notch and see, and I'll also take a look at pgAdmin. -- Kind Regards, Jan Danielsson Te audire non possum. Musa sapientum fixa est in aure.