Re: Vacuums on large busy databases

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
>