Re: waiting for client write

[Ayub Khan <ayub.hp@xxxxxxxxx>]

Ranier,

I tried to VACCUM ANALYZE the tables involved multiple times and also tried the statistics approach as well

Pavan, 

I upgraded to 42.2.21 version of jdbc driver and using HikariCp connection pool management 3.1.0

jProfiler shows the threads are stuck with high cpu usage on.

org.postgresql.jdbc.PgPreparedStatement.execute , 

   below is the java code which calls postgresql  

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;
}

On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan <ayub.hp@xxxxxxxxx> wrote:

attached is the screenshot of RDS performance insights for AWS and it shows high waiting client writes. The api performance is slow. I read that this might be due to IOPS on RDS. However we have 80k IOPS on this test RDS. 

Below is the query which is being load tested

SELECT

                       a.menu_item_id,
                       a.menu_item_name,
                       a.menu_item_category_id,
                       b.menu_item_category_desc,
                       c.menu_item_variant_id,
                       c.menu_item_variant_type_id,
                       c.price,
                       c.size_id,
                       c.parent_menu_item_variant_id,
                       d.menu_item_variant_type_desc,
                       e.size_desc,
                       f.currency_code,
                       a.image,
                       a.mark_id,
                       m.mark_name

                     FROM .menu_item_category AS b, .menu_item_variant AS c,
                       .menu_item_variant_type AS d, .item_size AS e, .restaurant AS f,
                       .menu_item AS a

                       LEFT OUTER JOIN .mark AS m
                         ON (a.mark_id = m.mark_id)

                     WHERE a.menu_item_category_id = b.menu_item_category_id AND a.menu_item_id = c.menu_item_id AND
                           c.menu_item_variant_type_id = d.menu_item_variant_type_id AND d.is_hidden = 'false' AND
                           c.size_id = e.size_id AND a.restaurant_id = f.restaurant_id AND f.restaurant_id = 1528 AND
                           (a.menu_item_category_id = NULL OR NULL IS NULL)

                           AND c.menu_item_variant_id = (SELECT min(menu_item_variant_id)
                                                         FROM .menu_item_variant
                                                         WHERE menu_item_id = a.menu_item_id AND deleted = 'N'
                                                         LIMIT 1) AND a.active = 'Y'
                           AND (CONCAT_WS('', ',', a.hidden_branch_ids, ',') NOT LIKE CONCAT_WS('', '%,4191,%') OR
                                NULL IS NULL)
   AND .is_menu_item_available(a.menu_item_id, 'Y') = 'Y'

   ORDER BY a.row_order, menu_item_id;

--Ayub

--

--------------------------------------------------------------------

Sun Certified Enterprise Architect 1.5
Sun Certified Java Programmer 1.4
Microsoft Certified Systems Engineer 2000
http://in.linkedin.com/pub/ayub-khan/a/811/b81

mobile:+966-502674604

----------------------------------------------------------------------
It is proved that Hard Work and kowledge will get you close but attitude will get you there. However, it's the Love
of God that will put you over the top!!