Skip site navigation (1) Skip section navigation (2)

Re: JBoss CMP Performance Problems with PostgreSQL 7.2.3

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: "Darryl A(dot) J(dot) Staflund" <darryl(dot)staflund(at)shaw(dot)ca>,pgsql-performance(at)postgresql(dot)org
Subject: Re: JBoss CMP Performance Problems with PostgreSQL 7.2.3
Date: 2003-02-14 04:20:38
Message-ID: 200302132020.38033.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2003-02-14 04:23:44
Subject: Re: [HACKERS] More benchmarking of wal_buffers
Previous:From: Tom LaneDate: 2003-02-14 04:18:52
Subject: Re: JBoss CMP Performance Problems with PostgreSQL 7.2.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group