pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matt Clark <matt@ymogen.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Sullivan <andrew@libertyrms.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: reindex/vacuum locking/performance?
Date: Sun, 5 Oct 2003 18:59:14 +0100
Message-ID: <LFEIJBEOKGPDHCEMDGNFOEPMCFAA.matt@ymogen.net> (raw)
In-Reply-To: <1368.1065373870@sss.pgh.pa.us>

> The point is that a big seqscan (either VACUUM or a plain table scan)
> hits a lot of pages, and thereby tends to fill your cache with pages
> that aren't actually likely to get hit again soon, perhaps pushing out
> pages that will be needed again soon.  This happens at both the
> shared-buffer and kernel-disk-cache levels of caching.

OK, I had thought (wrongly it seems, as usual, but this is how we learn!)
that a plain VACUUM did not incur a read of all pages.  I still don't
understand *why* it does, but I'll take your word for it.

Clearly if it distorts the 'normal' balance of pages in any caches, PG's or
the OS's, that's a _bad thing_.  I am currently in the nice position of
having a DB that (just about) fits in RAM, so I pretty much don't care about
read performance, but I will have to soon as it grows beyond 3GB :-(  These
conversations are invaluable in planning for that dread time...

> It would be good to find some way to prevent big seqscans from
> populating cache, but I don't know of any portable way to tell the OS
> that we don't want it to cache a page we are reading.

Quite.  The only natural way would be to read those pages through some
special device, but then you might as well do raw disk access from the
get-go.  Portability vs. Performance, the age old quandary.  FWIW I and many
others stand back in pure amazement at the sheer _quality_ of PostgreSQL.


Rgds,

Matt





view thread (37+ messages)  latest in thread

Message-ID: <LFEIJBEOKGPDHCEMDGNFOEPMCFAA.matt@ymogen.net>
Permalink:  ../LFEIJBEOKGPDHCEMDGNFOEPMCFAA.matt@ymogen.net/
Also on:    postgresql.org/message-id/LFEIJBEOKGPDHCEMDGNFOEPMCFAA.matt@ymogen.net

 · 

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: matt@ymogen.net, tgl@sss.pgh.pa.us, andrew@libertyrms.info
  Subject: Re: reindex/vacuum locking/performance?
  In-Reply-To: <LFEIJBEOKGPDHCEMDGNFOEPMCFAA.matt@ymogen.net>

* 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