0

Issue : I am getting below error

ORA-12519, TNS:no appropriate service handler found

Desc:

I want to configure threads and connection pool in tomcat to use them at optimum level. can i configure highly throughput up-to 500-600 threads, I have few more servlets which will use the database with similar threads.

I am calling a servlet to insert entry in db (code mentioned below) with 150 Threads with tasks to complete are 100000

    static int joblimit = 100000;
    static int TPS = 150;

     public static void insertMethod()
{

    RateLimiter rateLimiter = RateLimiter.create(TPS);
    ExecutorService executorService = Executors.newCachedThreadPool();
    start = System.currentTimeMillis();

    for (int i = 0; i < joblimit; i++) {
        rateLimiter.acquire(); // may wait
        final int j = i;
        executorService.execute(new Runnable() {
            @Override
            public void run() {
                log.info(str);
                try {
                    callInsertURL(j);
                } catch (Exception e) {
                    log.error(e);
                }
                if (index == joblimit) {
                    long timeTaken = System.currentTimeMillis() - start;
                    String oneThreadProcessedInSecs = ""+(float)(1*(timeTaken/60)/joblimit);
                    log.info(joblimit + " jobs completed in "
                            + timeTaken 
                            + " msec i.e ["+(float)(timeTaken/60)+"sec,"+"] with " + TPS + " tps, oneThreadProcessedInSecs="+oneThreadProcessedInSecs);
                }
            }
        });
    }
    try {
        executorService.shutdown();
        executorService.awaitTermination(100, TimeUnit.SECONDS);
        log.info("--------------All Tasks Processed Processed----------------------------");
    } catch (Exception e) {
            e.printStackTrace();
    }
}

My Tomcat and Java configurations : Context.xml entry

<Resource name="jdbc/myoracle" global="jdbc/myoracle" auth="Container" type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@myip:1521:mydb" username="myusername" password="mypassword" maxActive="250" maxIdle="30" maxWait="10000" />

server.xml entry

<Connector port="8443" protocol="HTTP/1.1" SSLEnabled="true" maxThreads="150" scheme="https" secure="true" clientAuth="false" sslProtocol="TLS" />

Springconfiguration: servlet-context.xml

<beans:bean id="dbDataSource" class="org.springframework.jndi.JndiObjectFactoryBean"> <beans:property name="jndiName" value="java:comp/env/jdbc/myoracle"/> </beans:bean>

ServeltController will be calling below method to insert into DB:

@Autowired
@Qualifier("commonDAO")
protected CommonDAO commonDAO;

String qry = "INSERT INTO TB_CUSTOMER(customer_msisdn,op_id,cuser,country_id) VALUES(?,?,?,?)";
String[] colNamesTobeReturned = {"CUSTOMER_ID"};
commonDAO.insertOrUpdateWithReturnPK(qry, colNamesTobeReturned, msisdn,opid,cuser,countryid);

Method which will access DB:

    public Long insertOrUpdateWithReturnPK(final String qry,final String[] colNamesTobeReturned,final Object... params)
        {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            try {
                getJdbcTemplate().update(
                        new PreparedStatementCreator() {
                            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                                PreparedStatement ps = connection.prepareStatement(qry, colNamesTobeReturned);
                                utilMapParamsToPStmt(ps,params);
                                return ps;
                            }
                        },
                        keyHolder);
                if(keyHolder != null)
                    return keyHolder.getKey().longValue();
            } catch (Exception e) {
                handleDBExceptions(e);
            }
                return 0L;
        }
M. Deinum
  • 115,695
  • 22
  • 220
  • 224
Azhar
  • 933
  • 1
  • 12
  • 28
  • Your DB method can be improved, as you don't want to construct a `JdbcTEmplate` each time you need one. It is a very heavy object to construct, you want to create it once and reuse it. And you shouldn't pass along the `DataSource`. For calculating pool size [this](https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing) is an interesting read, basically don't make it that high. – M. Deinum Jan 12 '15 at 07:04
  • @M.Deinum, thank you for the reply, I would want to create only object once like datasource, I have controller class like below, from there i pass datasource, you want me to create JdbcTemplate also like this ? Can i use the same object for other controllers also? `@Controller public class CntCustomer extends BaseController{} public class BaseController { @Autowired @Qualifier(CommonDAOConstants.JNDI_NAME) protected DataSource dataSource; public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } }` – Azhar Jan 12 '15 at 07:19
  • Don't add code in comments add it to your question. Yuor controllers, daos' etc. should be singletons (they shouldn't keep state), you also want to construct the `JdbcTemplate` just once, after construction it is thread safe and as mentioned it is a heavy object to construct as it does a lookup to the database to determine type etc. It doesn't make sense to inject a `DataSource` in your controller, that is a web thing and shouldn't know anything about it. – M. Deinum Jan 12 '15 at 07:22
  • @M.Deinum, sure , I will modify the question accordingly. – Azhar Jan 12 '15 at 07:30
  • @M.Deinum I have change the code still with 60 threads also i am getting the same error. – Azhar Jan 12 '15 at 11:19
  • Check http://stackoverflow.com/questions/4073434/spring-connection-pooling-issue. So as stated earlier make `JdbcTemplate` a singleton and I wouldn't use that high level of db connections (if you have read the link I gave earlier you should be able to make some calculations). – M. Deinum Jan 12 '15 at 11:35
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/68648/discussion-between-azhar-and-m-deinum). – Azhar Jan 12 '15 at 12:21

0 Answers0