Re: AutoVacuum Behaviour Question - Mailing list pgsql-general

Alvaro Herrera wrote:
> Bruce McAlister wrote:
>> Hi All,
>>
>> I have enabled autovacuum in our PostgreSQL cluster of databases. What I
>> have noticed is that the autovacuum process keeps selecting the same
>> database to perform autovacuums on and does not select any of the others
>> within the cluster. Is this normal behaviour or do I need to do
>> something more elaborate with my settings?
>
> There are two reasons autovacuum would keep picking up the same
> database:
>
> 1. the other databases do not have pgstat entries.
>
> 2. this database is in danger of Xid wraparound and the vacuum run
> fails to complete for some reason.
>
>> Our main concern is the "blueface-service" database. The sipaccounts
>> table has some high traffic, mainly updates.
>
> Are there non-null values in the pg_stat views for tables in
> blueface-service database?  If there are, then you can discard (1) as
> the problem.  If all values are nulls for all tables, then you have the
> stats collector disabled for that database, or something (maybe by ALTER
> DATABASE ... SET).  In this case, reenable it and issue a manual VACUUM
> so that pgstat is populated.  (I think the easiest way to check is
> SELECT datname, datconfig FROM pg_database).

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        |
 blueface-billingreports |
 blueface-service        |
 blueface-cards          |
 template1               |
 template0               |
 blueface-crmsupport     |
 blueface-qualmon        |
 asterisk-cdrgw          |
 hylafax                 |
 thelab-sipswitch        |
 whitelabel-ibb          |
 whitelabel-pleasant     |
 whitelabel-rapid        |
 whitelabel-test         |
 whitelabel-worlddest    |
 blueface-crm            |
 blueface-billedcalls    |
 asterisk-cdr            |
 mysipswitch             |
 whitelabel-ice          |



>
> Regarding (2) you would need to check whether the autovacuum run dies
> with an ERROR.  I'd advise setting a log_line_prefix that included the
> PID (%p) so that you can check whether the process goes away cleanly or
> it dies early.  It is not impossible that a corrupt index or table is
> causing autovacuum to die, but it should certainly show up in the logs.
>
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 cant really tell from these logs if the process is dying early or not.

I have also just run a 'REINDEX DATABASE "blueface-crm";' just to ensure
that the indexes are sane.

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

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

Any other suggestions would be greatly appreciated.

Thanks