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 5743E54D2C for ; Wed, 23 Feb 2005 19:46:27 +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 13524-03 for ; Wed, 23 Feb 2005 19:46:18 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 0EA9254CF5 for ; Wed, 23 Feb 2005 19:46:16 +0000 (GMT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.2/8.12.9) with ESMTP id j1NJkDBo001670; (envelope-from ) Wed, 23 Feb 2005 13:46:14 -0600 (CST) Received: from [192.168.1.174] (67-41-93-122.cdrr.qwest.net [67.41.93.122]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j1NJkA3X011918 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 23 Feb 2005 13:46:12 -0600 (CST) Message-ID: <421CDD83.3070609@arbash-meinel.com> Date: Wed, 23 Feb 2005 13:46:11 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: John Allgood 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> In-Reply-To: <421CD668.7010503@turbocorp.com> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigC0883B91617944F7AE152678" X-Virus-Scanned: ClamAV 0.80/682/Mon Jan 24 12:53:43 2005 clamav-milter version 0.80j on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV 0.80/706/Sun Feb 13 18:14:02 2005, clamav-milter version 0.75 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Status: Clean 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/336 X-Sequence-Number: 10665 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigC0883B91617944F7AE152678 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 =:-> --------------enigC0883B91617944F7AE152678 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCHN2DJdeBCYSNAAMRAmhoAJ9IxhOahOT8clbOOe1GBghT55ooFwCePfl+ sKwu/5dJ7hyce3LOx8ZgVLk= =aFBq -----END PGP SIGNATURE----- --------------enigC0883B91617944F7AE152678--