Search: JBoss datasource + Spring problem

boudewijn · **Joined:** Fri Feb 24, 2006 9:15 am **Posts:** 17

Hello,

I'm developing an application, to be run on JBoss AS 4.2.1, using hibernate, JPA, configuration is done by Spring. Database is Oracle.

I'm having a problem with indexing.
When I configure Spring to access the database directly (or through 3cp0), there is no problem and everything is perfect.
But I need to access the data through a JBoss Datasource. When I try to index lucene, I get the following error:

Code:

2011-03-16 22:22:47,187 WARN  [org.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: null
2011-03-16 22:22:47,187 ERROR [org.hibernate.util.JDBCExceptionReporter] No ManagedConnections available within configured blocking timeout ( 45000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 45000 [ms] ))
2011-03-16 22:22:47,187 ERROR [org.hibernate.search.batchindexing.IdentifierProducer] error during batch indexing: 
org.hibernate.exception.GenericJDBCException: Cannot open connection
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
   at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
   at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
   at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:160)
   at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:81)
   at org.hibernate.search.batchindexing.IdentifierProducer.inTransactionWrapper(IdentifierProducer.java:110)
   at org.hibernate.search.batchindexing.IdentifierProducer.run(IdentifierProducer.java:92)
   at org.hibernate.search.batchindexing.OptionallyWrapInJTATransaction.run(OptionallyWrapInJTATransaction.java:110)
   at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:650)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:675)
   at java.lang.Thread.run(Thread.java:595)
Caused by: org.jboss.util.NestedSQLException: No ManagedConnections available within configured blocking timeout ( 45000 [ms] ); - nested throwable: (javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 45000 [ms] ))
   at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:94)
   at org.hibernate.ejb.connection.InjectedDataSourceConnectionProvider.getConnection(InjectedDataSourceConnectionProvider.java:71)
   at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
   ... 9 more
Caused by: javax.resource.ResourceException: No ManagedConnections available within configured blocking timeout ( 45000 [ms] )
   at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(InternalManagedConnectionPool.java:301)
   at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(JBossManagedConnectionPool.java:500)
   at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(BaseConnectionManager2.java:341)
   at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(TxConnectionManager.java:315)
   at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(BaseConnectionManager2.java:396)
   at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(BaseConnectionManager2.java:842)
   at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:88)
   ... 11 more

This happens both on Oracle XE and production edition.

The rest of the database accesses work just fine in the application. Any idea what's happening?

Cheers, Bo

sanne.grinovero · **Posted:** Thu Mar 17, 2011 12:37 pm

hi, which version of Hibernate Search is that?
also, make sure your datasource has an high enough max setting, the massindexer needs many connections in parallel.

boudewijn · **Joined:** Fri Feb 24, 2006 9:15 am **Posts:** 17

Thanks for the answer.

I'm using 3.4.0-SNAPSHOT

The code I use right now to reindex is:

Code:

   FullTextEntityManager fullTextEntityManager = Search.getFullTextEntityManager(em);
   try {
       fullTextEntityManager.createIndexer().startAndWait();
   } catch (InterruptedException e) {
       logger.debug(e);
   }

I was about to replace it with:

Code:

fullTextSession.setFlushMode(FlushMode.MANUAL);
fullTextSession.setCacheMode(CacheMode.IGNORE);
transaction = fullTextSession.beginTransaction();
//Scrollable results will avoid loading too many objects in memory
ScrollableResults results = fullTextSession.createCriteria( Email.class )
    .setFetchSize(BATCH_SIZE)
    .scroll( ScrollMode.FORWARD_ONLY );
int index = 0;
while( results.next() ) {
    index++;
    fullTextSession.index( results.get(0) ); //index each element
    if (index % BATCH_SIZE == 0) {
        fullTextSession.flushToIndexes(); //apply changes to indexes
        fullTextSession.clear(); //free memory since the queue is processed
    }
}
transaction.commit();

Is there a way to tune the number of database connections required? Is this done by applying the parameter .threadsToLoadObjects( )?

sanne.grinovero · **Posted:** Thu Mar 17, 2011 12:58 pm

Code looks good.
using threadsToLoadObjects() you can reduce the amount of required connections, but will reduce performance as well.
you should open the JBoss datasource reference, there are parameters to define for min/max number of queries, helpers to check for connection health, many cool things. What you need especially is to raise the maximum limit.

Note the database might have limits as well, but as it worked with c3p0 I guess you didn't reach them yet.

boudewijn · **Joined:** Fri Feb 24, 2006 9:15 am **Posts:** 17

Hello,

allowing more connections seems to work:

Code:

<max-pool-size>100</max-pool-size>

However, this concerns me: if for some reason the container can't provide the necesary connections, the complete JBoss becomes unstable because of this error. The client isn't going to like this for his production system..

Is there a way I can control the amount of threads I'm going to need?
Are the amounts for threadsToLoadObjects and threadsForSubsequentFetching to be added to know the total amount of conections needed?

I just did a test with .threadsToLoadObjects(5).threadsForSubsequentFetching(5) and max-pool-size=10, and I got the same "No ManagedConnections available within configured blocking timeout" error.

Bo

sanne.grinovero · **Posted:** Thu Mar 17, 2011 1:58 pm

Quote:

However, this concerns me: if for some reason the container can't provide the necesary connections, the complete JBoss becomes unstable because of this error. The client isn't going to like this for his production system..

The massindexer won't ask more and more connections, it will create at most one per worker thread. The max. worker threads is the sum of the size of the threadpools used at each stage, you can configure them all, for each root entity which is going to be indexed at the same time.

(threadsToLoadObjects + threadsForSubsequentFetching are the two that consume connections, multiply the result for each @Indexed type.
(actually subclasses of indexed type will share one).

So assuming you run it with 3 threadsToLoadObjects, 5 threadsForSubsequentFetching, and have two indexed objects, it will use at maximum 8*2=16 connections.

you can reduce thread usage by targeting a single instance each time, will take more time of course.

JMX exposes all the actual numbers so you can double check. Also via JConsole, the threads have labels so you can see them.

boudewijn · **Joined:** Fri Feb 24, 2006 9:15 am **Posts:** 17

Ok, great, I've been able to reduce the number of max-pool-size without the datasource hanging.

Cheers!