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 D145E54E34 for ; Wed, 23 Feb 2005 20:05:50 +0000 (GMT) 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 19566-01 for ; Wed, 23 Feb 2005 20:05:47 +0000 (GMT) Received: from Mercury.turbocorp.com (mercury.turbocorp.com [198.212.166.40]) by svr1.postgresql.org (Postfix) with ESMTP id 79CAB54D78 for ; Wed, 23 Feb 2005 20:05:46 +0000 (GMT) Received: from [198.212.166.61] (Voyager.turbocorp.com [198.212.166.61]) by Mercury.turbocorp.com (8.12.8/8.12.8) with ESMTP id j1NK5CvD005652; Wed, 23 Feb 2005 15:05:12 -0500 Message-ID: <421CE1FD.80003@turbocorp.com> Date: Wed, 23 Feb 2005 15:05:17 -0500 From: John Allgood User-Agent: Mozilla Thunderbird 1.0 (X11/20041207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: John Arbash Meinel Cc: Tom Lane , Bruno Almeida do Lago , "'Michael Adler'" , pgsql-performance@postgresql.org Subject: Re: Peformance Tuning Opterons/ Hard Disk Layout References: <421cca80.3da18bb5.6940.3b12@smtp.gmail.com> <21021.1109183848@sss.pgh.pa.us> <421CD668.7010503@turbocorp.com> <421CDD83.3070609@arbash-meinel.com> In-Reply-To: <421CDD83.3070609@arbash-meinel.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/338 X-Sequence-Number: 10667 This some good info. The type of attached storage is a Kingston 14 bay Fibre Channel Infostation. I have 14 36GB 15,000 RPM drives. I think the way it is being explained that I should build a mirror with two disk for the pg_xlog and the striping and mirroring the rest and put all my databases into one cluster. Also I might mention that I am running clustering using Redhat Clustering Suite. John Arbash Meinel wrote: > John Allgood wrote: > >> I think maybe I didn't explain myself well enough. At most we will >> service 200-250 connections across all the 9 databases mentioned. The >> database we are building is for a trucking company. Each of the >> databases represents a different division. With one master database >> that everything is updated to. Most of the access to the database is >> by simple queries. Most of the IO intensive stuff is done when revenue >> reports are generated and when we have our month/year end processing. >> All the trucking loads that are mark as delivered are transferred to >> our master database during night time processing. All that will be >> handled using custom scripts. Maybe I have given a better explanation >> of the application. my biggest concern is how to partition the shared >> storage for maximum performance. Is there a real benifit to having >> more that one raid5 partition or am I wasting my time. >> >> Thanks >> >> John Allgood - ESC > > If you read the general advice statements, it's actually better to not > use raid5, but to use raid10 (striping and mirroring). Simply because > raid5 writing is quite poor. > > Also, if you have the disks, the next best improvement is to move > pg_xlog onto it's own set of disks. I think that gets as much as 25% > improvement by itself. pg_xlog is an append process, which must complete > before the actual data gets updated, so giving it it's own set of > spindles reduces seek time, and lets the log be written quickly. > I think there is even some benefit to making pg_xlog be a solid state > disk, as it doesn't have to be huge, but having high I/O rates can > remove it as a bottleneck. (I'm not positive how large pg_xlog gets, but > it is probably small compared with the total db size, and I think it can > be flushed periodically as transactions are completed.) > > I'm not sure what you are considering "shared storage". Are you thinking > that all the machines will be mounting a remote drive for writing the > DB? They should all have their own local copy (multiple masters on the > same database is not supported). > > I think it is possible to get better performance by having more raid > systems. But it is application dependent. If you know that you have 2 > tables that are being updated often and independently, then having each > one on it's own raid would allow better concurrency. > > But it sounds like in your app you get concurrency by having a bunch of > remote databases, which then do bulk updates on the master database. I > think if you are careful to have each of the remote dbs update the > master at a slightly different time, you could probably get very good > transaction rates. > > John > =:-> >