Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cache hit ratio

Cache hit ratio

From
Jean Arnaud
Date:
Hi

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

Cheers

--
-- Jean Arnaud
-- Projet SARDES
-- INRIA Rhône-Alpes / LSR-IMAG
-- http://sardes.inrialpes.fr/~jarnaud

Re: Cache hit ratio

From
Guillaume Cottenceau
Date:
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

Re: Cache hit ratio

From
"Luke Lonergan"
Date:
Set log_executor_stats=true;

Then look in the log after running statements (or tail -f logfile).

- Luke


On 4/3/07 7:12 AM, "Jean Arnaud" <Jean.Arnaud@inrialpes.fr> wrote:

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

Re: Cache hit ratio

From
Ben
Date:
Have you looked at the pg_stat_* views? You must enable stats collection
to see any data in them, but that's probably what you're looking for.

On Tue, 3 Apr 2007, Jean Arnaud wrote:

> Hi
>
> Is there a way to get the cache hit ratio in PostGreSQL ?
>
> Cheers
>
> --
> -- Jean Arnaud
> -- Projet SARDES
> -- INRIA Rhône-Alpes / LSR-IMAG
> -- http://sardes.inrialpes.fr/~jarnaud
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>              http://archives.postgresql.org
>

Re: Cache hit ratio

From
Josh Berkus
Date:
Guillaume,


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

or, more likely, from the FS cache.

> 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.

Yes.  The only way you'd see a differeence is on a mixed load of
concurrent read & write queries.  Any single-query test is unlikely to
show a difference between using the FS cache and shared_buffers.

--Josh