Re: Autovacuum / full vacuum

От: Andrew Sullivan
Тема: Re: Autovacuum / full vacuum
Дата: ,
Msg-id: 20060117151944.GI21092@phlogiston.dyndns.org
(см: обсуждение, исходный текст)
Ответ на: Re: Autovacuum / full vacuum  (Michael Riess)
Список: 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 04:04:41PM +0100, Michael Riess wrote:
>
> I thought that vacuum full only locks the table which it currently
> operates on? I'm pretty sure that once a table has been vacuumed, it can
> be accessed without any restrictions while the vacuum process works on
> the next table.

Yes, I think the way I phrased it was unfortunate.  But if you issue
VACUUM FULL you'll get an exclusive lock on everything, although not
all at the same time.  But of course, if your query load is like
this

BEGIN;
SELECT from t1, t2 where t1.col1 = t2.col2;
[application logic]
UPDATE t3 . . .
COMMIT;

you'll find yourself blocked in the first statement on both t1 and
t2; and then on t3 as well.  You sure don't want that to happen
automagically, in the middle of your business day.

> I have 5000 tables and a workstation with 1 GB RAM which hosts an Apache
>   Web Server, Tomcat Servlet Container and PostgreSQL. RAM is not
> something that I have plenty of ... and the hardware is fixed and cannot
> be changed.

I see.  Well, I humbly submit that your problem is not the design of
the PostgreSQL server, then.  "The hardware is fixed and cannot be
changed," is the first optimisation I'd make.  Heck, I gave away a
box to charity only two weeks ago that would solve your problem
better than automatically issuing VACUUM FULL.

A

--
Andrew Sullivan  | 
Information security isn't a technological problem.  It's an economics
problem.
        --Bruce Schneier


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

От: mark@mark.mielke.cc
Дата:
Сообщение: Re: Suspending SELECTs
От: Mark Lewis
Дата:
Сообщение: Re: Suspending SELECTs