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

From Vijaykumar Jain
Subject Re: waiting for client write
Date
Msg-id CAM+6J94LAFWnh_+LgMgoJF7By7JLgkDAMDfov2a2-fTCoAHnrQ@mail.gmail.com
Whole thread Raw
In response to Re: waiting for client write  (Jeff Janes <jeff.janes@gmail.com>)
List pgsql-performance
Ayub,

Ideally when i have to deal with this,
i run a pgbench stress test locally on the db server on lo interface which does not suffer mtu / bandwidth saturation issues.
then run the same pgbench from a remote server in the same subnet as the app and record the results and compare.
that helps me get rid of any non standard client issues or network latency issues.


A typical case where people above are pointing to is
1) for ex. When I am in India and query a server in the US across WAN on a client like pgadmin (which may not handle loading million rows efficiently), I have a high chance of getting ClientWrite ,ClientRead wait events. ( Read client and or network issues )
Of course this is much worse than ec2 and db  in the same region, but you get the point that you have to rule out sketchy networks between the servers.
Ideally an iperf like stress test can help to test bandwidth.

So if you can run pgbench from across some test servers and get consistent results, then you can come back with a reply that more people can help with.
using a custom script

postgres@go:~/pgbench_example$ more pgbench.script
\set r1 random(0, 10000) -- you can use them below in queries as params like col = :r1
\set r2 random(0, 8000)

begin;
select random();
end;

-- put in any query that you use in jmeter between begin/end like above
-- select * from foo where (u1 = :r1  and u2 = :r2);
-- insert into foo values (:u1v, :u2v) on conflict do nothing;
-- update foo set u1 = :u1v where u2 = 100;
-- select pg_sleep(1);


and then run pgbench with the custom script

postgres@go:~/pgbench_example$ pgbench -c 10 -f ./pgbench.script -j 10 -n -T 30
transaction type: ./pgbench.script
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 10
duration: 30 s
number of transactions actually processed: 528984
latency average = 0.567 ms
tps = 17631.650584 (including connections establishing)
tps = 17642.174229 (excluding connections establishing)

pgsql-performance by date:

Previous
From: Ranier Vilela
Date:
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Next
From: "Dean Gibson (DB Administrator)"
Date:
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster