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 9680D5363E for ; Tue, 29 Mar 2005 05:31:45 +0100 (BST) 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 91281-08 for ; Tue, 29 Mar 2005 04:31:36 +0000 (GMT) Received: from mail.tecarta.com (66.238.115.135.ptr.us.xo.net [66.238.115.135]) by svr1.postgresql.org (Postfix) with ESMTP id 65A1D53313 for ; Tue, 29 Mar 2005 05:31:35 +0100 (BST) Received: from mail pickup service by mail.tecarta.com with Microsoft SMTPSVC; Mon, 28 Mar 2005 20:34:30 -0800 Received: from mail.tecarta.com ([192.168.160.2]) by mail.tecarta.com with Microsoft SMTPSVC(6.0.3790.0); Mon, 28 Mar 2005 20:34:28 -0800 Received: from barracuda.tecarta.com ([192.168.160.200]) by mail.tecarta.com (SAVSMTP 3.1.0.29) with SMTP id M2005032820342719796 for ; Mon, 28 Mar 2005 20:34:27 -0800 X-ASG-Debug-ID: 1112070542-3425-4-0 X-Barracuda-URL: http://192.168.160.200:8000/cgi-bin/mark.cgi Received: from mail2 (mail2.hq.corp [192.168.160.6]) by barracuda.tecarta.com (Spam Firewall) with SMTP id 7C73D2029F3D for ; Mon, 28 Mar 2005 20:29:02 -0800 (PST) Received: from [192.168.0.13] ([63.206.203.145]) by mail.tecarta.com with Microsoft SMTPSVC(6.0.3790.0); Mon, 28 Mar 2005 17:51:39 -0800 Message-ID: <424840AE.3050200@sfnet.cc> Date: Mon, 28 Mar 2005 17:36:46 +0000 From: Steve Poe User-Agent: Mozilla Thunderbird 1.0 (X11/20041228) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Cott Lang Cc: pgsql-performance@postgresql.org X-ASG-Orig-Subj: Re: [PERFORM] How to improve db performance with $7K? Subject: Re: How to improve db performance with $7K? References: <42443863.4040302@sfnet.cc> <4244A90B.2030200@sfnet.cc> <42453273.2060509@turtle-entertainment.de> <200503261255.59007.josh@agliodbs.com> <42455DEC.2080301@sfnet.cc> <1112049794.23284.29.camel@localhost.localdomain> In-Reply-To: <1112049794.23284.29.camel@localhost.localdomain> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 29 Mar 2005 01:51:39.0381 (UTC) FILETIME=[D9372A50:01C53401] X-Virus-Scanned: by Barracuda Spam Firewall at tecarta.com X-Barracuda-Spam-Score: -4.55 X-Barracuda-Spam-Status: No, SCORE=-4.55 using global scores of TAG_LEVEL=4.0 QUARANTINE_LEVEL=1000.0 KILL_LEVEL=7.0 tests=BAYES_00, DATE_IN_PAST_06_12 X-Barracuda-Spam-Report: Code version 2.64, rules version 2.1.2212 Rule breakdown below pts rule name description ---- ---------------------- ------------------------------------------- -5.40 BAYES_00 BODY: Bayesian spam probability is 0 to 1% [score: 0.0011] 0.85 DATE_IN_PAST_06_12 Date: is 6 to 12 hours before Received: date X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.33 tagged_above=0 required=5 tests=AWL, DATE_IN_PAST_06_12, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/623 X-Sequence-Number: 11328 Cott Lang wrote: >Have you already considered application/database tuning? Adding >indexes? shared_buffers large enough? etc. > >Your database doesn't seem that large for the hardware you've already >got. I'd hate to spend $7k and end up back in the same boat. :) > > Cott, I agree with you. Unfortunately, I am not the developer of the application. The vendor uses ProIV which connects via ODBC. The vendor could certain do some tuning and create more indexes where applicable. I am encouraging the vendor to take a more active role and we work together on this. With hardware tuning, I am sure we can do better than 35Mb per sec. Also moving the top 3 or 5 tables and indexes to their own slice of a RAID10 and moving pg_xlog to its own drive will help too. Since you asked about tuned settings, here's what we're using: kernel.shmmax = 1073741824 shared_buffers = 10000 sort_mem = 8192 vacuum_mem = 65536 effective_cache_size = 65536 Steve Poe