Re: Cache hit ratio

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(at)postgresql(dot)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(dot)org(at)postgresql(dot)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(dot)org(at)postgresql(dot)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(at)postgresql(dot)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(dot)7070108(at)agliodbs(dot)com>
Date: Tue, 03 Apr 2007 11:29:56 -0700
From: Josh Berkus <josh(at)agliodbs(dot)com>
User-Agent: Thunderbird 1.5.0.10 (Macintosh/20070221)
MIME-Version: 1.0
To: Guillaume Cottenceau <gc(at)mnc(dot)ch>
CC: Jean Arnaud <Jean(dot)Arnaud(at)inrialpes(dot)fr>,
PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Cache hit ratio
References: <461260C4(dot)8050508(at)inrialpes(dot)fr> <87zm5ppkzw(dot)fsf(at)meuh(dot)mnc(dot)lan>
In-Reply-To: <87zm5ppkzw(dot)fsf(at)meuh(dot)mnc(dot)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