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?