X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 16F75DBFCF for ; Sun, 27 Nov 2005 13:10:56 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 68560-03 for ; Sun, 27 Nov 2005 13:10:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtpauth07.mail.atl.earthlink.net (smtpauth07.mail.atl.earthlink.net [209.86.89.67]) by svr1.postgresql.org (Postfix) with ESMTP id 7E7F5D7798 for ; Sun, 27 Nov 2005 13:10:53 -0400 (AST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=dk20050327; d=earthlink.net; b=smSxls9bRxZLcZV7ZCzkGfNTQWHVqHCoNt55GX1uxE9XpY0N3PWE1SE/C83UkIQI; h=Received:Message-Id:X-Mailer:Date:To:From:Subject:In-Reply-To:References:Mime-Version:Content-Type:X-ELNK-Trace:X-Originating-IP; Received: from [70.22.193.119] (helo=ron-6d52adff2a6.earthlink.net) by smtpauth07.mail.atl.earthlink.net with asmtp (Exim 4.34) id 1EgQ3A-0003wt-0m; Sun, 27 Nov 2005 12:10:52 -0500 Message-Id: <6.2.5.6.0.20051127114155.01dbf868@earthlink.net> X-Mailer: QUALCOMM Windows Eudora Version 6.2.5.6 Date: Sun, 27 Nov 2005 12:10:44 -0500 To: "Luke Lonergan" , pgsql-performance@postgresql.org From: Ron Subject: Re: Hardware/OS recommendations for large databases In-Reply-To: <3E37B936B592014B978C4415F90D662DE11EBC@MI8NYCMAIL06.Mi8.co m> References: <3E37B936B592014B978C4415F90D662DE11EBC@MI8NYCMAIL06.Mi8.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-ELNK-Trace: acd68a6551193be5d780f4a490ca69563f9fea00a6dd62bcdbd45f42b791e69503975ada0daad77b350badd9bab72f9c350badd9bab72f9c350badd9bab72f9c X-Originating-IP: 70.22.193.119 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.359 required=5 tests=[AWL=-0.120, DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.359 X-Spam-Level: X-Archive-Number: 200511/530 X-Sequence-Number: 15787 At 01:18 AM 11/27/2005, Luke Lonergan wrote: >For data warehousing its pretty well open and shut. To use all cpus >and io channels on each query you will need mpp. > >Has anyone done the math.on the original post? 5TB takes how long >to scan once? If you want to wait less than a couple of days just >for a seq scan, you'd better be in the multi-gb per second range. More than a bit of hyperbole there Luke. Some common RW scenarios: Dual 1GbE NICs => 200MBps => 5TB in 5x10^12/2x10^8= 25000secs= ~6hrs57mins. Network stuff like re-transmits of dropped packets can increase this, so network SLA's are critical. Dual 10GbE NICs => ~1.6GBps (10GbE NICs can't yet do over ~800MBps apiece) => 5x10^12/1.6x10^9= 3125secs= ~52mins. SLA's are even moire critical here. If you are pushing 5TB around on a regular basis, you are not wasting your time & money on commodity <= 300MBps RAID HW. You'll be using 800MBps and 1600MBps high end stuff, which means you'll need ~1-2hrs to sequentially scan 5TB on physical media. Clever use of RAM can get a 5TB sequential scan down to ~17mins. Yes, it's a lot of data. But sequential scan times should be in the mins or low single digit hours, not days. Particularly if you use RAM to maximum advantage. Ron