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
Date: Tue, 18 Apr 2006 15:06:41 -0500
Message-ID: <20060418200641.GG49405@pervasive.com> (raw)
In-Reply-To: <443F3BB3.30404@elde.net>
References: <85979594-1CCA-43C5-A8F2-08685CF41ACE@pervasive.com>
<443F3BB3.30404@elde.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jnasby@pervasive.com, terje@elde.net
Subject: Re: Blocks read for index scans
In-Reply-To: <20060418200641.GG49405@pervasive.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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