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.