Обсуждение: pg_autovacuum / pg_class
Hello, I have a simple question about auto-vacuuming. We are running a Postgres 8.1 and I've noticed that some of the tables are not analyzed by the pg_autovacuum daemon. That is - when I select the rows from pg_class, all the important values (relpages, reltuples) are 0. I've noticed this in case of newly created tables (that is not older than 1 month). My 'theory' is this - the table has to be analyzed by hand at the beginning, otherwise the pg_autovacuum won't process it. Am I right? We use default values for all pg_autovacuum related parameters in postgresql.conf, and there are no rows in pg_autovacuum. The new tables are growing pretty fast (about 1.000.000 of rows each month), so the thresholds should be exceeded pretty very fast. But maybe this is caused by some stupid misconfiguration and/or is a known feature ... thanks for all your advices Tomas
Tomas Vondra wrote: > Hello, > > I have a simple question about auto-vacuuming. We are running a Postgres > 8.1 and I've noticed that some of the tables are not analyzed by the > pg_autovacuum daemon. That is - when I select the rows from pg_class, > all the important values (relpages, reltuples) are 0. > > I've noticed this in case of newly created tables (that is not older > than 1 month). My 'theory' is this - the table has to be analyzed by > hand at the beginning, otherwise the pg_autovacuum won't process it. Am > I right? Hum. Just by inserting tuples into those tables, autovacuum should "be able to see" them. Autovacuum won't touch, and indeed won't even consider, tables that are just created. But as soon as you insert a single tuple in them, it will start to consider vacuuming or analyzing them. If you insert a hundred thousand rows in a table, autovacuum most certainly should analyze it. If it's not, we'd really like to know why. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I feel very ashamed, as I've found the reason - the pg_autovacuum was not running at all :( About month ago we've upgraded to PG 8.1, since then the database was running without a restart but noone noticed the warning about enabling stats_start_collector/stats_row_level. I've noticed that only for the new tables (having 0 values in all columns), as most of the other tables are not changing very frequently (resp. have about the same number of inserts and deletes). So now the pg_autovacuum is up and running, and I'll check how it works tomorrow. But I guess this time it'll be fine. Tomas > Tomas Vondra wrote: >> Hello, >> >> I have a simple question about auto-vacuuming. We are running a Postgres >> 8.1 and I've noticed that some of the tables are not analyzed by the >> pg_autovacuum daemon. That is - when I select the rows from pg_class, >> all the important values (relpages, reltuples) are 0. >> >> I've noticed this in case of newly created tables (that is not older >> than 1 month). My 'theory' is this - the table has to be analyzed by >> hand at the beginning, otherwise the pg_autovacuum won't process it. Am >> I right? > > Hum. Just by inserting tuples into those tables, autovacuum should "be > able to see" them. Autovacuum won't touch, and indeed won't even > consider, tables that are just created. But as soon as you insert a > single tuple in them, it will start to consider vacuuming or analyzing > them. > > If you insert a hundred thousand rows in a table, autovacuum most > certainly should analyze it. If it's not, we'd really like to know why. >
> Tomas Vondra wrote: >> Hello, >> >> I have a simple question about auto-vacuuming. We are running a Postgres >> 8.1 and I've noticed that some of the tables are not analyzed by the >> pg_autovacuum daemon. That is - when I select the rows from pg_class, >> all the important values (relpages, reltuples) are 0. >> >> I've noticed this in case of newly created tables (that is not older >> than 1 month). My 'theory' is this - the table has to be analyzed by >> hand at the beginning, otherwise the pg_autovacuum won't process it. Am >> I right? > > Hum. Just by inserting tuples into those tables, autovacuum should "be > able to see" them. Autovacuum won't touch, and indeed won't even > consider, tables that are just created. But as soon as you insert a > single tuple in them, it will start to consider vacuuming or analyzing > them. > > If you insert a hundred thousand rows in a table, autovacuum most > certainly should analyze it. If it's not, we'd really like to know why. I feel very ashamed, as I've found the reason - the pg_autovacuum was not running at all :( About month ago we've upgraded to PG 8.1, since then the database was running without a restart but noone noticed the warning about enabling stats_start_collector/stats_row_level. I've noticed that only for the new tables (having 0 values in all columns), as most of the other tables are not changing very frequently (resp. have about the same number of inserts and deletes). So now the pg_autovacuum is up and running, and I'll check how it works tomorrow. But I guess this time it'll be fine. Tomas