Received: from localhost (unknown [200.46.204.183])
	by developer.postgresql.org (Postfix) with ESMTP id 3129C2E0092
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Mon, 31 Mar 2008 16:49:37 -0300 (ADT)
Received: from developer.postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 28981-02-5
	for <pgsql-performance-postgresql.org@developer.postgresql.org>;
	Mon, 31 Mar 2008 16:49:36 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from pih-relay08.plus.net (pih-relay08.plus.net [212.159.14.134])
	by developer.postgresql.org (Postfix) with ESMTP id 9D7EF2E009B
	for <pgsql-performance@postgresql.org>;
	Mon, 31 Mar 2008 16:49:35 -0300 (ADT)
Received: from [80.229.150.39] (helo=mansionfamily.plus.com)
	by pih-relay08.plus.net with esmtp (Exim) id 1JgQ0b-0001HW-B9
	for pgsql-performance@postgresql.org; Mon, 31 Mar 2008 20:49:33 +0100
Received: from [192.168.0.89] ([192.168.0.89]:1212)
	by mansionfamily.plus.com with [XMail 1.22 ESMTP Server]
	id <S15085> for <pgsql-performance@postgresql.org> from
	<james@mansionfamily.plus.com>; Mon, 31 Mar 2008 20:54:29 +0100
Message-ID: <47F1413B.40109@mansionfamily.plus.com>
Date: Mon, 31 Mar 2008 20:53:31 +0100
From: James Mansion <james@mansionfamily.plus.com>
Organization: MsgWare
User-Agent: Thunderbird 2.0.0.0 (Windows/20070326)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: POSIX file updates
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Plusnet-Relay: cf02792f8266e4c7ba03bf5ad2f53d08
X-Archive-Number: 200803/529
X-Sequence-Number: 29483

(Declaration of interest: I'm researching for a publication
on OLTP system design)

I have a question about file writes, particularly on POSIX.
This arose while considering the extent to which cache memory
and command queueing on disk
drives can help improve performance.

Is it correct that POSIX requires that the updates to a single
file are serialised in the filesystem layer?

So, if we have a number of dirty pages to write back to a single
file in the database (whether a table or index) then we cannot
pass these through the POSIX filesystem layer into the TCQ/NCQ
system on the disk drive, so it can reorder them?

I have seen suggestions that on Solaris this can be relaxed.

I *assume* that PostgreSQL's lack of threads or AIO and the
single bgwriter means that PostgreSQL 8.x does not normally
attempt to make any use of such a relaxation but could do so if the
bgwriter fails to keep up and other backends initiate flushes.

Does anyone know (perhaps from other systems) whether it is
valuable to attempt to take advantage of such a relaxation
where it is available?

Does the serialisation for file update apply in the case
where the file contents have been memory-mapped and we
try an msync (or equivalent)?