Re: [PERFORM] Re: [PERFORM] Re: [PERFORM] Re: 回复： [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?

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>
>>> 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