X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B962314B2806 for ; Tue, 10 Aug 2004 20:06:55 -0300 (ADT) 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 13472-03 for ; Tue, 10 Aug 2004 23:06:52 +0000 (GMT) Received: from tht.net (vista.tht.net [216.126.88.2]) by svr1.postgresql.org (Postfix) with ESMTP id 148C214B27FF for ; Tue, 10 Aug 2004 20:06:51 -0300 (ADT) Received: from [134.22.70.202] (dyn-70-202.tor.dsl.tht.net [134.22.70.202]) by tht.net (Postfix) with ESMTP id 81DE076A54; Tue, 10 Aug 2004 19:06:58 -0400 (EDT) Subject: Re: Hardware upgrade for a high-traffic database From: Rod Taylor To: Jason Coene Cc: Postgresql Performance In-Reply-To: <200408101917.i7AJHR0E064106@mail.gotfrag.com> References: <200408101917.i7AJHR0E064106@mail.gotfrag.com> Content-Type: text/plain Message-Id: <1092179211.11635.30.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Tue, 10 Aug 2004 19:06:52 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/138 X-Sequence-Number: 7774 > Our database is about 20GB on disk, we have some quite large tables - 2M > rows with TEXT fields in a sample table, accessed constantly. We average > about 4,000 - 5,000 queries per second - all from web traffic. As you can 99% is reads? and probably the same data over and over again? You might want to think about a small code change to cache sections of page output in memory for the most commonly generated pages (there are usually 3 or 4 that account for 25% to 50% of web traffic -- starting pages). The fact you're getting 5k queries/second off IDE drives tells me most of the active data is in memory -- so your actual working data set is probably quite small (less than 10% of the 20GB). If the above is all true (mostly reads, smallish dataset, etc.) and the database is not growing very quickly, you might want to look into RAM and RAM bandwidth over disk. An Opteron with 8GB ram using the same old IDE drives. Get a mobo with a SCSI raid controller in it, so the disk component can be upgraded in the future (when necessary).