Skip site navigation (1) Skip section navigation (2)

Re: VACCUM FULL ANALYZE PROBLEM

From: "Iain" <iain(at)mst(dot)co(dot)jp>
To: "Michael Ryan S(dot) Puncia" <mpuncia(at)census(dot)gov(dot)ph>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: VACCUM FULL ANALYZE PROBLEM
Date: 2005-02-15 02:31:14
Message-ID: 009201c51306$6e9ece80$7201a8c0@mst1x5r347kymb (view raw or flat)
Thread:
Lists: pgsql-performance
>> But I need to do full vacuum because I deleted some of the fields that 
>> are not use anymore and I also add another fields. Is there

>> another  way  to speed up full vacuum?



Hmmm... a full vacuum may help to re-organize the structure of modified 
tables, but whether this is significant or not is another matter. I don't 
know enough of the internals to comment on that maybe someone else who knows 
more can.



The obvious thing is the vacuum memory setting (in postgresql.conf). 
Presumably, you could set this quite high at least just for the duration of 
the vacuum anyway.



Would the total time be reduced by dropping the indexes, then vacuuming and 
rebuilding the indexes? I havn't tried anything like this so I can't say.



You should probably say what version of the db you are using and describe 
your system a little.



Regards

Iain

----- Original Message ----- 

  From: Michael Ryan S. Puncia
  To: 'Iain' ; pgsql-performance(at)postgresql(dot)org
  Sent: Tuesday, February 15, 2005 11:10 AM
  Subject: RE: [PERFORM] VACCUM FULL ANALYZE PROBLEM




  But I need to do full vacuum because I deleted some of the fields that are 
not use anymore and I also add another fields. Is there

  another  way  to speed up full vacuum?






------------------------------------------------------------------------------

  From: Iain [mailto:iain(at)mst(dot)co(dot)jp]
  Sent: Tuesday, February 15, 2005 9:52 AM
  To: Michael Ryan S. Puncia; pgsql-performance(at)postgresql(dot)org
  Subject: Re: [PERFORM] VACCUM FULL ANALYZE PROBLEM



  Hi,



  just make sure that your freespace map is big enough and then do a vacuum 
analyse without the full option.



  I can imagine that database performance might not be as good as it would 
be after a vacuum full, though I expect that it wouldn't make much 
difference.



  regards

  Iain

    ----- Original Message ----- 

    From: Michael Ryan S. Puncia

    To: pgsql-performance(at)postgresql(dot)org

    Sent: Tuesday, February 15, 2005 10:34 AM

    Subject: [PERFORM] VACCUM FULL ANALYZE PROBLEM



    Hi,



    I have 3 tables in the database with 80G of data, one of them is almost 
40G and the remaining 2 tables has 20G each.

    We use this database mainly for query and updating is done only 
quarterly and the database perform well. My problem

    is after updating and then run VACCUM FULL ANALYZE  vacuuming the tables 
takes days to complete. I hope someone

    can help me solve my problem.



    Thanks







  __________ NOD32 1.998 (20050212) Information __________

  This message was checked by NOD32 Antivirus System.
  http://www.nod32.com

In response to

Responses

pgsql-performance by date

Next:From: IainDate: 2005-02-15 03:55:33
Subject: Re: seq scan cache vs. index cache smackdown
Previous:From: Rod TaylorDate: 2005-02-15 02:21:56
Subject: Re: VACCUM FULL ANALYZE PROBLEM

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group