Patch for SQL and SQLIPPool performance

[Alan DeKok]

Roy Walker wrote:
> 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?

  That looks very useful.  A speedup of 250x is always good.

  As for username versus calling-station-id... yes, they may be 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.

  Why?  Doesn't the SQL database already do caching?  Re-inventing that
in the RADIUS server worries me.

> 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.

  Yes... that's a side-effect of the server design.  Each module is
independent.  We *don't* want to allocate an SQL socket for each
request, as we have no idea if/when that socket will be used.

  The solution is to increase the number of SQL sockets.  See
"num_sql_socks".  You can set this to 10, 20, or more.

> 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.

  There is no Open Source DHCP server as fast, capable, and configurable
as FreeRADIUS.  There's no DHCP server (open source or commercial) that
does 500 requests/s without taking horrible short-cuts.

  Alan DeKok.