Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6EA432E3108 for ; Tue, 4 Dec 2007 12:12:49 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 54830-02-3 for ; Tue, 4 Dec 2007 12:12:46 -0400 (AST) Received: from ironport2-out.pppoe.ca (ironport2-out.pppoe.ca [206.248.154.182]) by postgresql.org (Postfix) with ESMTP id 675E92E30EE for ; Tue, 4 Dec 2007 12:12:06 -0400 (AST) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: Ah4FAC4KVUfO+Igp/2dsb2JhbACCPjWOLA X-IronPort-AV: E=Sophos;i="4.23,249,1194238800"; d="scan'208,217";a="10769636" Received: from smtp.pppoe.ca ([65.39.196.238]) by ironport2-out.pppoe.ca with ESMTP; 04 Dec 2007 11:12:05 -0500 Received: from [206.248.136.41] ([206.248.136.41]) by smtp.pppoe.ca (Internet Mail Server v1.0) with ESMTP id JCQ80405; Tue, 04 Dec 2007 11:12:05 -0500 Message-ID: <47557C30.10809@mark.mielke.cc> Date: Tue, 04 Dec 2007 11:11:28 -0500 From: Mark Mielke User-Agent: Thunderbird 2.0.0.9 (X11/20071115) MIME-Version: 1.0 To: Matthew CC: pgsql-performance@postgresql.org Subject: Re: RAID arrays and performance References: <87lk8aiqf3.fsf@oxford.xeocode.com> <475559F4.7060104@mark.mielke.cc> <4755648C.90905@mark.mielke.cc> <87fxyiik94.fsf@oxford.xeocode.com> <47557519.6090506@mark.mielke.cc> In-Reply-To: Content-Type: multipart/alternative; boundary="------------010508010305000907080708" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/47 X-Sequence-Number: 27863 This is a multi-part message in MIME format. --------------010508010305000907080708 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Matthew wrote: > On Tue, 4 Dec 2007, Mark Mielke wrote: > >>> The larger the set of requests, the closer the performance will scale to >>> the number of discs >>> >> This assumes that you can know which pages to fetch ahead of time - >> which you do not except for sequential read of a single table. >> > There are circumstances where it may be hard to locate all the pages ahead > of time - that's probably when you're doing a nested loop join. However, > if you're looking up in an index and get a thousand row hits in the index, > then there you go. Page locations to load. > Sure. >> Please show one of your query plans and how you as a person would design >> which pages to request reads for. >> > How about the query that "cluster " was trying to get > to run faster a few days ago? Tom Lane wrote about it: > > | Wouldn't help, because the accesses to "questions" are not the problem. > | The query's spending nearly all its time in the scan of "posts", and > | I'm wondering why --- doesn't seem like it should take 6400msec to fetch > | 646 rows, unless perhaps the data is just horribly misordered relative > | to the index. > > Which is exactly what's going on. The disc is having to seek 646 times > fetching a single row each time, and that takes 6400ms. He obviously has a > standard 5,400 or 7,200 rpm drive with a seek time around 10ms. > Your proposal would not necessarily improve his case unless he also purchased additional disks, at which point his execution time may be different. More speculation. :-) It seems reasonable - but still a guess. > Or on a similar vein, fill a table with completely random values, say ten > million rows with a column containing integer values ranging from zero to > ten thousand. Create an index on that column, analyse it. Then pick a > number between zero and ten thousand, and > > "SELECT * FROM table WHERE that_column = the_number_you_picked This isn't a real use case. Optimizing for the worst case scenario is not always valuable. Cheers, mark -- Mark Mielke --------------010508010305000907080708 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Matthew wrote: 
On Tue, 4 Dec 2007, Mark Mielke wrote:
  
The larger the set of requests, the closer the performance will scale to
the number of discs
      
This assumes that you can know which pages to fetch ahead of time -
which you do not except for sequential read of a single table.
    
There are circumstances where it may be hard to locate all the pages ahead
of time - that's probably when you're doing a nested loop join. However,
if you're looking up in an index and get a thousand row hits in the index,
then there you go. Page locations to load.
Sure.

  

    
Please show one of your query plans and how you as a person would design
which pages to request reads for.
    

  

  
How about the query that "cluster <skrald@amossen.dk>" was trying to get
to run faster a few days ago? Tom Lane wrote about it:

| Wouldn't help, because the accesses to "questions" are not the problem.
| The query's spending nearly all its time in the scan of "posts", and
| I'm wondering why --- doesn't seem like it should take 6400msec to fetch
| 646 rows, unless perhaps the data is just horribly misordered relative
| to the index.

Which is exactly what's going on. The disc is having to seek 646 times
fetching a single row each time, and that takes 6400ms. He obviously has a
standard 5,400 or 7,200 rpm drive with a seek time around 10ms.
  



Your proposal would not necessarily improve his case unless he also
purchased additional disks, at which point his execution time may be
different. More speculation. :-)



It seems reasonable - but still a guess.





  
Or on a similar vein, fill a table with completely random values, say ten
million rows with a column containing integer values ranging from zero to
ten thousand. Create an index on that column, analyse it. Then pick a
number between zero and ten thousand, and

"SELECT * FROM table WHERE that_column = the_number_you_picked



This isn't a real use case. Optimizing for the worst case scenario is
not always valuable.



Cheers,

mark




-- 
Mark Mielke <mark@mielke.cc>





--------------010508010305000907080708--