Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] [PERFORMANCE] expanding to SAN: which portion best to move

From: Willy-Bas Loos <willybas(at)gmail(dot)com>
To: Robert Klemme <shortcutter(at)googlemail(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Jesper Krogh <jesper(at)krogh(dot)cc>, pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org
Subject: Re: [PERFORM] [PERFORMANCE] expanding to SAN: which portion best to move
Date: 2011-06-09 11:43:26
Message-ID: BANLkTikgWdz1giSdFDS90rW7uaPqp=wTNA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
On Mon, May 16, 2011 at 10:19 AM, Robert Klemme
<shortcutter(at)googlemail(dot)com>wrote:

> On Fri, May 13, 2011 at 9:04 PM, Robert Haas <robertmhaas(at)gmail(dot)com>
> wrote:
> 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-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.
>
> Well, after reading your article i have been reading some materail about it
on the internet, stating that separating indexes from data for performance
benefits is a myth.
I found your comment "So then a single query will only ever access one of
both at a time." very smart (no sarcasm there).
I also found a thread<http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:901906930328>on
AskTom that said mainly "the goal is to achieve even io." (that makes
absolute sense)

In my situation, where i need extra space on a SAN, it seems logical to
separate the tables from the indexes, to achieve just that: roughly even
IO.. (put tables on san, leave indexes on raid10 cluster)
Or am i being silly?

Cheers,

WBL
-- 
"Patriotism is the conviction that your country is superior to all others
because you were born in it." -- George Bernard Shaw

In response to

Responses

pgsql-performance by date

Next:From: Samuel GendlerDate: 2011-06-09 11:55:41
Subject: Re: poor performance when recreating constraints on large tables
Previous:From: Jochen ErwiedDate: 2011-06-09 10:53:23
Subject: Re: Postgresql on itanium server

pgsql-general by date

Next:From: Håvard Wahl KongsgårdDate: 2011-06-09 12:15:26
Subject: Write performance on a large database
Previous:From: Andrea PeriDate: 2011-06-09 10:05:09
Subject: Adding "quota user limit" using triggers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group