From: Richard Huxton <dev@archonet.com>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: David Lang <david@lang.hm>
Cc: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: x206-x225
Date: Tue, 14 Mar 2006 21:37:33 +0000
Message-ID: <4417379D.9020801@archonet.com> (raw)
In-Reply-To: <20060314203253.GT45250@pervasive.com>
References: <GMEAJIDMNDKCIHMMHDFLCEKHDHAA.hjs@rmax.nl>
<441181C6.3040401@archonet.com>
<1142063378.6634.13.camel@localhost>
<Pine.LNX.4.62.0603102355550.23148@qnivq.ynat.uz>
<20060314203253.GT45250@pervasive.com>
Jim C. Nasby wrote:
> On Fri, Mar 10, 2006 at 11:57:16PM -0800, David Lang wrote:
>> On Sat, 11 Mar 2006, Joost Kraaijeveld wrote:
>>
>>> On Fri, 2006-03-10 at 13:40 +0000, Richard Huxton wrote:
>>>> Your ATA disk is lying about disk caching being turned off. Assuming
>>>> each insert is in a separate transaction, then it's not going to do
>>>> 10,000 / 6 = 1667 transactions/sec - that's faster than it's rotational
>>>> speed.
>>> Could you explain the calculation? Why should the number of transactions
>>> be related to the rotational speed of the disk, without saying anything
>>> about the number of bytes per rotation?
>> each transaction requires a sync to the disk, a sync requires a real
>> write (which you then wait for), so you can only do one transaction per
>> rotation.
>
> But shouldn't it be possible to batch up WAL writes and syncs? In other
> words, if you have 5 transactions that all COMMIT at exactly the same
> time, it should be possible to get all 5 WAL pages (I'll assume each
> one generated a small enough change so as not to require multiple WAL
> pages) to the drive before the platter comes around to the right
> position. The drive should then be able to write all 5 at once. At
> least, theoretically...
I think you mean this...
http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html
commit_delay (integer)
Time delay between writing a commit record to the WAL buffer and
flushing the buffer out to disk, in microseconds. A nonzero delay can
allow multiple transactions to be committed with only one fsync() system
call, if system load is high enough that additional transactions become
ready to commit within the given interval. But the delay is just wasted
if no other transactions become ready to commit. Therefore, the delay is
only performed if at least commit_siblings other transactions are active
at the instant that a server process has written its commit record. The
default is zero (no delay).
commit_siblings (integer)
Minimum number of concurrent open transactions to require before
performing the commit_delay delay. A larger value makes it more probable
that at least one other transaction will become ready to commit during
the delay interval. The default is five.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, jnasby@pervasive.com, david@lang.hm, J.Kraaijeveld@Askesis.nl
Subject: Re: x206-x225
In-Reply-To: <4417379D.9020801@archonet.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