Re: waiting for client write

since you are willing to try out options :)

if your setup runs the same test plan queries on jmeter against oracle and
postgresql
and only postgresql shows waits or degraded performance I think this is
more then simply network.

can you simply boot up an ec2 ubuntu/centos and install postgresql.
and run pgbench locally on the installed db with the same queries.

and run the same pgbench against RDS and share the results.

On Sat, 12 Jun 2021 at 13:50, Ayub Khan <ayub(dot)hp(at)gmail(dot)com> wrote:

> 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(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
>>
>

--
Thanks,
Vijay
Mumbai, India