From: Greg Smith <greg@2ndquadrant.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Konrad Garus <konrad.garus@gmail.com>
Cc: Paul McGarry <paul@paulmcgarry.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: shared_buffers advice
Date: Fri, 28 May 2010 14:57:54 -0400
Message-ID: <4C001232.8030106@2ndquadrant.com> (raw)
In-Reply-To: <AANLkTikbvjCznp4vanWMsXbtt74-7Mt89WuWFzsUMRMg@mail.gmail.com>
References: <a056b1d41003101728h2bfe964cg43bf5ef1aabae14d@mail.gmail.com>
<AANLkTinFxeQuj8GdKR49Hn80E8Tf8WhAQI0CL9yRbs32@mail.gmail.com>
<AANLkTim6FolMPvH997nW5FSciJtR6VJevEsN0gE43clO@mail.gmail.com>
<AANLkTikbvjCznp4vanWMsXbtt74-7Mt89WuWFzsUMRMg@mail.gmail.com>
Merlin Moncure wrote:
> I would prefer to see the annotated performance oriented .conf
> settings to be written in terms of trade offs (too low? X too high? Y
> setting in order to get? Z). For example, did you know that if crank
> max_locks_per_transaction you also increase the duration of every
> query that hits pg_locks() -- well, now you do :-).
>
You can't do this without providing more context and tools for people to
measure their systems. At PGCon last week, I presented a talk
specifically about tuning shared_buffers and the checkpoint settings.
What's come out of my research there is that you can stare at the data
in pg_buffercache and pg_stat_bgwriter and classify systems based on the
distribution of usage counts in their buffer cache on how the background
writer copes with that. The right style of tuning to apply is dependent
on whether someone has a high proportion of buffers with a usage count
>=2. A tuning guide that actually covered that in enough detail to be
an improvement over what is in the "Tuning Your PostgreSQL Server" would
be overwhelming large, defeating the purpose of that document--providing
a fairly bite-size guide.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: greg@2ndquadrant.com, mmoncure@gmail.com, konrad.garus@gmail.com, paul@paulmcgarry.com
Subject: Re: shared_buffers advice
In-Reply-To: <4C001232.8030106@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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