X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 3C2849DCBBD for ; Tue, 7 Mar 2006 16:21:06 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 20020-08 for ; Tue, 7 Mar 2006 16:21:04 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id D441E9DCB73 for ; Tue, 7 Mar 2006 16:21:01 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id F16C25646F; Tue, 7 Mar 2006 14:21:02 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Tue, 7 Mar 2006 14:21:02 -0600 Date: Tue, 7 Mar 2006 14:21:02 -0600 From: "Jim C. Nasby" To: Joost Kraaijeveld Cc: "Pgsql-Performance (E-mail)" Subject: Re: Can anyone explain this pgbench results? Message-ID: <20060307202102.GE58405@pervasive.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060307:j.kraaijeveld@askesis.nl::KiUMAD93ZgjPeviH:0000000000000 0000000000000000000000003ifm X-Hashcash: 1:20:060307:pgsql-performance@postgresql.org::hg0oagzeE4sFMT+W:00000 000000000000000000000000056R X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.109 required=5 tests=[AWL=0.109] X-Spam-Score: 0.109 X-Spam-Level: X-Archive-Number: 200603/81 X-Sequence-Number: 17518 On Tue, Mar 07, 2006 at 09:15:37PM +0100, Joost Kraaijeveld wrote: > Jim C. Nasby wrote: > > Well, the problem is that you're using RAID5, which has a huge write > > overhead. You're unlikely to get good performance with it. > Apparently. But I had no idea that the performance hit would be that big. > > Running bonnie or copying a large file with dd show that the card can do 30-50 MB/sec. Running a large update on my postgresql database however, show a throughtput of ~ 2MB/sec, doing between ~ 2500 - 2300 writes/second (avarage). with an utilisation of almost always 100%, and large await times ( almost always > 700), large io-wait percentages (>50%), all measured with iostat. While there are some issues with PostgreSQL not getting as close to the theoretical maximum of a dd bs=8k (you did match the block size to PostgreSQL's page size, right? :) ), a bigger issue in this case is that better cards are able to remove much/all of the RAID5 write penalty in the case where you're doing a large sequential write, because it will just blow entire stripes down to disk. This is very different from doing a more random IO. And it's also very possible that if you use a block size that's smaller than the stripe size that the controller won't be able to pick up on that. In any case, RAID0 will absolutely be the fastest performance you can get. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461