Need inputs for Database Tuning using Spring and Dbcp Connection pool

Question

I am using Spring in my project and instantiating dataSource as below.

@Bean(destroyMethod="close")
    public DataSource restDataSource() {

        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(env.getProperty("hibernate.connection.driver_class"));
        dataSource.setUrl(env.getProperty("hibernate.connection.url"));
        dataSource.setUsername(env.getProperty("hibernate.connection.username"));
        dataSource.setPassword(env.getProperty("hibernate.connection.password"));
        dataSource.setInitialSize(env.getRequiredProperty("hibernate.dbcp.initialSize", Integer.class));
        dataSource.setMaxActive(env.getRequiredProperty("hibernate.dbcp.maxActive", Integer.class));
        dataSource.setMaxIdle(env.getRequiredProperty("hibernate.dbcp.maxIdle", Integer.class));
        dataSource.setMinIdle(env.getRequiredProperty("hibernate.dbcp.minIdle", Integer.class));
        return dataSource;
    }

Below is my properties file.

hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
hibernate.connection.driver_class=oracle.jdbc.driver.OracleDriver
hibernate.connection.username=<>
hibernate.connection.password=<>
hibernate.connection.url=jdbc:oracle:thin:@<Host>:1521:<SID>
hibernate.show_sql=true

hibernate.cache.use_query_cache=true
cache.provider_class=org.hibernate.cache.EhCacheProvider
hibernate.cache.use_second_level_cache=true
hibernate.cache.region.factory_class=org.hibernate.cache.ehcache.EhCacheRegionFactory
net.sf.ehcache.configurationResourceName=ehcache.xml
**hibernate.dbcp.initialSize=10
hibernate.dbcp.maxActive=100
hibernate.dbcp.maxIdle=30
hibernate.dbcp.minIdle=10**

Please suggest :-

Any changes in the properties marked in Bold(initialSize,maxActive,maxidle,minIdle). My application will be concurrently used by around 100 users and total users are around 3000.
I am using Tomcat Server to deploy my application.Should I be using JNDI for connections instead of directly specify connection properties? Is above way of using connections good for a production System?

Instead of commons I would suggest HikariCP. Also there is this which has a nice explanation of poolsize and why large poolsizes don't work/scale and can even slow down your application. — M. Deinum
– M. Deinum, Commented Oct 7, 2014 at 8:38

M. Deinum · Accepted Answer · 2014-10-07 08:45:09Z

3

Instead of Commons DBCP I would suggest using HikariCP (I'm having very good experiences with that lately or if you are already on tomcat use Tomcat JDBC instead.

There is a lot written on poolsizing (see here for a nice explanation and here for a short video from Oracle). In short large poolsizes don't work and probably will make performance worse.

A rule of thumb/formula (also in the article mentioned) is to use

connections = ((core_count * 2) + effective_spindle_count)

Where core_count is the number of (actual) cores in your server and effective_spindle_count the number of disks you have. If you have server with a large disk and 4 cores it would lead to a connection pool of size 9. This should be able to handle what you need, adding more will only add overhead of monitoring, thread switching etc.

answered Oct 7, 2014 at 8:45

M. Deinum

127k22 gold badges235 silver badges253 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Santhosh Over a year ago

Can you please help me out with this stackoverflow.com/questions/26231165/…

Collectives™ on Stack Overflow

Need inputs for Database Tuning using Spring and Dbcp Connection pool

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related