Alan DeKok wrote:

Angelos Karageorgiou wrote:

...

Has anyone had the time to do a DB performance comparison for heavily loaded freeradius servers ?

If your server is busy enough to be heavily loaded, you need multiple machines to maintain quality service. Once you have multiple machines, DB performance matters a lot less, because the load is spread across multiple machines.

For DB specific issues, look for DB performance on google. PostgreSQL usually has better performance than MySQL. The application using the DB (radius, web, etc.) has very little effect on DB performance.

However if you do choose to use MySQL, setting up query caching properly will have a huge (positive) impact on performance. Same data being read out of the database four times, per authentication session .... Clustering is a good idea too, though it's not a good idea to run an SQL server / SQL cluster node / LDAP directory server on the same box as FreeRADIUS as it will almost always have a negative impact on performance. -- Arran Cudbard-Bell (A.Cudbard-Bell@sussex.ac.uk) Authentication, Authorisation and Accounting Officer Infrastructure Services | ENG1 E1-1-08 University Of Sussex, Brighton EXT:01273 873900 | INT: 3900

Hi,

Let me elucidate my situation a bit, the organization where I am currently employed is split into factions. The faction I run is heavily OSS friendly , the application development/ DBA faction is not!

yes. i can imagine. I'd always imagine what it could be like to setup your FR/MySQL solution and have it ticking away, then letting them go ahead with the IAS/Oracle/Consultancy route... but I feel its unethical (waste of money, waste of time, wrong tools etc). however, if they DO want to pay 50k EUR then let them - FreeRADIUS, MySQL + Linux could always do with some hard cash to developer time! ;-)

What I need is proof positive that mysql / postgresql is at least as good as oracle for a radius DB.

pah. can of worms and a mix of politics. there are PLENTY of articles that feature such details and reports. the issue of FreeRADIUS is nothing here - the importance is how each database scales, how many trans/sec it can do. how you can configure failover/redundancy/hot-spare etc. but support is a major factor in many context. alan

Angelos Karageorgiou wrote:

I have deployed freeradius with mysql backends in the past with great success (100K users etc.) but the current people being insecure prefer to fork out 50K euros / year for oracle RAC licenses instead of looking into an "unsupported" platform

That is FUD from people who are biased towards large companies, and are biased against small companies. There are any number of companies offering support for MySQL, PostgreSQL, FreeRADIUS, or any other open source software.

What I need is proof positive that mysql / postgresql is at least as good as oracle for a radius DB.

I know of multiple sites with 10-15 million users who are using FreeRADIUS and MySQL. My experience is that some people get *better* support from MySQL and PostgreSQL than form Oracle. i.e. If you're spending a million dollars a year for Oracle support, you're a medium-sized customer. If you're spending 10K a year for MySQL support, you're a medium sized customer. On top of that, MySQL && PostgreSQL give you source, so you can hire *anyone* to find & fix a bug for you. If Oracle is slow answering your support questions, there's *nothing* else you can do... except move to an OSS platform.

BTW , I have been using freeradius for a number of years , I would like to thank the developers for an awesome product

It's going to get better. :) Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog

Hi,

Unless you are doing monthly or yearly summary reports which can take a lightly loaded DB server and peg it for minutes at a time...

The lesson.. Keep a second DB for reporting :-)

or use a DB that can handle non-blocking selects? <ducks> ;-) alan

Alan is right , under www.unix.gr you can find some stats phps I wrote for FR+mysql , small reports are fine indeed, large multi table selects with mYIsam tables lock the tables and freeradius starts timing requests out. The reports are nice though :-) You see , I know FR,mysql,posgres,postfix and all the OSS incantations I need a power point presentation of all this knowledge, and no Peter I cannot hire you. O/H Peter Nixon έγραψε:

On Thu 17 May 2007, A.L.M.Buxey@lboro.ac.uk wrote:

...

Hi,

...

Unless you are doing monthly or yearly summary reports which can take a lightly loaded DB server and peg it for minutes at a time...

The lesson.. Keep a second DB for reporting :-)

or use a DB that can handle non-blocking selects? <ducks> ;-)

I use PostgreSQL... I didnt say that it stops the DB or queries.. Just that it "works" it in a way the FreeRADIUS will not. If the report is big enough and you have a heavy enough RADIUS load, it _can_ be enough load to push your box over the limit and start timing out requests...

