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: Wed, 15 Mar 2006 09:26:09 +0000
Message-ID: <4417DDB1.8000308@archonet.com> (raw)
In-Reply-To: <20060314220817.GA45250@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>
<4417379D.9020801@archonet.com>
<20060314220817.GA45250@pervasive.com>
Jim C. Nasby wrote:
>> I think you mean this...
>>
>> http://www.postgresql.org/docs/8.1/static/runtime-config-wal.html
>>
>> commit_delay (integer)
>
> No, that's not what I mean at all. On a system doing a large number of
> WAL-generating transactions per second, it's certainly possible for
> multiple transactions to commit in the period of time it takes for the
> platter to rotate back into position to allow for writing of the WAL
> data. What I don't know is if those multiple transactions would actually
> make it to the platter on that rotation, or if they'd serialize,
> resulting in one commit per revolution. I do know that there's no
> theoretical reason that they couldn't, it's just a matter of putting
> enough intelligence in the drive.
>
> Perhaps this is something that SCSI supports and (S)ATA doesn't, since
> SCSI allows multiple transactions to be 'in flight' on the bus at once.
SCSI Command queueing:
http://www.storagereview.com/guide2000/ref/hdd/if/scsi/protCQR.html
SATA "native command queuing":
http://www.tomshardware.com/2004/11/16/can_command_queuing_turbo_charge_sata/> But since you mention commit_delay, this does lead to an interesting
> possible use: set it equal to the effective rotational period of the
> drive. If you know your transaction load well enough, you could possibly
> gain some benefit here. But of course a RAID controller with a BBU would
> be a better bet...
I suppose as long as you always have several transactions trying to
commit, have a separate spindle(s) for the WAL then you could improve
throughput at the cost of the shortest transaction times. Of course, it
might be that the increase in lock duration etc. might outweigh any
benefits. I'd suspect the cost/gain would be highly variable with
changes in workload, and as you say write-cache+BBU seems more sensible.
--
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: <4417DDB1.8000308@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