X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 00EFD475A6D for ; Thu, 13 Feb 2003 23:20:59 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 5C1ED475A45 for ; Thu, 13 Feb 2003 23:20:58 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2839329; Thu, 13 Feb 2003 20:21:10 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: "Darryl A. J. Staflund" , pgsql-performance@postgresql.org Subject: Re: JBoss CMP Performance Problems with PostgreSQL 7.2.3 Date: Thu, 13 Feb 2003 20:20:38 -0800 User-Agent: KMail/1.4.3 References: <000f01c2d3dc$f8ad2d80$ac584618@ltd924607.ca> In-Reply-To: <000f01c2d3dc$f8ad2d80$ac584618@ltd924607.ca> MIME-Version: 1.0 Message-Id: <200302132020.38033.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/72 X-Sequence-Number: 1128 Darryl, Your last e-mail got mangled in transit. I think you'll want to re-send it= so=20 you get responses from others. You said: "On the assumption that PostgreSQL was auto-committing each of the 1400 queries wrapped within the a BEGIN; and COMMIT;, I asked my DBA to disable auto-commit so that I could see if there was any performance improvement." Er, no, Darryl, you mis-understood. CMP is Auto-Committing. PostgreSQL i= s=20 performing normally. Your problem is with CMP, not Postgres.=20 " I was told he couldn't do that in our current version of PostgreSQL (7.2.3?) but he did turn fsync off in order to achieve a similar effect." Which is fine up until you have an unexpected power-out, at which point you= 'd=20 better have good backups. "What a difference. Whereas before it took at least a minute to insert 1400 queries into the database, with fsync turned off it only took 12 seconds. If anything, this shows me that JBoss' implementation, although slow in comparison to other CMP implementations, is not the primary source of the performance problems. Also, I'm inclined to believe that PostgreSQL is auto-committing each query within the transaction even though it shouldn't. Otherwise, why would turning fsync off make such a big performance difference?" Perhaps because CMP is sending a "commit" statement after each query to=20 PostgreSQL? "So the questions I have now is -- is this improvement in performance evidence of my assumption that PostgreSQL is auto-committing each of the 1400 queries within the transaction (the logs sure suggest this.) And if so, why is it auto-committing these queries? I thought PostgreSQL suspended auto-commit after encountering a BEGIN; statement." See above. You need to examine your tools better, and to read the e-mails= =20 which respond to you. Another poster already gave you a link to a bug repo= rt=20 mentioning that this is a problem with CMP. --=20 Josh Berkus Aglio Database Solutions San Francisco