Re: waiting for client write

[Ayub Khan <ayub.hp@xxxxxxxxx>]

Vijay,

below is the benchmark result when executed against bench_mark database instead of running the test with slow query on the application database. This shows that it might not be an issue with MTU but some issue with the application database itself and the query.

postgres@localhost:~$ pgbench -h test-cluster -p 5432 -U testuser -c 50 -j 2 -P 60 -T 600 bench_mark
starting vacuum...end.
progress: 60.0 s, 17830.3 tps, lat 2.765 ms stddev 0.632
progress: 120.0 s, 18450.3 tps, lat 2.681 ms stddev 0.582
progress: 180.0 s, 18405.0 tps, lat 2.688 ms stddev 0.588
progress: 240.0 s, 17087.9 tps, lat 2.897 ms stddev 0.717
progress: 300.0 s, 18280.6 tps, lat 2.706 ms stddev 0.595
progress: 360.0 s, 18433.9 tps, lat 2.683 ms stddev 0.582
progress: 420.0 s, 18308.4 tps, lat 2.702 ms stddev 0.599
progress: 480.0 s, 18156.7 tps, lat 2.725 ms stddev 0.615
progress: 540.0 s, 16803.3 tps, lat 2.946 ms stddev 0.764
progress: 600.0 s, 18266.6 tps, lat 2.708 ms stddev 0.602
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 150
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 10801425
latency average = 2.747 ms
latency stddev = 0.635 ms
tps = 18001.935315 (including connections establishing)
tps = 18002.205940 (excluding connections establishing)

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

--

--------------------------------------------------------------------

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