Re: PostgreSQL, and ODBC statement handles

Hi Reza,

On 2017/05/04 17:15, Inoue, Hiroshi wrote:
> Hi Reza,
>
> On 2017/05/04 16:57, Reza Taheri wrote:
>>
>> On 5/4/17, 12:11 AM, "Inoue, Hiroshi" <h-inoue(at)dream(dot)email(dot)ne(dot)jp
>> <mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp>> wrote:
>>
>> On 2017/05/04 15:17, Reza Taheri wrote:
>>
>> Hi Hiroshi,
>>
>> In /etc/odbcinst.ini, I set “Threading = 0”. I can
>> see multiple server processes in transaction in the backend
>> server. The contention appears to be only for access to the
>> statement handle, not actual database access. For example, when
>> I replaced looping over SQLFetch(stmt) with a single
>> SQLFetchScroll(stmt, SQL_FETCH_NEXT, 0), I got better results.
>> Same with reducing the frequency of calls to SQLBindCol()
>>
>> Thanks,
>> Reza
>>
>> On 5/3/17, 10:38 PM, "Inoue, Hiroshi" <h-inoue(at)dream(dot)email(dot)ne(dot)jp
>> <mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp>> wrote:
>>
>> Hi Reza,
>>
>> How are you setting 'Threading' in odbcinst.ini?
>>
>> regards,
>> Hiroshi
>>
>> On 2017/05/04 14:08, Reza Taheri wrote:
>>
>> I am running a benchmark (TPCx-V) with a single process on
>> the client system handing all the load. Each connection to
>> the server is in a separate thread with its own connection to
>> PGSQL, and its own connection handle and statement handle. I
>> am facing a contention problem with ODBC on the client side.
>> strace and perf top show we are serializing over what appears
>> to be accesses to the ODBC statement handlepgsq. Contention
>> goes away if I use multiple processes instead of multiple
>> threads within a process.
>>
>> I suppose I don’t understand the concept of “handles” well,
>> but I am surprised that
>>
>>
>> all the threads get the same connection handle number and the
>> same statement handle number.
>>
>>
>> Hmm, strange. Can I confirm the situation?
>>
>> regards,
>> Hiroshi Inoue
>>
>> Does that mean some data structure is shared between the
>> different threads? Is there a way to force different
>> statement handles (or handle numbers???) for different
>> threads within one process? I have asked this question on the
>> ODBC mailing list, and they suggested it could be something
>> in the postgresql driver. I can provide detailed performance
>> data, but maybe someone can help me figure out what might be
>> a very basic configuration or parameter setting problem. I am
>> running the following RPMs on RHEL 7.1:
>>
>> postgresql93-9.3.5-2PGDG.rhel7.x86_64
>>
>> postgresql93-odbc-09.03.0300-1PGDG.rhel7.x86_64
>>
>> unixODBC-2.3.1-10.el7.x86_64
>>
>> Thanks,
>> Reza
>>
>> Hi Hiroshi,
>>
>> I am not sure what you mean by “Can I confirm the situation”. Is
>> there some data you would like me to collect and share?
>>
>
> I'd like to see the record of each thread what connection handle was
> allocated and where it was connected to.

Or the code how you allocate a connection handle and connect.

regards,
Hiroshi Inoue