Thread: drop in performance using jdbc driver version 9
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
Can you be specific about which meta data calls you are making ? Test case ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Tue, Oct 9, 2012 at 3:44 AM, BENHAMOU Mathieu <mathieu.benhamou@cegedim-srh.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 > >
-----Message d'origine----- De : davecramer@gmail.com [mailto:davecramer@gmail.com] De la part de Dave Cramer Envoyé : mardi 9 octobre 2012 13:06 À : BENHAMOU Mathieu Cc : pgsql-jdbc@postgresql.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 http://www.credativ.ca On Tue, Oct 9, 2012 at 3:44 AM, BENHAMOU Mathieu <mathieu.benhamou@cegedim-srh.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 > >
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@gmail.com [mailto:davecramer@gmail.com] De la part de Dave Cramer Envoyé : mardi 9 octobre 2012 13:06 À : BENHAMOU Mathieu Cc : pgsql-jdbc@postgresql.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 http://www.credativ.ca On Tue, Oct 9, 2012 at 3:44 AM, BENHAMOU Mathieu <mathieu.benhamou@cegedim-srh.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 > >
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@postgresql.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@gmail.com [mailto:davecramer@gmail.com] De la part de Dave Cramer Envoyé : mardi 9 octobre 2012 13:06 À : BENHAMOU Mathieu Cc : pgsql-jdbc@postgresql.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.benhamou@cegedim-srh.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
Hello MARCHAND, As you have confirmed with the test case the time differential is present. After reviewing the differences between the 8.4-703.jdbc & 9.1-903 source code it is clear as you indicated there is a considerable number of changes to the code for getIndexInfo() & getPrimaryKeys(). This appears to be the result of providing the correct information for those calls to collect the required information for newer versions of the database, 8,9. To try and give some answer to your questions, 1-3. Please review the source code for those different versions of the jdbc that you highlight to understand the limited respond that I may provide to your questions. Perhaps others may also help where I may be incorrect in nature. 1. If you review that code for later versions of the jdbc, 9.0xxx, it is clear that a conditional check is made to create a sql query that corresponds to the 8.3++ database and then older versions. if (connection.haveMinimumServerVersion("8.3")) A closer look at that query as compared to older database sql queries indicates it is condsiderably more complicated. Comments as such even. The time differential between 8.4-701.jdbc & 9.0-801.jdbc is most probable cause by the more complicated query, to your upgraded 9 database. The most likely reason for the 9.0dev-800.jdbc is because it does not contain the new conditional check for 8.33++ database. This I can not confirm though. 2. Though the sql query has become more complex, I do not understand where that translates to more requests. Perhaps this is a backend aspect at the database as a result of the more complex query. 3. It appears that in my opinion the more complex queries for the metadata getPrimaryKeys() & getIndexInfo() probably are the reason for the drop in performance you are seeing. You should do additional testing has indicated to confirm or others may answer more definitely. Clearly if you decide to use the 9.0dev-800jdbc or 8.4-701jdbc you may not be collecting the correct information for certain aspects of metadata for getPrimaryKeys() & getIndexInfo() in a PostgreSQL 9 database. danap. MARCHAND Franck wrote: > Hi, > > I made several tests: > > Red, the time is multiplied by 10. > ~ > ~ > ~ > > 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 > > > 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 > > Can you be specific about which meta data calls you are making ? > > Test case ? > Dave Cramer > dave.cramer(at)credativ(dot)ca > > http://www.credativ.ca > > On Tue, Oct 9, 2012 at 3:44 AM, BENHAMOU Mathieu > <mathieu.benhamou@cegedim-srh.com > <mailto:mathieu.benhamou@cegedim-srh.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