Обсуждение: 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