Some postgres statistics

#1

We're thinking about pg_stat_database which has two end columns
blks_read , blks_hit
which according to the docs below tell you which blocks have been read
from disc and which ones were in the buffer cache, ie how efficiently
your data is performing, (as opposed to reading of disc and doing
expensive IO)
If you add these together then divide by 100 (to get 1%) and divide
blks_hit by that 1% you get the percentage of blocks served from cache
and that tells you how efficient you're being. So far so good.

Somebody said something to me the other day about saving the totals,
and running the select * from pg_stat_database; every 5 minutes or ten
minutes or so. I believe what was being discussed was making the
percentage of blocks served from cache calculation at a later date.
But, having just done all the calculations on the output from a single
query and being left feeling pretty good about the logic of it all, it
concerns me that I can no longer see the benefit of running the select
* from pg_stat_database; query every ten minutes or so.

Can anyone else see a benefit of doing that? (Unless you wanted to
keep measuring the efficiency, but I don't think that was what was
being discussed.)

#2

On Thu, 30 Sep 2010 13:05:40 -0700, Paul Branon wrote:

Quote:

We're thinking about pg_stat_database which has two end columns
blks_read , blks_hit
which according to the docs below tell you which blocks have been read
from disc and which ones were in the buffer cache, ie how efficiently
your data is performing, (as opposed to reading of disc and doing
expensive IO)
If you add these together then divide by 100 (to get 1%) and divide
blks_hit by that 1% you get the percentage of blocks served from cache
and that tells you how efficient you're being. So far so good.

Somebody said something to me the other day about saving the totals, and
running the select * from pg_stat_database; every 5 minutes or ten
minutes or so. I believe what was being discussed was making the
percentage of blocks served from cache calculation at a later date. But,
having just done all the calculations on the output from a single query
and being left feeling pretty good about the logic of it all, it
concerns me that I can no longer see the benefit of running the select *
from pg_stat_database; query every ten minutes or so.

Can anyone else see a benefit of doing that? (Unless you wanted to keep
measuring the efficiency, but I don't think that was what was being
discussed.)

There is package called "pgstatspack", written by Frits Hoogland which
can be used to monitor things in a sensible way. My advice is to not re-
invent the wheel but to use the package.

--
http://mgogala.byethost5.com

#3

On 01.10.2010 04:35, Mladen Gogala wrote:

Quote:

On Thu, 30 Sep 2010 13:05:40 -0700, Paul Branon wrote:

We're thinking about pg_stat_database which has two end columns
blks_read , blks_hit
which according to the docs below tell you which blocks have been read
from disc and which ones were in the buffer cache, ie how efficiently
your data is performing, (as opposed to reading of disc and doing
expensive IO)
If you add these together then divide by 100 (to get 1%) and divide
blks_hit by that 1% you get the percentage of blocks served from cache
and that tells you how efficient you're being. So far so good.

Somebody said something to me the other day about saving the totals, and
running the select * from pg_stat_database; every 5 minutes or ten
minutes or so. I believe what was being discussed was making the
percentage of blocks served from cache calculation at a later date. But,
having just done all the calculations on the output from a single query
and being left feeling pretty good about the logic of it all, it
concerns me that I can no longer see the benefit of running the select *
from pg_stat_database; query every ten minutes or so.

Can anyone else see a benefit of doing that? (Unless you wanted to keep
measuring the efficiency, but I don't think that was what was being
discussed.)

There is package called "pgstatspack", written by Frits Hoogland which
can be used to monitor things in a sensible way. My advice is to not re-
invent the wheel but to use the package.

Also, buffer cache hit ratios are probably not the best indicator of DB
efficiency at all. This is a widely disputed in the Oracle world.
Paul, please see this for example:

http://richardfoote.wordpress.com/20...useful-or-not/

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

#4

On Fri, 01 Oct 2010 08:11:19 +0200, Robert Klemme wrote:

Quote:

Also, buffer cache hit ratios are probably not the best indicator of DB
efficiency at all. This is a widely disputed in the Oracle world. Paul,
please see this for example:

Oh, I am well aware of that. However, pgstatspack reports gives you
additional useful information about the SQL statements that have consumed
the most time, most read or written into objects and alike. The report is
quite useful. I never suggested using BCHR as an efficiency criteria.

--
http://mgogala.byethost5.com

#5

On 01.10.2010 15:36, Mladen Gogala wrote:

Quote:

On Fri, 01 Oct 2010 08:11:19 +0200, Robert Klemme wrote:

Also, buffer cache hit ratios are probably not the best indicator of DB
efficiency at all. This is a widely disputed in the Oracle world. Paul,
please see this for example:

Oh, I am well aware of that. However, pgstatspack reports gives you
additional useful information about the SQL statements that have consumed
the most time, most read or written into objects and alike. The report is
quite useful. I never suggested using BCHR as an efficiency criteria.

I did not want to suggest that you suggested using BCHR neither did I
assume you were not aware of the BCHR quirks. :-) I just wanted to
reinforce your statement that the approach Paul wants to take has some
issues.

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Some postgres statistics

comp.databases.postgresql comp.databases.postgresql

Some postgres statistics - 09-30-2010 , 03:05 PM

Re: Some postgres statistics - 09-30-2010 , 09:35 PM

Re: Some postgres statistics - 10-01-2010 , 01:11 AM

Re: Some postgres statistics - 10-01-2010 , 08:36 AM

Re: Some postgres statistics - 10-02-2010 , 03:42 AM