Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 6748F63451A;
	Mon, 28 Dec 2009 23:26:33 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 21826-01; Tue, 29 Dec 2009 03:26:13 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.anarazel.de (mail.anarazel.de [217.115.131.40])
	by mail.postgresql.org (Postfix) with ESMTP id 4012A6345F6;
	Mon, 28 Dec 2009 23:11:22 -0400 (AST)
Received: from alap2.localnet (e178200165.adsl.alicedsl.de [85.178.200.165])
	by mail.anarazel.de (Postfix) with ESMTPSA id 59535448006;
	Tue, 29 Dec 2009 04:11:17 +0100 (CET)
From: Andres Freund <andres@anarazel.de>
To: Michael Clemmons <glassresistor@gmail.com>
Subject: Re: [HACKERS] Faster CREATE DATABASE by delaying fsync (was 8.4.1
	ubuntu karmic slow createdb)
Date: Tue, 29 Dec 2009 04:11:14 +0100
User-Agent: KMail/1.13.0 (Linux/2.6.33-rc2-andres-00001-g6e0f169; KDE/4.3.85;
	x86_64; ; )
Cc: pgsql-hackers@postgresql.org, Greg Smith <greg@2ndquadrant.com>,
	Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org,
	Hannu Krosing <hannu@2ndquadrant.com>,
	Scott Marlowe <scott.marlowe@gmail.com>
References: <4427a97a0912101235x3ade510blcc00d5ce9883c6b6@mail.gmail.com>
	<200912290355.38382.andres@anarazel.de>
	<4427a97a0912281904qc90817i4edd6852b26dde7f@mail.gmail.com>
In-Reply-To: <4427a97a0912281904qc90817i4edd6852b26dde7f@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-15"
Content-Transfer-Encoding: 7bit
Message-Id: <200912290411.15659.andres@anarazel.de>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.43 tagged_above=-10 required=5 tests=AWL=0.169,
	BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200912/261
X-Sequence-Number: 36875

On Tuesday 29 December 2009 04:04:06 Michael Clemmons wrote:
> Maybe not crash out but in this situation.
> N=0
> while(N>=0):
>     CREATE DATABASE new_db_N;
> Since the fsync is the part which takes the memory and time but is
>  happening in the background want the fsyncs pile up in the background
>  faster than can be run filling up the memory and stack.
> This is very likely a mistake on my part about how postgres/processes
The difference should not be visible outside the "CREATE DATABASE ..." at all.
Currently the process simplifiedly works like:

------------
for file in source directory:
	copy_file(source/file, target/file);
	fsync(target/file);
------------

I changed it to:

-------------
for file in source directory:
	copy_file(source/file, target/file);

	/*please dear kernel, write this out, but dont block*/
	posix_fadvise(target/file, FADV_DONTNEED); 

for file in source directory:
	fsync(target/file);
-------------

If at any point in time there is not enough cache available to cache anything 
copy_file() will just have to wait for the kernel to write out the data.
fsync() does not use memory itself.

Andres