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?