Re: Full vacuuming of BIG tables takes too long

Hi,

I don't have a solution but :
1) Is your system spending time in Wait I/O, while vacuum analyze is running
? Perhaps, you can save time by incrising I/O throughput.

2) In the alternative dump/recreate/restore, do you recreate the Foreign Key
? This step takes long time (depending of your Database schema). I have try
this scenario :
Dump data / Drop Foreign Key / Truuncate Tables / restore / Recreate the
Foreign Key
The step Recreate FK takes 2 times the four first steps.

Thierry

"Eugene M. Zheganin" wrote:

> Hi, all.
>
> Just example. I have an table in ISP billing base, which every 2
> months grows and contains up to 35,000,000 of records. That takes
> 13Gb of disk space. On that amount 'vacuum analyze' (every night) is
> inefficient, cause after it the table continues to grow (but not
> very fast).
>
> When trying to do 'vacuum full' it takes too long- I can wait only
> 5-6 hours (and that is not enough), cause it locks the table and
> the number of procecces, awaiting their inserts becomes too high.
> So it is much faster (40-50 mins) to dump the entire database,
> then drop it, recreate and resore it.
>
> I know that 'vacuum_mem = 65536' is not enough to do 'vacuum full'
> fast enough - but I wanna ask- if I dedcide to increase that number
> - will be 512 megs for example be better ?
>
> Is there any other init parameters that can helkp me ?
>
> Or speaking of such amount of data dump/recreate/restore will be the
> best way ?
>
> WBR, Eugene.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html