Thread: Maintenance_work_mem question

On Nov 7, 2007 2:34 PM, Mark Steben <msteben@autorevenue.com> wrote:

> We are upgrading from postgres 7.4.5 to 8.2.4 in a couple weeks.  I am
> debating as to what
>
> I should set my maintenance_work_mem and shared_buffers parameters to.
>
> Here are some of our current resources
>
>  Total memory on machine: 32GB
>
>  Total disks – 5 Raid 10 (so five primary,  five mirrored)

Not sure what that means.  Are you running a mirror of two large
stripe sets?  normally you see a stripe set of many small mirrors.  Or
is this something other than either of those and I'm not getting it?

> We currently set our 7.4.5 vacuum_mem to 1 GB.  I understand we don't want
> That any higher as we risk overflowing our 32 bit configuration.  But, since
> we will be running autovacuum for the first time I assume that this will be
> pulling memory

vacuum is vacuum is vacuum... autovacuum just runs vacuum
automagically when it detects enough change to trigger it on one
specific table etc...  Generally vacuum runs faster with more memory,
but 1 Gig is pretty big.

In 7.4 and before, vacuum could often suck up so much I/O bandwidth it
would affect other things.  Starting with 8.0 or so, there were
tunables added to vacuum that basically told it to sleep every x pages
for y milliseconds. By setting these GUCs, you could make vacuum not
be a bad neighbor to all your currently running transactions.

> According to our maintenance_work_mem value perpetually during the day, so
> perhaps
> I should set it smaller than 1 Gig?

I'm not sure what you're saying there.  Are you saying vacuum ran
perpetually during the day? Or that vacuum was always using 1 Gig.
Having it use 1 Gig is not surprising, it's how much memory you told
it to use.

> Also, our 7.4.5 shared buffers value is currently at 150,000 buffers.  I
> know this is
> Very large for 7.4.5 but may be more appropriate for 8.2.4.  Any thoughts?

150,000 buffers or 1.2 Gig isn't all that big for 8.2.5 (note the 5,
8.2.5 is out.  You need to get in the habit of keeping your minor
number up to date.  7.4.5 is missing almost 2 years worth of updates
in the 7.4 series, many of them data eaters.)

Note that you can also now set your shared_buffers in human readable form:

shared_buffers=1200M

Note that this question borders on performance tuning, which you'll
usually get a better answer to on pgsql-perform.