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,
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 <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 testedSELECT
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.5Sun 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!!
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!!
