From: Greg Smith <greg@2ndQuadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: [GENERAL] [PERFORMANCE] expanding to SAN: which portion best to move
Date: Thu, 09 Jun 2011 13:44:04 -0400
Message-ID: <4DF10664.7020906@2ndQuadrant.com> (raw)
In-Reply-To: <BANLkTikgWdz1giSdFDS90rW7uaPqp=wTNA@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>
<BANLkTindOvBQphvGrc-PtxKDNAHkCemssw@mail.gmail.com>
<BANLkTikgWdz1giSdFDS90rW7uaPqp=wTNA@mail.gmail.com>
On 06/09/2011 07:43 AM, Willy-Bas Loos wrote:
> 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)
The idea that separating indexes and tables from one another via a
tablespace is inherently good is a myth. Particularly nowadays, where
the fastest part of a drive is nearly twice as fast as the slowest one
in sequential transfers, and the ratio between sequential and random I/O
is huge. Trying to get clever about breaking out a tablespace is
unlikely to outsmart what you'd get if you just let the OS deal with
that stuff.
What is true is that when you have multiple tiers of storage speeds
available, allocating the indexes and tables among them optimally is
both difficult and potentially worthwhile. A customer of mine has two
drive arrays, one of which is about 50% faster than the other; second
was added as expansion once the first filled. Nowadays, both are 75%
full, and I/O on each has to be carefully balanced. Making sure the
heavily hit indexes are on the fast array, and that less critical things
are not placed there, is the difference between that site staying up or
going down.
The hidden surprise in this problem for most people is the day they
discover that *the* most popular indexes, the ones they figured had to
go on the fastest storage around, are actually sitting in RAM all the
time anyway. It's always fun and sad at the same time to watch someone
spend a fortune on some small expensive storage solution, move their
most performance critical data to it, and discover nothing changed.
Some days that works great; others, it's no faster all, because that
data was already in memory.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
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: greg@2ndQuadrant.com
Subject: Re: [GENERAL] [PERFORMANCE] expanding to SAN: which portion best to move
In-Reply-To: <4DF10664.7020906@2ndQuadrant.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