Thread: pgAdmin complains about vacuuming required after fresh 8.1 install

pgAdmin complains about vacuuming required after fresh 8.1 install

From
"Collin Peters"
Date:
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

Re: pgAdmin complains about vacuuming required after fresh 8.1 install

From
"Collin Peters"
Date:
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
>

Re: pgAdmin complains about vacuuming required after fresh 8.1 install

From
"Dave Page"
Date:
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

Re: pgAdmin complains about vacuuming required after fresh 8.1 install

From
Guillaume Lelarge
Date:
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

Re: pgAdmin complains about vacuuming required after fresh 8.1 install

From
"Dave Page"
Date:
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