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

All is good, false alarm, simulation of latency was wrong, now after
applying proper latency (using
http://www.linuxfoundation.org/collaborate/workgroups/networking/netem
) with v3 protocol log for same query looks like:

19:39:31.392 (1) simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@15dfd77,
maxRows=0, fetchSize=0, fl
ags=17
19:39:31.393 (1)  FE=> Parse(stmt=null,query="select
nextval('hibernate_sequence')",oids={})
19:39:31.394 (1)  FE=> Bind(stmt=null,portal=null)
19:39:31.396 (1)  FE=> Describe(portal=null)
19:39:31.397 (1)  FE=> Execute(portal=null,limit=0)
19:39:31.398 (1)  FE=> Sync
19:39:31.460 (1)  <=BE ParseComplete [null]
19:39:31.460 (1)  <=BE BindComplete [null]
19:39:31.461 (1)  <=BE RowDescription(1)
19:39:31.462 (1)  <=BE DataRow
19:39:31.463 (1)  <=BE CommandStatus(SELECT)
19:39:31.464 (1)  <=BE ReadyForQuery(I)

Thanks again for great support!

Regards,
Stevo.

On Fri, Nov 18, 2011 at 9:00 AM, Stevo Slavić <sslavic@gmail.com> wrote:
> 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.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@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@opencloud.com> wrote:
>> On 19 November 2011 00:21, Stevo Slavić <sslavic@gmail.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
>>
>