autovacuum blues

Anton Melser wrote:

Hi,
I just can't understand why autovacuum is not working. I have a test
db/table which I insert values into (by the thousands) and can't work
out why my stats don't get updated. Could someone have a quick look at
my attached .conf and tell me what I am doing?
I am running it on FC5 8.1.4.fc5.1.

1. Check "ps auxw | grep postgres" to see if it's running.
2. If so, check your PG logs and see if you see any activity. Turn
connection and statement logging on and see what appears.

Oh, and you might want to upgrade to 8.1.5 when convenient - that
shouldn't affect this though.

--
Richard Huxton
Archonet Ltd

On 09/11/06, Richard Huxton <dev@archonet.com> wrote:

Anton Melser wrote:

Hi,
I just can't understand why autovacuum is not working. I have a test
db/table which I insert values into (by the thousands) and can't work
out why my stats don't get updated. Could someone have a quick look at
my attached .conf and tell me what I am doing?
I am running it on FC5 8.1.4.fc5.1.

1. Check "ps auxw | grep postgres" to see if it's running.
2. If so, check your PG logs and see if you see any activity. Turn
connection and statement logging on and see what appears.

...
postgres 1300 0.0 1.1 20180 3048 ? S 12:03 0:00
/usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 1302 0.0 0.2 9968 544 ? S 12:03 0:00
postgres: logger process
postgres 1304 0.0 0.4 20316 1188 ? S 12:03 0:00
postgres: writer process
postgres 1305 0.0 0.6 10968 1544 ? S 12:03 0:00
postgres: stats buffer process
postgres 1306 0.0 0.3 10200 796 ? S 12:03 0:00
postgres: stats collector process
...
Any chance you could give me some pointers on activating logging? My
thoughts were to log
log_planner_stats = on
log_min_messages = info

Anything else?
Cheers
Antoine

Hi Anton!

I'm not sure how this is with 8.1 but on 7.4.14 we have to enable row level
statistics collection for autovacuum:
stats_row_level = true

Greetings,
Matthias

Anton Melser wrote:

Hi,
I just can't understand why autovacuum is not working. I have a test
db/table which I insert values into (by the thousands) and can't work
out why my stats don't get updated. Could someone have a quick look at
my attached .conf and tell me what I am doing?
I am running it on FC5 8.1.4.fc5.1.

You can tell whether autovacuum is running by using

SHOW autovacuum;

Note that if you have a firewall of some sort stopping UDP messages from
being transmitted inside your machine, the stats collector may not
start, which may stop the autovacuum daemon from starting. If you're
not seeing stat updates then there's probably something like that going
on. Maybe the system has emitted a warning message at server start;
check the logs.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On 09/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Anton Melser wrote:

Hi,
I just can't understand why autovacuum is not working. I have a test
db/table which I insert values into (by the thousands) and can't work
out why my stats don't get updated. Could someone have a quick look at
my attached .conf and tell me what I am doing?
I am running it on FC5 8.1.4.fc5.1.

You can tell whether autovacuum is running by using

SHOW autovacuum;

Note that if you have a firewall of some sort stopping UDP messages from
being transmitted inside your machine, the stats collector may not
start, which may stop the autovacuum daemon from starting. If you're
not seeing stat updates then there's probably something like that going
on. Maybe the system has emitted a warning message at server start;
check the logs.

Thanks for that. Just a clarification, can someone tell me what the
"number of tuples" means in the context of the multipliers? I mean,
when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I
get the min val, but what are we multiplying by 0.4? The total number
of tuples in the table? The total modified/deleted?
I guess it is just that I assumed that it would kick in regularly, and
it doesn't seem to. Probably it is not needed but I always got the
feeling that after a good vacuum performance seemed better.
Cheers
Antoine

On Thu, 2006-11-09 at 18:16 +0100, Anton Melser wrote:

On 09/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Anton Melser wrote:

Hi,
I just can't understand why autovacuum is not working. I have a test
db/table which I insert values into (by the thousands) and can't work
out why my stats don't get updated. Could someone have a quick look at
my attached .conf and tell me what I am doing?
I am running it on FC5 8.1.4.fc5.1.

