Re: JBoss CMP Performance Problems with PostgreSQL 7.2.3

"Darryl A. J. Staflund" <darryl(dot)staflund(at)shaw(dot)ca> writes:
> MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH
> AaCAJIAEggX+Q29udGVudC1UeXBlOiB0ZXh0L3BsYWluOw0KCWNoYXJzZXQ9
> IlVTLUFTQ0lJIg0KQ29udGVudC1UcmFuc2Zlci1FbmNvZGluZzogN2JpdA0K
> DQpIaSBldmVyeW9uZSwNCg0KVGhhbmtzIGZvciB5b3VyIGNvbW1lbnRzIGFu
> ZCBsaW5rcyB0byB0aGUgdmFyaW91cyB0b29scy4gIEkgd2lsbCBtYWtlIHN1
> cmUNCnRvIGludmVzdGlnYXRlIHRoZW0hDQoNCk5vdyBvbnRvIHRoaXMgbGV0
> dGVyLi4uDQoNCk9uIHRoZSBhc3N1bXB0aW9uIHRoYXQgUG9zdGdyZVNRTCB3
> [etc]

I might answer this if it weren't so unremittingly nastily encoded...
*please* turn off whatever the heck that is.

regards, tom lane

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

Tom Lane wrote:
> "Darryl A. J. Staflund" <darryl(dot)staflund(at)shaw(dot)ca> writes:
> > MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEH
> > AaCAJIAEggX+Q29udGVudC1UeXBlOiB0ZXh0L3BsYWluOw0KCWNoYXJzZXQ9
> > IlVTLUFTQ0lJIg0KQ29udGVudC1UcmFuc2Zlci1FbmNvZGluZzogN2JpdA0K
> > DQpIaSBldmVyeW9uZSwNCg0KVGhhbmtzIGZvciB5b3VyIGNvbW1lbnRzIGFu
> > ZCBsaW5rcyB0byB0aGUgdmFyaW91cyB0b29scy4gIEkgd2lsbCBtYWtlIHN1
> > cmUNCnRvIGludmVzdGlnYXRlIHRoZW0hDQoNCk5vdyBvbnRvIHRoaXMgbGV0
> > dGVyLi4uDQoNCk9uIHRoZSBhc3N1bXB0aW9uIHRoYXQgUG9zdGdyZVNRTCB3
> > [etc]
>
> I might answer this if it weren't so unremittingly nastily encoded...
> *please* turn off whatever the heck that is.

Tom, the answer is obvious:

ljasdfJOJAAJDJFoaijwsdojoAIJSDOIJAsdoFIJSoiJFOIJASDOFIJOLJAASDFaoiojfos

:-)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073