Re: waiting for client write - Mailing list pgsql-performance

From Ayub Khan
Subject Re: waiting for client write
Date
Msg-id CAHdeyEJbPY5jn6bq1yNcaE_0sjNCG2HZU6cXjrqgP9ZyKzTQ5Q@mail.gmail.com
Whole thread Raw
In response to waiting for client write  (Ayub Khan <ayub.hp@gmail.com>)
List pgsql-performance
Ranier,

Both production and test vms are running on Ubuntu:

the below command when executed from client VM shows that its using PMTU 9001.

# tracepath dns-name-of-rds
 1?: [LOCALHOST]                                         pmtu 9001

On Wed, Jun 9, 2021 at 5:47 PM 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 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!!

pgsql-performance by date:

Previous
From: Ranier Vilela
Date:
Subject: Re: waiting for client write
Next
From: Jeff Janes
Date:
Subject: Re: waiting for client write