X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2408ED1D8AE for ; Tue, 17 Feb 2004 22:35:09 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 67889-06 for ; Tue, 17 Feb 2004 18:35:08 -0400 (AST) Received: from bayswater1.ymogen.net (host-154-240-27-217.pobox.net.uk [217.27.240.154]) by svr1.postgresql.org (Postfix) with ESMTP id 08810D1D4E7 for ; Tue, 17 Feb 2004 18:35:06 -0400 (AST) Received: by bayswater1.ymogen.net (Postfix, from userid 99) id 6EE589AD7A; Tue, 17 Feb 2004 22:34:52 +0000 (GMT) Received: from 82.68.132.233 (SquirrelMail authenticated user matt@ymogen.net) by webmail.ymogen.com with HTTP; Tue, 17 Feb 2004 22:34:52 -0000 (GMT) Message-ID: <1634.82.68.132.233.1077057292.squirrel@webmail.ymogen.com> In-Reply-To: <20040217135342.1924326f.mpg4@duluoz.net> References: <20040217135342.1924326f.mpg4@duluoz.net> Date: Tue, 17 Feb 2004 22:34:52 -0000 (GMT) Subject: Re: RAID or manual split? From: matt@ymogen.net To: "Mike Glover" Cc: pgsql-performance@postgresql.org User-Agent: SquirrelMail/1.4.2-0.1.7.x MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/198 X-Sequence-Number: 5722 > It seems, that if I know the type and frequency of the queries a > database will be seeing, I could split the database by hand over > multiple disks and get better performance that I would with a RAID array > with similar hardware. Unlikely, but possible if you had radically different hardware for different tables. > Six large (3-7 Mrow) 'summary' tables, each being updated continuously > by 5-20 processes with about 0.5 transactions/second/process. Well you should get close to an order of magnitude better performance from a RAID controller with write-back cache on those queries. > Periodically (currently every two weeks), join queries are > performed between one of the 'summary' tables(same one each time) and > each of the other five. Each join touches most rows of both tables, > indexes aren't used. Results are written into a separate group of > 'inventory' tables (about 500 Krow each), one for each join. The more disks the data is spread over the better (the RAID controller will help here with striping). > There are frequent (100-1000/day) queries of both the > inventory and summary tables using the primary key -- always using the > index and returning < 10 rows. RAM is what you need, to cache the data and indexes, and then as much CPU power as you can get. > We're currently getting (barely) acceptable performance from a single > 15k U160 SCSI disk, but db size and activity are growing quickly. > I've got more disks and a battery-backed LSI card on order. 3 or more disks in a stripe set, with write back caching, will almost certainly give a huge performance boost. Try that first, and only if you have issues should you think about futzing with symlinks etc. M