Patch for SQL and SQLIPPool performance

[Roy Walker]

This patch has 2 things.  

 

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?

 

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.

 

Roy

 

****

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.

 

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.

****

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20070810/b7a81d07/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: rlm_sql-caching.patch.gz
Type: application/x-gzip
Size: 18149 bytes
Desc: rlm_sql-caching.patch.gz
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20070810/b7a81d07/attachment.bin>