Обсуждение: vacuum full taking much longer than dump + restore?

We have a few larger tables (~3,5 million rows largest, ~ 1 million rows
smallest) on our production database which had at least one column
defined as char(nn) (nn being larger or equal to 60).
I did an alter table where I changed the definition of those columns to
varchar(nn), and after that did a 'UPDATE TABLE some_table SET
column_name = RTRIM(column_name)'.
Since it effectively doubled the number of rows I decided to do a vacuum
full. After some 10 hours I had to stop it since it was monday morning,
and vacuum was blocking the querys. After thad I did a pg_dump and a
restore and it was finished in about an hour and a half, with additional
15-20 minutes of vacuum analyze. I'm guessing that this
dump->restore->analyze has done effectively the same thing what vacuum
full was supposed to do. How is it possible that vacuum full was so
slow, are there some configuration parameters that might be
misconfigured? I am using fedora core 4 with pre-built 8.1.3 rpms.
Server has 2 gb of ram.

Tnx in advance

Dragan Matic

On Mon, Mar 20, 2006 at 10:50:42AM +0100, Dragan Matic wrote:
> We have a few larger tables (~3,5 million rows largest, ~ 1 million rows
> smallest) on our production database which had at least one column
> defined as char(nn) (nn being larger or equal to 60).

<snip>

> 15-20 minutes of vacuum analyze. I'm guessing that this
> dump->restore->analyze has done effectively the same thing what vacuum
> full was supposed to do. How is it possible that vacuum full was so
> slow, are there some configuration parameters that might be
> misconfigured? I am using fedora core 4 with pre-built 8.1.3 rpms.
> Server has 2 gb of ram.

How many indexes did you have on that table? Once vacuum has decided to
clear out an old tuple, it needs to remove it from the index. Depending
the number and size of indexes, this can be quite expensive. so
dropping the indexes first, vacuuming and recreating may be faster.

Incidently, some changes have been made to vacuum recently to make this
a bit better, but what it have is kind of the worst case scenario. It
is known that sometimes clustering a table is faster than vacuuming it.

I think "maintainence_work_mem" has a significant impact on vacuum,
especially how many passes it needs to make. Upping that should help.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.