Re: client write waits on postgresql RDS

[Christopher Schultz]

Ayub,

On 6/11/21 13:52, Ayub Khan wrote:

jProfiler shows the threads are stuck with high cpu usage on. I am using
HikariCp connection pool management 3.1.0

org.postgresql.jdbc.PgPreparedStatement.execute

I am using 42.2.21 version of jdbc driver

Below is the Java code

Connection con = null;
CallableStatement callableStatement = null;
ResultSet rs = null;
ResultSet rs1 = null;
PreparedStatement ps = null;
try {
   con = connectionManager.getConnetion();
con.setAutoCommit(false);
   callableStatement = con.prepareCall("call
menu_pkg$get_menu_items_p_new(?,?,?,?,?,?)");
   if (catId == 0)
     callableStatement.setNull(2, Types.BIGINT);
   else
     callableStatement.setLong(2, catId);
   callableStatement.setString(3, "Y");

   if (branchId == 0)
     callableStatement.setString(4, null);
   else
     callableStatement.setLong(4, branchId);

   callableStatement.setNull(5, Types.OTHER);
   callableStatement.setNull(6, Types.OTHER);
   callableStatement.registerOutParameter(5, Types.OTHER);
   callableStatement.registerOutParameter(6, Types.OTHER);
   callableStatement.execute();
   rs = (ResultSet) callableStatement.getObject(5);
   rs1 = (ResultSet) callableStatement.getObject(6);
   MenuMobile menuMobile;

   try {
     while (rs.next()) {

       //process rs
     }
     MenuCombo menuCombo;
     while (rs1.next()) {
      //process rs1
     }

     menuMobileListCombo.setMenuComboList(menuComboList);
     menuMobileListCombo.setMenuMobileList(menuMobileList);
   } catch (SQLException e) {
     LOG.error(e.getLocalizedMessage(), e);
   }

   con.commit();
   con.setAutoCommit(true);
} catch (SQLException e) {
   LOG.error(e.getLocalizedMessage(), e);
   throw e;
} finally {
   if (rs != null)
     rs.close();
   if (rs1 != null)
     rs1.close();
    if (ps != null)
     ps.close();

   if (callableStatement != null) callableStatement.close();
   if (con != null) con.close();
}
return menuMobileListCombo;
}

Thread dump(s)?

-chris

On Thu, Jun 10, 2021 at 8:10 AM Ayub Khan <ayub...@gmail.com> wrote:

Seeing client write waits on postgresql as attached in the image. Is there
any bottle neck which is causing the client write waits on postgresql ?

Below is the test setup

Jmeter-->(load balanced tomcat on ec2 instances)---->rds read replicas

All these are running on different ec2 instances in AWS cloud in the same
region

below is the config of the http connector on tomcat:

<Connector port="8080"
protocol="org.apache.coyote.http11.Http11NioProtocol"
                connectionTimeout="120000" maxThreads="50000"
maxConnections="50000"
                URIEncoding="UTF-8"
                redirectPort="8443" />

Below are the specs of the server:

Ec2 instance which is running tomcat 8.5

c5.9x large
36 vpcu
72GB memory
10GBPS network
EBS band width 9500


postgresql RDS db.r6g.16xlarge

512 GB memory
64 VCPU
25 Gibs network
AWS Gravitron cpu

--Ayub

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org