Re: PostgreSQL protocol 3 JDBC drivers, sub-protocols, and latency

Hello Oliver,

Thanks for replying!

Yes, I'm aware that newer is not that new, I guess it's ever since v3
protocol was introduced, with varying behavior.

Here is the log output from 9.1-901 JDBC 4 driver with v3
frontend/backend protocol for a very simple query and 60ms latency
(simulated in one way only, from db back to db client):

19:59:40.057 (1) simple execute,
handler=org(dot)postgresql(dot)jdbc2(dot)AbstractJdbc2Statement$StatementResultHandler(at)7f72455,
maxRows=0, fetchSize=0, flags=17
19:59:40.057 (1) FE=> Parse(stmt=null,query="select
nextval('hibernate_sequence')",oids={})
19:59:40.057 (1) FE=> Bind(stmt=null,portal=null)
19:59:40.057 (1) FE=> Describe(portal=null)
19:59:40.057 (1) FE=> Execute(portal=null,limit=0)
19:59:40.057 (1) FE=> Sync
19:59:40.120 (1) <=BE ParseComplete [null]
19:59:40.180 (1) <=BE BindComplete [null]
19:59:40.240 (1) <=BE RowDescription(1)
19:59:40.300 (1) <=BE DataRow
19:59:40.300 (1) <=BE CommandStatus(SELECT 1)
19:59:40.360 (1) <=BE ReadyForQuery(I)

Maybe simulated latency is serializing things which should be
occurring concurrently. But wouldn't that happen for DataRow and
CommandStatus backend message too?

As soon as I configured protocolVersion=2, I got 5 times better
performance, but another issue popped-up, defaults for parameters seem
to be different in v2 compared to v3, but found a quick workaround.

Regards,
Stevo.

On Fri, Nov 18, 2011 at 7:26 AM, Oliver Jowett <oliver(at)opencloud(dot)com> wrote:
> On 19 November 2011 00:21, Stevo Slavić <sslavic(at)gmail(dot)com> wrote:
>
>> It seems newer jdbc drivers which support extended query sub-protocol are using it by default.
>
> Not "newer" drivers - it's been doing that since 8.0, which is very
> old now. What driver versions are you comparing to?
>
>> This sub-protocol is advertised that "it might
>> allow improvements in performance or functionality", but with high
>> latency environment in fact it performs much worse since query
>> execution time is (n-2) * latency bigger compared to simple query
>> protocol, where n is number of messages extended query sub-protocol
>> uses for each query. psql seems to be using (by default) "simple
>> query" protocol with only one frontend and one backend message.
>
> There's no reason the extended query protocol needs to be worse. in
> general the driver tries to send many messages without waiting for
> responses, so while there are several logical messages involved, there
> are few round trips. Basically you will be seeing one round trip per
> Sync or Flush message. How many of those do you see? I know I tried to
> minimize the chattiness of the original implementation exactly to
> avoid problems with high-latency connections, but it may have got
> worse over time since it's not a case that many people appear to be
> using. The original implementation would end up doing something like
> this most of the time:
>
> send Parse, Describe, Execute, Sync
> wait for results
>
> which is no worse than the simple query protocol for latency.
>
>> Are there any (significant) down-sides in using protocol version 2
>> instead of protocol version 3?
>
> Yes, you'll lose all sorts of random features, using v2 should be a last resort.
>
> Oliver
>