Skip site navigation (1) Skip section navigation (2)

Re: seq scan cache vs. index cache smackdown

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Subject: Re: seq scan cache vs. index cache smackdown
Date: 2005-02-17 03:22:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Josh Berkus wrote:
> Now you can see why other DBMSs don't use the OS disk cache.  ...
> 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:
    /* the generator of dirty data writes back at this ratio */
   /* start background writeback */
   /* the interval between [some style of] writebacks */
   /* 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.

In response to

pgsql-performance by date

Next:From: JMDate: 2005-02-17 13:29:01
Subject: PG proper configuation for a php forum
Previous:From: Kevin BrownDate: 2005-02-16 21:09:28
Subject: Re: How to interpret this explain analyse?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group