Re: waiting for client write

Em sex., 11 de jun. de 2021 às 13:28, Ayub Khan <ayub(dot)hp(at)gmail(dot)com>
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 <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
>>
> Can you post the results with: explain analyze?
EXPLAIN ANALYZE
SELECT ....

regards,
Ranier Vilela