pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_reset_stats + cache I/O %
8+ messages / 3 participants
[nested] [flat]
* pg_reset_stats + cache I/O %
@ 2006-03-07 19:07 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 1 reply; 8+ messages in thread
From: mcelroy, tim @ 2006-03-07 19:07 UTC (permalink / raw)
To: pgsql-performance
Good afternoon,
Relatively new to PostgreSQL and have been assigned the task of capturing
cache I/O % hits. I figured out (thanks to other posts) how to turn on the
capture and what stats to (kind of) capture. I did find a view in the
archives as shown below but it does not execute, error follows. I'm using
8.0.1 so that shouldn't be the issue. Any help will be appreciated.
Also, I also found pg_reset_stats.tar.gz in the archives with a lot of talk
regarding its addition as a patch, did it ever make it in? If not, can I
get a copy of it somewhere? The tar.gz gets corrupted when I download it.
Thank you,
Tim
CREATE VIEW cache_hits AS SELECT relname, ROUND(CASE WHEN heap_blks_hit = 0
THEN 0 ELSE ((heap_blks_hit::float /(heap_blks_read::float +
heap_blks_hit::float)) * 100) END ,2) as heap, ROUND(CASE WHEN idx_blks_hit
= 0 THEN 0 ELSE ((idx_blks_hit::float /(idx_blks_read::float +
idx_blks_hit::float)) * 100) END,2) as index,ROUND(CASE WHEN toast_blks_hit
= 0 THEN 0 ELSE ((toast_blks_hit::float /(toast_blks_read::float +
toast_blks_hit::float)) * 100) END,2) as toast FROM pg_statio_user_tables
WHERE heap_blks_read <> 0 or idx_blks_read <> 0 OR toast_blks_read <> 0
union select 'ALL TABLES', ROUND(CASE WHEN sum(heap_blks_hit) = 0 THEN 0
ELSE ((sum(heap_blks_hit::float) /(sum(heap_blks_read::float) +
sum(heap_blks_hit::float))) * 100) END ,2) as heap, ROUND(CASE WHEN
sum(idx_blks_hit) = 0 THEN 0 ELSE ((sum(idx_blks_hit::float)
/(sum(idx_blks_read::float) + sum(idx_blks_hit::float))) * 100) END,2) as
index,ROUND(CASE WHEN sum(toast_blks_hit) = 0 THEN 0 ELSE
((sum(toast_blks_hit::float) /(sum(toast_blks_read::float) +
sum(toast_blks_hit::float))) * 100) END,2) as toast FROM
pg_statio_user_tables HAVING sum(heap_blks_read) <> 0 or sum(idx_blks_read)
<> 0 OR sum(toast_blks_read) <> 0 ;
ERROR: function round(double precision, integer) does not exist
HINT: No function matches the given name and argument types. You may need
to add explicit type casts.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_reset_stats + cache I/O %
@ 2006-03-07 19:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-03-07 19:36 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_reset_stats + cache I/O %
@ 2006-03-08 13:59 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 1 reply; 8+ messages in thread
From: mcelroy, tim @ 2006-03-08 13:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_reset_stats + cache I/O %
@ 2006-03-08 18:27 Jim C. Nasby <jnasby@pervasive.com>
parent: mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-03-08 18:27 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_reset_stats + cache I/O %
@ 2006-03-08 18:35 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 1 reply; 8+ messages in thread
From: mcelroy, tim @ 2006-03-08 18:35 UTC (permalink / raw)
To: 'Jim C. Nasby' <jnasby@pervasive.com>; +Cc: pgsql-performance
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.
Tim
-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com]
Sent: Wednesday, March 08, 2006 1:28 PM
To: mcelroy, tim
Cc: 'Tom Lane'; 'pgsql-performance@postgresql.org'
Subject: Re: [PERFORM] pg_reset_stats + cache I/O %
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_reset_stats + cache I/O %
@ 2006-03-09 03:23 Jim C. Nasby <jnasby@pervasive.com>
parent: mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-03-09 03:23 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_reset_stats + cache I/O %
@ 2006-03-09 13:13 mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 1 reply; 8+ messages in thread
From: mcelroy, tim @ 2006-03-09 13:13 UTC (permalink / raw)
To: 'Jim C. Nasby' <jnasby@pervasive.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_reset_stats + cache I/O %
@ 2006-03-10 15:15 Jim C. Nasby <jnasby@pervasive.com>
parent: mcelroy, tim <tim.mcelroy@bostonstock.com>
0 siblings, 0 replies; 8+ messages in thread
From: Jim C. Nasby @ 2006-03-10 15:15 UTC (permalink / raw)
To: mcelroy, tim <tim.mcelroy@bostonstock.com>; +Cc: pgsql-performance
On Thu, Mar 09, 2006 at 08:13:30AM -0500, mcelroy, tim wrote:
> 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
Ugh... the default config won't get you far. Take a look here:
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html
Or, I've been planning on posting a website with some better "canned"
postgresql.conf config files for different configurations; if you send
me specs on the machine you're running on I'll come up with something
that's at least more reasonable.
--
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-03-10 15:15 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-07 19:07 pg_reset_stats + cache I/O % mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-03-07 19:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-03-08 13:59 Re: pg_reset_stats + cache I/O % mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-03-08 18:27 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-08 18:35 Re: pg_reset_stats + cache I/O % mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-03-09 03:23 ` Jim C. Nasby <jnasby@pervasive.com>
2006-03-09 13:13 Re: pg_reset_stats + cache I/O % mcelroy, tim <tim.mcelroy@bostonstock.com>
2006-03-10 15:15 ` Jim C. Nasby <jnasby@pervasive.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