Обсуждение: Slow performance with 9.3-110x JDBC 4
Dear All,
Time(ms) PG 8.4.7 9.3.4
Driver
8.4-703 193390 188379
9.3.1101 349404 344344
Does anybody else saw such a difference ? What reasons can explain it ?
Another point, is that the bulk insertions are prerformed by parallel processes and 9.3-110x JDBC4 drivers appear to scale poorly.
Thanks for any help.
Hi,
Prepared statements are handled much differently in the 9.3 drivers. They actually use server side prepared statements.
The 8.4 driver just does selects or inserts, but does not plan a statement on the server
That being said in order to get to the root of your problem we would have to see exactly what your code is doing.
Dave Cramer
On Thu, Mar 27, 2014 at 9:35 AM, ChatPristi <cchristo_0899@yahoo.fr> wrote:
Dear All,I have a java program that does bulk insertions (with transactions and PreparedStatements) in a postgresql database. I have a performance divided between two and four when using 9.3-1100 JDBC4 and 9.3-1101 in comparison with postgresql-8.4-703 JDBC4 independently of postgresql version.
Time(ms) PG 8.4.7 9.3.4
Driver
8.4-703 193390 188379
9.3.1101 349404 344344
Does anybody else saw such a difference ? What reasons can explain it ?
Another point, is that the bulk insertions are prerformed by parallel processes and 9.3-110x JDBC4 drivers appear to scale poorly.
Thanks for any help.
You might want to do the same tests after adding ?protocolVersion=2 to the connection URL. i.e.: jdbc:postgresql://localhost/database?protocolVersion=2 2014-03-27 15:35 GMT+02:00 ChatPristi <cchristo_0899@yahoo.fr>: > Dear All, > > I have a java program that does bulk insertions (with transactions and > PreparedStatements) in a postgresql database. I have a performance divided > between two and four when using 9.3-1100 JDBC4 and 9.3-1101 in comparison > with postgresql-8.4-703 JDBC4 independently of postgresql version. > > Time(ms) PG 8.4.7 9.3.4 > Driver > 8.4-703 193390 188379 > 9.3.1101 349404 344344 > > Does anybody else saw such a difference ? What reasons can explain it ? > > Another point, is that the bulk insertions are prerformed by parallel > processes and 9.3-110x JDBC4 drivers appear to scale poorly. > > Thanks for any help.
Em 27/03/2014 13:58, Lachezar Dobrev escreveu:
What to expect from "protocolVersoin=2"?You might want to do the same tests after adding ?protocolVersion=2 to the connection URL. i.e.: jdbc:postgresql://localhost/database?protocolVersion=2 2014-03-27 15:35 GMT+02:00 ChatPristi <cchristo_0899@yahoo.fr>:Dear All, I have a java program that does bulk insertions (with transactions and PreparedStatements) in a postgresql database. I have a performance divided between two and four when using 9.3-1100 JDBC4 and 9.3-1101 in comparison with postgresql-8.4-703 JDBC4 independently of postgresql version. Time(ms) PG 8.4.7 9.3.4 Driver 8.4-703 193390 188379 9.3.1101 349404 344344 Does anybody else saw such a difference ? What reasons can explain it ? Another point, is that the bulk insertions are prerformed by parallel processes and 9.3-110x JDBC4 drivers appear to scale poorly. Thanks for any help.
Speed up or down?
Edson
--
Edson Carlos Ericksson Richter Sistemas para Bibliotecas, Escolinhas Infantis Projetos sob medida para sua empresa | |
Celular: | (51) 9318-9766 (51) 8585-0796 |
"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho original" - Albert Einstein |
I wanted to avoid that recommendation, but since it is out there. protocolVersion reverts to the same protocol as 8.4 uses which does not use server side prepared statements
And I would expect it to speed up, or at least be the same as 8.4.7
Dave
On Thu, Mar 27, 2014 at 1:45 PM, Edson Richter <edsonrichter@hotmail.com> wrote:
Em 27/03/2014 13:58, Lachezar Dobrev escreveu:What to expect from "protocolVersoin=2"?You might want to do the same tests after adding ?protocolVersion=2 to the connection URL. i.e.: jdbc:postgresql://localhost/database?protocolVersion=2 2014-03-27 15:35 GMT+02:00 ChatPristi <cchristo_0899@yahoo.fr>:Dear All, I have a java program that does bulk insertions (with transactions and PreparedStatements) in a postgresql database. I have a performance divided between two and four when using 9.3-1100 JDBC4 and 9.3-1101 in comparison with postgresql-8.4-703 JDBC4 independently of postgresql version. Time(ms) PG 8.4.7 9.3.4 Driver 8.4-703 193390 188379 9.3.1101 349404 344344 Does anybody else saw such a difference ? What reasons can explain it ? Another point, is that the bulk insertions are prerformed by parallel processes and 9.3-110x JDBC4 drivers appear to scale poorly. Thanks for any help.
Speed up or down?
Edson--
Edson Carlos Ericksson Richter
Sistemas para Bibliotecas, Escolinhas Infantis
Projetos sob medida para sua empresaCelular: (51) 9318-9766
(51) 8585-0796"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho original"
- Albert Einstein
For the record: I was recommending that for testing purposes only, to check if the reduced performance is related to protocol version (I believe there is a significant change it might).
2014-03-27 21:52 GMT+02:00 Dave Cramer <davecramer@gmail.com>:
I wanted to avoid that recommendation, but since it is out there. protocolVersion reverts to the same protocol as 8.4 uses which does not use server side prepared statementsAnd I would expect it to speed up, or at least be the same as 8.4.7DaveOn Thu, Mar 27, 2014 at 1:45 PM, Edson Richter <edsonrichter@hotmail.com> wrote:Em 27/03/2014 13:58, Lachezar Dobrev escreveu:What to expect from "protocolVersoin=2"?You might want to do the same tests after adding ?protocolVersion=2 to the connection URL. i.e.: jdbc:postgresql://localhost/database?protocolVersion=2 2014-03-27 15:35 GMT+02:00 ChatPristi <cchristo_0899@yahoo.fr>:Dear All, I have a java program that does bulk insertions (with transactions and PreparedStatements) in a postgresql database. I have a performance divided between two and four when using 9.3-1100 JDBC4 and 9.3-1101 in comparison with postgresql-8.4-703 JDBC4 independently of postgresql version. Time(ms) PG 8.4.7 9.3.4 Driver 8.4-703 193390 188379 9.3.1101 349404 344344 Does anybody else saw such a difference ? What reasons can explain it ? Another point, is that the bulk insertions are prerformed by parallel processes and 9.3-110x JDBC4 drivers appear to scale poorly. Thanks for any help.
Speed up or down?
Edson--
Edson Carlos Ericksson Richter
Sistemas para Bibliotecas, Escolinhas Infantis
Projetos sob medida para sua empresaCelular: (51) 9318-9766
(51) 8585-0796"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho original"
- Albert Einstein
Thank you very much for your help and explanations.
Indeed, the Lachezar's recommendation ( jdbc:postgresql://localhost/database?protocolVersion=2) restores the speed: 190413 ms with jdbc-9.3.1101 and postgresql 9.3.4.
As I used bytea and due to change since postgresql 9.0 for handling them (bytea_output parameter), my programs only work with jdbc-9.3.1101 (and no longer with jdbc-8.4-703).
So, I must use 9.3.1101. However, what does the sentence "I was recommending that for testing purposes only" by Lachezar means ? How to increase performance of server side prepared statements ?
Le Vendredi 28 mars 2014 10h50, Lachezar Dobrev <l.dobrev@gmail.com> a écrit :
For the record: I was recommending that for testing purposes only, to check if the reduced performance is related to protocol version (I believe there is a significant change it might).
2014-03-27 21:52 GMT+02:00 Dave Cramer <davecramer@gmail.com>:
I wanted to avoid that recommendation, but since it is out there. protocolVersion reverts to the same protocol as 8.4 uses which does not use server side prepared statementsAnd I would expect it to speed up, or at least be the same as 8.4.7DaveOn Thu, Mar 27, 2014 at 1:45 PM, Edson Richter <edsonrichter@hotmail.com> wrote:Em 27/03/2014 13:58, Lachezar Dobrev escreveu:What to expect from "protocolVersoin=2"?You might want to do the same tests after adding ?protocolVersion=2 to the connection URL. i.e.: jdbc:postgresql://localhost/database?protocolVersion=2 2014-03-27 15:35 GMT+02:00 ChatPristi <cchristo_0899@yahoo.fr>:Dear All, I have a java program that does bulk insertions (with transactions and PreparedStatements) in a postgresql database. I have a performance divided between two and four when using 9.3-1100 JDBC4 and 9.3-1101 in comparison with postgresql-8.4-703 JDBC4 independently of postgresql version. Time(ms) PG 8.4.7 9.3.4 Driver 8.4-703 193390 188379 9.3.1101 349404 344344 Does anybody else saw such a difference ? What reasons can explain it ? Another point, is that the bulk insertions are prerformed by parallel processes and 9.3-110x JDBC4 drivers appear to scale poorly. Thanks for any help.
Speed up or down?
Edson--
Edson Carlos Ericksson Richter
Sistemas para Bibliotecas, Escolinhas Infantis
Projetos sob medida para sua empresaCelular: (51) 9318-9766
(51) 8585-0796"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho original"
- Albert Einstein
In order to get to the root cause of this I'd have to see exactly what you are doing ?
Can you share the relevant code ?
On Fri, Mar 28, 2014 at 6:25 AM, ChatPristi <cchristo_0899@yahoo.fr> wrote:
Thank you very much for your help and explanations.Indeed, the Lachezar's recommendation ( jdbc:postgresql://localhost/database?protocolVersion=2) restores the speed: 190413 ms with jdbc-9.3.1101 and postgresql 9.3.4.As I used bytea and due to change since postgresql 9.0 for handling them (bytea_output parameter), my programs only work with jdbc-9.3.1101 (and no longer with jdbc-8.4-703).So, I must use 9.3.1101. However, what does the sentence "I was recommending that for testing purposes only" by Lachezar means ? How to increase performance of server side prepared statements ?Le Vendredi 28 mars 2014 10h50, Lachezar Dobrev <l.dobrev@gmail.com> a écrit :For the record: I was recommending that for testing purposes only, to check if the reduced performance is related to protocol version (I believe there is a significant change it might).2014-03-27 21:52 GMT+02:00 Dave Cramer <davecramer@gmail.com>:I wanted to avoid that recommendation, but since it is out there. protocolVersion reverts to the same protocol as 8.4 uses which does not use server side prepared statementsAnd I would expect it to speed up, or at least be the same as 8.4.7DaveOn Thu, Mar 27, 2014 at 1:45 PM, Edson Richter <edsonrichter@hotmail.com> wrote:Em 27/03/2014 13:58, Lachezar Dobrev escreveu:What to expect from "protocolVersoin=2"?You might want to do the same tests after adding ?protocolVersion=2 to the connection URL. i.e.: jdbc:postgresql://localhost/database?protocolVersion=2 2014-03-27 15:35 GMT+02:00 ChatPristi <cchristo_0899@yahoo.fr>:Dear All, I have a java program that does bulk insertions (with transactions and PreparedStatements) in a postgresql database. I have a performance divided between two and four when using 9.3-1100 JDBC4 and 9.3-1101 in comparison with postgresql-8.4-703 JDBC4 independently of postgresql version. Time(ms) PG 8.4.7 9.3.4 Driver 8.4-703 193390 188379 9.3.1101 349404 344344 Does anybody else saw such a difference ? What reasons can explain it ? Another point, is that the bulk insertions are prerformed by parallel processes and 9.3-110x JDBC4 drivers appear to scale poorly. Thanks for any help.
Speed up or down?
Edson--
Edson Carlos Ericksson Richter
Sistemas para Bibliotecas, Escolinhas Infantis
Projetos sob medida para sua empresaCelular: (51) 9318-9766
(51) 8585-0796"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho original"
- Albert Einstein
My apologize, my previous test was wrong. It was old jars. So the time is still improved but remain less than with jdbc-8.4: 223528 ms ( jdbc-9.3) vs 190413 ms ( jdbc-8.4) .
Le Vendredi 28 mars 2014 11h26, ChatPristi <cchristo_0899@yahoo.fr> a écrit :
Thank you very much for your help and explanations.
Indeed, the Lachezar's recommendation ( jdbc:postgresql://localhost/database?protocolVersion=2) restores the speed: 190413 ms with jdbc-9.3.1101 and postgresql 9.3.4.
As I used bytea and due to change since postgresql 9.0 for handling them (bytea_output parameter), my programs only work with jdbc-9.3.1101 (and no longer with jdbc-8.4-703).
So, I must use 9.3.1101. However, what does the sentence "I was recommending that for testing purposes only" by Lachezar means ? How to increase performance of server side prepared statements ?
Le Vendredi 28 mars 2014 10h50, Lachezar Dobrev <l.dobrev@gmail.com> a écrit :
For the record: I was recommending that for testing purposes only, to check if the reduced performance is related to protocol version (I believe there is a significant change it might).
2014-03-27 21:52 GMT+02:00 Dave Cramer <davecramer@gmail.com>:
I wanted to avoid that recommendation, but since it is out there. protocolVersion reverts to the same protocol as 8.4 uses which does not use server side prepared statementsAnd I would expect it to speed up, or at least be the same as 8.4.7DaveOn Thu, Mar 27, 2014 at 1:45 PM, Edson Richter <edsonrichter@hotmail.com> wrote:Em 27/03/2014 13:58, Lachezar Dobrev escreveu:What to expect from "protocolVersoin=2"?You might want to do the same tests after adding ?protocolVersion=2 to the connection URL. i.e.: jdbc:postgresql://localhost/database?protocolVersion=2 2014-03-27 15:35 GMT+02:00 ChatPristi <cchristo_0899@yahoo.fr>:Dear All, I have a java program that does bulk insertions (with transactions and PreparedStatements) in a postgresql database. I have a performance divided between two and four when using 9.3-1100 JDBC4 and 9.3-1101 in comparison with postgresql-8.4-703 JDBC4 independently of postgresql version. Time(ms) PG 8.4.7 9.3.4 Driver 8.4-703 193390 188379 9.3.1101 349404 344344 Does anybody else saw such a difference ? What reasons can explain it ? Another point, is that the bulk insertions are prerformed by parallel processes and 9.3-110x JDBC4 drivers appear to scale poorly. Thanks for any help.
Speed up or down?
Edson--
Edson Carlos Ericksson Richter
Sistemas para Bibliotecas, Escolinhas Infantis
Projetos sob medida para sua empresaCelular: (51) 9318-9766
(51) 8585-0796"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho original"
- Albert Einstein