pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Mark Aufflick <mark@pumptheory.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: seq scan cache vs. index cache smackdown
Date: Tue, 15 Feb 2005 09:22:59 -0800
Message-ID: <200502150922.59176.josh@agliodbs.com> (raw)
In-Reply-To: <87vf8u2qgw.fsf@stark.xeocode.com>
References: <FC8ED065-7EE0-11D9-904E-003065D62456@pumptheory.com>
	<9326.1108452962@sss.pgh.pa.us>
	<87vf8u2qgw.fsf@stark.xeocode.com>

Tom, Greg, Merlin,

>  But for example,
> if our buffer management algorithm recognizes an index page as being
> heavily hit and therefore keeps it in cache for a long time, then when
> it does fall out of cache you can be sure it's going to need to be read
> from disk when it's next used, because the OS-level buffer cache has not
> seen a call for that page in a long time.  Contrariwise a page that we
> think is only on the fringe of usefulness is going to stay in the OS
> cache because we repeatedly drop it and then have to ask for it again.

Now you can see why other DBMSs don't use the OS disk cache.  There's other 
issues as well; for example, as long as we use the OS disk cache, we can't 
eliminate checkpoint spikes, at least on Linux.  No matter what we do with 
the bgwriter, fsyncing the OS disk cache causes heavy system activity.

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

Why is mmap not workable?    It would require far-reaching changes to our code 
-- certainly -- but I don't think it can be eliminated from consideration.

> What about going the other way and simply letting the o/s do all the
> caching?  How bad (or good) would the performance really be?  

Pretty bad.   You can simulate this easily by turning your shared_buffers way 
down ...

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



view thread (24+ messages)  latest in thread

Message-ID: <200502150922.59176.josh@agliodbs.com>
Permalink:  ../200502150922.59176.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200502150922.59176.josh@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, gsstark@mit.edu, tgl@sss.pgh.pa.us, mark@pumptheory.com
  Subject: Re: seq scan cache vs. index cache smackdown
  In-Reply-To: <200502150922.59176.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox