Thread: pgAdmin complains about vacuuming required after fresh 8.1 install
Hi all - I am wondering if I can get a consensus on what to do about this minor issue. I have looked through the archives and can't find a definitive answer. So I have a new 8.1 install on Linux (have not yet been able to upgrade to 8.3). The documentation say that autovacuum is enabled by default in 8.1 and sure enough I see messages in the logs that autovacuum is "processing database "postgres"", etc... In my postgresql.conf I see 'autovacuum = on', 'stats_start_collector = on', and 'stats_row_level = on' However, despite all this pgAdmin still gives me messages on certain tables recommending a vacuum to be run. I see some messages saying that you need to run a VACUUM ANALYZE every week or night to 'make sure things are up to date', but then in the commits I see a comment: "Update documentation to mention that autovacuum also does analyze so we don't need to recommend nightly analyzes anymore unless autovacuum is off." So I am looking for the definitive answer on this. Is pgAdmin wrong and I should ignore the messages? Is autovacuum not fully running? Do they just have different threshold values and pgadmin is a bit pickier? Regards, Collin
Bump Does anyone have *any* thoughts on this? This seems to be a fairly common problem. Does anybody have any good links that they can provide to find an answer? My current test is that I have a table where all the rows were purged, and then new ones inserted using a specific job. pgAdmin reports 0 estimated rows and 46 counted rows and therefore displays the popup saying a vacuum should be run. I see in the PostgreSQL log that autovacuum is vacuuming this database regularly. Is this simply because pgAdmin has tighter settings and autovacuum hasn't actually done anything with this table yet? On Thu, Jun 5, 2008 at 5:47 PM, Collin Peters <cadiolis@gmail.com> wrote: > Hi all - I am wondering if I can get a consensus on what to do about > this minor issue. I have looked through the archives and can't find a > definitive answer. > > So I have a new 8.1 install on Linux (have not yet been able to > upgrade to 8.3). The documentation say that autovacuum is enabled by > default in 8.1 and sure enough I see messages in the logs that > autovacuum is "processing database "postgres"", etc... > > In my postgresql.conf I see 'autovacuum = on', 'stats_start_collector > = on', and 'stats_row_level = on' > > However, despite all this pgAdmin still gives me messages on certain > tables recommending a vacuum to be run. I see some messages saying > that you need to run a VACUUM ANALYZE every week or night to 'make > sure things are up to date', but then in the commits I see a comment: > "Update documentation to mention that autovacuum also does analyze so > we don't need to recommend nightly analyzes anymore unless autovacuum > is off." > > So I am looking for the definitive answer on this. Is pgAdmin wrong > and I should ignore the messages? Is autovacuum not fully running? > Do they just have different threshold values and pgadmin is a bit > pickier? > > Regards, > Collin >
On Thu, Jun 12, 2008 at 7:18 AM, Collin Peters <cadiolis@gmail.com> wrote:
> Bump
>
> Does anyone have *any* thoughts on this? This seems to be a fairly
> common problem. Does anybody have any good links that they can
> provide to find an answer?
>
> My current test is that I have a table where all the rows were purged,
> and then new ones inserted using a specific job. pgAdmin reports 0
> estimated rows and 46 counted rows and therefore displays the popup
> saying a vacuum should be run. I see in the PostgreSQL log that
> autovacuum is vacuuming this database regularly.
>
> Is this simply because pgAdmin has tighter settings and autovacuum
> hasn't actually done anything with this table yet?
>
pgAdmin's code for that is far older than PostgreSQL's:
bool pgTable::GetVacuumHint()
{
bool canHint=false;
if (rowsCounted)
{
if (!estimatedRows || (estimatedRows == 1000 &&
rows.GetValue() != 1000))
canHint = (rows >= 20);
else
{
double rowsDbl=(wxLongLong_t)rows.GetValue();
double quot=rowsDbl *10. / estimatedRows;
canHint = ((quot > 12 || quot < 8) && (rowsDbl <
estimatedRows-20. || rowsDbl > estimatedRows+20.));
}
}
else if (estimatedRows == 1000)
{
canHint = true;
}
return canHint;
}
in there, estimatedRows is the number of rows noted in the pg_class
entry, and rows is the number of rows actually counted in the table,
if rowsCounted is true (which will be the case if you've forced a
count from the menu, or the row count threshold (under File ->
Options) is >= estimatedRows) .
Suggestions for improvements are welcome.
--
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com
Collin Peters a écrit : > Does anyone have *any* thoughts on this? This seems to be a fairly > common problem. Does anybody have any good links that they can > provide to find an answer? > > My current test is that I have a table where all the rows were purged, > and then new ones inserted using a specific job. pgAdmin reports 0 > estimated rows and 46 counted rows and therefore displays the popup > saying a vacuum should be run. I see in the PostgreSQL log that > autovacuum is vacuuming this database regularly. > I don't think so. Your previous message says you see messages like "processing database X", which doesn't mean the database is vacuumed. It just says that the autovacuum process connects to this database and tries to find if a table needs vacuum (according to the parameters). How many rows did you purge in your database ? if it's less than autovacuum_vacuum_threshold, then you're sure your table has not been vacuumed. > Is this simply because pgAdmin has tighter settings and autovacuum > hasn't actually done anything with this table yet? > They don't use the same algorithms. pgAdmin's algorithm is this one : it shows the hint if estimated rows (reltuples in pg_class) is equal to 1000 or, if he knows the exact number of rows, if the exact number of rows is different from the estimated rows but greater than 20 or another complex calculation. autovacuum's algorithm use some parameters and some PostgreSQL statistics. Read http://www.postgresql.org/docs/8.3/interactive/runtime-config-autovacuum.html and http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#AUTOVACUUM for more details. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
On Thu, Jun 12, 2008 at 9:30 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > > They don't use the same algorithms. > Which, it should be noted, is intentional, so pgAdmin has a chance to warn you if your autovac settings are screwy. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com