PostgreSQL, and ODBC statement handles
Reza Taheri <[email protected]> Wed, 3 May 2017 23:41:33 +0000
| Newsgroups | gmane.comp.db.postgresql.interfaces |
|---|---|
| Message-ID | <CY4PR05MB2791D721221B213FFDF133AFDE160@CY4PR05MB2791.namprd05.prod.outlook.com> |
--_000_CY4PR05MB2791D721221B213FFDF133AFDE160CY4PR05MB2791namp_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
I am running a benchmark (TPCx-V) with a single process on the client syste=
m handing all the load. Each connection to the server is in a separate thre=
ad with its own connection to PGSQL, and its own connection handle and stat=
ement handle. I am facing a contention problem with ODBC on the client sid=
e. strace and perf top show we are serializing over what appears to be acce=
sses to the ODBC statement handle. Contention goes away if I use multiple p=
rocesses instead of multiple threads within a process.
I suppose I don't understand the concept of "handles" well, but I am surpri=
sed that all the threads get the same connection handle number and the same=
statement handle number. Does that mean some data structure is shared betw=
een the different threads? Is there a way to force different statement hand=
les (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 d=
ata, but maybe someone can help me figure out what might be a very basic co=
nfiguration or parameter setting problem. I am running the following RPMs o=
n 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
--_000_CY4PR05MB2791D721221B213FFDF133AFDE160CY4PR05MB2791namp_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#0563C1" vlink=3D"#954F72">
<div class=3D"WordSection1">
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">I am running a benc=
hmark (TPCx-V) with a single process on the client system handing all the l=
oad. Each connection to the server is in a separate thread with its own con=
nection to PGSQL, and its own connection
handle and statement handle. I am facing a contention problem with O=
DBC on the client side. strace and perf top show we are serializing over wh=
at appears to be accesses to the ODBC statement handle. Contention goes awa=
y if I use multiple processes instead
of multiple threads within a process.<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p> </o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">I suppose I donR=
17;t understand the concept of “handles” well, but I am surpris=
ed that all the threads get the same connection handle number and the same =
statement handle number. 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 sugg=
ested it could be something in the
postgresql driver. I can provide detailed performance data, but maybe some=
one can help me figure out what might be a very basic configuration or para=
meter setting problem. I am running the following RPMs on RHEL 7.1:<o:p></o=
:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">postgresql93-9.3.5-=
2PGDG.rhel7.x86_64<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">postgresql93-odbc-0=
9.03.0300-1PGDG.rhel7.x86_64<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">unixODBC-2.3.1-10.e=
l7.x86_64<o:p></o:p></span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt"><o:p> </o:p></=
span></p>
<p class=3D"MsoNormal"><span style=3D"font-size:11.0pt">Thanks,<br>
Reza<o:p></o:p></span></p>
</div>
</body>
</html>
--_000_CY4PR05MB2791D721221B213FFDF133AFDE160CY4PR05MB2791namp_--