Announcement Announcement Module
Collapse
No announcement yet.
Performance Issues - JdbcTemplate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Performance Issues - JdbcTemplate

    Hi,

    I am having a performance issue when load testing a simple application that reads from a database. My initial thoughts that this is due to the JdbcTemplate being a singleton and as a result being the bottleneck in my application.

    The load testing was places a load of 100 concurrent users on the system.

    The problem i am experiencing is as follows:

    1st request to DB takes 232ms
    2nd request to DB takes 410ms
    3rd request to DB takes 684ms
    ...
    ...
    199th request to DB takes 66221ms
    200th request to DB takes 252ms
    201st request to DB takes 554ms

    This pattern continues until the testing completes i.e. DB performance gets progressively worse for 199 requests and on the 200th requests something seems to refresh / recycle and DB performance improves significantly.

    My config is as follows:


    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
    <property name="URL" value="jdbc:oracle:thin:xxx " />
    </bean>

    <!-- Define eWEb DAO Layer -->
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.simple.Simple JdbcTemplate">
    <constructor-arg ref="dataSource" />
    </bean>
    <bean id="CustomerDAO" class="com.xxx.dao.CustomerDAOImpl">
    <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

    My initial thoughts are that the problem is the configuration of the JdbcTemplate. JdbcTemplate is configured as a singleton by default. As a result each request to access the DB needs to queue up to get access to the JdbcTemplate, making it a bottleneck.

    Will the calls to JDBCTemplate be multi-threaded by default? If so, is my bottleneck more likely to be the connection to the DB?

    I am aware that I don’t have connection pooling configured (connectionCachingEnabled). Based on my configuration at the moment what is the default behavior? Is it a pool with a single connection or will each request establish it's own connection?

    Based on my performance logs each call to DB gets approx 200ms slower on each request. This suggests that requests are waiting for resources somewhere during the processing.

    I would appreciate any help or explanation of what I am doing wrong or how best to configure my JdbcTemplate / DB Source.

    I am also confused as to why the issues seems to go in cycles of 199. This is more than coincidence because it happens consistently throughout my test cycle.

    Any help is greatly appreciated.

    Thanks
    Damo
    Last edited by damo; Apr 26th, 2011, 04:59 PM.

  • #2
    Hello

    use code tags

    I am having a performance issue when load testing a simple application that reads from a database.
    OK

    Could you post your DAO method and your Test method?
    I want to know what are you doing exactly in each method, perhaps you are doing something extra

    if you are only reading your could use @Transactional + readonly attribute

    Are you working with transactions? I dont see come code included about that

    Comment


    • #3
      My initial thoughts are that the problem is the configuration of the JdbcTemplate. JdbcTemplate is configured as a singleton by default. As a result each request to access the DB needs to queue up to get access to the JdbcTemplate, making it a bottleneck.
      No it isn't. JdbcTemplate is thread safe by design, its methods aren't synchronized so multiple threads can access the same object at the same time.

      I am aware that I don’t have connection pooling configured (connectionCachingEnabled). Based on my configuration at the moment what is the default behavior? Is it a pool with a single connection or will each request establish it's own connection?
      Each call to jdbc template will create, use and close it's own connection, there is no pooling, no single connection. Start by adding a connectionpool, that will increase performance (Use something like BoneCP)

      As dr_pompeii is suggesting, post your dao (and use a connection pool). imho I think your database setup, or the way you use jdbctemplate is the bottleneck.

      Comment


      • #4
        Thanks for getting back to me.


        My DAO code is as follows

        Code:
        package com.xxx.dao;
        
        import java.sql.ResultSet;
        import java.sql.SQLException;
        
        import org.springframework.dao.EmptyResultDataAccessException;
        import org.springframework.jdbc.core.RowMapper;
        import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
        
        import com.xxx.domain.Customer;
        import com.xxx.domain.CustomerPK;
        import com.xxx.domain.Title;
        
        public class CustomerDAOImpl implements CustomerDAO
        {
        	private static String	SQL_CUSTOMER_DETAILS_QUERY="SELECT a.user_id, b.title, b.first_name, b.last_name, b.date_of_birth, c.address, c.city, c.state, a.user_alias, c.home_phone, b.data_protection_indicator, b.isactive FROM customer1 a, customer2 b, customer3 c WHERE b.user_id = c.user_id AND b.user_id = a.user_id AND a.user_id=?";
        		
        	private	SimpleJdbcTemplate	jdbcTemplate;
        	
        	/**
        	 * @param jdbcTemplate the jdbcTemplate to set
        	 */
        	public void setJdbcTemplate(SimpleJdbcTemplate jdbcTemplate)
        	{
        		this.jdbcTemplate = jdbcTemplate;
        	}
        
        	/* (non-Javadoc)
        	 * @see com.xxx.dao.CustomerDAO#getCustomerDetails(com.xxx.domain.CustomerPK)
        	 */
        	@Override
        	public Customer getCustomerDetails(CustomerPK customerPK)
        	{
        		Customer	customerDetails;
        		Object[]	sqlQueryArgs = new Object[] { customerPK.getUserID() };
        		
        		try
        		{
        			customerDetails = this.jdbcTemplate.queryForObject(SQL_CUSTOMER_DETAILS_QUERY, new CustomerMapper(), sqlQueryArgs);
        		}
        		catch (EmptyResultDataAccessException exception)
        		{
        			customerDetails=null;
        		}
        		
        		return customerDetails;
        	}
        }
        I have a number of DAOs all coded the exact same (full config file below)

        Code:
        <?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:mvc="http://www.springframework.org/schema/mvc"
        	xmlns:context="http://www.springframework.org/schema/context"
        	xmlns:aop="http://www.springframework.org/schema/aop"
        	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        		http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd	
        		http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd
        		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd">
        
        	 <!-- Scans within the base package of the application for @Components to 
        		configure as beans -->
        	 <context:component-scan base-package="com.xxx" />
        
        	<!-- Define  Quick Wins Service Controller -->
        	 <bean id="serviceController"
        		class="com.xxx.controllers.ServiceController">
        		
        		<property name="customerDAO">
        			<ref bean="CustomerDAO" />
        		</property>
        		<property name="serviceDAO">
        			<ref bean="ServiceDAO" />
        		</property>
        		<property name="accountDAO">
        			<ref bean="AccountDAO" />
        		</property>
        	</bean>
        
        		
        	<!-- Define Database Data Source -->
        	<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource">
        		<property name="URL" value="jdbc:oracle:thin:xxx" />
        	</bean>
        	
        	<!-- Define DAO Layer -->
        	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
        		<constructor-arg ref="dataSource" />
        	</bean>
        	<bean id="CustomerDAO"
        		class="com.xxx.dao.CustomerDAOImpl">
        		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
        	</bean>
        	<bean id="ServiceDAO"
        		class="com.xxx.dao.ServiceDAOImpl">
        		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
        	</bean>
        	<bean id="AccountDAO"
        		class="com.xxx.dao.AccountDAOImpl">
        		<property name="jdbcTemplate" ref="jdbcTemplate"></property>
        	</bean>

        My calling code is via a REST service.

        Code:
        package com.xxx.controllers;
        
        import java.io.IOException;
        import java.util.List;
        
        import javax.servlet.http.HttpServletRequest;
        import javax.servlet.http.HttpServletResponse;
        
        import org.apache.http.auth.InvalidCredentialsException;
        import org.springframework.stereotype.Controller;
        import org.springframework.util.MultiValueMap;
        import org.springframework.web.bind.annotation.CookieValue;
        import org.springframework.web.bind.annotation.PathVariable;
        import org.springframework.web.bind.annotation.RequestMapping;
        import org.springframework.web.bind.annotation.RequestMethod;
        import org.springframework.web.bind.annotation.RequestParam;
        
        import com.gargoylesoftware.htmlunit.FailingHttpStatusCodeException;
        
        import com.xxx.dao.CustomerDAO;
        
        @Controller
        public class ServiceController extends ServiceControllerBase {
        
        	private CustomerDAO customerDAO;
        	private ServiceDAO serviceDAO;
        	private AccountDAO accountDAO;
        	
        	private Envelope buildIsValidSessionResponse(Envelope responseEnvelope,
        			int isValidSession) {
        		responseEnvelope.setData(isValidSession == -1 ? null : new CustomerPK(isValidSession));
        		responseEnvelope.setStatus(RestServiceResultStatusType.SUCCESS.getStatusCode());
        		responseEnvelope.setMessage(isValidSession != -1 ? "Session is valid."
        				: "Session is invalid.");
        
        		return responseEnvelope;
        	}
        
        	@RequestMapping(method = RequestMethod.GET, value = "/customer")
        	public Envelope getCustomerByUserId(
        			@CookieValue(value="BV_SESSION_ID",required=false) String bvSessionId, 
        			@CookieValue(value="BV_ENGINE_ID",required=false) String bvEngineId, 
        			@CookieValue(value="USER_ID",defaultValue="-1") int userId, 
        			HttpServletRequest request,
        			HttpServletResponse response) {
        		
        		Envelope responseEnvelope = new Envelope(bvSessionId, bvEngineId);
        		
        		Customer customer = customerDAO.getCustomerDetails(new CustomerPK(
        				userId));
        
        		responseEnvelope.setStatus(RestServiceResultStatusType.SUCCESS.getStatusCode());
        		responseEnvelope.setData(customer);
        
        		return responseEnvelope;
        	}
        
        
        	public void setCustomerDAO(CustomerDAO customerDAO) {
        		this.customerDAO = customerDAO;
        	}
        
        	
        }

        Comment


        • #5
          Post your RowMapper....

          For starters I suggest using JdbcTemplate basically SimpleJdbcTemplate is deprecated (it is still there for backwards compatibility. Next don't use an object[] for your parameters.

          Code:
          @Override
          public Customer getCustomerDetails(CustomerPK customerPK) {	
          	try{
          		return this.jdbcTemplate.queryForObject(SQL_CUSTOMER_DETAILS_QUERY, new CustomerMapper(), customerPK.getUserId());
          	} catch (EmptyResultDataAccessException exception) {
          		return null;
          	}
          }
          Next thing I notice is you don't have any transaction setup, which basically means you have to manage connections yourself... I suggest adding tx management (read the tx chapter of the reference guide for more information).

          Finally as stated before I really suggest you use a connection pool, creating a connection to the database is slow, really slow.. You don't want to recreate a connection to the database for each query executed (which is basically what happens now), depending on your database the getting of the connection hangs until the resources are free or are timed out (which eventually frees them).

          Comment


          • #6
            Hi. Thanks for the suggestions i will give them a go

            Can you think of any reason why I would be seeing the pattern of 199 requests being progressively slower and then the 200th request being really fast and then continually repeating?


            My rowMapper is as follows

            Code:
            private static final class CustomerMapper implements RowMapper<Customer> 
            	{
            	    public Customer mapRow(ResultSet rs, int rowNum) throws SQLException 
            	    {
            	        Customer customer = new Customer();
            
            	        customer.setUserID(rs.getInt(SQL_USER_ID_COLUMN_NAME));
            	        customer.setTitle(new Title(rs.getInt(SQL_TITLE_COLUMN_NAME)));
            	        customer.setFirstName(rs.getString(SQL_FIRST_NAME_COLUMN_NAME));
            	        customer.setLastName(rs.getString(SQL_LAST_NAME_COLUMN_NAME));
            	        customer.setDateOfBirth(rs.getDate(SQL_DATE_OF_BIRTH_COLUMN_NAME));
            	        customer.setAddress(rs.getString(SQL_ADDRESS_COLUMN_NAME));
            	        customer.setCity(rs.getString(SQL_CITY_COLUMN_NAME));
            	        customer.setState(rs.getString(SQL_STATE_COLUMN_NAME));
            	        customer.setUserAlias(rs.getString(SQL_USER_ALIAS_COLUMN_NAME));
            	        customer.setHomePhone(rs.getString(SQL_HOME_PHONE_COLUMN_NAME));
            	        customer.setDataProtectionIndicator(rs.getInt(SQL_DATA_PROTECTION_INDICATOR_COLUMN_NAME) == SQL_ON_FLAG);
            	        customer.setIsActive(rs.getInt(SQL_ISACTIVE_COLUMN_NAME) == SQL_ON_FLAG);
            	        
            	        return customer;
            	    }        
            	}

            Comment


            • #7
              My guess that is what the datasource/connection is doing... Maybe something in your database settings...

              Again I suggest to switch datasource and use a proper connectionpool and implement/configure tx management. I strongly expect to see more consistent results then...

              Comment


              • #8
                The suggestions by Marten are valuable about connections, transactions and pool

                Again I suggest to switch datasource and use a proper connectionpool and implement/configure tx management. I strongly expect to see more consistent results then...
                Totally agree

                To complement some points

                1) Post your test method, perhaps you have some problematic line

                2) About your mapper

                Code:
                customer.setDateOfBirth(rs.getDate(SQL_DATE_OF_BIRTH_COLUMN_NAME));
                Post your Customer class, I want to know what Date type you are using (util, sql), and SQL structure of your table

                3) About this
                Code:
                <bean id="serviceController"
                		class="com.xxx.controllers.ServiceController">
                		
                		<property name="customerDAO">
                			<ref bean="CustomerDAO" />
                		</property>
                		<property name="serviceDAO">
                			<ref bean="ServiceDAO" />
                		</property>
                		<property name="accountDAO">
                			<ref bean="AccountDAO" />
                		</property>
                	</bean>
                Is no wise that the controller access directly the DAO scope, it breaks the MVC pattern, the controller should access only the Service(BO) classes and these then the DAO classes

                Comment

                Working...
                X