Re: AutoVacuum Behaviour Question - Mailing list pgsql-general

Bruce McAlister wrote:

> I have just checked the pg_stat_all_tables in the pg_catalog schema and
> I can see the index scans etc table values incrementing. The data in the
> tables seems to be updating. Just an FYI, I've enabled manual vacuum
> analyze runs on the blueface-service database up until we've found whats
> going wrong here. The output from the select query you suggested is as
> follows:
>
>          datname         | datconfig
> -------------------------+-----------
>  postgres                |
>  blueface-webmail        |
[etc]

Ok so it's not that you manually disabled autovacuum.  And pgstat is
working on those databases.  And all databases share the postgresql.conf
autovacuum configuration.

> I enabled the log_line_prefix option and put in a %p in the value, and I
> only get the following output from the logs:
>
> Jun 26 23:46:07 bfiedb01 postgres[2836]: [ID 748848 local0.debug] [5-1]
>    2836 DEBUG:  autovacuum: processing database "blueface-crm"
> Jun 26 23:47:32 bfiedb01 postgres[2863]: [ID 748848 local0.debug] [5-1]
>    2863 DEBUG:  autovacuum: processing database "blueface-crm"

I assume that there is no other line for process 2836 before the line
for process 2863.  Can you recheck that?

What does
select datname, age(datfrozenxid) from pg_database;
show?


> I've also just run a manual 'VACUUM ANALYZE FULL VERBOSE;' just to
> ensure it goes through manually.

And it does finish successfully?


> What does the DEBUG1 output of a normal autovacuum run look like in the
> log file?

Nothing interesting shows up:

LOG:  autovacuum: processing database "test1"
LOG:  autovacuum: processing database "test2"

If you try with debug2, it looks a bit more interesting:

LOG:  autovacuum: processing database "test2"
DEBUG:  autovac: will VACUUM foo
DEBUG:  vacuuming "public.foo"
DEBUG:  "foo": removed 10000 row versions in 55 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
DEBUG:  "foo": found 10000 removable, 0 nonremovable row versions in 55 pages
DETAIL:  0 dead row versions cannot be removed yet.
        There were 0 unused item pointers.
        0 pages are entirely empty.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
DEBUG:  "foo": truncated 55 to 0 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.


The only thing I can think of right now is that pgstats does not have
entries for the other databases for some reason.  How can that happen
escapes me.

--
Alvaro Herrera                          Developer, http://www.PostgreSQL.org/
"Doing what he did amounts to sticking his fingers under the hood of the
implementation; if he gets his fingers burnt, it's his problem."  (Tom Lane)