PostgreSQL protocol 3 JDBC drivers, sub-protocols, and latency
От | Stevo Slavić |
---|---|
Тема | PostgreSQL protocol 3 JDBC drivers, sub-protocols, and latency |
Дата | |
Msg-id | CAAUywg8YOc5gsbObNuOQ2FNxtctPmzTDnVfXJFhnXqm3uXJz4w@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: PostgreSQL protocol 3 JDBC drivers, sub-protocols, and latency
|
Список | pgsql-jdbc |
Hello pgsql-jdbc users, When executing same queries from psql and from application over WAN with high latency it was easier to notice there is big difference in query execution times. Where pgsql would execute statements in latency + couple of more ms, application using latest 9.1 jdbc driver would take n * latency + couple of more ms. After turning on the logging level (see [1] for connection params) I've noticed multiple backend protocol messages being exchanged between the database and the driver, each of them taking latency + couple more ms. Reading through protocol specs (see [2]) I've found out about "extended query" sub-protocol, which adds for every query multiple frontend and backend messages. It seems newer jdbc drivers which support extended query sub-protocol are using it by default. 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. Since I can not eliminate latency, I'd like to force pgsql-jdbc driver to use simple query sub-protocol. I couldn't find direct info in pgsql-jdcb driver docs on how to force simple query sub-protocol. Connecting info in [3] I've found out that if I put protocolVersion to 2 that simple query sub-protocol gets used and query times are on par with psql. Version 3 of postgres frontend/backend protocol added support for extended query sub-protocol, so it isn't available in protocol version 2. Is there a connection parameter in pgsql-jdbc which would configure jdbc driver to force usage of simple query sub-protocol while still using protocol version 3? Are there any (significant) down-sides in using protocol version 2 instead of protocol version 3? Thanks in advance! Regards, Stevo [1] pgsql-jdbc driver specific connection parameters http://jdbc.postgresql.org/documentation/80/connect.html#connection-parameters [2] Postgres Frontend/Backend protocol http://www.postgresql.org/docs/9.1/static/protocol-overview.html [3] Summary of changes in Postgres Frontend/Backend protocol between version 2 and 3 http://www.postgresql.org/docs/9.1/static/protocol-changes.html
В списке pgsql-jdbc по дате отправления: