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 714BB5370B for ; Wed, 27 Apr 2005 13:13:53 -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 03844-10 for ; Wed, 27 Apr 2005 16:13:43 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 96E6453711 for ; Wed, 27 Apr 2005 13:13:41 -0300 (ADT) X-EthosMedia-Virus-Scanned: no infections found Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7292439; Wed, 27 Apr 2005 09:15:39 -0700 From: Josh Berkus Organization: Aglio Database Solutions To: "Joel Fradkin" Subject: Re: Final decision Date: Wed, 27 Apr 2005 09:13:35 -0700 User-Agent: KMail/1.8 Cc: "PostgreSQL Perform" References: <000001c54b3a$054d3720$797ba8c0@jfradkin> In-Reply-To: <000001c54b3a$054d3720$797ba8c0@jfradkin> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200504270913.35952.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/729 X-Sequence-Number: 12126 Joel, > So I am planning on sticking with postgres fro our production database > (going live this weekend). Glad to have you. > I did not find any resolutions to my issues with Commandprompt.com (we only > worked together 2.5 hours). BTW, your performance troubleshooting will continue to be hampered if you can't share actual queries and data structure. I strongly suggest that you make a confidentiality contract with a support provider so that you can give them detailed (rather than general) problem reports. > Most of my application is working about the same speed as MSSQL server > (unfortunately its twice the speed box, but as many have pointed out it > could be an issue with the 4 proc dell). I spent considerable time with > Dell and could see my drives are delivering 40 meg per sec. FWIW, on a v40z I get 180mb/s. So your disk array on the Dell is less than ideal ... basically, what you have is a more expensive box, not a faster one :-( > Things I still have to make better are my settings in config, I have it set > to no merge joins and no seq scans. Yeah, I'm also finding that our estimator underestimates the real cost of merge joins on some systems. Basically we need a sort-cost variable, because I've found an up to 2x difference in sort cost depending on architecture. > I am going to have to use flattened history files for reporting (I saw huge > difference here the view for audit cube took 10 minutes in explain analyze > and the flattened file took under one second). > I understand both of these practices are not desirable, but I am at a place > where I have to get it live and these are items I could not resolve. Flattening data for reporting is completely reasonable; I do it all the time. > I believe that was totally IIS not postgres, but I am curious as to if > using postgres odbc will put more stress on the IIS side then MSSQL did. Actually, I think the problem may be ODBC. Our ODBC driver is not the best and is currently being re-built from scratch. Is using npgsql, a much higher-performance driver (for .NET) out of the question? According to one company, npgsql performs better than drivers supplied by Microsoft. > I did have a question if any folks are using two servers one for reporting > and one for data entry what system should be the beefier? Depends on the relative # of users. This is often a good approach, because the requirements for DW reporting and OLTP are completely different. Basically: OLTP: Many slow processors, disk array set up for fast writes, moderate shared mem, low work_mem. DW: Few fast processors, disk array set up for fast reads, high shared mem and work mem. If reporting is at least 1/4 of your workload, I'd suggest spinning that off to the 2nd machine before putting one client on that machine. That way you can also use the 2nd machine as a failover back-up. -- Josh Berkus Aglio Database Solutions San Francisco