From: Jim C. Nasby <jnasby@pervasive.com>
To: mcelroy, tim <tim.mcelroy@bostonstock.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 'pgsql-performance@postgresql.org' <pgsql-performance@postgresql.org>
Subject: Re: pg_reset_stats + cache I/O %
Date: Wed, 8 Mar 2006 12:27:40 -0600
Message-ID: <20060308182740.GF45250@pervasive.com> (raw)
In-Reply-To: <0C4841B42F87D51195BD00B0D020F5CB044B241C@morpheus.bostonstock.com>
References: <0C4841B42F87D51195BD00B0D020F5CB044B241C@morpheus.bostonstock.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, tim.mcelroy@bostonstock.com, tgl@sss.pgh.pa.us
Subject: Re: pg_reset_stats + cache I/O %
In-Reply-To: <20060308182740.GF45250@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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