SEO

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


---------------------------(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

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

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


---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend