1

We have a web app deployed on a Tomcat 8 server which is build with Spring Framework. Under development environment everything goes fine, the back-end response times are ok and the time it takes to gain a JDBC connections is good, the issue comes on production environment. Once we have deployed the .war file of the application in the production server, for some reason, the requests response times for each HTTP request takes too long, profiling the application we've found out that there's always a bottleneck trying to get the JDBC connection for each request querying the data base, but we have failed to identify the reason of this behavior so far. The data base server is located on the same server where the app is being deployed so we have discarded network issues so far.

This only happens on production but not on development. Do you guys have any idea of why this could be happening?? Any suggestion is welcome, thanks.

UPDATE
The database engine is SQL Server 2012, the isolation level set is the default one for SQL Server: Read committed

Here's the ApplicationConfig class DB connection settings:

@Autowired
private Environment environment;

@Bean
public DataSource dataSource() {
    SQLServerDataSource dataSource = new SQLServerDataSource();
    dataSource.setURL(environment.getProperty("cendb.url"));
    dataSource.setUser(environment.getProperty("cendb.user"));
    dataSource.setPassword(environment.getProperty("cendb.password"));
    return dataSource;

}

@Bean
public EntityManagerFactory entityManagerFactory() {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(false);
    vendorAdapter.setShowSql(true);
    vendorAdapter.setDatabasePlatform("org.hibernate.dialect.SQLServerDialect");
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("com.model");
    factory.setDataSource(dataSource());
    factory.afterPropertiesSet();
    return factory.getObject();
}

@Bean
public PlatformTransactionManager transactionManager() {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory());
    return txManager;
}

@Bean
public AuditorAware<String> createAuditorProvider() {
    return new CustomAuditorAware();
}

@Bean
public AuditingEntityListener createAuditingListener() {
    return new AuditingEntityListener();
}

This is the db.properties file being used to get the connection properties (cendb.user and cendb.password are skipped due to privacy policies):

  • junit.version=4.11
  • cendb.url=jdbc\:sqlserver\://localhost\:1433;databaseName\=CENTAURO_TEST
  • org.springframework.security.version=3.2.5.RELEASE
  • release.plugin.version=2.4.2
  • joda-time.version=2.3
  • slf4j-log4j12.version=1.7.7
  • maven-war-plugin.version=2.5
  • jackson.annotations.version=2.3.0
  • h2.version=1.4.182
  • commons.codec.version=1.9
  • javax.validation-api.version=1.1.0.Final
  • mvn.source.plugin=2.2.1
  • commons.login.version=1.2.17
  • opencsv.version=2.3
  • jsr250-api.version=1.0
  • mvn.javadoc.plugin=2.9
  • commons-logging.version=1.2
  • jackson.core.version=2.3.3
  • slf4j.version=1.7.7
  • java.version=1.8.0_131
  • c3p0.version=0.9.1.2
  • javax.servlet-api.version=3.1.0
  • commons-repository.version=0.0.1-PARENT
  • checkstyle.version=2.14
  • log4j.version=1.2.17
  • org.springframework.version=4.1.2.RELEASE
  • commons.fileupload.version=1.3.1
  • ojdbc6.version=11.2.0.4
Daniel G.
  • 126
  • 1
  • 12
  • the connection properties for jdbc are the same in production and development ? did u check the production database and eliminated that as the cause of the problem ? – user641887 Sep 29 '17 at 23:51
  • Yes, connection properties are the same beside from the own properties of the database for production such as the username and password. I've checked some basic database related settings such as connections pool, queries response times, etc. We even settle a demo server from scratch to discard server configurations and we got the same behavior seen in production, is taking too long trying to get the JBDC connection to database. – Daniel G. Oct 02 '17 at 14:39
  • can u check the isolation level which is set for your database ? if its set to SERIALIZABLE it can be a cause of concern . plus also can u post some code where u create a database connection and connection properties ? – user641887 Oct 02 '17 at 19:52
  • which database are u connecting to – user641887 Oct 02 '17 at 19:53
  • I'm using SQL Server 2012. I'll update my question with the connection set up – Daniel G. Oct 02 '17 at 20:21
  • this link may have your answer https://stackoverflow.com/questions/26501791/why-does-microsoft-sql-server-2012-query-take-minutes-over-jdbc-4-0-but-seconds. Also what jdbc jar u are using to connect ? – user641887 Oct 02 '17 at 22:17
  • I'm using sqljdbc4-4.0.jar, I'll check the suggested link to get further information about the JDBC driver. – Daniel G. Oct 06 '17 at 14:43

0 Answers0