Re: [PERFORM] pg_reset_stats + cache I/O %
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_reset_stats + cache I/O %
Title: RE: [PERFORM] pg_reset_stats + cache I/O % 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:[EMAIL PROTECTED]] 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(5, 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [PERFORM] pg_reset_stats + cache I/O %
Title: RE: [PERFORM] pg_reset_stats + cache I/O % 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:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] 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
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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] pg_reset_stats + cache I/O %
Title: RE: [PERFORM] pg_reset_stats + cache I/O % 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:[EMAIL PROTECTED]] 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:[EMAIL PROTECTED]] 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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(5, 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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] pg_reset_stats + cache I/O %
mcelroy, tim [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
