Re: [PERFORM] Cache hit ratio

[Guillaume Cottenceau]

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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [PERFORM] Cache hit ratio

[Luke Lonergan]

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 [EMAIL PROTECTED] wrote:

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



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Cache hit ratio

[Ben]

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


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Cache hit ratio

[Josh Berkus]

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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster