Very poor jdbc performance with Spring 3.0.0

[scoopex]

Hi,

my application runs of very poor performance.

How can i maximize insert performance for a high number and similar types of inserts?
(only the data differs)
Insert performance was 30times better while using a pure EJB3 mechanism.

- Is there a way to disable autocommit?
- How can i perform a commit?
- What a your general hints to maximize insert performance?

The details of my application:

- Spring 3.0.0
- Jboss 5.1
- IBM JDK 1.6
- Messagedriven Bean which collects data from ActiveMQ
- Application writes to a Oracle 10 database
- i have not configured any transaction-management
- i use tx-datasources which are defined in the appserver
- i only do inserts like this:
INSERT INTO FOO.BAR (ID, STATUS, XMLNACHRICHT) VALUES(FOO.BAR_ID_SEQ.nextval, ....)
=> The XMLNACHRICHT is XML fragment and 150 KB
- According to my profiler each insert needs 150ms
- I use org.springframework.jdbc.core.JdbcTemplate.update( sql, Object[])
- Application Conext for JDBC

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:jms="http://www.springframework.org/schema/jms" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
http://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

<jee:jndi-lookup id="db2DataSource" jndi-name="java:DB2XADS" />
<jee:jndi-lookup id="oracleDatasource" jndi-name="java:RacDS" />
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHan dler" />
</beans>

- Application Conext for JMS

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:jms="http://www.springframework.org/schema/jms" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
http://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms-3.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

<!--
<bean id="connectionFactory" class="org.apache.activemq.ActiveMQConnectionFactory">
<property name="brokerURL" value="${jms.brokerUrl}" />
</bean>
-->

<jee:jndi-lookup id="connectionFactory" jndi-name="java:activemq/QueueConnectionFactory"></jee:jndi-lookup>


<bean id="messageReceiver"
class="foo.bar.jms.QueueNachrichtMdp" />


<jms:listener-container connection-factory="connectionFactory"
acknowledge="transacted" concurrency="1-4" cache="none">
<jms:listener destination="${jms.destination}" ref="messageReceiver"
method="onMessage" />
</jms:listener-container>
</beans>

[Marten Deinum]

If you don't have transactions there is no connection re-use which means a connection is obtained each time.

So start by configuring transactions, also don't recreate a JdbcTemplate etc. each time re-use one.

[scoopex]

I installed a XA-Datasource and configured transaction management.

Application performance is still very poor.
One transaction contains 500 JdbcTemplate.update() calls - is there a possibility to find out whats going on between spring and my jdbc driver?


The JMS application context:


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:jms="http://www.springframework.org/schema/jms" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-3.0.xsd
http://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms-3.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

<!--
<bean id="connectionFactory" class="org.apache.activemq.ActiveMQConnectionFactory">
<property name="brokerURL" value="${jms.brokerUrl}" />
</bean>
-->

<jee:jndi-lookup id="connectionFactory" jndi-name="java:activemq/QueueConnectionFactory"></jee:jndi-lookup>


<bean id="messageReceiver"
class="foo.bar.jms.QueueNachrichtMdp" />

<bean id="messageReceiverEinarbeitungsnachricht"
class="foo.bar.jms.FooMdp" />

<jms:listener-container connection-factory="connectionFactory"
acknowledge="transacted" concurrency="1-4" cache="none">
<jms:listener destination="${jms.destination}" ref="messageReceiver"
method="onMessage" />
</jms:listener-container>

</beans>


The JDBC application context:


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
xmlns:jms="http://www.springframework.org/schema/jms" xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.0.xsd
http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
http://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

<context:component-scan base-package="foo.bar.dao" />

<jee:jndi-lookup id="db2DataSource" jndi-name="java:DB2XADS" />

<jee:jndi-lookup id="oracleDatasource" jndi-name="java:RacDS" />

<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransaction Manager">
<property name="transactionManagerName" value="java:TransactionManager"/>
<property name="userTransactionName" value="java:comp/UserTransaction"/>
</bean>


<tx:annotation-driven transaction-manager="transactionManager" />

<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHan dler" />

</beans>

[Marten Deinum]

Can you post the code, just looking at the config isn't giving much away.

[scoopex]

It´s difficult to post a clearly arranged example.

We have a Method like this:


@Override
public void insertFooBar(Map<String, Object> header, Map<String, Object> initialdaten) {
long start = 0L;
if (LOGGER.isTraceEnabled()) {
start = System.currentTimeMillis();
}

oracleJdbcTemplate.update("INSERT INTO " + TBL_FOOO
+ " (ID, IDNR, ERSTELLUNGMB, PKDB2, STATUS, STATUSAEND, TAGESVORGANGSZ, XMLNACHRICHT) VALUES(" + TBL_FOOO_SEQ
+ ".nextval, ?,?,?,?,?,?,?)", new Object[] { initialdaten.get("idnr"), header.get("ersmb"), header.get("pkdb2"),
header.get("status"), initialdaten.get("statusaend"), header.get("tagesz"), initialdaten.get("xmlnachricht") });
if (LOGGER.isTraceEnabled()) {
LOGGER.trace("Dauer Insert = " + (System.currentTimeMillis() - start));
}
// TODO Daten aus Map auslesen
}


This method is invoked from a method "doInserts" which loops executes ~500 Inserts.

The method "doInserts" is called by a method like this:


@Service
public class SammelnachrichtenServiceImpl implements SammelnachrichtenService {

private Db2Dao db2Dao;
private SammelnachrichtenXmlService xmlService;

@Autowired
public void setDb2Dao(Db2Dao db2Dao) {
this.db2Dao = db2Dao;
}

@Autowired
public void setXmlService(SammelnachrichtenXmlService xmlService) {
this.xmlService = xmlService;
}

@Override
@Transactional
public void verarbeiteId(Long id) {
String nachricht = db2Dao.findXmlNachrichtByPk(id);
if (nachricht != null) {
xmlService.doInserts(nachricht, id);
db2Dao.setSTatus(id, Db2Status.VERARBEITET.value());
}
}

}

[Marten Deinum]

Which method is called in a loop? If it is the verarbeiteId method it isn't really strange it takes a while, because each insert is a new transaction. Starting and committing a transaction takes quite long.

I would also suggest using batchUpdates instead of single updates, that way you will issue 1 statement instead of ~500 which will also gain performance.



It´s difficult to post a clearly arranged example.

Well I asked for the code, not a sample :). And german shouldn't be a problem at least it doesn't scare me :).

[scoopex]

The call hierarchy:

=> onMessage() in Message Driven Bean calls verarbeiteId() one time
(Transaction starts by @Transactional annotation)

=> verarbeiteId() calls doInserts() one time

=> doInserts() calls doInsert 500 times

[Marten Deinum]

Hmm which means it should run in a single transaction, it could be a misconfiguration or misuse.

To improve performance I suggest using a batch update, which reduces the amount of statements issued to the server. Furthermore I suggest more monitoring, there must be something in your configuration and/or code that isn't as it should be.

[scoopex]

Maybe my datasource is not aware of beeing part of a xa-transaction - maybe spring is not aware that the datasource referenced by jndi is a xa-datasource?


How does spring transaction management find out that a datasource is supporting xa ?

My datasource looks like this:


<datasources>
<xa-datasource>
<jndi-name>RacDS</jndi-name>

<track-connection-by-tx>false</track-connection-by-tx>
<isSameRM-override-value>false</isSameRM-override-value>
<xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
<xa-datasource-property name="URL">jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADD RESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = applk01)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = applk02)(PORT = 1521))(FAILOVER=on)(LOAD_BALANCE=off))(CONNECT_DAT A=(SERVER=DEDICATED)(SERVICE_NAME=FOO.BAR.BOOO.BAA AR.DE)))
</xa-datasource-property>
<xa-datasource-property name="User">FOOO</xa-datasource-property>
<xa-datasource-property name="Password">BAR</xa-datasource-property>
<!-- <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExcep tionSorter</exception-sorter-class-name> -->
<!-- <<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation> -->
<!-- <xa-datasource-property name="defaultAutoCommit">false</xa-datasource-property> -->


<track-statements>false</track-statements>
<prepared-statement-cache-size>100</prepared-statement-cache-size>

<min-pool-size>2</min-pool-size>
<max-pool-size>200</max-pool-size>
<blocking-timeout-millis>30000</blocking-timeout-millis>
<new-connection-sql>select count(0) from dual</new-connection-sql>
<check-valid-connection-sql>select count(0) from dual</check-valid-connection-sql>
<idle-timeout-minutes>2</idle-timeout-minutes>

<no-tx-separate-pools/>
</xa-datasource>

[Marten Deinum]

Sring doesn't need to find out, that should all be handled by the transactionmanager of the app-server.

How is everything being loaded?