To: Jean Arnaud <Jean.Arnaud@inrialpes.fr>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Cache hit ratio
References: <461260C4.8050508@inrialpes.fr>
From: Guillaume Cottenceau <gc@mnc.ch>
Date: 03 Apr 2007 16:53:39 +0200
In-Reply-To: <461260C4.8050508@inrialpes.fr>
Message-ID: <87zm5ppkzw.fsf@meuh.mnc.lan>
Lines: 64
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Jean Arnaud <Jean.Arnaud 'at' inrialpes.fr> writes:

> Hi
> 
> Is there a way to get the cache hit ratio in PostGreSQL ?

When you activate:

    stats_block_level = true
    stats_row_level = true

you will get global statistics, per table and per index, about
read disk blocks and saved reads thanks to buffers.


That said, I'd like to add that however, I am not sure what
performance gain we should expect by increasing the buffers to
increase the cache hit ratio.

For example, for a bunch of given heavy SQL queries, with -B 1000
(pg 7.4) the difference is:

         select * from pg_statio_user_indexes where indexrelname = 'pk_themes';
      relid   | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit 
    ----------+------------+------------+---------+--------------+---------------+--------------
     77852514 |   86437474 | public     | themes  | pk_themes    |           220 |            0
    
        select * from pg_statio_user_indexes where indexrelname = 'pk_themes';
      relid   | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit 
    ----------+------------+------------+---------+--------------+---------------+--------------
     77852514 |   86437474 | public     | themes  | pk_themes    |           275 |            0

which shows the index on primary keys is used, but is always read
from disk.

If I then use -B 20000 (kernel reports the postmaster process
enlarges from 22M to 173M of RSS), the difference is:

        select * from pg_statio_user_indexes where indexrelname = 'pk_themes';
      relid   | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit 
    ----------+------------+------------+---------+--------------+---------------+--------------
     77852514 |   86437474 | public     | themes  | pk_themes    |            55 |          110
    
        select * from pg_statio_user_indexes where indexrelname = 'pk_themes';
      relid   | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit 
    ----------+------------+------------+---------+--------------+---------------+--------------
     77852514 |   86437474 | public     | themes  | pk_themes    |            55 |          165

which shows postmaster manages to keep the index in buffers.

But, the clock time used for the request is actually identical
when using -B 1000 or -B 20000. I suppose the kernel is bringing
the performance difference thanks to filesystem caching.

In conclusion, I guess that using postmaster cache rather than
kernel cache is probably better in the long run, because
postmaster might be able to make better caching decisions than
the kernel because it has additional information, but I am not
sure in which circumstances and the amount of better decisions it
can take.

-- 
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36