Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 3EE83B5D90E for ; Thu, 9 Jun 2011 14:44:17 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 47662-05 for ; Thu, 9 Jun 2011 17:44:09 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.186]) by mail.postgresql.org (Postfix) with ESMTP id 301D2B5D91F for ; Thu, 9 Jun 2011 14:44:09 -0300 (ADT) Received: from [192.168.1.7] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mreu3) with ESMTP (Nemesis) id 0LfLLx-1PkSMQ2i4X-00p3TI; Thu, 09 Jun 2011 19:44:08 +0200 Message-ID: <4DF10664.7020906@2ndQuadrant.com> Date: Thu, 09 Jun 2011 13:44:04 -0400 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20110505 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: [GENERAL] [PERFORMANCE] expanding to SAN: which portion best to move References: <4DC0D968.3030106@krogh.cc> <4DC0E525.4060108@krogh.cc> In-Reply-To: Content-Type: multipart/alternative; boundary="------------060201090006000207080904" X-Provags-ID: V02:K0:dEAXtcSv8sz3pqqyrvZKwB9BcxSUFXjF/E+Fj9rZmci rHZHyfuQeLue1sQD/IVqtDxtLLu7RzjbDoUZRyJNJVOFNKRtvc z0A0ljtrm3sF9dY1hqLhgiaJFbgRDUv7BHmSPwz9h1IZLb7EJQ reuj4MQ3crSqP2jcK2xhn+Xvp4/NItbVlUioQmpeibntElybUe xU7RE2KYuaxrQAaGb4d6uOngiEvj/jkQte49Z1OY/g= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201106/105 X-Sequence-Number: 44038 This is a multi-part message in MIME format. --------------060201090006000207080904 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 > > 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 --------------060201090006000207080904 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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 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
--------------060201090006000207080904--