pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Recourse Usage 2+ messages / 2 participants [nested] [flat]
* Recourse Usage @ 2008-07-03 23:28 Steve Kecskes <steve@outtalimits.com.au> 0 siblings, 1 reply; 2+ messages in thread From: Steve Kecskes @ 2008-07-03 23:28 UTC (permalink / raw) To: pgsql-admin G'day, I have been tasked to tune our company's Postgres database. From the research I have done I feel the recourse consumption parameters are set far to low. I have come up with the following modifications to the configuration and wanted to run this by the mailing list for any suggestions. The database is on a semi dedicated box, shared only with memcached which has been allocated 512MB. -Dual Xeon 2.33GHz -4GB RAM -Twin SAS drives in RAID 1 Shared Buffers Currently: 8192 pages @ 8 KB each // 64MB To be set to: (256MB - 32768 pages @ 8 KB each) Work Mem Currently: 1024kb To be set to: 1536kb Effective Cache Size Currently: 50000 pages // 390MB To be set to: 128000 pages // 1000MB Random Page Cost Currently: 4.0 To be set to: 3.0 Wal Buffers Currently: 8 @ 8kb each To be set to: 32 @ 8kb each Kind Regards, -- ------------------------------------------------- Steve Kecskes steve@outtalimits.com.au ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Recourse Usage @ 2008-07-04 00:38 Scott Marlowe <scott.marlowe@gmail.com> parent: Steve Kecskes <steve@outtalimits.com.au> 0 siblings, 0 replies; 2+ messages in thread From: Scott Marlowe @ 2008-07-04 00:38 UTC (permalink / raw) To: Steve Kecskes <steve@outtalimits.com.au>; +Cc: pgsql-admin On Thu, Jul 3, 2008 at 5:28 PM, Steve Kecskes <steve@outtalimits.com.au> wrote: > > G'day, > > I have been tasked to tune our company's Postgres database. From the > research I have done I feel the recourse consumption parameters are set far > to low. I have come up with the following modifications to the > configuration and wanted to run this by the mailing list for any > suggestions. The database is on a semi dedicated box, shared only with > memcached which has been allocated 512MB. > > -Dual Xeon 2.33GHz > -4GB RAM > -Twin SAS drives in RAID 1 > > Shared Buffers > Currently: 8192 pages @ 8 KB each // 64MB > To be set to: (256MB - 32768 pages @ 8 KB each) That's still pretty small. I'd look at setting it to 1G on a machine with that much memory. > Work Mem > Currently: 1024kb > To be set to: 1536kb This setting is very dependent on how man connections you'll support. If only a few users will ever connect, then you can easily set it to 16 to 64 Megs and be ok. If you'll have dozens to hundreds of users keep in mind that work_mem limits the amount of memory each sort type function can use in each query for each user so it can add up fast. Generally most systems can handle 8M or so for work_mem fine. > > Effective Cache Size > Currently: 50000 pages // 390MB > To be set to: 128000 pages // 1000MB Probably also too low. That machine is quite likely to have 2+g of kernel cache and buffer. But this is a course tuning knob so it's not a huge deal. > Random Page Cost > Currently: 4.0 > To be set to: 3.0 > > Wal Buffers > Currently: 8 @ 8kb each > To be set to: 32 @ 8kb each Both of these are fine. I assume you're running at least 8.0 or above here. I would highly recommend upgrading to 8.3 if you're on 8.1 or older due to the fact that 8.3 is so much faster than the older versions. ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2008-07-04 00:38 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2008-07-03 23:28 Recourse Usage Steve Kecskes <steve@outtalimits.com.au> 2008-07-04 00:38 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox