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.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
