Re: Degradation of performance when upgrade psqlodbc to 09.03.0400

Hi,

> 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.
>

I have confirmed commits in version 09.03.0100 and found that,
the below commit exactly caused the degration of perfomance.

http://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commitdiff;h=9fd39495580ada6ff7d989d19fa9f83ed3b2c82e

MSDTC is not related to my application (i'm using unixODBC). So, the other
changes in above commit made performance down, but i am not clearly what
they are.
Could you confirm for me the above commit.

If it is not a bug, is there any changing of parameters to solve this issue?

Thanks and best regards.
bocap

> Thanks and best regards.
> bocap
>
>> On Feb 5, 28 Heisei, at 07:29, Dang Minh Huong <kakalot49(at)gmail(dot)com <mailto:kakalot49(at)gmail(dot)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
>