Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id F24732E0032 for ; Tue, 13 May 2008 06:54:27 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 43772-05 for ; Tue, 13 May 2008 06:54:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 30.mail-out.ovh.net (30.mail-out.ovh.net [213.186.62.213]) by developer.postgresql.org (Postfix) with SMTP id 8A57F2E0031 for ; Tue, 13 May 2008 06:54:23 -0300 (ADT) Received: (qmail 15881 invoked by uid 503); 13 May 2008 09:42:44 -0000 Received: from unknown (HELO mail243.ha.ovh.net) (213.186.33.59) by 30.mail-out.ovh.net with SMTP; 13 May 2008 09:42:43 -0000 Received: from b0.ovh.net (HELO queue-out) (213.186.33.50) by b0.ovh.net with SMTP; 13 May 2008 09:42:19 -0000 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13.peufeu.com) (88.161.102.87) by ns0.ovh.net with SMTP; 13 May 2008 09:42:17 -0000 Date: Tue, 13 May 2008 11:48:29 +0200 To: "Francisco Reyes" , "Pgsql performance" Subject: Re: RAID controllers for Postgresql on large setups From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 References: Content-Transfer-Encoding: Quoted-Printable Message-ID: In-Reply-To: User-Agent: Opera Mail/9.24 (Linux) X-Ovh-Tracer-Id: 7095702688125093496 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|H 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.656 tagged_above=0 required=5 tests=AWL=0.656 X-Spam-Level: X-Archive-Number: 200805/160 X-Sequence-Number: 30128 > Will it pay to go to a controller with higher memory for existing = > machines? The one machine I am about to redo has PCI which seems to = > somewhat limit our options. Urgh. You say that like you don't mind having PCI in a server whose job is to= = perform massive query over large data sets. Your 12 high-end expensive SCSI drives will have a bandwidth of ... say= = 800 MB/s total (on reads), perhaps more. PCI limits you to 133 MB/s (theoretical), actual speed being around = 100-110 MB/s. Conclusion : 85% of the power of your expensive drives is wasted by = hooking them up to the slow PCI bus ! (and hence your money is wasted to= o) For instance here I have a box with PCI, Giga Ethernet and a software = RAID5 ; reading from the RAID5 goes to about 110 MB/s (actual disk = bandwidth is closer to 250 but it's wasted) ; however when using the gig= a = ethernet to copy a large file over a LAN, disk and ethernet have to shar= e = the PCI bus, so throughput falls to 50 MB/s. Crummy, eh ? =3D> If you do big data imports over the network, you lose 50% speed ag= ain = due to the bus sharing between ethernet nic and disk controller. In fact for bulk IO a box with 2 SATA drives would be just as fast as = your monster RAID, lol. And for bulk imports from network a $500 box with a few SATA drives and= a = giga-ethernet, all via PCIexpress (any recent Core2 chipset) will be = faster than your megabuck servers. Let me repeat this : at the current state of SATA drives, just TWO of = them is enough to saturate a PCI bus. I'm speaking desktop SATA drives, = = not high-end SCSI ! (which is not necessarily faster for pure throughput= = anyway). Adding more drives will help random reads/writes but do nothing for = throughput since the tiny PCI pipe is choking. So, use PCIe, PCIx, whatever, but get rid of the bottleneck. Your money is invested in disk drives... keep those, change your RAID = controller which sucks anyway, and change your motherboard ... If you're limited by disk throughput (or disk <-> giga ethernet PCI bus= = contention), you'll get a huge boost by going PCIe or PCIx. You might ev= en = need less servers. > For future machines I plan to look into controllers with at least 512M= B, = > which likely will be PCI-X/PCI-e.. > not seen anything with large caches for PCI. That's because high performance !=3D PCI > Whether a SAN or just an external enclosure is 12disk enough to substa= in = > 5K inserts/updates per second on rows in the 30 to 90bytes territory? = At = > 5K/second inserting/updating 100 Million records would take 5.5 hours.= = > That is fairly reasonable if we can achieve. Faster would be better, b= ut = > it depends on what it would cost to achieve. If you mean 5K transactions with begin / insert or update 1 row / commi= t, = that's a lot, and you are going to need cache, BBU, and 8.3 so fsync isn= 't = a problem anymore. On your current setup with 15K drives if you need 1 fsync per INSERT yo= u = won't do more than 250 per second, which is very limiting... PG 8.3's "o= ne = fsync per second instead of one at each commit" feature is a really chea= p = alternative to a BBU (not as good as a real BBU, but much better than = nothing !) If you mean doing large COPY or inserting/updating lots of rows using o= ne = SQL statement, you are going to need disk bandwidth. For instance if you have your 100M x 90 byte rows + overhead, that's = about 11 GB The amount of data to write is twice that because of the xlog, so 22 GB= = to write, and 11 GB to read, total 33 GB. On your setup you have a rather low 110 MB/s throughput it would take a= = bit more than 3 min 20 s. With 800 MB/s bandwidth it would take 45 = seconds. (but I don't know if Postgres can process data this fast, = although I'd say probably). Of course if you have many indexes which need to be updated this will a= dd = random IO and more WAL traffic to the mix. Checkpoints andbgwriter also need to be tuned so they don't kill your = performance when writing lots of data. For your next servers as the other on the list will tell you, a good RA= ID = card, and lots of SATA drives is a good choice. SATA is cheap, so you ca= n = get more drives for the same price, which means more bandwidth : http://tweakers.net/reviews/557/17/comparison-of-nine-serial-ata-raid-5-= adapters-pagina-17.html Of course none of those uses PCI. RAID5 is good for read speed, and big sequential writes. So if the only= = thing that you do is load up a multi-gigabyte dump and process it, it's = = good. Now if you do bulk UPDATEs (like updating all the rows in one of the = partitions of your huge table) RAID5 is good too. However RAID5 will choke and burn on small random writes, which will co= me = from UPDATing random rows in a large table, updating indexes, etc. Sinc= e = you are doing this apparently, RAID5 is therefore NOT advised ! Also consider the usual advice, like CLUSTER, or when you load a large = = amount of data in the database, COPY it to a temp table, then INSERT it = in = the main table with INSERT INTO table SELECT FROM temp_table ORDER BY = (interesting_fields). If the "interesting_fields" are something like the= = date and you often select or update on a date range, for instance, you'l= l = get more performance if all the rows from the same day are close on disk= . Have you considered Bizgres ?