Re: waiting for client write

Vijay,

I did not change the MTU on the network interface but created incoming
rule on the security group as per the below documentation:

PMTUD enables the receiving host to respond to the originating host with
the following ICMP message: Destination Unreachable: fragmentation needed
and DF set (ICMP Type 3, Code 4). This message instructs the originating
host to use the lowest MTU size along the network path to resend the
request. Without this negotiation, packet drop can occur because the
request is too large for the receiving host to accept.

I also did another test, instead of using RDS, installed postgresql on a
similar VM as that of where oracle is installed and tested it. Now even
when both client and postgresql VMs have the same MTU settings still in the
pg activity table I could see clientwrite waits.

-Ayub

On Wed, Jun 9, 2021 at 5:47 PM Ayub Khan <ayub(dot)hp(at)gmail(dot)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!!