Patch for SQL and SQLIPPool performance

[Peter Nixon]

On Fri 10 Aug 2007, Roy Walker wrote:
> 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).

Hmm.. I need to go through the query flow to double check this but you may be 
correct.

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

Yep. UserName is supplied by the user. CallingStationId in my deployments is 
the user's GSM number supplied by the GSM operator. Depending on the network 
type you have this will change of course. It could also be the user's MAC 
address for example.

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

ok. Interesting.


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

I am fairly sure I have already recomended that to you and many other on the 
list. DO use a separate DB instance for sqlippool! I run with a total of 150 
DB sockets assigned to FreeRADIUS (Auth (50), Acct(50) and SQLIPPool(50))

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

These modules exist for other RADIUS servers. Personally I think its a really 
messy way of doing things, and doesn't allow you to virtualise overlapping 
IP pools, but if you wish to write a FreeRADIUS module to do it we would be 
happy to have it as a 4th IP Pool module ;-)

Cheers
-- 

Peter Nixon
http://peternixon.net/