On 28 Únor 2012, 14:52, Claudio Freire wrote: > On Tue, Feb 28, 2012 at 10:38 AM, Tomas Vondra <tv@xxxxxxxx> wrote: >> On 28 Únor 2012, 14:08, Claudio Freire wrote: >>> On Tue, Feb 28, 2012 at 5:30 AM, Stefan Keller <sfkeller@xxxxxxxxx> >>> 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 as >>>> possible assuming that there is enough memory. >>> >>> fsync = 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=off goes half way there without sacrificing crash > recovery, which is another option. > >> Moreover, "fsyn=off" does not mean "not writing" and writing does not >> 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=off or fsync=off), then it's not full > 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. Which 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 querying 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=off". 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 was asking. kind regards Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
