Freeradius Mysql Performance

[Fajar A. Nugraha]

On Sat, Jan 28, 2012 at 6:10 PM, Phil Mayers <p.mayers at imperial.ac.uk> wrote:
> On 01/28/2012 09:57 AM, Morteza Milani wrote:
>>
>> Hi,
>>
>> Our company is using freeradius as a VPN authentication&
>> authorization system. In worse-case say we would have 1 Million users.
>> Beside
>> scaling our market, we are going to develop an application to analyze
>> users with data mining algorithms.
>>
>> Currently we use a server with the following features:
>>
>> * RAM: 4 GB
>> * Processor: 1x E8400 3.0 GHz
>>
>> For some queries it takes 15 seconds or more to get answer from mysql
>> specially when queries work with radacct table.

Duh :P

If you use interim updates, or you use radacct heavily (e.g. using
simultaneous-use limit), or have high number of users online at the
same time (e.g most of the 1-million user), then you're seriously
underpowered.

For starters, as a (very, very, rough) estimate, think something in
the range of 2 servers, each with 2 x 4 cores, 256 GB RAM, and 16 HDD.
And that doesn't include redundancy.

Remember though, it's very rough estimate. It can be overkill, or
still seriously underpowered, depending on your implementation detail.

>>
>> Any suggestion to improve performance?
>
>
> This isn't really a FreeRADIUS question. It's an SQL question, and you want
> a trained DBA to inspect the DB.

+1
Having someone who understands how FR works, plus a competent
sysadmin, also helps.

>
> Most likely you've got too many indices, or too many rows in the table.

... and need to do your homework, and calculate sizing requirements correctly.

-- 
Fajar