pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Darryl A. J. Staflund <darryl.staflund@shaw.ca>
To: pgsql-performance@postgresql.org
Subject: Re: JBoss CMP Performance Problems with PostgreSQL 7.2.3
Date: Thu, 13 Feb 2003 20:20:38 -0800
Message-ID: <200302132020.38033.josh@agliodbs.com> (raw)
In-Reply-To: <000f01c2d3dc$f8ad2d80$ac584618@ltd924607.ca>
References: <000f01c2d3dc$f8ad2d80$ac584618@ltd924607.ca>

Darryl,

Your last e-mail got mangled in transit.  I think you'll want to re-send it so 
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 is 
performing normally.  Your problem is with CMP, not Postgres. 

" 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 
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 
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 
which respond to you.  Another poster already gave you a link to a bug report 
mentioning that this is a problem with CMP.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



view thread (6+ messages)  latest in thread

Message-ID: <200302132020.38033.josh@agliodbs.com>
Permalink:  ../200302132020.38033.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200302132020.38033.josh@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, darryl.staflund@shaw.ca
  Subject: Re: JBoss CMP Performance Problems with PostgreSQL 7.2.3
  In-Reply-To: <200302132020.38033.josh@agliodbs.com>
  References: <000f01c2d3dc$f8ad2d80$ac584618@ltd924607.ca> <200302132020.38033.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox