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
The step Recreate FK takes 2 times the four first steps.
"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?
In response to
pgsql-admin by date
|Next:||From: Bruno Wolff III||Date: 2003-05-22 12:13:15|
|Subject: Re: Date question....|
|Previous:||From: Sean Chittenden||Date: 2003-05-22 09:53:07|
|Subject: Re: Increasing Shared Memory size, in order to increase shared buffer size|