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...@gmail.com> 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!!
