<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@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:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal>This patch has 2 things. <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Rewritten SQL queries for Postgres on the SQLIPPool.
This actually makes using the SQLIPPool possible with a lot of clients (for
Postgres at least, the FOR UPDATE was unnecessary since it is already in a
transaction block, and actually dangerous as you could leave have dead lock
scenarios). Query times dropped from 250+ ms to under 1 ms.
For my needs I had removed CallingStationId from the query and index since it
is always the same as username, but I left it in for the patch, is there really
a situation where those 2 are different?<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>There is now a configurable cache option for the 5
read-heavy tables involved in an auth request. You can of course as
the config file sales, just leave it at 0 to disable the caching.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Roy<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>****<o:p></o:p></p>
<p class=MsoNormal>Some warnings for those that are trying use SQLIPPool.
Even after optimizing the query, the performance still will not allow more than
about 10 or 20 simultaneous requests. The biggest problem I see is that
one connection is not used to finish one client request all the way through.
Ie the client requests and is auth’d against the check and reply tables,
then the SQLIPPool call is made, but all the DB connections are in use, so your
client gets a reject because the SQLIPPool call is not able to complete.
One potential fix is to setup another SQL DB for just the IPPool and so you
ensure that any connection that is handled can get an IP.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>One thought is to make an IPPool module that calls to a DHCP
server (or a pool of DHCP servers). Regardless, the IP allocation has to
be able to scale to 500 or so simultaneous IP requests.<o:p></o:p></p>
<p class=MsoNormal>****<o:p></o:p></p>
</div>
</body>
</html>