0

We use Hibernate search(version 3.1) and Lucene (version 2.4) for indexing the content runs on Jboss 7.2. Database team had reported huge database session spike. They reported db session go to idle after serving few requests. Here is the code:

public void updateDocumentByIds(IndexMessage indexMessage, java.io.Serializable[] entityPKs,FullTextSession session, boolean isSelfRebuild) throws AAException
{
if (indexMessage.getServProvCode() == null && indexMessage.getSourceNumber() == null) {
logger.error("Agency Code and Source Number are null!");
return;
}
TransactionManager transactionManager = null;
boolean isNewTransaction = false;
FullTextSession searchSession = session;
try
{
IIndexAdapter indexAdapter = IndexAdapter.getIndexAdapter(indexMessage.getEntityType());

IndexDirectoryManager directoryManager = IndexDirectoryManager.getInstance();
String specifyIndexName = directoryManager.getSpecifyIndexName(indexMessage.getServProvCode());

if (searchSession == null)
{
transactionManager = getTransactionManager();
transactionManager.begin();
entityManager = getEntityManager();
Session hibernateSession = (Session) entityManager.getDelegate();
searchSession = SwitchSession.getFullTextSession(hibernateSession, specifyIndexName);
searchSession.setFlushMode(FlushMode.MANUAL); // disable flush operations
searchSession.setCacheMode(CacheMode.IGNORE); // disable 2nd level cache operations
isNewTransaction = true;
}
AgencyModel agencyModel = null;
if (indexMessage.getServProvCode() != null)
{
agencyModel = getAgencyByAgencyCode(searchSession, indexMessage.getServProvCode().toUpperCase());

if (agencyModel == null)
{
logger.warn("No such agency:" + indexMessage.getServProvCode());
return;
}
}
else if (indexMessage.getSourceNumber() == null)
{
logger.warn("Please specify one Agency Code or Source Number!");
return;
}

directoryManager.chooseSyncOrSearchDirectory(specifyIndexName, indexMessage.getEntityType(),
ActionType.SYNC);

for (java.io.Serializable entityPK : entityPKs)
{
try
{
logger.info("========Start Update Index==========");
logger.info("Entity Type: " + indexMessage.getEntityType());
logger.info("Primary Key: " + entityPK.toString());
Object object = indexAdapter.getObjectByPK(searchSession, entityPK, agencyModel);
if (object == null)
{
// Remove index when the data was deleted from DB
searchSession.purge(EntityMapHelper.getEntityClass(indexMessage.getEntityType()), entityPK);
}
else
{
// Update Index when this entity can be found in DB
searchSession.index(object);
}

// While the related index is rebuilding, this record need be tracked into a LOG table.
if (!isSelfRebuild
&& directoryManager.needTrackForSync(searchSession, specifyIndexName, indexMessage
.getEntityType()))
{
saveToUnindexedData(indexMessage, indexMessage.getServProvCode(), entityPK);
}

logger.info("============ End Now =================\n");
}
catch (Exception e)
{
logger.error("Exception occured during update index for " + specifyIndexName + "/"
+ entityPK.toString(), e);

continue;
}
}
searchSession.flushToIndexes();
searchSession.clear();
if (isNewTransaction)
{
commit(entityManager, searchSession, transactionManager);
transactionManager = null;
}
}
catch (Exception e)
{
throw new SyncIndexException("", e);
}
finally
{
if (transactionManager != null)
{
try
{
transactionManager.rollback();
}
catch (Exception e)
{
;
}
}
IndexDirectoryThreadLocal.remove();
}
}

