On 28 Únor 2012, 14:52, Claudio Freire wrote:
> On Tue, Feb 28, 2012 at 10:38 AM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> On 28 Únor 2012, 14:08, Claudio Freire wrote:
>>> On Tue, Feb 28, 2012 at 5:30 AM, Stefan Keller <sfkeller(at)gmail(dot)com>
>>>> 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
> 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
>> "removing from shared buffers". A page written/fsynced during a
>> 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
>> dirty buffer is written to a disk (because it needs to, to keep ACID)
>> 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
In response to
pgsql-performance by date
|Next:||From: Claudio Freire||Date: 2012-02-28 14:24:09|
|Subject: Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复?==?UTF-8?Q?： [PERFORM] PG as in-memory db? How to warm up and re-popu?==?UTF-8?Q?late buffers? How to read in all tuples into memory?|
|Previous:||From: Claudio Freire||Date: 2012-02-28 13:52:28|
|Subject: Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] ?==?UTF-8?Q?PG as in-memory db? How to warm up and re-populate buffers? ?==?UTF-8?Q?How to read in all tuples into memory?|