X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 996D19DC815
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Mar 2006 14:27:43 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 47225-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Wed,  8 Mar 2006 14:27:42 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from noel.decibel.org (noel.decibel.org [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id 4A7749DC826
	for <pgsql-performance@postgresql.org>;
	Wed,  8 Mar 2006 14:27:40 -0400 (AST)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 844B956444; Wed,  8 Mar 2006 12:27:41 -0600 (CST)
Received: (hashcash-sendmail, from uid 1001);
	Wed, 8 Mar 2006 12:27:40 -0600
Date: Wed, 8 Mar 2006 12:27:40 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: "mcelroy, tim" <tim.mcelroy@bostonstock.com>
Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>,
	"'pgsql-performance@postgresql.org'" <pgsql-performance@postgresql.org>
Subject: Re: pg_reset_stats + cache I/O %
Message-ID: <20060308182740.GF45250@pervasive.com>
References: 
 <0C4841B42F87D51195BD00B0D020F5CB044B241C@morpheus.bostonstock.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <0C4841B42F87D51195BD00B0D020F5CB044B241C@morpheus.bostonstock.com>
X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.11
X-Hashcash: 
 1:20:060308:tim.mcelroy@bostonstock.com::EBH6kZ8+QskV2maa:0000000000
	0000000000000000000000004VbS
X-Hashcash: 
 1:20:060308:tgl@sss.pgh.pa.us::osI3MjFw2ZFqWvU7:00000000000000000000
	00000000000000000000000023uo
X-Hashcash: 
 1:20:060308:pgsql-performance@postgresql.org::Pv+P7Wt47081rGes:00000
	000000000000000000000000292z
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.109 required=5 tests=[AWL=0.109]
X-Spam-Score: 0.109
X-Spam-Level: 
X-Archive-Number: 200603/88
X-Sequence-Number: 17525

Out of curiosity, why do you want this info? More important, do the
folks who are looking at this understand that a key part of PostgreSQL's
tuning strategy is to let the OS handle the bulk of the caching?

On Wed, Mar 08, 2006 at 08:59:51AM -0500, mcelroy, tim wrote:
> Thanks Tom, sorry I neglected to copy the list on my previous email.....
> 
> Does this query make sense and is it valid for an accurate cache % hit ratio
> for the entire DB?  I would assume I could use the same logic with other
> views such as pg_stat_user_tables to get a per table ratio?
> 
> SELECT 100 - round((blks_hit::numeric / (blks_hit::numeric +
> blks_read::numeric)) * 100,2)
> AS "Cache % Hit"
> FROM pg_stat_database
> WHERE datname = 'Fix1';
> 
> <RETURNS>
> 
> Cache % Hit
> --------------------
>        98.06
> (1 row)
> 
> Thank you,
> Tim
> 
>  -----Original Message-----
> From: 	Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Sent:	Tuesday, March 07, 2006 2:37 PM
> To:	mcelroy, tim
> Cc:	'pgsql-performance@postgresql.org'
> Subject:	Re: [PERFORM] pg_reset_stats + cache I/O % 
> 
> "mcelroy, tim" <tim.mcelroy@bostonstock.com> writes:
> > ERROR:  function round(double precision, integer) does not exist
> 
> Try coercing to numeric instead of float.  Also, it'd be a good idea to
> put that coercion outside the sum()'s instead of inside --- summing
> bigints is probably noticeably faster than summing numerics.
> 
> 			regards, tom lane

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461