Re: Optimizing Postgresql server and FreeBSD for heavy read and writes

Robert Haas wrote:
> On Wed, Feb 3, 2010 at 10:10 AM, Amitabh Kant <amitabhkant(at)gmail(dot)com> wrote:
>
>> work_mem = 160MB # pg_generate_conf wizard 2010-02-03
>>
>
> Overall these settings look sane, but this one looks like an
> exception. That is an enormous value for that parameter...
>

Yeah, I think I need to retune the suggestions for that parameter. The
idea behind the tuning profile used in the "web" and "OLTP" setups is
that you're unlikely to have all the available connections doing
something involving sorting at the same time with those workloads, and
when it does happen you want it to use the fastest approach possible
even if that takes more RAM so the client waiting for a response is more
likely to get one on time. That's why the work_mem figure in those
situations is set very aggressively: total_mem / connections, so on a
16GB server that comes out to the 160MB seen here. I'm going to adjust
that so that it's capped a little below (total_mem - shared_buffers) /
connections instead.

pgtune just got a major bit of refactoring recently from Matt Harrison
to make it more Python-esque, and I'll be pushing toward an official 1.0
with all the major loose ends cleaned up and an adjusted tuning model
that will be available before 9.0 ships. I'm seeing enough people
interested in it now to justify putting another block of work into
improving it.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us