Обсуждение: 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
thatit 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
phenomenonis not reproduced in version 09.00.0310. 
>
> I have confirmed release note. I thought changing of UseServerSidePrepare's default value made this perfomance
problembut 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

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
thatit 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
phenomenonis not reproduced in version 09.00.0310. 
>>
>> I have confirmed release note. I thought changing of UseServerSidePrepare's default value made this perfomance
problembut 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
>

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.

Hi Inoue-san,

Thanks for response.

And so sorry for disturbing you at this time.

Please ask if you need aditional information.

Hi Dang,

On 2016/02/09 20:13, Dang Minh Huong wrote:

Hi Inoue-san,

Thanks for response.

And so sorry for disturbing you at this time.

Please ask if you need aditional information.

Are your applications multi-threaded or multi-process?
How many connections do your applications use?

Hi Inoue-san,

Hi Dang,

On 2016/02/09 20:13, Dang Minh Huong wrote:

Hi Inoue-san,

Thanks for response.

And so sorry for disturbing you at this time.

Please ask if you need aditional information.

Are your applications multi-threaded or multi-process?
How many connections do your applications use?

It is about 5 connections. And one of them do almost task.

DB lock wait also not occured as my confirm.

Multi-thread is used but we do not using multi-thread in the same connection.

# multi-process is not used in the app.

Thanks and best regards,

bocap

regards,
Hiroshi Inoue

Hi Inoue-san,

Sorry for my lately response.

I am not remember exactly now, but they are like below.

# only connection related parameters 

odbc.ini

[PostgreSQL]

Driver              = PostgreSQLDriver

Database            = testdb

Servername          = localhost

UserName            = postgres

Password            = postgres

Port                = 5432

odbcinst.ini

[PostgreSQLDriver]

Description         = PostgreSQL Driver

Driver              = [path to libodbcpsqlw.so file]

Thanks and best regards,

bocap

On Feb 10, 28 Heisei, at 22:24, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:

Hi Dang,

Thanks.

How are you setting your ODBC.INI(and ODBCINST.INI)?

regards,
Hiroshi Inoue

On 2016/02/10 12:20, Dang Minh Huong wrote:

Hi Inoue-san,

Hi Dang,

On 2016/02/09 20:13, Dang Minh Huong wrote:

Hi Inoue-san,

Thanks for response.

And so sorry for disturbing you at this time.

Please ask if you need aditional information.

Are your applications multi-threaded or multi-process?
How many connections do your applications use?

It is about 5 connections. And one of them do almost task.

DB lock wait also not occured as my confirm.

Multi-thread is used but we do not using multi-thread in the same connection.

# multi-process is not used in the app.

Thanks and best regards,

bocap

regards,
Hiroshi Inoue

Hi Dang,

Doesn't your odbc.ini or connection string contain SOCKET option?

regards,
Hiroshi Inoue

On 2016/02/11 8:44, Dang Minh Huong wrote:

Hi Inoue-san,

Sorry for my lately response.

I am not remember exactly now, but they are like below.

# only connection related parameters 

odbc.ini

[PostgreSQL]

Driver              = PostgreSQLDriver

Database            = testdb

Servername          = localhost

UserName            = postgres

Password            = postgres

Port                = 5432

odbcinst.ini

[PostgreSQLDriver]

Description         = PostgreSQL Driver

Driver              = [path to libodbcpsqlw.so file]

Thanks and best regards,

bocap

On Feb 10, 28 Heisei, at 22:24, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:

Hi Dang,

Thanks.

How are you setting your ODBC.INI(and ODBCINST.INI)?

regards,
Hiroshi Inoue

On 2016/02/10 12:20, Dang Minh Huong wrote:

Hi Inoue-san,

Hi Dang,

On 2016/02/09 20:13, Dang Minh Huong wrote:

Hi Inoue-san,

Thanks for response.

And so sorry for disturbing you at this time.

Please ask if you need aditional information.

Are your applications multi-threaded or multi-process?
How many connections do your applications use?

It is about 5 connections. And one of them do almost task.

DB lock wait also not occured as my confirm.

Multi-thread is used but we do not using multi-thread in the same connection.

# multi-process is not used in the app.

Thanks and best regards,

bocap

regards,
Hiroshi Inoue

Hi Inoue-san,

Hi Dang,

Doesn't your odbc.ini or connection string contain SOCKET option?

odbc.ini does not contain SOCKET option.

I will check the app whether contain it in connection string or not.

And reply you soon.

Thanks and best regards.

bocap

regards,
Hiroshi Inoue

On 2016/02/11 8:44, Dang Minh Huong wrote:

Hi Inoue-san,

Sorry for my lately response.

I am not remember exactly now, but they are like below.

# only connection related parameters 

odbc.ini

[PostgreSQL]

Driver              = PostgreSQLDriver

Database            = testdb

Servername          = localhost

UserName            = postgres

Password            = postgres

Port                = 5432

odbcinst.ini

[PostgreSQLDriver]

Description         = PostgreSQL Driver

Driver              = [path to libodbcpsqlw.so file]

Thanks and best regards,

bocap

On Feb 10, 28 Heisei, at 22:24, Inoue, Hiroshi <h-inoue@dream.email.ne.jp> wrote:

Hi Dang,

Thanks.

How are you setting your ODBC.INI(and ODBCINST.INI)?

regards,
Hiroshi Inoue

On 2016/02/10 12:20, Dang Minh Huong wrote:

Hi Inoue-san,

Hi Dang,

On 2016/02/09 20:13, Dang Minh Huong wrote:

Hi Inoue-san,

Thanks for response.

And so sorry for disturbing you at this time.

Please ask if you need aditional information.

Are your applications multi-threaded or multi-process?
How many connections do your applications use?

It is about 5 connections. And one of them do almost task.

DB lock wait also not occured as my confirm.

Multi-thread is used but we do not using multi-thread in the same connection.

# multi-process is not used in the app.

Thanks and best regards,

bocap

regards,
Hiroshi Inoue

Hi Inoue-san,

Hi Inoue-san,

Hi Dang,

Doesn't your odbc.ini or connection string contain SOCKET option?

odbc.ini does not contain SOCKET option.

I will check the app whether contain it in connection string or not.

And reply you soon.

Connection string also not contain SOCKET option (only DSN is set in there).

Hi Inoue-san,

Thanks for your recomendation.
We have backed to stable version 09.00.0310 now.

9.5.0100 is required libpq version greater than 9.1 to build.
I am not yet tried but do it work fine with PostgreSQL 9.1?

Thanks and best regards,
bocap

> Hi Dang,
>
>> On 2016/02/12 12:43, Dang Minh Huong wrote:
>> Hi Inoue-san,
>>
>>> Hi Inoue-san,
>>>
>>>> Hi Dang,
>>>>
>>>> Doesn't your odbc.ini or connection string contain SOCKET option?
>>>
>>> odbc.ini does not contain SOCKET option.
>>> I will check the app whether contain it in connection string or not.
>>> And reply you soon.
>>
>> Connection string also not contain SOCKET option (only DSN is set in there).
>
> Hmm, could you try 9.5.0100 which was completely reorganized using libpq ?
>
> regards,
> Hiroshi Inoue

On 13 February 2016 15:54:53 EET, Dang Minh Huong <kakalot49@gmail.com> wrote:
>9.5.0100 is required libpq version greater than 9.1 to build.
>I am not yet tried but do it work fine with PostgreSQL 9.1?

Yeah, should work fine.

- Heikki

On 13 February 2016 15:54:53 EET, Dang Minh Huong <kakalot49@gmail.com> wrote:

9.5.0100 is required libpq version greater than 9.1 to build.

I am not yet tried but do it work fine with PostgreSQL 9.1?

Yeah, should work fine.

- Heikki

Hi Inoue-san, Heikki-san,

Sorry for continuous disturbing you in this topic.

We found the below SQLSetConnectAttr issue, which was solved in 09.05.

Follow the topic and we solved performance  problem in 09.03.0340 by do SQLSetConnectAttr after create connection.

http://postgresql.nabble.com/issue-SQLDriverConnect-will-clear-existing-connect-attribute-setting-td5832902.html#a5836129

As my confirmation, SQLSetConnectAttr issue is started from 09.03.0100. Which your "Revise MSDTC support" patch is applied.

Does your patch is related to SQLSetConnectAttr issue above? 

If yes, how it does ?

# Sorry but i just want to know the root cause.

Thanks and best regards,

bocap

Hi Dang,

I would examine it tomorrow.

regards,
Hiroshi Inoue

On 2016/02/15 18:12, Dang Minh Huong wrote:

Hi Inoue-san, Heikki-san,

Sorry for continuous disturbing you in this topic.

We found the below SQLSetConnectAttr issue, which was solved in 09.05.

Follow the topic and we solved performance  problem in 09.03.0340 by do SQLSetConnectAttr after create connection.

http://postgresql.nabble.com/issue-SQLDriverConnect-will-clear-existing-connect-attribute-setting-td5832902.html#a5836129

As my confirmation, SQLSetConnectAttr issue is started from 09.03.0100. Which your "Revise MSDTC support" patch is applied.

Does your patch is related to SQLSetConnectAttr issue above? 

If yes, how it does ?

# Sorry but i just want to know the root cause.

Thanks and best regards,

bocap

Hi Inoue-san,

I have tested some commits in revision 09.03.0100 and found that your "revise MSDTC support" patch made SQLSetConnectAttr issue.

Can you simply explain how does your patch cause that issue?

Hi Dang,

On 2016/02/16 12:40, Dang Minh Huong wrote:

Hi Inoue-san,

I have tested some commits in revision 09.03.0100 and found that your "revise MSDTC support" patch made SQLSetConnectAttr issue.

Can you simply explain how does your patch cause that issue?

MSDTC support isn't directly related to the issue.

Original mistake is I defined autocommit_public field as a member of ConnInfo.
While revising CC_xxxx functions, I found it inappropriate to call SOCK_Constructor() (and CC_conninfo_init())
 in CC_Constructor(). and I removed them from CC_Constructor(). However this leaves inconsistent status
that autocommit_public is off but transact_status(internal autocommit status) is on..
    SQLSetConnectAttr(.., SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, ..) before SQLDriverConnect()
does nothing and leaves transact_status on.

regards,
Hiroshi Inoue