Re: waiting for client write

[Ranier Vilela <ranier.vf@xxxxxxxxx>]

Em sex., 11 de jun. de 2021 às 13:28, Ayub Khan <ayub.hp@xxxxxxxxx> 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.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 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