pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
VACCUM FULL ANALYZE PROBLEM
8+ messages / 5 participants
[nested] [flat]

* VACCUM FULL ANALYZE PROBLEM
@ 2005-02-15 01:34  Michael Ryan S. Puncia <mpuncia@census.gov.ph>
  0 siblings, 2 replies; 8+ messages in thread

From: Michael Ryan S. Puncia @ 2005-02-15 01:34 UTC (permalink / raw)
  To: pgsql-performance

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

 

 

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: VACCUM FULL ANALYZE PROBLEM
@ 2005-02-15 01:52  Iain <iain@mst.co.jp>
  parent: Michael Ryan S. Puncia <mpuncia@census.gov.ph>
  1 sibling, 1 reply; 8+ messages in thread

From: Iain @ 2005-02-15 01:52 UTC (permalink / raw)
  To: Michael Ryan S. Puncia <mpuncia@census.gov.ph>; pgsql-performance

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@postgresql.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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: VACCUM FULL ANALYZE PROBLEM
@ 2005-02-15 02:10  Michael Ryan S. Puncia <mpuncia@census.gov.ph>
  parent: Iain <iain@mst.co.jp>
  0 siblings, 1 reply; 8+ messages in thread

From: Michael Ryan S. Puncia @ 2005-02-15 02:10 UTC (permalink / raw)
  To: 'Iain' <iain@mst.co.jp>; 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?

 

 

  _____  

From: Iain [mailto:iain@mst.co.jp] 
Sent: Tuesday, February 15, 2005 9:52 AM
To: Michael Ryan S. Puncia; pgsql-performance@postgresql.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 <mailto:mpuncia@census.gov.ph>  S. Puncia 

To: pgsql-performance@postgresql.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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: VACCUM FULL ANALYZE PROBLEM
@ 2005-02-15 02:21  Rod Taylor <pg@rbt.ca>
  parent: Michael Ryan S. Puncia <mpuncia@census.gov.ph>
  1 sibling, 0 replies; 8+ messages in thread

From: Rod Taylor @ 2005-02-15 02:21 UTC (permalink / raw)
  To: Michael Ryan S. Puncia <mpuncia@census.gov.ph>; +Cc: pgsql-performance

On Tue, 2005-02-15 at 09:34 +0800, Michael Ryan S. Puncia wrote:
> 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

I suspect the VACUUM FULL is the painful part. Try running CLUSTER on
the table or changing a column type (in 8.0) instead.
-- 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: VACCUM FULL ANALYZE PROBLEM
@ 2005-02-15 02:31  Iain <iain@mst.co.jp>
  parent: Michael Ryan S. Puncia <mpuncia@census.gov.ph>
  0 siblings, 1 reply; 8+ messages in thread

From: Iain @ 2005-02-15 02:31 UTC (permalink / raw)
  To: Michael Ryan S. Puncia <mpuncia@census.gov.ph>; 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@postgresql.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@mst.co.jp]
  Sent: Tuesday, February 15, 2005 9:52 AM
  To: Michael Ryan S. Puncia; pgsql-performance@postgresql.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@postgresql.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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: VACCUM FULL ANALYZE PROBLEM
@ 2005-02-15 05:30  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Iain <iain@mst.co.jp>
  0 siblings, 2 replies; 8+ messages in thread

From: Tom Lane @ 2005-02-15 05:30 UTC (permalink / raw)
  To: Iain <iain@mst.co.jp>; +Cc: Michael Ryan S. Puncia <mpuncia@census.gov.ph>; pgsql-performance

"Iain" <iain@mst.co.jp> writes:
>> 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.

Actually, VACUUM FULL is designed to work nicely for the situation where
a table has say 10% wasted space and you want the wasted space all
compressed out.  When there is a lot of wasted space, so that nearly all
the rows have to be moved to complete the compaction operation, VACUUM
FULL is not a very good choice.  And it simply moves rows around, it
doesn't modify the rows internally; so it does nothing at all to reclaim
space that would have been freed up by DROP COLUMN operations.

