From: Chris Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL caching
Date: Fri, 21 May 2004 13:22:50 -0400
Message-ID: <604qq9hez9.fsf@dev6.int.libertyrms.info> (raw)
References: <40782517171.20040521174209@012.net.il>
<40AE2174.7060107@archonet.com>
dev@archonet.com (Richard Huxton) writes:
> If you could "pin" data in the cache it would run quicker, but at the
> cost of everything else running slower.
>
> Suggested steps:
> 1. Read the configuration/tuning guide at:
> http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
> 2. Post a sample query/explain analyse that runs very slowly when not
> cached.
> 3. If needs be, you can write a simple timed script that performs a
> query. Or, the autovacuum daemon might be what you want.
I don't think this case will be anywhere near so simple to resolve.
I have seen this phenomenon occur when a query needs to pull a
moderate number of blocks into memory to satisfy a query that involves
some moderate number of rows.
Let's say you need 2000 rows, which fit into 400 blocks.
The first time the query runs, it needs to pull those 400 blocks off
disk, which requires 400 reads of 8K of data. That can easily take a
few seconds of I/O.
The second time, not only are those blocks cached, they are probably
cached in the buffer cache, so that the I/O overhead disappears.
There's very likely no problem with the table statistics; they are
leading to the right query plan, which happens to need to do 5 seconds
of I/O to pull the data into memory.
What is essentially required is the "prescient cacheing algorithm,"
where the postmaster must consult /dev/esp in order to get a
prediction of what blocks it may need to refer to in the next sixty
seconds.
--
(format nil "~S@~S" "cbbrowne" "cbbrowne.com")
http://cbbrowne.com/info/linuxdistributions.html
"Normally, we don't do people's homework around here, but Venice is a
very beautiful city, so I'll make a small exception."
--- Robert Redelmeier compromises his principles
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: cbbrowne@acm.org
Subject: Re: PostgreSQL caching
In-Reply-To: <604qq9hez9.fsf@dev6.int.libertyrms.info>
* 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