pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedCache hit ratio
4+ messages / 4 participants
[nested] [flat]
* Cache hit ratio
@ 2007-04-03 14:12 Jean Arnaud <Jean.Arnaud@inrialpes.fr>
0 siblings, 3 replies; 4+ messages in thread
From: Jean Arnaud @ 2007-04-03 14:12 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cache hit ratio
@ 2007-04-03 14:53 Guillaume Cottenceau <gc@mnc.ch>
parent: Jean Arnaud <Jean.Arnaud@inrialpes.fr>
2 siblings, 0 replies; 4+ messages in thread
From: Guillaume Cottenceau @ 2007-04-03 14:53 UTC (permalink / raw)
To: Jean Arnaud <Jean.Arnaud@inrialpes.fr>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cache hit ratio
@ 2007-04-03 16:17 Luke Lonergan <llonergan@greenplum.com>
parent: Jean Arnaud <Jean.Arnaud@inrialpes.fr>
2 siblings, 0 replies; 4+ messages in thread
From: Luke Lonergan @ 2007-04-03 16:17 UTC (permalink / raw)
To: Jean Arnaud <Jean.Arnaud@inrialpes.fr>; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Cache hit ratio
@ 2007-04-03 17:33 Ben <bench@silentmedia.com>
parent: Jean Arnaud <Jean.Arnaud@inrialpes.fr>
2 siblings, 0 replies; 4+ messages in thread
From: Ben @ 2007-04-03 17:33 UTC (permalink / raw)
To: Jean Arnaud <Jean.Arnaud@inrialpes.fr>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-04-03 17:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-03 14:12 Cache hit ratio Jean Arnaud <Jean.Arnaud@inrialpes.fr>
2007-04-03 14:53 ` Guillaume Cottenceau <gc@mnc.ch>
2007-04-03 16:17 ` Luke Lonergan <llonergan@greenplum.com>
2007-04-03 17:33 ` Ben <bench@silentmedia.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox