Thread: JBoss CMP Performance Problems with PostgreSQL 7.2.3
Darryl A. J. Staflund wrote: >Hi Everyone, > >I am developing a JBoss 3.0.x application using PostgreSQL 7.2.3 as a >back-end database and Solaris 2.8 (SPARC) as my deployment OS. In this >application, I am using an EJB technology called Container Managed >Persistence (CMP 2.0) to manage data persistence for me. Instead of >writing and submitting my own queries to the PostgreSQL database, JBoss is >doing this for me. > >Although this works well for the most part, the insertion of many records >within the context of a single transaction can take a very long time to >complete. Inserting 800 records, for instance, can take upward of a >minute to finish - even though the database is fully indexed and records >consist of no more than a string field and several foreign key integer >values. > >I think I've tracked the problem down to the way in which PostgreSQL >manages transactions. Although on the Java side of things I perform all >my insertions and updates within the context of a single transaction, >PostgreSQL seems to treat each individual query as a separate transaction >and this is slowing down performance immensely. Here is a sample of my >PostgreSQL logging output: > > [...] I think the problem isn't PostgreSQL. This is the JBoss-CMP. Take a look on EJB Benchmark from urbancode (http://www.urbancode.com/projects/ejbbenchmark/default.jsp). Best Regards, Rafal
Rafal Kedziorski wrote: > Darryl A. J. Staflund wrote: > > >Hi Everyone, > > > >I am developing a JBoss 3.0.x application using PostgreSQL 7.2.3 as a > >back-end database and Solaris 2.8 (SPARC) as my deployment OS. In this > >application, I am using an EJB technology called Container Managed > >Persistence (CMP 2.0) to manage data persistence for me. Instead of > >writing and submitting my own queries to the PostgreSQL database, JBoss is > >doing this for me. > > > >Although this works well for the most part, the insertion of many records > >within the context of a single transaction can take a very long time to > >complete. Inserting 800 records, for instance, can take upward of a > >minute to finish - even though the database is fully indexed and records > >consist of no more than a string field and several foreign key integer > >values. > > > >I think I've tracked the problem down to the way in which PostgreSQL > >manages transactions. Although on the Java side of things I perform all > >my insertions and updates within the context of a single transaction, > >PostgreSQL seems to treat each individual query as a separate transaction > >and this is slowing down performance immensely. Here is a sample of my > >PostgreSQL logging output: > > > > > [...] > > I think the problem isn't PostgreSQL. This is the JBoss-CMP. Take a look > on EJB Benchmark from urbancode > (http://www.urbancode.com/projects/ejbbenchmark/default.jsp). > > Best Regards, > Rafal > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org I think the problem is not in the jboss. I am using pg + jboss from a long time and if you know how to wirk with it the combination is excelent. The main problem in this case is CMP and also EntityBeans. By CMP jboss will try to insert this 800 records separate. In this case pg will be slow. I never got good results by using EB and CMP. If you will to have working produkt use BMP. regards, ivan.
You may want to look at this tool as well: http://hibernate.bluemars.net/1.html On Thursday 13 February 2003 3:01 am, pginfo wrote: > Rafal Kedziorski wrote: > > Darryl A. J. Staflund wrote: > > >Hi Everyone, > > > > > >I am developing a JBoss 3.0.x application using PostgreSQL 7.2.3 as a > > >back-end database and Solaris 2.8 (SPARC) as my deployment OS. In this > > >application, I am using an EJB technology called Container Managed > > >Persistence (CMP 2.0) to manage data persistence for me. Instead of > > >writing and submitting my own queries to the PostgreSQL database, JBoss > > > is doing this for me. > > > > > >Although this works well for the most part, the insertion of many > > > records within the context of a single transaction can take a very long > > > time to complete. Inserting 800 records, for instance, can take upward > > > of a minute to finish - even though the database is fully indexed and > > > records consist of no more than a string field and several foreign key > > > integer values. > > > > > >I think I've tracked the problem down to the way in which PostgreSQL > > >manages transactions. Although on the Java side of things I perform all > > >my insertions and updates within the context of a single transaction, > > >PostgreSQL seems to treat each individual query as a separate > > > transaction and this is slowing down performance immensely. Here is a > > > sample of my PostgreSQL logging output: > > > > [...] > > > > I think the problem isn't PostgreSQL. This is the JBoss-CMP. Take a look > > on EJB Benchmark from urbancode > > (http://www.urbancode.com/projects/ejbbenchmark/default.jsp). > > > > Best Regards, > > Rafal > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > I think the problem is not in the jboss. > I am using pg + jboss from a long time and if you know how to wirk with it > the combination is excelent. > The main problem in this case is CMP and also EntityBeans. > By CMP jboss will try to insert this 800 records separate. > In this case pg will be slow. > > I never got good results by using EB and CMP. > If you will to have working produkt use BMP. > regards, > ivan. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Nick Pavlica EchoStar Communications CAS-Engineering (307)633-5237
"Darryl A. J. Staflund" <darryl.staflund@shaw.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.staflund@shaw.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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073