Date: Tue, 18 Apr 2006 15:06:41 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Terje Elde <terje@elde.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Blocks read for index scans
Message-ID: <20060418200641.GG49405@pervasive.com>
References: <85979594-1CCA-43C5-A8F2-08685CF41ACE@pervasive.com>
	<443F3BB3.30404@elde.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <443F3BB3.30404@elde.net>
User-Agent: Mutt/1.5.11

On Fri, Apr 14, 2006 at 08:05:39AM +0200, Terje Elde wrote:
> Jim Nasby wrote:
> >While working on determining a good stripe size for a database, I 
> >realized it would be handy to know what the average request size is. 
> >Getting this info is a simple matter of joining pg_stat_all_tables and 
> >pg_statio_all_tables and doing some math, but there's one issue I've 
> >found; it appears that there's no information on how many heap blocks 
> >were read in by an index scan. Is there any way to get that info?
<snip> 
> Knowing what the average stripe size is can be a good place to start, 
> but the real question is;  which stripe size will allow the majority of 
> your transactions to be possible to satisfy without having to go to two 
> spindles?

And of course right now there's not a very good way to know that...
granted, I can look at the average request size on the machine, but that
will include any seqscans that are happening, and for stripe sizing I
think it's better to leave that out of the picture unless your workload
is heavily based on seqscans.

> That said, it's the transactions against disk that typically matter.  On 
> FreeBSD, you can get an impression of this using 'systat -vmstat', and 
> watch the KB/t column for your drives.

On a related note, you know of any way to determine the breakdown
between read activity and write activity on FreeBSD? vmstat, systat,
iostat all only return aggregate info. :(
-- 
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