Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5661E2E37A0 for ; Fri, 25 Jan 2008 12:55:50 -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 86880-06-2 for ; Fri, 25 Jan 2008 12:55:46 -0400 (AST) Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id 55C1D2E376A for ; Fri, 25 Jan 2008 12:55:45 -0400 (AST) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id m0PGth31022068; Fri, 25 Jan 2008 11:55:43 -0500 (EST) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id m0PGthSF022056; Fri, 25 Jan 2008 11:55:43 -0500 (EST) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Fri, 25 Jan 2008 11:55:43 -0500 (EST) From: Greg Smith X-X-Sender: gsmith@westnet.com To: David Brain cc: pgsql-performance@postgresql.org Subject: Re: 1 or 2 servers for large DB scenario. In-Reply-To: Message-ID: References: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/233 X-Sequence-Number: 28472 On Fri, 25 Jan 2008, David Brain wrote: > The hardware storing this DB (a software RAID6) array seems to be very > IO bound for writes and this is restricting our insert performance to > ~50TPS. If you're seeing <100TPS you should consider if it's because you're limited by how fast WAL commits can make it to disk. If you really want good insert performance, there is no substitute for getting a disk controller with a good battery-backed cache to work around that. You could just put the WAL xlog directory on a RAID-1 pair of disks to accelerate that, you don't have to move the whole database to a new controller. > Also are there any settings that might boost our TPS on the existing hardware > (sync=off isn't an option.. Having a good write cache lets you run almost as fast as when fsync is off. > Verson of Postgresql is 8.2.3. I'm hearing an echo here...8.2 versions before 8.2.4 have a bug related to statistics that can limit performance in several situations. You should consider an upgrade just to remove that as a potential contributor to your issues. To do a quick check on whether this is impacting things, run top, press "c" to show the full process lines, and note whether the statistics collector process is taking up a significant amount of CPU time. If it is, you're definately being nailed by the bug, and you really need that upgrade. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD