Thread: Auto vacuum
I asked questions about auto vacuum earlier. As I said in my earlier email, when I select autovacuum the system responds that it's "on." Some here told me when I asked the questions before that I should see log entries for it when it "kicks off." In postgresql.conf, it says that the log_destination is 'stderr." Then it redirects "stderr" to a file named 'postgresql -%Y-%m-%d_%H%M%S.log' in a directory called pg_log. I don't see any references to auto-vacuum in the log. What would the references look like? Is there a parameter I need to set in order to make in put auto vacuum entries in the log? client_min_messages is set to "notice." log_min_messages is set to "info." log_error_verbosity is set to "default." log_min_error_statement is set to "warning." log_min_duration_statement is set to 1000 Thanks Carol
Carol Walter <walterc@indiana.edu> writes: > I don't see any references to auto-vacuum in the log. What > would the references look like? Which PG version are you running, exactly? regards, tom lane
8.2.3 is what is reported when I run psql with the -V option. Thanks, Carol On Apr 17, 2007, at 4:14 PM, Tom Lane wrote: > Carol Walter <walterc@indiana.edu> writes: >> I don't see any references to auto-vacuum in the log. What >> would the references look like? > > Which PG version are you running, exactly? > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly
Carol Walter <walterc@indiana.edu> writes: > 8.2.3 is what is reported when I run psql with the -V option. In 8.2 you need to increase log_min_messages to see anything about autovacuum. It reports per-database activity at DEBUG1 and per-table activity at DEBUG2 level. Unfortunately DEBUG2 will fill your log with a lot of other chatter as well :-(. There's work going on right now to improve the reporting of autovac activity for 8.3. regards, tom lane
Robert, I did as you suggested and the values for autovacuum are null, therefore I'm guessing it isn't running. Since postgres reports that it is running, what other things might keep in from running? Carol Walter On Apr 17, 2007, at 9:57 PM, Robert Treat wrote: > On Tuesday 17 April 2007 16:30, Tom Lane wrote: >> Carol Walter <walterc@indiana.edu> writes: >>> 8.2.3 is what is reported when I run psql with the -V option. >> >> In 8.2 you need to increase log_min_messages to see anything about >> autovacuum. It reports per-database activity at DEBUG1 and per-table >> activity at DEBUG2 level. Unfortunately DEBUG2 will fill your log >> with a lot of other chatter as well :-(. There's work going on >> right now to improve the reporting of autovac activity for 8.3. >> > > Till then, take a look at pg_stat_all_tables, which contains > information on > last autovacuum and autoanalyze runs for each table. > > -- > Robert Treat > Database Architect > http://www.omniti.com
Carol Walter wrote: > Robert, > > I did as you suggested and the values for autovacuum are null, > therefore I'm guessing it isn't running. Since postgres reports that > it is running, what other things might keep in from running? Too high thresholds? All tables disabled in pg_autovacuum? naptime set too high? I suggest you turn log_min_messages more verbose, to DEBUG2, and see if there are any entries in there regarding autovacuum. If it's running, you would see it. You would get a message after autovacuum_naptime seconds. How long is autovacuum_naptime anyway? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
I certainly appreciate the help you've given. Here's what the code in this part of postgresql.conf file says. autovacuum = on autovacuum_naptime = 30 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 500 # min # of tuple updates before # vacuum autovacuum_analyze_threshold = 250 # min # of tuple updates before # analyze autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit Thanks so much, Carol Walter On Apr 19, 2007, at 10:53 AM, Alvaro Herrera wrote: > Carol Walter wrote: >> Robert, >> >> I did as you suggested and the values for autovacuum are null, >> therefore I'm guessing it isn't running. Since postgres reports that >> it is running, what other things might keep in from running? > > Too high thresholds? All tables disabled in pg_autovacuum? > naptime set > too high? > > I suggest you turn log_min_messages more verbose, to DEBUG2, and > see if > there are any entries in there regarding autovacuum. If it's running, > you would see it. You would get a message after autovacuum_naptime > seconds. > > How long is autovacuum_naptime anyway? > > -- > Alvaro Herrera http:// > www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
My pg_catalog.pg_autovacuum table contains no entries. Carol Walter > > On Apr 19, 2007, at 10:53 AM, Alvaro Herrera wrote: > >> Carol Walter wrote: >>> Robert, >>> >>> I did as you suggested and the values for autovacuum are null, >>> therefore I'm guessing it isn't running. Since postgres reports >>> that >>> it is running, what other things might keep in from running? >> >> Too high thresholds? All tables disabled in pg_autovacuum? >> naptime set >> too high? >> >> I suggest you turn log_min_messages more verbose, to DEBUG2, and >> see if >> there are any entries in there regarding autovacuum. If it's >> running, >> you would see it. You would get a message after autovacuum_naptime >> seconds. >> >> How long is autovacuum_naptime anyway? >> >> -- >> Alvaro Herrera http:// >> www.CommandPrompt.com/ >> PostgreSQL Replication, Consulting, Custom Development, 24x7 support >> >> ---------------------------(end of >> broadcast)--------------------------- >> TIP 9: In versions below 8.0, the planner will ignore your desire to >> choose an index scan if your joining column's datatypes do not >> match > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
Carol Walter wrote: > My pg_catalog.pg_autovacuum table contains no entries. Good. Now, are there autovacuum entries in the server log? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Are you talking about the log that is configured in postgresql.conf? it goes to stderr and is redirected to pg_log directory with a time and date stamp in the filename. It doesn't seem to logging anything but planner statistics. Carol On Apr 19, 2007, at 3:55 PM, Alvaro Herrera wrote: > Carol Walter wrote: >> My pg_catalog.pg_autovacuum table contains no entries. > > Good. > > Now, are there autovacuum entries in the server log? > > -- > Alvaro Herrera http:// > www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Carol Walter wrote: > Are you talking about the log that is configured in postgresql.conf? > it goes to stderr and is redirected to pg_log directory with a time > and date stamp in the filename. It doesn't seem to logging anything > but planner statistics. Yes, that one. If it's not logging anything but planner statistics, then maybe those are the only things configured to be logged, which would be weird but I think it's possible. Change log_min_messages to debug2 and try again. A lot more messages should show up, including some about autovacuum. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I'm not sure if we've done a reload. I'm in an academic environment and our department is in a state of flux right now. I'm in the process of trying to get up to speed on postgreSQL as fast as I can. Here is the result set from that query. select * from pg_settings where name = 'autovacuum'; name | setting | unit | category | short_desc | extra_desc | context | vartype | source | min_val | max_val ------------+---------+------+------------ +-----------------------------------+- -----------+---------+---------+--------------------+---------+--------- autovacuum | on | | Autovacuum | Starts the autovacuum subprocess. | | sighup | bool | configuration file | | (1 row) Thank you, Carol On Apr 19, 2007, at 4:59 PM, Robert Treat wrote: > On Thursday 19 April 2007 13:54, Carol Walter wrote: >> I certainly appreciate the help you've given. Here's what the code >> in this part of postgresql.conf file says. >> >> autovacuum = on >> >> autovacuum_naptime = 30 # time between autovacuum runs, in >> secs >> autovacuum_vacuum_threshold = 500 # min # of tuple updates >> before >> # vacuum >> autovacuum_analyze_threshold = 250 # min # of tuple updates >> before >> # analyze >> autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before >> # vacuum >> autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before >> # analyze >> autovacuum_vacuum_cost_delay = -1 # default vacuum cost >> delay for >> # autovac, -1 means use >> # vacuum_cost_delay >> #autovacuum_vacuum_cost_limit = -1 # default vacuum cost >> limit for >> # autovac, -1 means use >> # vacuum_cost_limit >> > > Do you know if you have done a reload of the config files since > these were > changed? Try doing a select * from pg_settings where name ~ > 'autovacuum' to > see what your db is currently using. > > -- > Robert Treat > Database Architect > http://www.omniti.com
Carol Walter wrote: > I'm not sure if we've done a reload. I'm in an academic environment > and our department is in a state of flux right now. I'm in the > process of trying to get up to speed on postgreSQL as fast as I can. > Here is the result set from that query. > > select * from pg_settings where name = 'autovacuum'; Actually could you please try select * from pg_settings where name like 'autovacuum%'; so that all the other settings show up as well? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Yes, Here's the results table name | setting | unit | category | short_desc | ex tra_desc | context | vartype | source | min_val | max_val ---------------------------------+-----------+------+------------ +-------------- ------------------------------------------------------------------------ ----+--- ---------+------------+---------+--------------------+----------- +------------ autovacuum | on | | Autovacuum | Starts the au tovacuum subprocess. | | sighup | bool | configuration file | | autovacuum_analyze_scale_factor | 0.1 | | Autovacuum | Number of tup le inserts, updates or deletes prior to analyze as a fraction of reltuples. | | sighup | real | configuration file | 0 | 100 autovacuum_analyze_threshold | 250 | | Autovacuum | Minimum numbe r of tuple inserts, updates or deletes prior to analyze. | | sighup | integer | configuration file | 0 | 2147483647 autovacuum_freeze_max_age | 200000000 | | Autovacuum | Age at which to autovacuum a table to prevent transaction ID wraparound. | | postmaster | integer | default | 100000000 | 2000000000 autovacuum_naptime | 30 | s | Autovacuum | Time to sleep between autovacuum runs, in seconds. | | sighup | integer | configuration file | 1 | 2147483647 autovacuum_vacuum_cost_delay | -1 | ms | Autovacuum | Vacuum cost d elay in milliseconds, for autovacuum. | | sighup | integer | configuration file | -1 | 1000 autovacuum_vacuum_cost_limit | -1 | | Autovacuum | Vacuum cost a mount available before napping, for autovacuum. | | sighup | integer | default | -1 | 10000 autovacuum_vacuum_scale_factor | 0.2 | | Autovacuum | Number of tup le updates or deletes prior to vacuum as a fraction of reltuples. | | sighup | real | configuration file | 0 | 100 autovacuum_vacuum_threshold | 500 | | Autovacuum | Minimum numbe r of tuple updates or deletes prior to vacuum. | | sighup | integer | configuration file | 0 | 2147483647 (9 rows) Got lots more data with that command. ;-) Carol Walter On Apr 19, 2007, at 5:35 PM, Alvaro Herrera wrote: > Carol Walter wrote: >> I'm not sure if we've done a reload. I'm in an academic environment >> and our department is in a state of flux right now. I'm in the >> process of trying to get up to speed on postgreSQL as fast as I can. >> Here is the result set from that query. >> >> select * from pg_settings where name = 'autovacuum'; > > Actually could you please try > > select * from pg_settings where name like 'autovacuum%'; > > so that all the other settings show up as well? > > -- > Alvaro Herrera http:// > www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > ---------------------------(end of > broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
On Tuesday 17 April 2007 16:30, Tom Lane wrote: > Carol Walter <walterc@indiana.edu> writes: > > 8.2.3 is what is reported when I run psql with the -V option. > > In 8.2 you need to increase log_min_messages to see anything about > autovacuum. It reports per-database activity at DEBUG1 and per-table > activity at DEBUG2 level. Unfortunately DEBUG2 will fill your log > with a lot of other chatter as well :-(. There's work going on > right now to improve the reporting of autovac activity for 8.3. > Till then, take a look at pg_stat_all_tables, which contains information on last autovacuum and autoanalyze runs for each table. -- Robert Treat Database Architect http://www.omniti.com
On Thursday 19 April 2007 13:54, Carol Walter wrote: > I certainly appreciate the help you've given. Here's what the code > in this part of postgresql.conf file says. > > autovacuum = on > > autovacuum_naptime = 30 # time between autovacuum runs, in secs > autovacuum_vacuum_threshold = 500 # min # of tuple updates before > # vacuum > autovacuum_analyze_threshold = 250 # min # of tuple updates before > # analyze > autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before > # vacuum > autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before > # analyze > autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for > # autovac, -1 means use > # vacuum_cost_delay > #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for > # autovac, -1 means use > # vacuum_cost_limit > Do you know if you have done a reload of the config files since these were changed? Try doing a select * from pg_settings where name ~ 'autovacuum' to see what your db is currently using. -- Robert Treat Database Architect http://www.omniti.com