On 14-Sep-06, at 11:23 AM, Francisco Reyes wrote:
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
shared buffers should be considerably more, depending on what else
is running
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.
What is effective_cache set to ?
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
why not just let autovac do it's thing ?
.
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?
---------------------------(end of
broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
