Postgres Pro
Downloads
Home   >   mailing lists

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

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

I did the MTU change and it did seem to bring down the clientWrite waits to half. 

The change I did was to enable ICMP to have Destination Unreachable  fragmentation needed and DF set

"When there is a difference in the MTU size in the network between two hosts, first make sure that your network settings don't block path MTU discovery (PMTUD). PMTUD enables the receiving host to respond to the originating host with the following ICMP message: Destination Unreachable: fragmentation needed and DF set (ICMP Type 3, Code 4). This message instructs the originating host to use the lowest MTU size along the network path to resend the request. "

https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-drop-issues.html



Vijay,

Below is the pgbench result which was executed from the remote instance pointing to RDS

postgres@localhost:/archive$ pgbench -h pg-cluster -p 5432 -U testuser -c 50 -j 2 -P 60 -T 600 testdb -f /archive/test.sql
starting vacuum...pgbench: error: ERROR:  relation "pgbench_branches" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_tellers" does not exist
pgbench: (ignoring this error and continuing anyway)
pgbench: error: ERROR:  relation "pgbench_history" does not exist
pgbench: (ignoring this error and continuing anyway)
end.
progress: 60.0 s, 18.3 tps, lat 2631.655 ms stddev 293.592
progress: 120.0 s, 19.6 tps, lat 2533.271 ms stddev 223.722
progress: 180.0 s, 20.3 tps, lat 2446.050 ms stddev 158.397
progress: 240.1 s, 19.2 tps, lat 2575.506 ms stddev 292.418
progress: 300.0 s, 20.0 tps, lat 2482.908 ms stddev 181.770
progress: 360.0 s, 22.1 tps, lat 2245.147 ms stddev 110.855
progress: 420.0 s, 20.7 tps, lat 2397.270 ms stddev 289.324
progress: 480.0 s, 18.8 tps, lat 2625.595 ms stddev 240.250
progress: 540.0 s, 20.1 tps, lat 2467.336 ms stddev 133.121
progress: 600.0 s, 20.2 tps, lat 2455.824 ms stddev 137.976
transaction type: /archive/test.sql
scaling factor: 1
query mode: simple
number of clients: 50
number of threads: 2
duration: 600 s
number of transactions actually processed: 12007
latency average = 2480.042 ms
latency stddev = 242.243 ms
tps = 19.955602 (including connections establishing)
tps = 19.955890 (excluding connections establishing)

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: Ayub Khan
Date:
Subject: Re: waiting for client write
Next
From: Vijaykumar Jain
Date:
Subject: Re: waiting for client write