Note: This below Hibernate EntityManagerFactory configuration used for unit test

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="packagesToScan" value="com.comp" />
<property name="persistenceProviderClass" value="com.comp.orm.hibernate3.compHibernatePersistence" />   
<property name="dataSource" ref="dataSource" />
<property name="persistenceUnitName" value="AAPU"/>
<property name="jpaPropertyMap">
<map>
<entry key="hibernate.show_sql"
value="false"/>
<entry key="hibernate.format_sql"
value="true"/>
<entry key="use_sql_comments"
value="true"/>   
<entry key="hibernate.bytecode.use_reflection_optimizer"
value="true"/>
<entry key="hibernate.max_fetch_depth"
value="1"/>
<entry key="hibernate.default_batch_fetch_size"
value="30"/>   
<entry key="hibernate.jdbc.fetch_size"
value="30"/>  
<entry key="hibernate.jdbc.batch_size"
value="15"/> 
<entry key="hibernate.cache.use_second_level_cache"
value="false"/>     
<entry key="hibernate.jdbc.use_scrollable_resultset"
value="true"/> 
<entry key="hibernate.temp.use_jdbc_metadata_defaults"
value="false"/> 
<entry key="hibernate.jdbc.factory_class"
value="org.hibernate.jdbc.BatchingBatcherFactory"/> 
<entry key="hibernate.c3p0.min_size"
value="50"/>    
<entry key="hibernate.c3p0.max_size"
value="100"/> 
<entry key="hibernate.c3p0.timeout"
value="120"/> 
<entry key="hibernate.c3p0.max_statements"
value="100"/> 
<entry key="hibernate.c3p0.idle_test_period"
value="3000"/>

<entry key="hibernate.search.default.directory_provider"
value="com.comp.aa.globalsearch.directory.IndexDirectoryProvider"/>
<entry key="hibernate.search.default.indexBase"
value="/index"/>
<entry key="hibernate.search.reader.strategy"
value="com.comp.aa.globalsearch.directory.SwitchReaderProvider"/>
<entry key="hibernate.jdbc.factory_class"
value="org.hibernate.jdbc.BatchingBatcherFactory"/>

<entry key="hibernate.transaction.manager_lookup_class"
value="org.hibernate.transaction.JBossTransactionManagerLookup"/>
<entry key="hibernate.current_session_context_class"
value="jta"/>              
</map>
</property>


<property name="persistenceUnitPostProcessors">
<list>
<bean class="com.comp.orm.util.JtaPersistenceUnitPostProcessor">
<property name="jtaDataSource" ref="dataSource"/>
</bean>             
</list>
</property>
</bean>

In some of the forums, i saw hibernate.c3p0.idle_test_period should be less or equal to hibernate.c3p0.timeout. We changed both the values to 5 minutes, but still the problem exists

Any idea why db sessions are idle?

pacification
  • 5,838
  • 4
  • 29
  • 51
  • Here is the database configurations on Jboss server av.db.debug=false av.db.minsize=0 av.db.maxsize=300 av.db.maxpooledstatements=50 av.db.interval.seconds=180 av.db.timeout.seconds=780 av.db.blocking.timeout.millis=5000 av.db.idle.timeout.minutes=1 – hithendra sharma Apr 04 '18 at 00:57
  • cant really get whats your issue, you are defining 50 min sessions to be maintained by the c3p0_pool , without defining the maxIdleTimeout , and also (i suppose) that your unit test is not concurrent, thus it simple reuses one connection out of the 50......check this [answer](https://stackoverflow.com/questions/12507021/best-configuration-of-c3p0) and the [c3p0_spec](http://www.mchange.com/projects/c3p0/) – AntJavaDev Apr 04 '18 at 07:47
  • The libraries you're using are 10 years old: you really should update. Also, the code you are showing is not using Hibernate directly so it doesn't help, if you want some sensible suggestions you will have better luck by improving your question. – Sanne Apr 04 '18 at 09:36
  • @Sanne, I had updated the ticket with Jboss db configurations in the comment section. Sure, we will improve the libraries. We have seen searchSession.flushToIndexes(); taking few seconds/minutes on App Dynamics. Can you please suggest is there anyway to improve the code so that it can release the db connection? – hithendra sharma Apr 04 '18 at 14:38
  • @AntJavaDev, Thanks for your response. I will take a look. – hithendra sharma Apr 04 '18 at 14:41

0 Answers0