Re: waiting for client write - Mailing list pgsql-performance
| From | Ayub Khan |
|---|---|
| Subject | Re: waiting for client write |
| Date | |
| Msg-id | CAHdeyEJSywS6QXat1weQRqj_M8G9pVxP1D12fgO0Sazcj_8TCg@mail.gmail.com Whole thread Raw |
| In response to | waiting for client write (Ayub Khan <ayub.hp@gmail.com>) |
| Responses |
Re: waiting for client write
(Ranier Vilela <ranier.vf@gmail.com>)
Re: waiting for client write (Vijaykumar Jain <vijaykumarjain.github@gmail.com>) |
| List | pgsql-performance |
Ranier,
This issue is only with queries which are slow, if it's an MTU issue then it should be with all the APIs.
I tried on Aurora db and I see same plan and also same slowness
On Wed, 9 Jun 2021, 17:47 Ayub Khan, <ayub.hp@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 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
pgsql-performance by date: