Thread: VACCUM FULL ANALYZE PROBLEM

----- 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

 

 

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

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

"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

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

    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
>