Skip site navigation (1) Skip section navigation (2)

Re: Full vacuuming of BIG tables takes too long

From: Thierry Missimilly <THIERRY(dot)MISSIMILLY(at)BULL(dot)NET>
To: "Eugene M(dot) Zheganin" <emz(at)norma(dot)perm(dot)ru>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Full vacuuming of BIG tables takes too long
Date: 2003-05-22 11:45:26
Message-ID: 3ECCB856.C3DB2926@BULL.NET (view raw or flat)
Thread:
Lists: pgsql-admin
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

Attachment: THIERRY.MISSIMILLY.vcf
Description: text/x-vcard (327 bytes)

In response to

Responses

pgsql-admin by date

Next:From: Bruno Wolff IIIDate: 2003-05-22 12:13:15
Subject: Re: Date question....
Previous:From: Sean ChittendenDate: 2003-05-22 09:53:07
Subject: Re: Increasing Shared Memory size, in order to increase shared buffer size

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group