From: Greg Smith <greg@2ndquadrant.com>
To: Hannu Krosing <hannu@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Peter Hussey <peter@labkey.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Questions on query planner, join types, and work_mem
Date: Wed, 04 Aug 2010 15:16:56 -0400
Message-ID: <4C59BCA8.2060603@2ndquadrant.com> (raw)
In-Reply-To: <1280948634.3735.69.camel@hvost>
References: <AANLkTikMwVZKDYLitnsK31R=VNOuTH8NtmDHDGkBR7k8@mail.gmail.com>
<18551.1280275502@sss.pgh.pa.us>
<AANLkTimXMR0jMdw+66E4Xx=SqNV37Ne9WfkFjPFZVjbV@mail.gmail.com>
<25534.1280498632@sss.pgh.pa.us>
<AANLkTik3oY1=F_uC843c84LxBLNdHxZaWdfjxrAtyBeu@mail.gmail.com>
<1280819031.14579.38.camel@hvost>
<AANLkTik6UBt2ezVFzdCM8iaUPkra_7f-Hs_AmGZgvFhh@mail.gmail.com>
<1280944268.3735.12.camel@hvost>
<22961.1280944836@sss.pgh.pa.us>
<1280947287.3735.56.camel@hvost>
<1280948634.3735.69.camel@hvost>
Hannu Krosing wrote:
> There was ample space for keeping the indexes in linux cache (it has 1GB
> cached currently) though the system may have decided to start writing it
> to disk, so I suspect that most of the time was spent copying random
> index pages back and forth between shared buffers and disk cache.
>
Low shared_buffers settings will result in the same pages more often
being written multiple times per checkpoint, particularly index pages,
which is less efficient than keeping in the database cache and updating
them there. This is a slightly different issue than just the overhead
of copying them back and forth; by keeping them in cache, you actually
reduce writes to the OS cache. What I do to quantify that is...well,
the attached shows it better than I can describe; only works on 9.0 or
later as it depends on a feature I added for this purpose there. It
measures exactly how much buffer cache churn happened during a test, in
this case creating a pgbench database.
--
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, hannu@2ndquadrant.com, tgl@sss.pgh.pa.us, robertmhaas@gmail.com, peter@labkey.com
Subject: Re: Questions on query planner, join types, and work_mem
In-Reply-To: <4C59BCA8.2060603@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