Re: OpenJPA pool with DBCP

Hi wojtek,

Answers inlined.

Best regards

Benoit

On 05/06/2023 23:48, Wojtek wrote:
> Hi Benoit
>
> Please find my comments below
>
> On 04/06/2023 23:05, Benoit TELLIER wrote:
>> Hello Wojtek,
>>
>> Sorry, this goes beyond my JPA skills, this would be hard for me to 
>> support you on this issue.
>>
>> I also think that this email lacks a bit of context.
>>
>>   -> Why do you try setting up connection pooling? Because of 
>> suspicions of James continuously opening new connections? CF 
>> https://github.com/apache/james-project/pull/1579#issuecomment-1574414163 
>> ?
>
> Yes, that was one of the reasons.
> In general persistent database connection is better: faster query 
> execution - no need to establish connection each time and less 
> resource (memory consumption) witch connection objects.
> In that case, having a fixed connection pool helps in concurrent 
> environment.
>
>>   -> Just in doubt always mention the version you are using (3.8.0? A 
>> given pull request?)
>
> Noted for the next time.
> I'm usually on the newest version (3.9-SNAPSHOT) but in this case I 
> was testing PR #1579
>
>> For instance https://github.com/apache/james-project/pull/1579 
>> requires to allow JPA in a multithread enviroment by setting 
>> -Dopenjpa.Multithreaded=true (required for the inter-operability with 
>> reactor...) cf 
>> https://github.com/chibenwa/james-project/blob/46168bb2f74d9186f10c101a7b6df3f5468927a5/server/apps/jpa-app/sample-configuration/jvm.properties#L53
>
> I actually included that configuration option but from my tests it 
> seemed it didn't have any effect. After adding 
> `properties.put("openjpa.Multithreaded", "true");` in 
> org.apache.james.modules.data.JPAEntityManagerModule#provideEntityManagerFactory 
> fixed the issue.
>
>
> Though, with that config and exceptions gone I only got to 5 database 
> connections (default max is 8) - most likely inserts were not 
> concurrent enough to trigger more connections?


I think so.

I would suggest formally testing it with gatling: 
https://github.com/apache/james-project/blob/master/server/apps/distributed-app/docs/modules/ROOT/pages/benchmark/james-benchmark.adoc

