Re: Degradation of performance when upgrade psqlodbc to 09.03.0400

I have tested some version of psqlODBC between 09.00.0310 ~ 09.03.0400,

and found that exactly version 09.03.0100 made the  degradation of performance

in executing my batch (it is not related to replication, i found that).

I will try to find out which commit did it and how to avoid this issues.

Thanks and best regards.

bocap

On Feb 5, 28 Heisei, at 07:29, Dang Minh Huong <kakalot49@gmail.com> wrote:

Hi,

Thanks for prompt response sir.

On 04/02/16 16:54, Dang Minh Huong wrote:
I'm using psqlodbc version 09.00.0310 for my project. Recently i tried to upgrade to version 09.03.0400 and found that it is very slow in processing my batch (batch with milion of SELECT, UPDATE, INSERT statements).

[environment]

OS: Redhat 6.7 x 2 nodes

PostgreSQL 9.1.19: Synchronouse Streaming replication & hot_standby.

DB connect method: psqlodbc/unixODBC.

[results of performance]

It took below timing to execute my batch

In version 09.00.0310: 11minute

In version 09.03.0400: 53minute

When my batch run in 09.03.0400, ps command repeatedly show below processes.

Something delay the transmitting & replaying WAL in STANDBY db node?

-----

... UPDATE waiting for 0/xxx

or

... INSERT waiting for 0/yyy

------

Note that, my replication connection is working well, and above messages just display within a second. This phenomenon is not reproduced in version 09.00.0310.

I have confirmed release note. I thought changing of UseServerSidePrepare's default value made this perfomance problem but not. I don't know what change make it slower.

Sorry for lack of infomation but, is any big changes related to performance between above versions?

I don't recall anything relevant. It seems strange that changing the ODBC driver would make any difference to the replication.

I also think that. I wonder if something missed in my environment but not found, and it is not reproduced in 9.00.0310.

Something to do with autocommit, perhaps?

Sorry but did you mean that, default of autocommit was changed to "on", between above versions?

Thanks and best regards,
bocap.

- Heikki