Re: waiting for client write

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Pavan,

In jProfiler , I see that most cpu is consumed when the Tomcat thread is stuck at PgPreparedStatement.execute. I am using version 42.2.16 of JDBC driver.


Ranier,

EXPLAIN ANALYZE

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' Nested Loop Left Join  (cost=5.15..162.10 rows=1 width=148) (actual time=0.168..5.070 rows=89 loops=1)
  Join Filter: (a.mark_id = m.mark_id)
  Rows Removed by Join Filter: 267
  ->  Nested Loop  (cost=5.15..161.04 rows=1 width=144) (actual time=0.161..4.901 rows=89 loops=1)
        ->  Nested Loop  (cost=4.86..158.72 rows=1 width=148) (actual time=0.156..4.729 rows=89 loops=1)
              ->  Nested Loop  (cost=4.57..158.41 rows=1 width=140) (actual time=0.151..4.572 rows=89 loops=1)
                    ->  Nested Loop  (cost=4.28..158.10 rows=1 width=132) (actual time=0.145..4.378 rows=89 loops=1)
                          ->  Nested Loop  (cost=0.71..152.51 rows=1 width=95) (actual time=0.121..3.334 rows=89 loops=1)
                                ->  Index Scan using menu_item_restaurant_id on menu_item a  (cost=0.42..150.20 rows=1 width=83) (actual time=0.115..3.129 rows=89 loops=1)
                                      Index Cond: (restaurant_id = 1528)
"                                      Filter: ((active = 'Y'::bpchar) AND (is_menu_item_available(menu_item_id, 'Y'::bpchar) = 'Y'::bpchar))"
                                      Rows Removed by Filter: 194
                                ->  Index Scan using menu_item_category_pk on menu_item_category b  (cost=0.29..2.31 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=89)
                                      Index Cond: (menu_item_category_id = a.menu_item_category_id)
                          ->  Index Scan using menu_item_variant_pk on menu_item_variant c  (cost=3.57..5.59 rows=1 width=45) (actual time=0.002..0.002 rows=1 loops=89)
                                Index Cond: (menu_item_variant_id = (SubPlan 1))
                                Filter: (a.menu_item_id = menu_item_id)
                                SubPlan 1
                                  ->  Limit  (cost=3.13..3.14 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=89)
                                        ->  Aggregate  (cost=3.13..3.14 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=89)
                                              ->  Index Scan using "idx$$_023a0001" on menu_item_variant  (cost=0.43..3.11 rows=8 width=8) (actual time=0.003..0.007 rows=7 loops=89)
                                                    Index Cond: (menu_item_id = a.menu_item_id)
                                                    Filter: (deleted = 'N'::bpchar)
                                                    Rows Removed by Filter: 4
                    ->  Index Scan using menu_item_variant_type_pk on menu_item_variant_type d  (cost=0.29..0.31 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=89)
                          Index Cond: (menu_item_variant_type_id = c.menu_item_variant_type_id)
                          Filter: ((is_hidden)::text = 'false'::text)
              ->  Index Scan using size_pk on item_size e  (cost=0.29..0.31 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=89)
                    Index Cond: (size_id = c.size_id)
        ->  Index Scan using "restaurant_idx$$_274b003d" on restaurant f  (cost=0.29..2.30 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=89)
              Index Cond: (restaurant_id = 1528)
  ->  Seq Scan on mark m  (cost=0.00..1.03 rows=3 width=12) (actual time=0.000..0.001 rows=3 loops=89)
Planning Time: 2.078 ms
Execution Time: 5.141 ms


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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux