From: | Stevo Slavić <sslavic(at)gmail(dot)com> |
---|---|
To: | Oliver Jowett <oliver(at)opencloud(dot)com> |
Cc: | pgsql-jdbc <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL protocol 3 JDBC drivers, sub-protocols, and latency |
Date: | 2011-11-18 16:00:30 |
Message-ID: | CAAUywg9qKjw_cL53B_c=8d--aARjR21zTwOJnb1e4_duRNeVtg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Stevo Slavić | 2011-11-18 17:24:50 | Re: Publish 9.1-901 JDBC drivers on maven central |
Previous Message | Bruce Adams | 2011-11-18 14:38:36 | Re: Tomcat7 connection pool with postgresql |