Vacuums on large busy databases

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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?


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux