Re: Degradation of performance when upgrade psqlodbc to 09.03.0400

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. Something to
do with autocommit, perhaps?

- Heikki