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 Tue, Feb 28, 2012 at 1:05 PM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:
> On 28 Únor 2012, 15:24, Claudio Freire wrote:
>> 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 ...

AFAIK, stats need to be constantly updated.
Not sure about the rest.

And yes, it's quite possible to require temp files without a database
that doesn't fit in memory, only big OLAP-style queries and small
enough work_mem.

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

Exactly

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

So, we're saying the same.

With all that, and enough RAM, it already does what was requested.

Maybe it would help to tune shared_buffers-to-os-cache ratio, and
dirty_ratio to allow a big portion of RAM used for write caching (if
there were enough writes which I doubt), but, in essence, un
unmodified postgres installation with enough RAM to hold the whole DB
+ shared buffers in RAM should perform quite optimally.