Re: seq scan cache vs. index cache smackdown

Josh Berkus wrote:
>
> Now you can see why other DBMSs don't use the OS disk cache.  ...
> ...as long as we use the OS disk cache, we can't
> eliminate checkpoint spikes, at least on Linux.

Wouldn't the VM settings like the ones under /proc/sys/vm
and/or the commit=XXX mount option if using ext3 be a good
place to control this?

It seems if you wanted, by setting /proc/sys/vm/dirty_background_ratio
and /proc/sys/vm/dirty_expire_centisecs very low you'd be constantly
flushing dirty pages.


Has anyone experimented with these kinds of values:
/proc/sys/vm/dirty_ratio
    /* the generator of dirty data writes back at this ratio */
/proc/sys/vm/dirty_background_ratio
   /* start background writeback */
/proc/sys/vm/dirty_writeback_centisecs
   /* the interval between [some style of] writebacks */
/proc/sys/vm/dirty_expire_centisecs
   /* the number of centiseconds that data is allowed to remain dirty


I tried these to workaround the opposite kind of problem.... on a
laptop running linux under vmware I wanted to avoid having it do writes
quickly to make each individual transaction go faster; at the expense
of a big spike in IO that the sales guy would trigger explicitly before
talking a while.  Setting each of those very high and using a
commit=600 mount option made the whole demo run with very little
IO except for the explicit sync; but I never took the time
to understand which setting mattered to me or why.


>>It seems inevitable that Postgres will eventually eliminate that redundant
>>layer of buffering. Since mmap is not workable, that means using O_DIRECT
>>to read table and index data.