AWS and on-premise time lag

Verification environment Red Hat Enterprise Linux release 8.1 (Ootpa) Tomcat-9.0.30 PostgreSQL 10.7 Aurora PostgreSQL 10.6

Execution result of the following program. Average value of 10 runs. The number of records is several for overhead verification. Local in Linux: 541 microseconds Local in-machine (Windows on vm host and Linux on vm guest): 988 microseconds Home machine (Yokohama), AWS Tokyo region (Aurora PostgreSQL) remote: 7304 microseconds

In the case of Oracle, the fetch size of the JDBC driver is small (default 10), so tuning is required. It should be increased according to the response requirements of the application. In a certain environment, it was 38ms (ping response time) on-premise and AWS, so the case where the protocol overhead is correspondingly large should be taken into consideration.

package test;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.annotation.Resource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 * Servlet implementation class TestServlet
 */
@WebServlet("/TestServlet")
public class TestServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
 
	@Resource(name = "jdbc/postgres")
	private DataSource ds;
	
    /**
     * @see HttpServlet#HttpServlet()
     */
    public TestServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		PrintWriter out = response.getWriter();
        Connection con = null;
        long start = 0;
        long end = 0;       

        try {
            con = ds.getConnection();
            //start = System.currentTimeMillis();
            start = System.nanoTime();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select id from test2");
            int recordCount = 0;
            while (rs.next()) {
                out.println(rs.getInt("id"));
                recordCount++;
            }
            out.println("<br>");
            out.print("recordCount:");
            out.println(recordCount);
            out.println("<br>");
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                con.close();
                end = System.nanoTime();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        out.print("time:");
        out.print((end - start)/1000);
        out.println(" micro sec");
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

Additional memo to check the actual machine directly from Tomcat included in Eclipse Place context.xml under WebContent / META-INF.

<Context>

    <!-- Default set of monitored resources. If one of these changes, the    -->
    <!-- web application will be reloaded.                                   -->
    <WatchedResource>WEB-INF/web.xml</WatchedResource>
    <WatchedResource>WEB-INF/tomcat-web.xml</WatchedResource>
    <WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>

    <!-- Uncomment this to disable session persistence across Tomcat restarts -->
    <!--
    <Manager pathname="" />
    -->

    <Manager className="org.apache.catalina.session.PersistentManager"
        maxIdleBackup="1"
        minIdleSwap="0"
        maxIdleSwap="0"
        processExpiresFrequency="1"
        saveOnRestart='true'
        >
        <Store className="org.apache.catalina.session.JDBCStore"
                connectionURL="jdbc:postgresql://localhost:5432/tomcat?user=postgres&amp;password=postgres"
                driverName="org.postgresql.Driver"
                sessionAppCol="app_name"
                sessionDataCol="session_data"
                sessionIdCol="session_id"
                sessionLastAccessedCol="last_access"
                sessionMaxInactiveCol="max_inactive"
                sessionTable="tomcat_sessions"
                sessionValidCol="valid_session"
                />
    </Manager>

    <Resource name="jdbc/postgres" auth="Container"
          type="javax.sql.DataSource" driverClassName="org.postgresql.Driver"
          url="jdbc:postgresql://192.168.80.131:5432/test"
          username="postgres" password="postgres" maxTotal="20" maxIdle="10" maxWaitMillis="-1"/>
</Context>

For Aurora, the following endpoints (deleted after operation verification) url="jdbc:postgresql://database-1-instance-1.cofxoo0c8wlr.ap-northeast-1.rds.amazonaws.com:5432/postgres"