Date: Tue, 3 Apr 2007 10:33:19 -0700 (PDT)
From: Ben <bench@silentmedia.com>
To: Jean Arnaud <Jean.Arnaud@inrialpes.fr>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Cache hit ratio
In-Reply-To: <461260C4.8050508@inrialpes.fr>
Message-ID: <Pine.LNX.4.64.0704031032200.26177@localhost.localdomain>
References: <461260C4.8050508@inrialpes.fr>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8BIT

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
>
From pgsql-performance-owner@postgresql.org  Tue Apr  3 15:31:59 2007
Received: from localhost (maia-1.hub.org [200.46.204.191])
	by postgresql.org (Postfix) with ESMTP id 9B2A49FBAAE
	for <pgsql-performance-postgresql.org@postgresql.org>; Tue,  3 Apr 2007 15:31:58 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
 by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024)
 with ESMTP id 24736-05-2
 for <pgsql-performance-postgresql.org@postgresql.org>;
 Tue,  3 Apr 2007 15:31:47 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from outmail129163.authsmtp.co.uk (outmail129163.authsmtp.co.uk [62.13.129.163])
	by postgresql.org (Postfix) with ESMTP id 098899FBAE9
	for <pgsql-performance@postgresql.org>; Tue,  3 Apr 2007 15:30:49 -0300 (ADT)
Received: from outmail128178.authsmtp.net (outmail128178.authsmtp.net [62.13.128.178])
	by punt3.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l33IUILU071444;
	Tue, 3 Apr 2007 19:30:19 +0100 (BST)
Received: from [192.168.2.2] (adsl-63-195-55-98.dsl.snfc21.pacbell.net [63.195.55.98])
	(authenticated bits=0)
	by mail.authsmtp.com (8.13.8/8.13.8/Kp) with ESMTP id l33IUEQU046624;
	Tue, 3 Apr 2007 19:30:15 +0100 (BST)
Message-ID: <46129D24.7070108@agliodbs.com>
Date: Tue, 03 Apr 2007 11:29:56 -0700
From: Josh Berkus <josh@agliodbs.com>
User-Agent: Thunderbird 1.5.0.10 (Macintosh/20070221)
MIME-Version: 1.0
To: Guillaume Cottenceau <gc@mnc.ch>
CC: Jean Arnaud <Jean.Arnaud@inrialpes.fr>,
        PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Cache hit ratio
References: <461260C4.8050508@inrialpes.fr> <87zm5ppkzw.fsf@meuh.mnc.lan>
In-Reply-To: <87zm5ppkzw.fsf@meuh.mnc.lan>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Server-Quench: 5e2c464c-e211-11db-a443-001185d377ca
X-AuthRoute: OCdyZgscClZXSx8a IioLCC5HRQ8+YBZL BAkGMA9GIUINWEQK c1ACdR16KEdbHwkB BHYKUl5XUFdwXC1z aBRQZABDZ09QVg11 Uk1LR01SWllrAmVk c397Uh10cQdCNn9x ZEEsWyVZD0B8cRVg F0xVFHAHZDMydTEb VENFdwNVcQtPKhxC bQMuGhFYa3VsBg8C MSgJGgV5FjxUKCkd QgARZUgbXUcMdmdp 
X-Authentic-SMTP: 61633136333939.squirrel.dmpriest.net.uk:556/Kp
X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse
X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system!
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200704/23
X-Sequence-Number: 23900

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