Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id D08519552D4 for ; Tue, 28 Feb 2012 10:16:06 -0400 (AST) Received: from [89.187.132.199] (helo=elizabeth.gransy.com) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S2NqQ-0004GS-AY for pgsql-performance@postgresql.org; Tue, 28 Feb 2012 14:16:05 +0000 Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id BFFF815E4327; Tue, 28 Feb 2012 15:15:24 +0100 (CET) Received: from 62.40.76.70 (proxying for unknown) (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Tue, 28 Feb 2012 15:15:24 +0100 Message-ID: <347477cf700da0b19ad62b50cae5ae1c.squirrel@sq.gransy.com> In-Reply-To: References: <1330346798.90731.YahooMailNeo@web30808.mail.mud.yahoo.com> <8da9b15646d973a5ee23ba49faeee8fd.squirrel@sq.gransy.com> Date: Tue, 28 Feb 2012 15:15:24 +0100 Subject: =?utf-8?B?UmU6IFtQRVJGT1JNXSBSZTogW1BFUkZPUk1dIFJlOiBbUEVSRk9STV0gUmU6?= =?utf-8?B?IOWbnuWkje+8miBbUEVSRk9STV0gUEcgYXMgaW4tbWVtb3J5IGRiPyBIb3cg?= =?utf-8?B?dG8gd2FybSB1cCBhbmQgcmUtcG9wdWxhdGUgYnVmZmVycz8gSG93IHRvIHJl?= =?utf-8?B?YWQgaW4gYWxsIHR1cGxlcyBpbnRvIG1lbW9yeT8=?= From: "Tomas Vondra" To: "Claudio Freire" Cc: "Tomas Vondra" , "Stefan Keller" , "Wales Wang" , "Jeff Janes" , "pgsql-performance@postgresql.org" , "Stephen Frost" User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 X-Priority: 3 (Normal) Importance: Normal Content-Transfer-Encoding: quoted-printable X-Host-Lookup-Failed: Reverse DNS lookup failed for 89.187.132.199 (deferred) X-Pg-Spam-Score: -1.1 (-) X-Archive-Number: 201202/245 X-Sequence-Number: 46417 On 28 =C3=9Anor 2012, 14:52, Claudio Freire wrote: > On Tue, Feb 28, 2012 at 10:38 AM, Tomas Vondra wrote: >> On 28 =C3=9Anor 2012, 14:08, Claudio Freire wrote: >>> On Tue, Feb 28, 2012 at 5:30 AM, Stefan Keller >>> wrote: >>>> >>>> But what I'm finally after is a solution, where records don't get >>>> pushed back to disk a.s.a.p. but rather got hold in memory as long a= s >>>> possible assuming that there is enough memory. >>> >>> fsync =3D off ? >> >> I don't think this is a viable idea, unless you don't care about the >> data. > > Well, if you "keep things in memory as long as possible" (as per the > quoted message), then you don't care about memory. There's no way > memory-only DBs can provide ACID guarantees. > > synchronous_commit=3Doff goes half way there without sacrificing crash > recovery, which is another option. > >> Moreover, "fsyn=3Doff" does not mean "not writing" and writing does no= t >> mean >> "removing from shared buffers". A page written/fsynced during a >> checkpoint >> may stay in shared buffers. > > The OS will write in the background (provided there's enough memory, > which was an assumption on the quoted message). It will not interfere > with other operations, so, in any case, writing or not, you get what > you want. > >> AFAIK the pages are not removed from shared buffers without a reason. = So >> a >> dirty buffer is written to a disk (because it needs to, to keep ACID) >> but >> stays in shared buffers as "clean" (unless it was written by a backend= , >> which means there's not enough memory). > > Just writing is not enough. ACID requires fsync. If you don't fsync > (be it with synchronous_commit=3Doff or fsync=3Doff), then it's not ful= l > ACID already. > Because a crash at a bad moment can always make your data nonpersistent= . I haven't said writing is sufficient for ACID, I said it's required. Whic= h is kind of obvious because of the "durability" part. > That's an unavoidable result of keeping things in memory. Why? IIRC the OP was interested in keeping the data in memory for queryin= g and that the database is read-only after it's populated with data (once a day). How does writing the transactional logs / data files properly interfere with that? I haven't investigated why exactly the data are not cached initially, but none of the options that I can think of could be "fixed" by setting "fsync=3Doff". That's something that influences writes (not read-only database) and I don't think it influences how buffers are evicted from shared buffers / page cache. It might speed up the initial load of data, but that's not what the OP wa= s asking. kind regards Tomas