Re: Postgresql cache (memory) performance + how to warm up the cache

On Mon, 1 Jun 2009, Shaul Dar wrote:

> 1. At any given time how can I check what portion (%) of specific tables and indexes is cached in memory?

This is a bit tricky. PostgreSQL caches information in its shared_buffers
cache, and you can get visibility into that if you install the
contrib/pg_buffercache library into your database. I go over the theory
here and give some sample queries, including the one you're asking for, in
my "Inside the PostgreSQL Buffer Cache" presentation at
http://www.westnet.com/~gsmith/content/postgresql/

However, in a normal installation, the operating system cache will have a
significant amount of data stored in it as well. Figuring out that is
more complicated. The best integrated script I've seen for that so far as
at http://www.kennygorman.com/wordpress/?p=250 but that's not really
integrated into an easy to use tool yet. Improving that is on a couple of
people's agendas for the next PostgreSQL release, that's as good as it
gets for what I'm aware that's already public.

> 2. What is the best way to warm up the cache before opening the DB to queries? E.g. "select *" forces a sequential scan (~15 minutes
> on cold DB) but response times following it are still poor. Is there a built-in way to do this instead of via queries?a

There is an optimization in PostgreSQL 8.3 and later that keeps sequential
scans from using large amounts of the PostgreSQL buffer cache, and full
table scans don't pull the index pages at all--and those are likely what
you really want cached.

What you probably want to do is run a query that uses an index
aggressively (confirm this via EXPLAIN) instead. You might be able to get
that to happen by selecting everything using an ORDER BY that is expensive
(from a planner cost perspective), therefore making the indexed scan seem
more attractive, but the exact behavior here depends on how you've got
your planner parameters setup.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD