Re: Blocks read for index scans

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461