You can tell whether autovacuum is running by using

SHOW autovacuum;

Note that if you have a firewall of some sort stopping UDP messages from
being transmitted inside your machine, the stats collector may not
start, which may stop the autovacuum daemon from starting. If you're
not seeing stat updates then there's probably something like that going
on. Maybe the system has emitted a warning message at server start;
check the logs.

Thanks for that. Just a clarification, can someone tell me what the
"number of tuples" means in the context of the multipliers? I mean,
when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I
get the min val, but what are we multiplying by 0.4? The total number
of tuples in the table? The total modified/deleted?

http://www.postgresql.org/docs/8.1/static/runtime-config-autovacuum.html

I read that to mean that it multiplies the total number of tuples in the
table by autovacuum_vacuum_scale_factor and adds the
autovacuum_vacuum_threshold to that number. If the number of
updated/deleted tuples exceeds that sum, autovacuum will issue a vacuum.

The docs could maybe be more clear on this.

Regards,
Jeff Davis

Anton Melser wrote:

Thanks for that. Just a clarification, can someone tell me what the
"number of tuples" means in the context of the multipliers? I mean,
when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I
get the min val, but what are we multiplying by 0.4? The total number
of tuples in the table? The total modified/deleted?

pg_class.reltuples

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 09/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Anton Melser wrote:

Thanks for that. Just a clarification, can someone tell me what the
"number of tuples" means in the context of the multipliers? I mean,
when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I
get the min val, but what are we multiplying by 0.4? The total number
of tuples in the table? The total modified/deleted?

pg_class.reltuples

And so...

For efficiency reasons, reltuples and relpages are not updated
on-the-fly, and so they usually contain somewhat out-of-date values.
They are updated by VACUUM, ANALYZE, and a few DDL commands such as
CREATE INDEX. A stand-alone ANALYZE, that is one not part of VACUUM,
generates an approximate reltuples value since it does not read every
row of the table. The planner will scale the values it finds in
pg_class to match the current physical table size, thus obtaining a
closer approximation.

So I am still a little unclear... I need to analyse to get relevant
stats for autovacuum... but autovacuum needs to be used to update the
relevant stats? Can I REALLY do without a cronjob, or am I just
thinking wishfully (I can invent expressions if I want!).
Cheers
Antoine

Anton Melser wrote:

On 09/11/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:

Anton Melser wrote:

Thanks for that. Just a clarification, can someone tell me what the
"number of tuples" means in the context of the multipliers? I mean,
when the vacuum min multiplier is at 0.4, we multiply 0.4 by what? I
get the min val, but what are we multiplying by 0.4? The total number
of tuples in the table? The total modified/deleted?

pg_class.reltuples

So I am still a little unclear... I need to analyse to get relevant
stats for autovacuum... but autovacuum needs to be used to update the
relevant stats? Can I REALLY do without a cronjob, or am I just
thinking wishfully (I can invent expressions if I want!).

This means that we use the previously-known value of tuples in the table, plus
the number of new tuples that have been inserted, deleted and/or updated
(numbers you can see in the pg_stat_* views), and compare them to the
thresholds. If I'm being too unclear, here is the relevant code comment:

* A table needs to be vacuumed if the number of dead tuples exceeds a
* threshold. This threshold is calculated as
*
* threshold = vac_base_thresh + vac_scale_factor * reltuples
*
* For analyze, the analysis done is that the number of tuples inserted,
* deleted and updated since the last analyze exceeds a threshold calculated
* in the same fashion as above. Note that the collector actually stores
* the number of tuples (both live and dead) that there were as of the last
* analyze. This is asymmetric to the VACUUM case.

(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/autovacuum.c?rev=1.28;content-type=text%2Fx-cvsweb-markup)

The idea is that you _can_ do without a cronjob. You may need to do a
first ANALYZE just to get things warmed up, and then let autovacuum do
its job.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

The idea is that you _can_ do without a cronjob. You may need to do a
first ANALYZE just to get things warmed up, and then let autovacuum do
its job.

Thanks guys, I think I have it now!
Cheers
Antoine