Re: Autovacuum / full vacuum

От: Andrew Sullivan
Тема: Re: Autovacuum / full vacuum
Дата: ,
Msg-id: 20060117181350.GN21092@phlogiston.dyndns.org
(см: обсуждение, исходный текст)
Ответ на: Re: Autovacuum / full vacuum  (Chris Browne)
Список: pgsql-performance

Скрыть дерево обсуждения

Autovacuum / full vacuum  (Michael Riess, )
 Re: Autovacuum / full vacuum  (Christopher Kings-Lynne, )
  Re: Autovacuum / full vacuum  (Michael Riess, )
   Re: Autovacuum / full vacuum  (Michael Stone, )
   Re: Autovacuum / full vacuum  ("Matthew T. O'Connor", )
    Re: Autovacuum / full vacuum  (Markus Schaber, )
    Re: Autovacuum / full vacuum  (Michael Riess, )
     Re: Autovacuum / full vacuum  (Tom Lane, )
     Re: Autovacuum / full vacuum  (Markus Schaber, )
    Re: Autovacuum / full vacuum  (Andrew Sullivan, )
  Re: Autovacuum / full vacuum  (Pandurangan R S, )
 Re: Autovacuum / full vacuum  (Alvaro Herrera <-ip.org>, )
 Re: Autovacuum / full vacuum  (Christopher Browne, )
  Re: Autovacuum / full vacuum  (Michael Riess, )
   Re: Autovacuum / full vacuum  (Andrew Sullivan, )
 Re: Autovacuum / full vacuum  ("Larry Rosenman", )
 Re: Autovacuum / full vacuum  (Christopher Browne, )
  Re: Autovacuum / full vacuum  (Michael Riess, )
   Re: Autovacuum / full vacuum  (Michael Stone, )
   Re: Autovacuum / full vacuum  (Andrew Sullivan, )
    Re: Autovacuum / full vacuum  (Scott Marlowe, )
     Re: Autovacuum / full vacuum  (Andrew Sullivan, )
      Re: Autovacuum / full vacuum  (Scott Marlowe, )
   Re: Autovacuum / full vacuum  (hubert depesz lubaczewski, )
   Re: Autovacuum / full vacuum  ("Jim C. Nasby", )
    Re: Autovacuum / full vacuum  (Michael Riess, )
     Re: Autovacuum / full vacuum  ("Jim C. Nasby", )
 Re: Autovacuum / full vacuum  (Andrew Sullivan, )
  Re: Autovacuum / full vacuum  (Michael Riess, )
   Re: Autovacuum / full vacuum  (Andrew Sullivan, )
 Re: Autovacuum / full vacuum  (Tom Lane, )
 Re: Autovacuum / full vacuum  (Chris Browne, )
  Re: Autovacuum / full vacuum  (Alvaro Herrera <-ip.org>, )
  Re: Autovacuum / full vacuum  (Andrew Sullivan, )
 Re: Autovacuum / full vacuum  (Chris Browne, )
  Re: Autovacuum / full vacuum  (Tom Lane, )
 Re: Autovacuum / full vacuum  ("Mindaugas", )
  Re: Autovacuum / full vacuum  (Alvaro Herrera <-ip.org>, )
 Re: Autovacuum / full vacuum  (Chris Browne, )
  Re: Autovacuum / full vacuum (off-topic?)  (Michael Crozier, )
   Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
    Re: Autovacuum / full vacuum (off-topic?)  (Michael Crozier, )
     Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
    Re: Autovacuum / full vacuum (off-topic?)  (Mark Kirkwood, )
     Re: Autovacuum / full vacuum (off-topic?)  (Bruce Momjian, )
     Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
      Re: Autovacuum / full vacuum (off-topic?)  ("Joshua D. Drake", )
       Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
        Re: Autovacuum / full vacuum (off-topic?)  ("Joshua D. Drake", )
         Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )
 Re: Autovacuum / full vacuum (off-topic?)  (Chris Browne, )
  Re: Autovacuum / full vacuum (off-topic?)  (Michael Crozier, )
 Re: Autovacuum / full vacuum (off-topic?)  (<>, )
  Re: Autovacuum / full vacuum (off-topic?)  ("Jim C. Nasby", )

On Tue, Jan 17, 2006 at 11:43:14AM -0500, Chris Browne wrote:
>  (Andrew Sullivan) writes:
> > Because nothing that runs automatically should ever take an exclusive
> > lock on the entire database,

> That's a bit more than what autovacuum would probably do...

Or even VACUUM FULL, as I tried to make clearer in another message:
the way I phrased it suggests that it's a simultaneous lock on the
entire database (when it is most certainly not).  I didn't intend to
mislead; my apologies.

Note, though, that the actual effect for a user might look worse
than a lock on the entire database, though, if you conider
statement_timeout and certain use patterns.

Suppose you want to issue occasional VACCUM FULLs, but your
application is prepared for this, and depends on statement_timeout to
tell it "sorry, too long, try again".  Now, if the exclusive lock on
any given table takes less than statement_timeout, so that each
statement is able to continue in its time, the application looks like
it's having an outage _even though_ it is actually blocked on
vacuums.  (Yes, it's poor application design.  There's plenty of that
in the world, and you can't always fix it.)

> There is *a* case for setting up full vacuums of *some* objects.  If
> you have a table whose tuples all get modified in the course of some
> common query, that will lead to a pretty conspicuous bloating of *that
> table.*

Sure.  And depending on your use model, that might be good.  In many
cases, though, a "rotor table + view + truncate" approach would be
better, and would allow improved uptime.  If you don't care about
uptime, and can take long outages every day, then the discussion is
sort of moot anyway.  And _all_ of this is moot, as near as I can
tell, given the OP's claim that the hardware is adequate and
immutable, even though the former claim is demonstrably false.

A

--
Andrew Sullivan  | 
The fact that technology doesn't work is no bar to success in the marketplace.
        --Philip Greenspun


В списке pgsql-performance по дате сообщения:

От: Stephan Szabo
Дата:
Сообщение: Re: Multiple Order By Criteria
От: J@Planeti.Biz
Дата:
Сообщение: Re: Multiple Order By Criteria