Thread: Maintenance_work_mem question

Maintenance_work_mem question

From
"Mark Steben"
Date:

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)

 Total database size: 60GB

 Total number of tables:   300

 Total number of indexes: 500

 Max connections: 200

 Average connections at one time: 10 – 15

 

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

According to our maintenance_work_mem value perpetually during the day, so perhaps

I should set it smaller than 1 Gig?

 

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?

 

Thank you,

 

Mark Steben

Senior Database Administrator
@utoRevenue™
A Dominion Enterprises Company
480 Pleasant Street
Suite B200
Lee, MA 01238
413-243-4800 Home Office 
413-243-4809 Corporate Fax

msteben@autorevenue.com

Visit our new website at
www.autorevenue.com

 

IMPORTANT: The information contained in this e-mail message is confidential and is intended only for the named addressee(s). If the reader of this e-mail message is not the intended recipient (or the individual responsible for the delivery of this e-mail message to the intended recipient), please be advised that any re-use, dissemination, distribution or copying of this e-mail message is prohibited.  If you have received this e-mail message in error, please reply to the sender that you have received this e-mail message in error and then delete it.  Thank you.

Come Visit Us at NADA!  Booth #5735N
February 9th - February 12th
Moscone Center in San Francisco, CA

 

Re: Maintenance_work_mem question

From
"Scott Marlowe"
Date:
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.