Обсуждение: Full vacuuming of BIG tables takes too long

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.

Hello Thierry,

Thursday, May 22, 2003, 5:45:26 PM, you wrote:

TM> Hi,

TM> I don't have a solution but :
TM> 1) Is your system spending time in Wait I/O, while vacuum analyze is running
TM> ?
Almost no. During fist 30 mins summary I/O (iostat 1) is 20/25
megs/sec, then only 3-5 megs/sec. the "i/o wait" counters in cpu
activity are not too high.

TM>  Perhaps, you can save time by incrising I/O throughput.
I.e. to use SCSI-HDD ? 8)) May be. But I stall hope tha the problem
can be solved by increasing memory/tuning initialization parameters...

TM> 2) In the alternative dump/recreate/restore, do you recreate the Foreign Key
TM> ? This step takes long time (depending of your Database schema). I have try
TM> this scenario :
TM> Dump data / Drop Foreign Key / Truuncate Tables / restore / Recreate the
TM> Foreign Key
TM> The step Recreate FK takes 2 times the four first steps.
I don't use foreign keys. Only primary keys. There is only 3 tables in
that db. 99% of space is taken by one table.

--
Best regards,
 Eugene                            mailto:emz@norma.perm.ru

On Thu, 2003-05-22 at 08:37, Eugene M. Zheganin wrote:

My first suggestion would be to check  your free space map settings in
the postgresql.conf and make sure they are set high enough.  Setting
them to low can cause Vacuum Full to take longer than necessary.

> Thursday, May 22, 2003, 5:45:26 PM, you wrote:
> TM> 2) In the alternative dump/recreate/restore, do you recreate the Foreign Key
> TM> ? This step takes long time (depending of your Database schema). I have try
> TM> this scenario :
> TM> Dump data / Drop Foreign Key / Truuncate Tables / restore / Recreate the
> TM> Foreign Key
> TM> The step Recreate FK takes 2 times the four first steps.
> I don't use foreign keys. Only primary keys. There is only 3 tables in
> that db. 99% of space is taken by one table.
>

Is it possible for you do Begin;  Create Table t2 As Select * From t1;
Drop Table t1 ; Alter table t2 Rename To t1; Commit;

Note you might want to lock t1 from writers, but people could still
select from it while you making the switch.

Robert Treat

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

"Eugene M. Zheganin" <emz@norma.perm.ru> writes:
>   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).

If you aren't getting results from plain vacuum then you need to
increase your FSM settings.

            regards, tom lane

You are likely to run into unhappy situations if you use views, rules, etc.
Depending on the version of PG it will either refuse to drop the table if
there are dependencies or it will drop it but the views won't be "connected"
to the new version of t1 after you run your opreation.

Cheers,
Steve

On Thursday 22 May 2003 07:01, Robert Treat wrote:
> Is it possible for you do Begin;  Create Table t2 As Select * From t1;
> Drop Table t1 ; Alter table t2 Rename To t1; Commit;
>
> Note you might want to lock t1 from writers, but people could still
> select from it while you making the switch.
>
> Robert Treat

Hello Robert,

Thursday, May 22, 2003, 8:01:58 PM, you wrote:

RT> On Thu, 2003-05-22 at 08:37, Eugene M. Zheganin wrote:

RT> My first suggestion would be to check  your free space map settings in
RT> the postgresql.conf and make sure they are set high enough.
They are set to defaults.

RT>   Setting
RT> them to low can cause Vacuum Full to take longer than necessary.
I read " Server Run-time Environment" from Postgresql docs, but I
don't understand how I can calculate optimum values ? (where I can
read about it in pgsql docs ?)

Can you give me any further explanations 8) ?

--
Best regards,
 Eugene                            mailto:emz@norma.perm.ru