From: | MARCHAND Franck <franck(dot)marchand(at)cegedim-srh(dot)com> |
---|---|
To: | BENHAMOU Mathieu <mathieu(dot)benhamou(at)cegedim-srh(dot)com>, Dave Cramer <pg(at)fastcrypt(dot)com> |
Cc: | "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>, ROUX Christian <christian(dot)roux(at)cegedim-srh(dot)com>, CREMIEUX Alain <alain(dot)cremieux(at)cegedim-srh(dot)com>, FORGEARD Thierry <thierry(dot)forgeard(at)cegedim-srh(dot)com> |
Subject: | Re: drop in performance using jdbc driver version 9 |
Date: | 2012-10-13 22:01:07 |
Message-ID: | 904A92815A43F54B9CEAEB40C53FDE243A2B1F3654@PEXCHCCR3.emea.cegedim.grp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Hi,
I made several tests:
Red, the time is multiplied by 10.
DataBase
V8.2.6
DataBase
V9.1
Driver
V8.4
V9.1
Driver
V8.4
V9.1
getColumns: 6048 columns
1387 ms
1379 ms
getColumns: 6253 columns
977 ms
1090 ms
getIndexInfos: 1432 indexInfos
595 ms
616 ms
getIndexInfos: 1503 indexInfos
543 ms
4204 ms
getPrimaryKeys: 1237 primaryKeys
387 ms
3330 ms
getPrimaryKeys: 1308 primaryKeys
381 ms
3612 ms
getExportedKeys: 6 exportedKeys
4295 ms
4348 ms
getExportedKeys: 6 exportedKeys
3844 ms
3851 ms
getImportedKeys: 6 importedKeys
4253 ms
4288 ms
getImportedKeys: 6 importedKeys
4092 ms
4101 ms
Total
10933 ms
13977 ms
Total
9846 ms
16869 ms
You may reproduce these tests with the attached java class.
Juste change library jdbc in your project to compare :
- postgresql-8.4-701.jdbc4.jar
- postgresql-9.1-902.jdbc4.jar
Regards,
Franck Marchand
-----Message d'origine-----
De : BENHAMOU Mathieu
Envoyé : mardi 9 octobre 2012 18:13
À : Dave Cramer
Cc : pgsql-jdbc(at)postgresql(dot)org; ROUX Christian; CREMIEUX Alain; FORGEARD Thierry; MARCHAND Franck
Objet : RE: [JDBC] drop in performance using jdbc driver version 9
Dave,
We are using the following methods:
dbmd.getColumns(null, schemaName, tableName, null); dbmd.getIndexInfo(null, schemaName, tableName, false, false); dbmd.getPrimaryKeys(null, schemaName, tableName); dbmd.getExportedKeys(null, schemaName, tableName); dbmd.getImportedKeys(null, schemaName, tableName);
where dbmd is an instance of AbstractJdbc2DatabaseMetaData (or a subclass).
Mathieu Benhamou
Cegedim-SRH
-----Message d'origine-----
De : davecramer(at)gmail(dot)com<mailto:davecramer(at)gmail(dot)com> [mailto:davecramer(at)gmail(dot)com] De la part de Dave Cramer Envoyé : mardi 9 octobre 2012 13:06 À : BENHAMOU Mathieu Cc : pgsql-jdbc(at)postgresql(dot)org<mailto:pgsql-jdbc(at)postgresql(dot)org>; ROUX Christian; CREMIEUX Alain; FORGEARD Thierry; MARCHAND Franck Objet : Re: [JDBC] drop in performance using jdbc driver version 9
Can you be specific about which meta data calls you are making ?
Test case ?
Dave Cramer
dave.cramer(at)credativ(dot)ca
On Tue, Oct 9, 2012 at 3:44 AM, BENHAMOU Mathieu <mathieu(dot)benhamou(at)cegedim-srh(dot)com<mailto:mathieu(dot)benhamou(at)cegedim-srh(dot)com>> wrote:
> Hello,
>
>
>
> We have been using postgreSQL for several years and we have just
> upgraded the jdbc driver (along with the db server) to version 9. As a
> result, we are getting poor performance with the new configuration:
> operations that took about 10 seconds using
> postgresql-8.4-701.jdbc4.jar take more than 2 minutes now.
>
>
>
> To be more accurate, our application relies on some kind of init
> function that uses select queries involving metadata and ordinary data.
>
> 1. This operation takes about 12 seconds when our application is
> running with postgresql-8.4-701.jdbc4.jar. It also takes 12 or 15
> seconds with postgresql-9.0dev-800.jdbc3.jar.
>
> However, it takes more than 120(!) seconds using
> postgresql-9.0-801.jdbc3.jar or later versions.
>
>
>
> 2. Our guess is that metadata functions have become much slower since
> 9.0-801. We also found that many more requests are generated by the
> metadata functions. About 1200 request are sent to the database server
> when running postgresql-8.4-701.jdbc4.jar, more than 2000 when running
> postgresql-9.1-902.jdbc4.jar.
>
> Methods such as get getIndexInfo() from AbstractJdbc2DatabaseMetaData
> seem to have changed a lot, but there are probably other major changes.
>
>
>
> 3. Can you confirm that changes in the metadata functions of the JDBC
> driver might explain the drop in performance ?
>
> Is there any workaround that would save us from major surgery in our code ?
>
> Can we use postgresql-9.0dev-800.jdbc3.jar instead of later versions ?
>
> Is it safe to use postgresql-8.4-701.jdbc4.jar against a 9.0 server ?
>
>
>
> Thanks for your help.
>
>
>
> Best regards,
>
>
>
> Mathieu Benhamou
>
> CEGEDIM-SRH
>
>
Attachment | Content-Type | Size |
---|---|---|
TestJdbc.java | application/octet-stream | 8.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-10-14 04:33:31 | Re: large object max size |
Previous Message | Craig Ringer | 2012-10-12 13:23:29 | Re: New participator in JDBC driver developing |