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 :),
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;
