Re: PostgreSQL, and ODBC statement handles

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.

regards,
Hiroshi Inoue