CLUSTER is actually a better bet if you want to repack a table that's
suffered a lot of updates or deletions.  In PG 8.0 you might also
consider one of the rewriting variants of ALTER TABLE.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: VACCUM FULL ANALYZE PROBLEM
@ 2005-02-15 05:58  Iain <iain@mst.co.jp>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 8+ messages in thread

From: Iain @ 2005-02-15 05:58 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Michael Ryan S. Puncia <mpuncia@census.gov.ph>; pgsql-performance

OK, that's interesting. So the original assumption that vacuum full was 
needed was completely wrong anyway.

If table re-organisation isn't required a plain vacuum would be fastest. I 
will take a guess that the next best alternative is to do the "create table 
newtable as select ... order by ..." thing and then create the indexes and 
stuff. This would reorganize the table completely. After that you have the 
cluster command, and coming in last place is vacuum full. Sound about right?

Michael, you said that a vacuum that runs for 3 days is too long, but hasn't 
given any specific requirements or limitations. Hopefully you can find 
something suitable in the alternatives listed above.

regards
Iain

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Iain" <iain@mst.co.jp>
Cc: "Michael Ryan S. Puncia" <mpuncia@census.gov.ph>; 
<pgsql-performance@postgresql.org>
Sent: Tuesday, February 15, 2005 2:30 PM
Subject: Re: [PERFORM] VACCUM FULL ANALYZE PROBLEM


> "Iain" <iain@mst.co.jp> writes:
>>> 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.
>
> Actually, VACUUM FULL is designed to work nicely for the situation where
> a table has say 10% wasted space and you want the wasted space all
> compressed out.  When there is a lot of wasted space, so that nearly all
> the rows have to be moved to complete the compaction operation, VACUUM
> FULL is not a very good choice.  And it simply moves rows around, it
> doesn't modify the rows internally; so it does nothing at all to reclaim
> space that would have been freed up by DROP COLUMN operations.
>
> CLUSTER is actually a better bet if you want to repack a table that's
> suffered a lot of updates or deletions.  In PG 8.0 you might also
> consider one of the rewriting variants of ALTER TABLE.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: VACCUM FULL ANALYZE PROBLEM
@ 2005-02-15 08:51  PFC <lists@boutiquenumerique.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 8+ messages in thread

From: PFC @ 2005-02-15 08:51 UTC (permalink / raw)
  To: pgsql-performance


	I don't know if this would work, but if you just want to restructure your  
rows, your could do this:

	UPDATE table SET id = id WHERE id BETWEEN 0 AND 20000;
	VACUUM table;
	UPDATE table SET id = id WHERE id BETWEEN 20001 AND 40000;
	VACUUM table;

	wash, rinse, repeat.

	The idea is that an update rewrites the rows (in your new format) and  
that VACUUM (not FULL) is quite fast when you just modified a part of the  
table, and non-locking.

	Would this work ?


> "Iain" <iain@mst.co.jp> writes:
>>> 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.
>
> Actually, VACUUM FULL is designed to work nicely for the situation where
> a table has say 10% wasted space and you want the wasted space all
> compressed out.  When there is a lot of wasted space, so that nearly all
> the rows have to be moved to complete the compaction operation, VACUUM
> FULL is not a very good choice.  And it simply moves rows around, it
> doesn't modify the rows internally; so it does nothing at all to reclaim
> space that would have been freed up by DROP COLUMN operations.
>
> CLUSTER is actually a better bet if you want to repack a table that's
> suffered a lot of updates or deletions.  In PG 8.0 you might also
> consider one of the rewriting variants of ALTER TABLE.
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2005-02-15 08:51 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-15 01:34 VACCUM FULL ANALYZE PROBLEM Michael Ryan S. Puncia <mpuncia@census.gov.ph>
2005-02-15 01:52 ` Iain <iain@mst.co.jp>
2005-02-15 02:10   ` Michael Ryan S. Puncia <mpuncia@census.gov.ph>
2005-02-15 02:31     ` Iain <iain@mst.co.jp>
2005-02-15 05:30       ` Tom Lane <tgl@sss.pgh.pa.us>
2005-02-15 05:58         ` Iain <iain@mst.co.jp>
2005-02-15 08:51         ` PFC <lists@boutiquenumerique.com>
2005-02-15 02:21 ` Rod Taylor <pg@rbt.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox