From: Robert Klemme <shortcutter@googlemail.com>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Willy-Bas Loos <willybas@gmail.com>
Cc: Jesper Krogh <jesper@krogh.cc>
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORMANCE] expanding to SAN: which portion best to move
Date: Mon, 16 May 2011 10:19:47 +0200
Message-ID: <BANLkTindOvBQphvGrc-PtxKDNAHkCemssw@mail.gmail.com> (raw)
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>
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 not
>> available for us now to do it myself, grmbl)
>> It just occurred to me that it is not necessarily the case that reading the
>> indexes causes a lot of random I/O (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. However 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. For 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. Or 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. As 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. The 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. I'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-some-thoughts-part-i-every...
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
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
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: shortcutter@googlemail.com, robertmhaas@gmail.com, willybas@gmail.com, jesper@krogh.cc
Subject: Re: [PERFORMANCE] expanding to SAN: which portion best to move
In-Reply-To: <BANLkTindOvBQphvGrc-PtxKDNAHkCemssw@mail.gmail.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