A good one for you , when using an rlm_sql module if the sql server is down the module fails to instantiate and FR does not start at all. Is there a way to force instantation of rml_sql no matter the status of the sql server. Specifically I am trying to do redundant { sql fastusers } the idea is to switch to a local file when the db is down. On a second thought can I do ? accounting { redundat { sql detail } }

Peter Nixon wrote:

On Thu 17 May 2007, Alan DeKok wrote:

...

Angelos Karageorgiou wrote:

...

Has anyone had the time to do a DB performance comparison for heavily loaded freeradius servers ?

If your server is busy enough to be heavily loaded, you need multiple machines to maintain quality service. Once you have multiple machines, DB performance matters a lot less, because the load is spread across multiple machines.

For DB specific issues, look for DB performance on google. PostgreSQL usually has better performance than MySQL. The application using the DB (radius, web, etc.) has very little effect on DB performance.

Unless you are doing monthly or yearly summary reports which can take a lightly loaded DB server and peg it for minutes at a time...

The lesson.. Keep a second DB for reporting :-)

Or use clustering :p What kind of performance are people getting in general ? On our test servers we get about 460 pap req/s using LDAP + SQL + SQL xlat for authorisation, and around 800ish when just using LDAP.... Which isn't that bad really... 1 LDAP lookup 5 sql selects and 1 sql insert per query ... Flat out using pap only and users file we only get 4600 req/s ... must be something weird with the G5s .... Would be nice if someone altered the make file to pass the G5 optimisation flags by default ;)

What kind of performance are people getting in general ?

This is a pretty small installation I guess, but to give you an idea... We have two servers; dual 3.2GHz HP DL380G4s with mirrored disks and 3Gb RAM. Both run identically configured radius instances. One of the radius servers also runs the main postgres server, the other radius server runs a postgres hot standby (slony replica). Postgres is v8.3, standard FR SQL schema. Network is ~450 Cisco heavyweight APs, ~1000 3Com 4400 switches (of which 1/3rd are doing MAC-based vlans - the rest soon) and two heavily used PPTP VPN servers. Quick disclaimer: these numbers were gathered quickly and may not be accurate, don't sell your house based on them yadda yadda. We did ~25k authentications in the last 24 hours, about a 90/10 mix of EAP-PEAP/MS-CHAP (wireless) and plain MS-CHAP (PPTP). All breakout to AD via winbind. Average EAP exchange for us is 10 packets (5 request, 5 response) and it's obviously crypto-heavy. We handled ~115k accounting packets (mix of start, interim @ 300-second intervals and stop; averaged ratio 1:3.8:1) all of which were inserted direct into the SQL db on the primary radius server - no radsqlrelay or similar. At the same time, the SQL data was replicated to the installation on the slave SQL server (i.e. 2nd radius server). We also handled about ~75k PAP requests (MAC-based vlans) on the primary radius server. Each of these used an Exec-Program (so, fork+exec) to syslog the info (different setup, no SQL there yet). Finally we dump the SQL rows for finished sessions >3 days old from the radacct table into .csv files nightly. These files average ~15-30k entries - our average daily NAS session count, in other words. The servers break even at about 3% utilisation per processor, most of which I'm confident is crypto. Basically, FreeRadius is *fast*.

Hi,

Yep. Last "production" load test I did was with Postgresql 7.x, FreeRADIUS 1.0 and my pgsql-voip.conf was on a 4GB table and I was happily pushing a steady 800 Accounting requests per second on a single CPU P4 3.0 desktop machine with a single 7200rpm PATA disk. This was around 5 years ago.

5 years ago...Pentium 4. hmmm. that would have been the rather poor Northwood P4 processor too - several functions missing from the core, small L2 cache and slow FSB.

My current production servers are single Opteron CPU SunFire 2100 machines with SATA disks. The backend DBs are the same. The RADIUS boxes never break 3% CPU load. The Postgresql servers are IO bound by the SATA disks....

dirty thought....if these are mainly queries then you *could* do the following (depending on memory and table size).... simpyl create a 4Gb tmpfs partition and use that for the database - with the real disk-based database being a synchronized DB.

Basically on a properly designed DB server, with the correct indexes for your data you are always going to be IO bound for any type of RADIUS requests that dont involve EAP (expensive crypto operations)

...and even those *might* be offloaded onto an SSL crypto acceleration card alan