Sorry I realized your fears :)
PostgreSQL is a new (last four months) install here and I'm responsible for it. Great DB and I enjoy working with it a lot and learning the nuances of it. Keep in mind that the management are 'old-time' system folks who love charts showing system and in this case DB performance. I'm basically just using the out-of-the-box defaults in my postgresql.conf file and that seems to be working so far. But as the DB grows I just need a way to prove the DB is functioning properly when apps get slow. You know the old you're guilty till proven innocent syndrome.... Ok enough on that.
Yes, thank you we try to keep on the ball regarding system monitoring. BTW - I'm still waiting to see if anyone out there can say yea or nay if the SQL I wrote is a valid indicator of overall cache % hit?
> 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: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: Wednesday, March 08, 2006 10:24 PM
To: mcelroy, tim
Cc: 'pgsql-performance@postgresql.org'
Subject: Re: [PERFORM] pg_reset_stats + cache I/O %
On Wed, Mar 08, 2006 at 01:35:35PM -0500, mcelroy, tim wrote:
> I actually need this info as I was tasked by management to provide it. Not
> sure if they understand that or not, I do but management does like to see
> how well the system and its components are performing. Also, I would
> utilize these results to test any cache tuning changes I may make.
What I feared. While monitoring cache hit % over time isn't a bad idea,
it's less than half the picture, which makes fertile ground for
optimizing for some mythical target instead of actual system
performance. If the "conclusion" from these numbers is that
shared_buffers needs to get set larger than min(50000, 10% of memory)
I'd very seriously re-consider how performance tuning is being done.
But hopefully I'm just being paranoid and you guys are just doing a
great job of monitoring things and keeping on the ball. :)
--
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