From: Tom Lane <tgl@sss.pgh.pa.us>
To: Christopher Petrilli <petrilli@gmail.com>
Cc: PFC <lists@boutiquenumerique.com>
Cc: Vivek Khera <vivek@khera.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Impact of checkpoint_segments under continual load conditions
Date: Tue, 19 Jul 2005 12:42:08 -0400
Message-ID: <7224.1121791328@sss.pgh.pa.us> (raw)
In-Reply-To: <59d991c40507190930244ba9bb@mail.gmail.com>
References: <59d991c4050717100874c08a86@mail.gmail.com>
<A50B8A1F-71A1-4F63-A5F4-1AA58F89557D@khera.org>
<59d991c405071811457ccdd3f1@mail.gmail.com>
<16394.1121715156@sss.pgh.pa.us>
<59d991c405071812342e290f57@mail.gmail.com>
<16794.1121718743@sss.pgh.pa.us>
<59d991c405071907482bb0689b@mail.gmail.com>
<op.st52tvunth1vuj@localhost>
<59d991c4050719084460b376f1@mail.gmail.com>
<4541.1121788647@sss.pgh.pa.us>
<59d991c40507190930244ba9bb@mail.gmail.com>
Christopher Petrilli <petrilli@gmail.com> writes:
> On 7/19/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I'm suddenly wondering if the performance dropoff corresponds to the
>> point where the indexes have grown large enough to not fit in shared
>> buffers anymore. If I understand correctly, the 5000-iterations mark
>> corresponds to 2.5 million total rows in the table; with 5 indexes
>> you'd have 12.5 million index entries or probably a couple hundred MB
>> total. If the insertion pattern is sufficiently random that the entire
>> index ranges are "hot" then you might not have enough RAM.
> This is entirely possible, currently:
> shared_buffers = 1000
Ah-hah --- with that setting, you could be seeing shared-buffer
thrashing even if only a fraction of the total index ranges need to be
touched. I'd try some runs with shared_buffers at 10000, 50000, 100000.
You might also try strace'ing the backend and see if behavior changes
noticeably when the performance tanks.
FWIW I have seen similar behavior while playing with MySQL's sql-bench
test --- the default 1000 shared_buffers is not large enough to hold
the "hot" part of the indexes in some of their insertion tests, and so
performance tanks --- you can see this happening in strace because the
kernel request mix goes from almost all writes to a significant part
reads. On a pure data insertion benchmark you'd like to see nothing
but writes.
regards, tom lane
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: tgl@sss.pgh.pa.us, petrilli@gmail.com, lists@boutiquenumerique.com, vivek@khera.org
Subject: Re: Impact of checkpoint_segments under continual load conditions
In-Reply-To: <7224.1121791328@sss.pgh.pa.us>
* 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