>
>
> I think all openjpa.*/datasource.* options from 
> `james-database.properties` should be passed to OpenJPA/DBCP. This way 
> one would get complete freedom in configuring/tweaking those libraries 
> configs 
> (https://openjpa.apache.org/builds/3.2.2/apache-openjpa/docs/manual.html 
> and https://commons.apache.org/proper/commons-dbcp/configuration.html)
+1
>
> (btw. it seems that TestOnBorrow option has a bug as it inserts 
> `TestOnBorrow=Optional[…]` and should use 
> `jpaConfiguration.isTestOnBorrow().ifPresent(testOnBorrow -> 
> connectionFactoryProperties.add("TestOnBorrow=" + testOnBorrow));` 
> like other config options.
Nice catch. Fix welcome.
>
> Wojtek
>
>> Also, if in doubt we could reach to the openjpa community...
>>
>> Best regards,
>>
>> Benoit TELLIER
>>
>> On 03/06/2023 09:01, Wojtek wrote:
>>> I'm trying to configure/use connection pool with OpenJPA and DBCP. 
>>> When adding commons-dbcp:1.4 to the classpath I get error: 
>>> "org.apache.openjpa.persistence.PersistenceException/SQLException/com.mysql.cj.exceptions.StatementIsClosedException: 
>>> No operations allowed after statement closed." and no pool, 
>>> coonections being re-created constantly and higher memory usage.
>>>
>>> With commons-dbcp2:2.9.0 I do get constant connection pool (lsof, 
>>> same ports after a couple of tries) but James floods logs with 
>>> exceptions about multithreading operation (below) and imapsync dies 
>>> shortly due to erroring out.
>>>
>>>
>>> Is connection pooling supported? How to correctly configure it?
>>>
>>>
>>> OpenJPA configuration 
>>> (https://openjpa.apache.org/builds/3.2.2/apache-openjpa/docs/manual.html#openjpa.jdbc.DriverDataSource) 
>>> seems to indicate, that DBCP2 should be available in the classpath, 
>>> which seems to be picked up corrently and create the pool, but there 
>>> seem to be issue with James utilizing it?
>>>
>>> ----
>>>
>>> StackTraces/logs
>>>
>>>
>>> 1:55:39.878 [WARN ] i.n.c.DefaultChannelPipeline - An 
>>> exceptionCaught() event was fired, and it reached at the tail of the 
>>> pipeline. It usually means the last handler in the pipeline did not 
>>> handle the exception.
>>> org.apache.openjpa.persistence.PersistenceException: Multiple 
>>> concurrent threads attempted to access a single broker. By default 
>>> brokers are not thread safe; if you require and/or intend a broker 
>>> to be accessed by more than one thread, set the 
>>> openjpa.Multithreaded property to true to override the default 
>>> behavior.
>>>     at 
>>> org.apache.openjpa.kernel.BrokerImpl.endOperation(BrokerImpl.java:2083)
>>>     at 
>>> org.apache.openjpa.kernel.BrokerImpl.isActive(BrokerImpl.java:2028)
>>>     at 
>>> org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:521)
>>>     at 
>>> org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
>>>     at 
>>> org.apache.openjpa.persistence.PersistenceExceptions$2.translate(PersistenceExceptions.java:82)
>>>     at 
>>> org.apache.openjpa.kernel.DelegatingBroker.translate(DelegatingBroker.java:108)
>>>     at 
>>> org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:523)
>>>     at 
>>> org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
>>>     at 
>>> org.apache.james.backends.jpa.EntityManagerUtils.safelyClose(EntityManagerUtils.java:37)
>>>     at 
>>> org.apache.james.mailbox.jpa.JPATransactionalMapper.endRequest(JPATransactionalMapper.java:91)
>>>     at 
>>> org.apache.james.mailbox.store.MailboxSessionMapperFactory.endProcessingRequest(MailboxSessionMapperFactory.java:143)
>>>     at 
>>> org.apache.james.mailbox.store.StoreMailboxManager.endProcessingRequest(StoreMailboxManager.java:915)
>>>     at 
>>> org.apache.james.mailbox.MailboxManager.lambda$manageProcessing$10(MailboxManager.java:436)
>>>     at 
>>> reactor.core.publisher.MonoUsing$MonoUsingSubscriber.onComplete(MonoUsing.java:275)
>>>     at 
>>> reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
>>>     at 
>>> reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
>>>     at 
>>> reactor.core.publisher.FluxHide$SuppressFuseableSubscriber.onComplete(FluxHide.java:147)
>>>     at 
>>> reactor.core.publisher.MonoIgnoreElements$IgnoreElementsSubscriber.onComplete(MonoIgnoreElements.java:89)
>>>     at 
>>> reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
>>>     at 
>>> reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
>>>     at 
>>> reactor.core.publisher.Operators$MultiSubscriptionSubscriber.onComplete(Operators.java:2205)
>>>     at 
>>> reactor.core.publisher.MonoRunnable.subscribe(MonoRunnable.java:50)
>>>     at 
>>> reactor.core.publisher.MonoDeferContextual.subscribe(MonoDeferContextual.java:55)
>>>     at reactor.core.publisher.Mono.subscribe(Mono.java:4444)
>>>     at 
>>> reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onError(FluxOnErrorResume.java:103)
>>>     at 
>>> reactor.core.publisher.MonoFlatMap$FlatMapMain.onError(MonoFlatMap.java:180)
>>>     at 
>>> reactor.core.publisher.MonoFlatMap$FlatMapMain.onError(MonoFlatMap.java:180)
>>>     at 
>>> reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onError(MonoIgnoreThen.java:278)
>>>     at 
>>> reactor.core.publisher.SerializedSubscriber.onError(SerializedSubscriber.java:124)
>>>     at 
>>> reactor.core.publisher.FluxRetryWhen$RetryWhenMainSubscriber.whenError(FluxRetryWhen.java:225)
>>>     at 
>>> reactor.core.publisher.FluxRetryWhen$RetryWhenOtherSubscriber.onError(FluxRetryWhen.java:274)
>>>     at 
>>> reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onError(FluxContextWrite.java:121)
>>>     at 
>>> reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.maybeOnError(FluxConcatMapNoPrefetch.java:326)
>>>     at 
>>> reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.onNext(FluxConcatMapNoPrefetch.java:211)
>>>     at 
>>> reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.onNext(FluxContextWrite.java:107)
>>>     at 
>>> reactor.core.publisher.SinkManyEmitterProcessor.drain(SinkManyEmitterProcessor.java:471)
>>>     at 
>>> reactor.core.publisher.SinkManyEmitterProcessor$EmitterInner.drainParent(SinkManyEmitterProcessor.java:615)
>>>     at 
>>> reactor.core.publisher.FluxPublish$PubSubInner.request(FluxPublish.java:602)
>>>     at 
>>> reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.request(FluxContextWrite.java:136)
>>>     at 
>>> reactor.core.publisher.FluxConcatMapNoPrefetch$FluxConcatMapNoPrefetchSubscriber.request(FluxConcatMapNoPrefetch.java:336)
>>>     at 
>>> reactor.core.publisher.FluxContextWrite$ContextWriteSubscriber.request(FluxContextWrite.java:136)
>>>     at 
>>> reactor.core.publisher.Operators$DeferredSubscription.request(Operators.java:1717)
>>>     at 
>>> reactor.core.publisher.FluxRetryWhen$RetryWhenMainSubscriber.onError(FluxRetryWhen.java:192)
>>>     at 
>>> reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:149)
>>>     at 
>>> reactor.core.publisher.MonoHasElement$HasElementSubscriber.onNext(MonoHasElement.java:72)
>>>     at 
>>> reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.onNext(MonoSubscribeOn.java:146)
>>>     at 
>>> reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
>>>     at 
>>> reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
>>>     at 
>>> reactor.core.publisher.MonoCallable$MonoCallableSubscription.request(MonoCallable.java:156)
>>>     at 
>>> reactor.core.publisher.Operators$MultiSubscriptionSubscriber.set(Operators.java:2341)
>>>     at 
>>> reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onSubscribe(FluxOnErrorResume.java:74)
>>>     at 
>>> reactor.core.publisher.MonoCallable.subscribe(MonoCallable.java:48)
>>>     at reactor.core.publisher.Mono.subscribe(Mono.java:4444)
>>>     at 
>>> reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.run(MonoSubscribeOn.java:126)
>>>     at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:84)
>>>     at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:37)
>>>     at 
>>> java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
>>>     at 
>>> java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
>>>     at 
>>> java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
>>>     at 
>>> java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
>>>     at java.base/java.lang.Thread.run(Thread.java:1623)
>>> 21:55:39.885 [ERROR] o.a.j.i.p.AbstractMailboxProcessor - Unexpected 
>>> error during IMAP processing
>>> org.apache.openjpa.persistence.PersistenceException: Multiple 
>>> concurrent threads attempted to access a single broker. By default 
>>> brokers are not thread safe; if you require and/or intend a broker 
>>> to be accessed by more than one thread, set the 
>>> openjpa.Multithreaded property to true to override the default 
>>> behavior.
>>>     at 
>>> org.apache.openjpa.kernel.BrokerImpl.endOperation(BrokerImpl.java:2083)
>>>     at 
>>> org.apache.openjpa.kernel.BrokerImpl.isActive(BrokerImpl.java:2028)
>>>     at 
>>> org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:521)
>>>     at 
>>> org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
>>>     at 
>>> org.apache.openjpa.persistence.PersistenceExceptions$2.translate(PersistenceExceptions.java:82)
>>>     at 
>>> org.apache.openjpa.kernel.DelegatingBroker.translate(DelegatingBroker.java:108)
>>>     at 
>>> org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:523)
>>>     at 
>>> org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
>>>     at 
>>> org.apache.james.mailbox.jpa.JPATransactionalMapper.rollback(JPATransactionalMapper.java:81)
>>>     at 
>>> org.apache.james.mailbox.store.transaction.TransactionalMapper.lambda$executeReactive$1(TransactionalMapper.java:52)
>>>     at 
>>> com.github.fge.lambdas.consumers.ConsumerChainer.lambda$sneakyThrow$9(ConsumerChainer.java:73)
>>>     at reactor.core.publisher.Mono.lambda$doOnError$20(Mono.java:2741)
>>>     at 
>>> reactor.core.publisher.MonoPeekTerminal$MonoTerminalPeekSubscriber.onError(MonoPeekTerminal.java:250)
>>>     at 
>>> reactor.core.publisher.MonoPeekTerminal$MonoTerminalPeekSubscriber.onError(MonoPeekTerminal.java:258)
>>>     at 
>>> reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.onError(MonoIgnoreThen.java:278)
>>>     at 
>>> reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:255)
>>>     at 
>>> reactor.core.publisher.MonoIgnoreThen.subscribe(MonoIgnoreThen.java:51)
>>>     at 
>>> reactor.core.publisher.InternalMonoOperator.subscribe(InternalMonoOperator.java:64)
>>>     at 
>>> reactor.core.publisher.MonoFlatMap$FlatMapMain.onNext(MonoFlatMap.java:165)
>>>     at 
>>> reactor.core.publisher.FluxSwitchIfEmpty$SwitchIfEmptySubscriber.onNext(FluxSwitchIfEmpty.java:74)
>>>     at 
>>> reactor.core.publisher.FluxMap$MapSubscriber.onNext(FluxMap.java:122)
>>>     at 
>>> reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.onNext(MonoSubscribeOn.java:146)
>>>     at 
>>> reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
>>>     at 
>>> reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onNext(FluxOnErrorResume.java:79)
>>>     at 
>>> reactor.core.publisher.MonoCallable$MonoCallableSubscription.request(MonoCallable.java:156)
>>>     at 
>>> reactor.core.publisher.Operators$MultiSubscriptionSubscriber.set(Operators.java:2341)
>>>     at 
>>> reactor.core.publisher.FluxOnErrorResume$ResumeSubscriber.onSubscribe(FluxOnErrorResume.java:74)
>>>     at 
>>> reactor.core.publisher.MonoCallable.subscribe(MonoCallable.java:48)
>>>     at reactor.core.publisher.Mono.subscribe(Mono.java:4444)
>>>     at 
>>> reactor.core.publisher.MonoSubscribeOn$SubscribeOnSubscriber.run(MonoSubscribeOn.java:126)
>>>     at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:84)
>>>     at reactor.core.scheduler.WorkerTask.call(WorkerTask.java:37)
>>>     at 
>>> java.base/java.util.concurrent.FutureTask.run(FutureTask.java:317)
>>>     at 
>>> java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
>>>     at 
>>> java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
>>>     at 
>>> java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
>>>     at java.base/java.lang.Thread.run(Thread.java:1623)
>>>     Suppressed: org.apache.openjpa.persistence.PersistenceException: 
>>> Multiple concurrent threads attempted to access a single broker. By 
>>> default brokers are not thread safe; if you require and/or intend a 
>>> broker to be accessed by more than one thread, set the 
>>> openjpa.Multithreaded property to true to override the default 
>>> behavior.
>>>         at 
>>> org.apache.openjpa.kernel.BrokerImpl.endOperation(BrokerImpl.java:2083)
>>>         at 
>>> org.apache.openjpa.kernel.BrokerImpl.isActive(BrokerImpl.java:2028)
>>>         at 
>>> org.apache.openjpa.kernel.DelegatingBroker.isActive(DelegatingBroker.java:521)
>>>         at 
>>> org.apache.openjpa.persistence.EntityManagerImpl.isActive(EntityManagerImpl.java:812)
>>>         at 
>>> org.apache.openjpa.persistence.PersistenceExceptions$2.translate(PersistenceExceptions.java:82)
>>>         at 
>>> org.apache.openjpa.kernel.DelegatingBroker.translate(DelegatingBroker.java:108)
>>>         at 
>>> org.apache.openjpa.kernel.DelegatingBroker.begin(DelegatingBroker.java:1028)
>>>         at 
>>> org.apache.openjpa.persistence.EntityManagerImpl.begin(EntityManagerImpl.java:684)
>>>         at 
>>> org.apache.james.mailbox.jpa.JPATransactionalMapper.begin(JPATransactionalMapper.java:59)
>>>         ... 25 common frames omitted
>>>     Wrapped by: org.apache.james.mailbox.exception.MailboxException: 
>>> Begin of transaction failed
>>>         at 
>>> org.apache.james.mailbox.jpa.JPATransactionalMapper.begin(JPATransactionalMapper.java:61)
>>>         at 
>>> com.github.fge.lambdas.runnable.RunnableChainer.lambda$sneakyThrow$215(RunnableChainer.java:71)
>>>         at 
>>> reactor.core.publisher.MonoRunnable.call(MonoRunnable.java:73)
>>>         at 
>>> reactor.core.publisher.MonoRunnable.call(MonoRunnable.java:32)
>>>         at 
>>> reactor.core.publisher.MonoIgnoreThen$ThenIgnoreMain.subscribeNext(MonoIgnoreThen.java:252)
>>>         ... 21 common frames omitted
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>