On Wed, 24 Mar 2004, pginfo wrote:

> Hi,
> I am running pg 7.4.1 on linux box.
> I have a midle size DB with many updates and after it I try to run
> vacuum full analyze.

Is there a reason to not use just regular vacuum / analyze (i.e. NOT 

> It takes about 2 h.

Full vacuums, by their nature, tend to be a bit slow.  It's better to let 
the database achieve a kind of "steady state" with regards to number of 
dead tuples, and use regular vacuums to reclaim said space rather than a 
full vacuum.

> How can I improve the vacuum full analyze time?
> My configuration:
> shared_buffers = 15000          # min 16, at least max_connections*2,
> 8KB each
> sort_mem = 10000                # min 64, size in KB

You might want to look at dropping sort_mem.  It would appear you've been 
going through the postgresql.conf file and bumping up numbers to see what 
works and what doesn't.  While most of the settings aren't too dangerous 
to crank up a little high, sort_mem is quite dangerous to crank up high, 
should you have a lot of people connected who are all sorting.  Note that 
sort_mem is a limit PER SORT, not per backend, or per database, or per 
user, or even per table, but per sort.  IF a query needs to run three or 
four sorts, it can use 3 or 4x sort_mem.  If a hundred users do this at 
once, they can then use 300 or 400x sort_mem.  You can see where I'm 

Note that for individual sorts in batch files, like import processes, you 
can bump up sort_mem with the set command, so you don't have to have a 
large setting in postgresql.conf to use a lot of sort mem when you need 
to, you can just grab it during that one session.

> vacuum_mem = 32000              # min 1024, size in KB

If you've got lots of memory, crank up vacuum_mem to the 200 to 500 meg 
range and see what happens.

For a good tuning guide, go here:


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Reply via email to