How fast can IP Pool SQL be? :) *a Shocker*

[Christiaan Rademan]

Greetings,

I was aware of it not working, but was told to continue. Used radperf to 
test performance.

Okay, fair enough, mysql is fast ;-) But how fast is it really with the 
queries SQLIPPool runs by default?

What should i be expecting? I am trying to find out whether recreating 
the wheel was all such a great idea.

Yes I recoded my own version of SQLIPPool, its running a whole lot 
faster than the one with standard queries. 25/s to 5000/s, same 
hardware, same database.

I tried to optimise the queries with the original IP Pool but it never 
really didn't help much.

So what I am actually trying to determine here is whether I stumbled 
across a great solution or something meaningless that worked around a 
problem with my specific configuration.

How should I test it? Is the standard template and schema suppose to-do 
the job? If so, would you say out of experience that I should get more 
than 25 IP pool assignments per second on the hardware I stated earlier 
in this thread.

If I did come across something nice, I would really try make an effort 
to clean it up. Maybe get others to help work on it..

I probably wont be able to release the code, but I know it works, I 
could probably give you the queries, that should give you a clue as how.

The way I am doing the queries it wont work within ippool.conf structure.

I am aware of only one problem tested under heavy load for my way of 
doing things... If a single user logs with the same username logs into 
the box more than once per second, their is a very slight possibility he 
could get handed the same IP. Only if the same user logs in. Which would 
never happen in a mobile operator. since all the users are msisdn.

So before I get all excited here, I would like to know from you, is 25/s 
very bad? Standard schema with standard queries? I assumed the stable 
code would be released with the best possible SQL indexes on the schema 
etc?












On 22/04/2012 11:35, Fajar A. Nugraha wrote:
> On Sun, Apr 22, 2012 at 1:08 PM, Timmy<moonyhk at netscape.net>  wrote:
>> On 2012-04-22 02:53 AM, Christiaan Rademan wrote:
>>> Greetings everyone,
>>>
>>> I previously had a post concerning authenticate over 2 million+ mobile
>>> subscriber users on FreeRadius. We did performance testing yet, failed but
>>> due to pressure from client we went a head with the migration.
> Well, fail to plan, plan to fail.
>
> You can use radperf/radclient for the test.
>
>>> The migration
>>> failed at this point, since the Radius Server could not hand out more than
>>> 25 IP addresses per second. Obviously this was due to slow database server /
>>> resources. FreeRadius was happy to hand out logins once the pool assignment
>>> was done on the GGSN.
>>>
> I'd say it's because of the design for allocate-find query, not
> because the db itself is slow.
>
>>> So I am wondering, I found a solution to the problem and we are now
>>> handing out IP addresses easily.. 5000+ accept-accept responses per second
>>> with framedipaddress included from a pool within SQL.
>>>
> Do you mean "I found a solution" or "I'm looking for a solution"?
>
>>> Quad Zeon, 4 core, 8 threads, 16gig ram runnning Ubuntu Linux. Is it
>>> possible to hand out that many ip addresses per second? :)
> Sure.
>
>>> The box is also
>>> running both the radiusd and mysql process using a standard storage engine.
>>> Not using NDB or anything special. Is this really an impossible task?
> No. But then again, it kinda contradict your "I found a solution"
> stamement. If you found the solution, you won't have to ask, would
> you?
>
>>> Maybe
>>> I can find out from our company if I may release the code we using to make
>>> this work.
>>>
>>> I would really like to help improve the SQLIPPool module. Since the
>>> version we were using could only do 25/s now we are over 5000/s.
> If you can, please contribute.
>
>>>
>> Migrate to IBM DB2.  There is a source of DB2 driver inside freeradius
>> source.
> I doubt that would work.
>
> IIRC the problem is that the default sql query use impiicit locks
> (i.e. SELECT ... FOR UPDATE) to make sure the allocated IP addresses
> are absolutely unique. In my case I traded uniqueness for performance
> by using randomization instead (which, most of the time, succesfully
> allocate unique IP addresses to clients).
>
> Then again, I could be wrong. If you HAVE perform a real test, and are
> able to hand out several hundreds IP/sec using the default query by
> ONLY changing the db, let me know.
>


-- 
Christiaan Rademan - JNCIE #661

Mobile: +27 83 419 2078
E-mail: christiaan.rademan at gmail.com