Re: Problems with autovacuum

From: Alvaro Herrera
Subject: Re: Problems with autovacuum
Date: ,
Msg-id: 20090608193642.GH5598@alvh.no-ip.org
(view: Whole thread, Raw)
In response to: Re: Problems with autovacuum  (Tom Lane)
Responses: Re: Problems with autovacuum  (Tom Lane)
List: pgsql-performance

Tree view

Problems with autovacuum  (Łukasz Jagiełło, )
 Re: Problems with autovacuum  (Scott Marlowe, )
  Re: Problems with autovacuum  (Grzegorz Jaśkiewicz, )
   Re: Problems with autovacuum  (Scott Marlowe, )
   Re: Problems with autovacuum  (Łukasz Jagiełło, )
    Re: Problems with autovacuum  (Tom Lane, )
     Re: Problems with autovacuum  (Łukasz Jagiełło, )
     Re: Problems with autovacuum  (Alvaro Herrera, )
      Re: Problems with autovacuum  (Tom Lane, )
       Re: Problems with autovacuum  (Alvaro Herrera, )
        Re: Problems with autovacuum  (Tom Lane, )
         Re: Problems with autovacuum  (Alvaro Herrera, )
          Re: Problems with autovacuum  (Tom Lane, )
           Re: Problems with autovacuum  (Alvaro Herrera, )
            Re: Problems with autovacuum  (Tom Lane, )
             Re: Problems with autovacuum  (Scott Marlowe, )
             Re: Problems with autovacuum  (Alvaro Herrera, )
              Re: Problems with autovacuum  (Tom Lane, )
               Re: Problems with autovacuum  (Alvaro Herrera, )
                Re: Problems with autovacuum  (Alvaro Herrera, )
                Re: Problems with autovacuum  (Tom Lane, )
       Re: Problems with autovacuum  (Łukasz Jagiełło, )
  Re: Problems with autovacuum  (Łukasz Jagiełło, )
   Re: Problems with autovacuum  (Grzegorz Jaśkiewicz, )

Tom Lane escribió:

> Bingo, that's surely exactly what was happening to the OP.  He had 2000
> databases and naptime at (I assume) the default; so he was rerunning
> rebuild_database_list every 100ms.
>
> So that recovery code path needs some more thought.  Maybe a lower bound
> on how often to do rebuild_database_list?  And/or don't set adl_next_worker
> to less than 100ms in the future to begin with?

I've been giving this some thought and tried several approaches.  In the
end the one that I like the most is raising autovacuum_naptime to a
reasonable value for the exiting number of databases.  The only problem
I have with it is that it's trivial to change it in the autovacuum
launcher process and have it stick, but there's no way to propagate the
value out to backends or postmaster to that they SHOW the actual value
in use by the launcher.  The best I can do is emit a WARNING with the
new value.

I have experimented with other choices such as not rebuilding the
database list if the time elapsed since last rebuild is not very long,
but there were small problems with that so I'd prefer to avoid it.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Attachment

pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Problems with autovacuum
From: Віталій Тимчишин
Date:
Subject: Re: Pointers needed on optimizing slow SQL statements