thanks. >latency average = 2480.042 ms
that latency is pretty high, even after changing the mtu ? for a query that takes 5ms to run (from your explain analyze above) and returns a few 100 rows. so it does look like a network latency, but it seems strange when you said the same query from the same ec2 host ran fast against oracle compared to postgres RDS. So oracle and RDS on vms with separate mtu setting ? i was tried to simulate issues with the client if any :), Slow ClientWrites · Issue #2480 · brianc/node-postgres (github.com) <https://github.com/brianc/node-postgres/issues/2480> Some intial performance work by brianc · Pull Request #2031 · brianc/node-postgres (github.com) <https://github.com/brianc/node-postgres/pull/2031> ClientRead statement_timeout · Issue #1952 · brianc/node-postgres (github.com) <https://github.com/brianc/node-postgres/issues/1952> I tried to play around with FEBE protocols to delay flush sync etc, but could not simulate clientwrite wait event :(. Sorry. and i am not touching java :) I was asking for a run like this, with -r that would have shown latency per statement. but anyways. Below I make use of tc (traffic control) to add a delay to my lo interface, and check how pgbench runs vary with increased latency at interface. useless for your case, but i use this to tell dev when they have slot queries if, roundtrip delay is high, it is not a pg fault :) postgres@db:~/playground$ sudo tc -s qdisc | head -3 qdisc noqueue 0: dev lo root refcnt 2 Sent 0 bytes 0 pkt (dropped 0, overlimits 0 requeues 0) backlog 0b 0p requeues 0 postgres@db:~/playground$ sudo tc qdisc add dev lo root netem delay 100ms # add a delay on lo of 100ms via tc module postgres@db:~/playground$ sudo tc -s qdisc | head -3 qdisc netem 8007: dev lo root refcnt 2 limit 1000 delay 100.0ms Sent 8398 bytes 15 pkt (dropped 0, overlimits 0 requeues 0) backlog 0b 0p requeues 0 postgres@db:~/playground$ pgbench -c 2 -C -j 2 -n -P 2 -T 10 -r -f pgbench.test -h 127.0.0.1 progress: 2.0 s, 1.0 tps, lat 603.211 ms stddev 0.007 progress: 4.2 s, 1.8 tps, lat 602.838 ms stddev 0.101 progress: 6.0 s, 1.1 tps, lat 603.730 ms stddev 0.034 progress: 8.0 s, 2.0 tps, lat 603.058 ms stddev 0.081 progress: 10.3 s, 1.8 tps, lat 600.852 ms stddev 0.030 pgbench (PostgreSQL) 14.0 transaction type: pgbench.test scaling factor: 1 query mode: simple number of clients: 2 number of threads: 2 duration: 10 s number of transactions actually processed: 18 latency average = 602.357 ms latency stddev = 1.112 ms average connection time = 605.499 ms tps = 1.655749 (including reconnection times) statement latencies in milliseconds: 200.672 begin; 200.797 select 1; 200.917 end; postgres@db:~/playground$ sudo tc qdisc del dev lo root netem # remove delay postgres@db:~/playground$ sudo tc -s qdisc | head -3 qdisc noqueue 0: dev lo root refcnt 2 Sent 0 bytes 0 pkt (dropped 0, overlimits 0 requeues 0) backlog 0b 0p requeues 0 postgres@db:~/playground$ pgbench -c 2 -C -j 2 -n -P 2 -T 10 -r -f pgbench.test -h 127.0.0.1 progress: 2.0 s, 1272.4 tps, lat 0.200 ms stddev 0.273 progress: 4.0 s, 1155.3 tps, lat 0.306 ms stddev 0.304 progress: 6.0 s, 1241.7 tps, lat 0.261 ms stddev 0.290 progress: 8.0 s, 1508.6 tps, lat 0.150 ms stddev 0.140 progress: 10.0 s, 1172.7 tps, lat 0.292 ms stddev 0.302 pgbench (PostgreSQL) 14.0 transaction type: pgbench.test scaling factor: 1 query mode: simple number of clients: 2 number of threads: 2 duration: 10 s number of transactions actually processed: 12704 latency average = 0.236 ms latency stddev = 0.271 ms average connection time = 1.228 ms tps = 1270.314254 (including reconnection times) statement latencies in milliseconds: 0.074 begin; 0.115 select 1; 0.048 end;
