Re: waiting for client write
Vijay,
Both tomcat and postgresql are on the same region as that of the database
server. It is an RDS so I do not have shell access to it.
Jeff,
The tomcat profile is suggesting that it's waiting for a response from the
database server.
Tomcat and RDS are in the same availability region as eu-central-1a
On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan 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!!
Re: waiting for client write
Hi Ayub
So, i understand the client are blocked waiting on a write to the database!
What does the blocked thread signature say?
Are you pre-creating any partitions?
Are you experiencing Timed outs??
What is the driver you are using now? If you are using Jdbc, can you update
your driver to the latest version?
Regards
Pavan
On Fri, Jun 11, 2021, 11:28 AM Ayub Khan wrote:
> Vijay,
>
> Both tomcat and postgresql are on the same region as that of the database
> server. It is an RDS so I do not have shell access to it.
>
> Jeff,
>
> The tomcat profile is suggesting that it's waiting for a response from the
> database server.
>
> Tomcat and RDS are in the same availability region as eu-central-1a
>
> On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan 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!!
>
Re: waiting for client write
Em sex., 11 de jun. de 2021 às 13:28, Ayub Khan
escreveu:
> Vijay,
>
> Both tomcat and postgresql are on the same region as that of the database
> server. It is an RDS so I do not have shell access to it.
>
> Jeff,
>
> The tomcat profile is suggesting that it's waiting for a response from the
> database server.
>
> Tomcat and RDS are in the same availability region as eu-central-1a
>
> On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan 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
>>
> Can you post the results with: explain analyze?
EXPLAIN ANALYZE
SELECT
regards,
Ranier Vilela
Re: waiting for client write
Pavan,
In jProfiler , I see that most cpu is consumed when the Tomcat thread is
stuck at PgPreparedStatement.execute. I am using version 42.2.16 of JDBC
driver.
Ranier,
EXPLAIN ANALYZE
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'
Nested Loop Left Join (cost=5.15..162.10 rows=1 width=148) (actual
time=0.168..5.070 rows=89 loops=1)
Join Filter: (a.mark_id = m.mark_id)
Rows Removed by Join Filter: 267
-> Nested Loop (cost=5.15..161.04 rows=1 width=144) (actual
time=0.161..4.901 rows=89 loops=1)
-> Nested Loop (cost=4.86..158.72 rows=1 width=148) (actual
time=0.156..4.729 rows=89 loops=1)
-> Nested Loop (cost=4.57..158.41 rows=1 width=140)
(actual time=0.151..4.572 rows=89 loops=1)
-> Nested Loop (cost=4.28..158.10 rows=1
width=132) (actual time=0.145..4.378 rows=89 loops=1)
-> Nested Loop (cost=0.71..152.51 rows=1
width=95) (actual time=0.121..3.334 rows=89 loops=1)
-> Index Scan using
menu_item_restaurant_id on menu_item a (cost=0.42..150.20 rows=1
width=83) (actual time=0.115..3.129 rows=89 loops=1)
Index Cond: (restaurant_id = 1528)
" Filter: ((active = 'Y'::bpchar)
AND (is_menu_item_available(menu_item_id, 'Y'::bpchar) =
'Y'::bpchar))"
Rows Removed by Filter: 194
-> Index Scan using
menu_item_category_pk on menu_item_category b (cost=0.29..2.31 rows=1
width=20) (actual time=0.002..0.002 rows=1 loops=89)
Index Cond:
(menu_item_category_id = a.menu_item_category_id)
-> Index Scan using menu_item_variant_pk on
menu_item_variant c (cost=3.57..5.59 rows=1 width=45) (actual
time=0.002..0.002 rows=1 loops=89)
Index Cond: (menu_item_variant_id = (SubPlan 1))
Filter: (a.menu_item_id = menu_item_id)
SubPlan 1
-> Limit (cost=3.13..3.14 rows=1
width=8) (actual time=0.008..0.008 rows=1 loops=89)
-> Aggregate
(cost=3.13..3.14 rows=1 width=8) (actual time=0.008..0.008 rows=1
loops=89)
-> Index Scan using
"idx$$_023a0001" on menu_item_variant (cost=0.43..3.11 rows=8
width=8) (actual time=0.003..0.007 rows=7 loops=89)
Index Cond:
(menu_item_id = a.menu_item_id)
Filter: (deleted =
'N'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using menu_item_variant_type_pk on
menu_item_variant_type d (cost=0.29..0.31 rows=1 width=16) (actual
time=0.002..0.002 rows=1 loops=89)
Index Cond: (menu_item_variant_type_id =
c.menu_item_variant_type_id)
Filter: ((is_hidden)::text = 'false'::text)
-> Index Scan using size_pk on item_size e
(cost=0.29..0.31 rows=1 width=16) (actual time=0.001..0.001 rows=1
loops=89)
Index Cond: (size_id = c.size_id)
-> Index Scan using "restaurant_idx$$_274b003d" on restaurant
f (cost=0.29..2.30 rows=1 width=12) (actual time=0.001..0.001 rows=1
loops=89)
Index Cond: (restaurant_id = 1528)
-> Seq Scan on mark m (cost=0.00..1.03 rows=3 width=12) (actual
time=0.000..0.001 rows=3 loops=89)
Planning Time: 2.078 ms
Execution Time: 5.141 ms
On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan 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.
Re: waiting for client write
Em sex., 11 de jun. de 2021 às 13:59, Ayub Khan
escreveu:
> Pavan,
>
> In jProfiler , I see that most cpu is consumed when the Tomcat thread is
> stuck at PgPreparedStatement.execute. I am using version 42.2.16 of JDBC
> driver.
>
>
> Ranier,
>
> EXPLAIN ANALYZE
>
> 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'
>
> Nested Loop Left Join (cost=5.15..162.10 rows=1 width=148) (actual
> time=0.168..5.070 rows=89 loops=1)
> Join Filter: (a.mark_id = m.mark_id)
> Rows Removed by Join Filter: 267
> -> Nested Loop (cost=5.15..161.04 rows=1 width=144) (actual
> time=0.161..4.901 rows=89 loops=1)
> -> Nested Loop (cost=4.86..158.72 rows=1 width=148) (actual
> time=0.156..4.729 rows=89 loops=1)
> -> Nested Loop (cost=4.57..158.41 rows=1 width=140) (actual
> time=0.151..4.572 rows=89 loops=1)
> -> Nested Loop (cost=4.28..158.10 rows=1 width=132)
> (actual time=0.145..4.378 rows=89 loops=1)
> -> Nested Loop (cost=0.71..152.51 rows=1
> width=95) (actual time=0.121..3.334 rows=89 loops=1)
> -> Index Scan using menu_item_restaurant_id
> on menu_item a (cost=0.42..150.20 rows=1 width=83) (actual time=0.115..3.129
> rows=89 loops=1)
> Index Cond: (restaurant_id = 1528)
> " Filter: ((active = 'Y'::bpchar) AND
> (is_menu_item_available(menu_item_id, 'Y'::bpchar) = 'Y'::bpchar))"
> Rows Removed by Filter: 194
> -> Index Scan using menu_item_category_pk on
> menu_item_category b (cost=0.29..2.31 rows=1 width=20) (actual
> time=0.002..0.002 rows=1 loops=89)
> Index Cond: (menu_item_category_id =
> a.menu_item_category_id)
> -> Index Scan using menu_item_variant_pk on
> menu_item_variant c (cost=3.57..5.59 rows=1 width=45) (actual
> time=0.002..0.002 rows=1 loops=89)
> Index Cond: (menu_item_variant_id = (SubPlan
> 1))
> Filter: (a.menu_item_id = menu_item_id)
> SubPlan 1
> -> Limit (cost=3.13..3.14 rows=1 width=8)
> (actual time=0.008..0.008 rows=1 loops=89)
> -> Aggregate (cost=3.13..3.14
> rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=89)
> -> Index Scan using
> "idx$$_023a0001" on menu_item_variant (cost=0.43..3.11 rows=8 width=8)
> (actual time=0.003..0.007 rows=7 loops=89)
> Index Cond: (menu_item_id
> = a.menu_item_id)
> Filter: (deleted =
> 'N'::bpchar)
> Rows Removed by Filter: 4
> -> Index Scan using menu_item_variant_type_pk on
> menu_item_variant_type d (cost=0.29..0.31 rows=1 width=16) (actual
> time=0.002..0.002 rows=1 loops=89)
> Index Cond: (menu_item_variant_type_id =
> c.menu_item_variant_type_id)
> Filter: ((is_hidden)::text = 'false'::text)
> -> Index Scan using size_pk on item_size e (cost=0.29..0.31
> rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=89)
> Index Cond: (size_id = c.size_id)
> -> Index Scan using "restaurant_idx$$_274b003d" on restaurant f
> (cost=0.29..2.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=89)
> Index Cond: (restaurant_id = 1528)
> -> Seq Scan on mark m (cost=0.00..1.03 rows=3 width=12) (actual
> time=0.000..0.001 rows=3 loops=89)
> Planning Time: 2.078 ms
> Execution Time: 5.141 ms
Re: waiting for client write
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 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!!
Re: waiting for client write
Em sex., 11 de jun. de 2021 às 14:59, Ayub Khan escreveu: > Ranier, > > I tried to VACCUM ANALYZE the tables involved multiple times and also > tried the statistics approach as well > Ayub you can try by the network side: https://stackoverflow.com/questions/50298447/postgres-jdbc-client-getting-stuck-at-reading-from-socket " We found out that this was caused by the database server's MTU setting. MTU was set to 9000 by default and resulted in packet loss. Changing it to 1500 resolved the issue." regards, Ranier Vilela
Re: waiting for client write
Ranier,
I verified the link you gave and also checked AWS documentation and found
the exact output as shown in AWS:
https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/network_mtu.html
[ec2-user ~]$ tracepath amazon.com
1?: [LOCALHOST] pmtu 9001
1: ip-xxx-xx-xx-1.us-west-1.compute.internal (xxx.xx.xx.x) 0.187ms pmtu 1500
Should the LOCALHOST pmtu needs to be updated to 1500 ?
On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan 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!!
Re: waiting for client write
Em sex., 11 de jun. de 2021 às 15:19, Ayub Khan escreveu: > Ranier, > > I verified the link you gave and also checked AWS documentation and found > the exact output as shown in AWS: > > https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/network_mtu.html > > [ec2-user ~]$ tracepath amazon.com > 1?: [LOCALHOST] pmtu 9001 > 1: ip-xxx-xx-xx-1.us-west-1.compute.internal (xxx.xx.xx.x) 0.187ms pmtu > 1500 > > Should the LOCALHOST pmtu needs to be updated to 1500 ? > > Or us-west-1.compute.internal should be set to 9000. I think both must match. 9000 are jumbo frames. The bigger the better. Try switching to 9000 first and 1500 if it does not work. regards, Ranier Vilela
Re: waiting for client write
Ranier,
Both production and test vms are running on Ubuntu:
the below command when executed from client VM shows that its using
PMTU 9001.
# tracepath dns-name-of-rds
1?: [LOCALHOST] pmtu 9001
On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan 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!!
Re: waiting for client write
On Fri, Jun 11, 2021 at 12:28 PM Ayub Khan wrote: > Vijay, > > Both tomcat and postgresql are on the same region as that of the database > server. It is an RDS so I do not have shell access to it. > > Jeff, > > The tomcat profile is suggesting that it's waiting for a response from the > database server. > But waiting for a response should consume zero CPU, that is why I wonder if this is a CPU profile or a wall-time profile. Tomcat and RDS are in the same availability region as eu-central-1a > I don't think that that necessarily guarantees high network performance. Some EC2 server classes have better networking than others. And if the server says it is waiting for the client, and the client says it is waiting server (assuming that that is really what it is saying), then what else could it be but the network? Cheers, Jeff
Re: waiting for client write
Jeff,
Both tomcat vm and RDS vms have 25Gbps
Postgresql Db class is db.r6g.16xlarge
Tomcat vm is c5.9xlarge
--Ayub
On Wed, 9 Jun 2021, 17:47 Ayub Khan, 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
>
