DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=ZAMaJS4edBY3x4gIH5JWqspwdnzG5Uz2V4yoi+cBO40+IsCr4aF0eUvGgrWArqFAZA
	OpWLNfckM/f6+Pf6WpvH8dmBQcaLp1HhY7StgX5/wC7JMVTT3UztA2ws2azv0LGAhbRr
	iFnLBGRuEEEWfYjuDw7D7THKrGNXK7kIiDxRM=
MIME-Version: 1.0
In-Reply-To: <BANLkTi=Oo4jRHpBY_74nJrcryr=BLDZR9A@mail.gmail.com>
References: <BANLkTimDjFDy8Q8vfDVZ4LBKXb1EjVsh4w@mail.gmail.com>
	<4DC0D968.3030106@krogh.cc>
	<BANLkTi=eNeQmYLqNm-E36G9UPwTnsTtHBw@mail.gmail.com>
	<4DC0E525.4060108@krogh.cc>
	<BANLkTikg=2SMu5eSBNfW_K8fCXBhZk0Hog@mail.gmail.com>
	<BANLkTi=Oo4jRHpBY_74nJrcryr=BLDZR9A@mail.gmail.com>
Date: Mon, 16 May 2011 10:19:47 +0200
Message-ID: <BANLkTindOvBQphvGrc-PtxKDNAHkCemssw@mail.gmail.com>
Subject: Re: [PERFORMANCE] expanding to SAN: which portion best to move
From: Robert Klemme <shortcutter@googlemail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Willy-Bas Loos <willybas@gmail.com>, Jesper Krogh <jesper@krogh.cc>,
	pgsql-performance@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Fri, May 13, 2011 at 9:04 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, May 4, 2011 at 6:31 AM, Willy-Bas Loos <willybas@gmail.com> wrote=
:
>> I'm asking them for (real) benchmarks, thanks for the advice. (fio is no=
t
>> available for us now to do it myself, grmbl)
>> It just occurred to me that it is not=A0necessarily the case that readin=
g the
>> indexes causes a lot of random I/O=A0(on the index itself).
>> I mean, maybe the index is generally read sequentially and then, when
>> retrieving the data, there is a lot of random I/O.
>> if it's a long story, any tips for info about this (book or web site)?
>
> If you don't do anything special, and if the query plan says "Index
> Scan" rather than "Bitmap Index Scan", then both the index I/O and the
> table I/O are likely to be fairly random. =A0However there are a number
> of cases in which you can expect the table I/O to be sequential:
>
> - In some cases, you may happen to insert rows with an ordering that
> matches the index. =A0For example, if you have a table with not too many
> updates and deletes, and an index on a serial column, then new rows
> will have a higher value in that column than old rows, and will also
> typically be physically after older rows in the file. =A0Or you might be
> inserting timestamped data from oldest to newest.
> - If the planner chooses a Bitmap Index Scan, it effectively scans the
> index to figure out which table blocks to read, and then reads those
> table blocks in block number order, so that the I/O is sequential,
> with skips.

Are these two separate phases (i.e. first scan index completely, then
access table)?

> - If you CLUSTER the table on a particular index, it will be
> physically ordered to match the index's key ordering. =A0As the table is
> further modified the degree of clustering will gradually decline;
> eventually you may wish to re-CLUSTER.
>
> It's also worth keeping in mind that the index itself won't
> necessarily be accessed in physically sequential order. =A0The point of
> the index is to emit the rows in key order, but if the table is
> heavily updated, it won't necessarily be the case that a page
> containing lower-valued keys physically precedes a page containing
> higher-valued keys. =A0I'm actually somewhat fuzzy on how this works,
> and to what extent it's a problem in practice, but I am fairly sure it
> can happen.

Separating index and tables might not be a totally good idea
generally.  Richard Foote has an excellent article about Oracle but I
assume at least a few things do apply to PostgreSQL as well - it's at
least worth as something to check PostgreSQL's access patterns
against:

http://richardfoote.wordpress.com/2008/04/16/separate-indexes-from-tables-s=
ome-thoughts-part-i-everything-in-its-right-place/

I would probably rather try to separate data by the nature and
frequency of accesses.  One reasonable separation would be to leave
all frequently accessed tables *and* their indexes on local RAID and
moving less frequently accessed data to the SAN.  This separation
could be easily identified if you have separate tables for current and
historic data.

Kind regards

robert


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