Vacuums on large busy databases

From: Francisco Reyes <lists(at)stringsutils(dot)com>
To: Pgsql performance <pgsql-performance(at)postgresql(dot)org>
Subject: Vacuums on large busy databases
Date: 2006-09-14 15:23:01
Message-ID: cone.1158247381.60704.30981.1000@zoraida.natserv.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

My setup:
Freebsd 6.1
Postgresql 8.1.4
Memory: 8GB
SATA Disks

Raid 1 10 spindles (2 as hot spares)
500GB disks (16MB buffer), 7200 rpm
Raid 10

Raid 2 4 spindles
150GB 10K rpm disks
Raid 10

shared_buffers = 10000
temp_buffers = 1500
work_mem = 32768 # 32MB
maintenance_work_mem = 524288 # 512MB

checkpoint_segments = 64
Just increased to 64 today.. after reading this may help. Was 5 before.

pg_xlog on second raid (which sees very little activity)

Database sizes: 1 200GB+ Db and 2 100GB+

I run 3 daily "vacuumdb -azv". The vacuums were taking 2 to 3 hours.
Recently we have started to do some data mass loading and now the vacuums
are taking close to 5 hours AND it seems they may be slowing down the loads.

These are not bulk loads in the sense that we don't have a big file that we
can do a copy.. instead it is data which several programs are processing
from some temporary tables so we have lots of inserts. There are also
updates to keep track of some totals.

I am looking to either improve the time of the vacuum or decrease it's
impact on the loads.
Are the variables:
#vacuum_cost_delay = 0 # 0-1000 milliseconds
#vacuum_cost_page_hit = 1 # 0-10000 credits
#vacuum_cost_page_miss = 10 # 0-10000 credits
#vacuum_cost_page_dirty = 20 # 0-10000 credits
#vacuum_cost_limit = 200 # 0-10000 credits

Is that the way to go to decrease impact?
Or should I try increasing maintenance_work_mem to 1GB?

A sum of all running processes from "ps auxw" shows about 3.5GB in "VSZ" and
1.5GB in "RSS".

I am also going to check if I have enough space to move the stage DB to the
second raid which shows very little activity in iostat.

Any other suggestions?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2006-09-14 15:56:52 Re: High CPU Load
Previous Message Jérôme BENOIS 2006-09-14 15:09:25 Re: High CPU Load