Patch for SQL and SQLIPPool performance
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. ****
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.
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/
participants (3)
-
Alan DeKok -
Peter Nixon -
Roy Walker