Re: [PERFORM] 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, 15:24, Claudio Freire wrote:
> On Tue, Feb 28, 2012 at 11:15 AM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
>> 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.
>
> It speeds a lot more than the initial load of data.
>
> Assuming the database is read-only, but not the filesystem (ie: it's
> not a slave, in which case all this is moot, as you said, there are no
> writes on a slave). That is, assuming this is a read-only master, then
> read-only queries don't mean read-only filesystem. Bookkeeping tasks
> like updating catalog dbs, statistics tables, page cleanup, stuff like
> that can actually result in writes.
>
> Writes that go through the WAL and then the filesystem.

I'm not sure what maintenance tasks you mean. Sure, there are tasks that
need to be performed after the load (stats, hint bits, updating system
catalogs etc.) but this may happen once right after the load and then
there's effectively zero write activity. Unless the database needs to
write temp files, but that contradicts the 'fits into RAM' assumption ...

> With fsync=off, those writes happen on the background, and are carried
> out by the OS. Effectively releasing postgres from having to wait on
> them, and, assuming there's enough RAM, merging repeated writes to the
> same sectors in one operation in the end. For stats, bookkeeping, and
> who knows what else, the merging would be quite effective. With enough
> RAM to hold the entire DB, the merging would effectively keep
> everything in RAM (in system buffers) until there's enough I/O
> bandwidth to transparently push that to persistent storage.

The writes are always carried out by the OS - except when dirty_ratio is
exceeded (but that's a different story) and WAL with direct I/O enabled.
The best way to allow merging the writes in shared buffers or page cache
is to set the checkpoint_segments / checkpoint_timeout high enough.

That way the transactions won't need to wait for writes to data files
(which is the part related to evictions of buffers from cache). And
read-only transactions won't need to wait at all because they don't need
to wait for fsync on WAL.

> In essence, what was required, to keep everything in RAM for as much
> as possible.
>
> It *does* in the same way affect buffer eviction - it makes eviction
> *very* quick, and re-population equally as quick, if everything fits
> into memory.

No it doesn't. Only a write caused by a background process (due to full
shared buffers) means immediate eviction. A simple write (caused by a
checkpoint) does not evict the page from shared buffers. Not even a
background writer evicts a page from shared buffers, it merely marks them
as 'clean' and leaves them there. And all those writes happen on the
background, so the clients don't need to wait for them to complete (except
for xlog checkpoints).

kind regards
Tomas