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 7F5443A3F57 for ; Tue, 16 Nov 2004 19:43:42 +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 65756-05 for ; Tue, 16 Nov 2004 19:43:39 +0000 (GMT) Received: from mx1.neopolitan.us (mx1.neopolitan.us [65.87.16.224]) by svr1.postgresql.org (Postfix) with ESMTP id 850F43A3D54 for ; Tue, 16 Nov 2004 19:43:39 +0000 (GMT) Received: from [65.87.16.98] (HELO vulture.corp.neopolitan.com) by mx1.neopolitan.us (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 6414907 for pgsql-performance@postgresql.org; Tue, 16 Nov 2004 11:43:39 -0800 Subject: Re: Insertion puzzles From: "J. Andrew Rogers" To: pgsql-performance@postgresql.org In-Reply-To: <102570-22004110142048396@M2W072.mail2web.com> References: <102570-22004110142048396@M2W072.mail2web.com> Content-Type: text/plain Organization: Message-Id: <1100633534.26741.28.camel@vulture.corp.neopolitan.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 16 Nov 2004 11:43:38 -0800 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.1 tagged_above=0.0 required=5.0 tests=MAILTO_TO_SPAM_ADDR X-Spam-Level: * X-Archive-Number: 200411/230 X-Sequence-Number: 9184 On Sat, 2004-11-13 at 18:00, dentfirst13@earthlink.net wrote: > I ran into the exact same problem you did. I tried many, many changes to > the conf file, I tried O.S. tuning but performance stunk. I had a fairly > simple job that had a lot of updates and inserts that was taking 4 1/2 > hours. I re-wrote it to be more "Postgres friendly" - meaning less > database updates and got it down under 2 1/2 hours (still horrible). > Understand, the legacy non-postgres ISAM db took about 15 minutes to > perform the same task. I assumed it was a system problem that would go > away when we upgraded servers but it did not. I converted to MySQL and the > exact same java process takes 5 minutes! Postgres is a great DB for some, > for our application it was not - you may want to consider other products > that are a bit faster and do not require the vacuuming of stale data. I have to wonder if the difference is in how your job is being chopped up by the different connection mechanisms. The only time I've had performance problems like this, it was the result of pathological and unwelcome behaviors in the way things were being handled in the connector or database design. We have a 15GB OLTP/OLAP database on five spindles with a large insert/update load and >100M rows, and I don't think it takes 2.5 hours to do *anything*. This includes inserts/updates of hundreds of thousands of rows at a shot, which takes very little time. I've gotten really bad performance before under postgres, but once I isolated the reason I've always gotten performance that was comparable to any other commercial RDBMS on the same hardware. J. Andrew Rogers