From: Richard Huxton <dev@archonet.com>
To: Vitaly Belman <vitalib@012.net.il>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: PostgreSQL caching
Date: Fri, 21 May 2004 16:34:12 +0100
Message-ID: <40AE2174.7060107@archonet.com> (raw)
In-Reply-To: <40782517171.20040521174209@012.net.il>
References: <40782517171.20040521174209@012.net.il>
Vitaly Belman wrote:
> Hello,
>
> I have the following problem:
>
> When I run some query after I just run the Postmaster, it takse
> several seconds to execute (sometimes more than 10), if I rerun it
> again afterwards, it takes mere milliseconds.
>
> So, I guess it has to do with PostgreSQL caching.. But how exactly
> does it work? What does it cache? And how can I control it?
There are two areas of cache - PostgreSQL's shared buffers and the
operating system's disk-cache. You can't directly control what data is
cached, it just keeps track of recently used data. It sounds like PG
isn't being used for a while so your OS decides to use its cache for
webserver files.
> I would like to load selected information in the memory before a user
> runs the query. Can I do it somehow? As PostgreSQL is used in my case
> as webserver, it isn't really helping if the user has to wait 10
> seconds every time he goes to a new page (even if refreshing the page
> would be really quick, sine Postgre already loaded the data to
> memory).
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.
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, vitalib@012.net.il
Subject: Re: PostgreSQL caching
In-Reply-To: <40AE2174.7060107@archonet.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