I am writing a small program that will run on the Apache web server (not Tomcat) via CGI in response to a POST request.
The program performs the following actions:
- read xml sent via http in request
- execute a stored procedure in a database with data extracted from xml
- returns the result of the stored procedure as a response to the POST request
The database is Oracle. I am using jdbc OCI to access it.
Class.forName("oracle.jdbc.OracleDriver"); String dbCS = "jdbc:oracle:oci:@//ip:port/service" Connection conn = DriverManager.getConnection(dbCS, dbUserId, dbPwd); CallableStatement cs = conn.prepareCall("{ call ? := my_pkg.my_sp(?,?,?,?)}"); cs.registerOutParameter(pReturnValue, OracleTypes.NUMBER); cs.setInt("p1", p1); cs.setString("p2", p2); cs.setString("p3", p3); cs.registerOutParameter("p_out", Types.VARCHAR); try { cs.executeQuery(); return cs.getString(pReqResponse); } finally { try { cs.close(); } catch (SQLException ex) {
Performing one request, it worked fine (the entire program completed in 2 seconds). However, if I tried to send several POST requests at once, I got all of them stuck for a certain amount of time, depending on the number of requests (this is approximately 10 seconds for 10 requests, 15 seconds for 15 requests).
I tried to evaluate how much of the code gave a delay. These seem to be two lines:
Connection conn = DriverManager.getConnection(dbConnectionString, dbUserId, dbPwd); CallableStatement cs = conn.prepareCall("{ call ? := my_pkg.my_sp(?,?,?,?)}");
The execution is completed almost immediately.
Why is this so?
PS: I experimented on Windows7. Of course, it did not start from a web server, but simply as a simple console process. It should also read xml from a file on the hard drive. All simultaneously running instances of the program completed in a second together.
What prevents him from working quickly on Linux through Apache?
Based on comments
I tried to set the pool properties for my connection, but all in vain. I tried the following:
When specifying UserId and Password in URL
jdbc:oracle:oci:login/ password@
I tried to set connection properties:
Properties p = new Properties(); p.setProperty("Pooling", "true"); p.setProperty("Min Pool Size", "1"); p.setProperty("Max Pool Size", "10"); p.setProperty("Incr Pool Size", "4"); Connection conn = DriverManager.getConnection(dbConnectionString, p);
I tried using OCI Connection Join :
OracleOCIConnectionPool cpool = new OracleOCIConnectionPool(); cpool.setUser("user"); cpool.setPassword("pwd"); cpool.setURL(dbConnectionString); Properties p = new Properties(); p.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "1"); p.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "5"); p.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "2"); p.put(OracleOCIConnectionPool.CONNPOOL_TIMEOUT, "10"); p.put(OracleOCIConnectionPool.CONNPOOL_NOWAIT, "true"); cpool.setPoolConfig(p); Connection conn = (OracleOCIConnection) cpool.getConnection();
I tried using the apache DBCP component:
basicDataSource = new BasicDataSource(); basicDataSource.setUsername("user"); basicDataSource.setPassword("pwd"); basicDataSource.setDriverClassName("oracle.jdbc.OracleDriver"); basicDataSource.setUrl(dbConnectionString); Connection conn = basicDataSource.getConnection();
The behavior remained unchanged, i.e. large delay on getConnection in all concurrent requests.
All these attempts seem to be trying to solve some other problem for me, since in my case all connections are established from separate processes, and to manage connections from the same pool between different processes (for example, I am mistaken) is unclear.
What options do I have? Or maybe I did something wrong? In addition, I must say that I am completely new to java in general, so I may miss the basic things.
Could this be a problem with the OS or web server? Perhaps something needs to be configured there, and not in the code ...?
I also tried using thin client instead of oci . However, this worked even stranger: the first request completed in a second, and the second was delayed by a minute .
Bad concurrency with Oracle JDBC drivers contains a problem similar to mine.
As a result, we found out that the processes launched by Apache through CGI took up all 100% of the CPU (and the lion's share of the memory), so they simply lacked resources. Unfortunately, I do not know why a very simple and simple program (reading xml and establishing one connection to the database to execute the stored procedure) starts only 20 times at a time, eats all the resources.
However, the solution was very obvious. I reworked it in a Java web application using servlets, we deployed it to Apache Tomcat and MAGIC .... it started working as expected, with no visible effect on resources.