Managing the RADIUS database

Steve Bertrand steve at ibctech.ca
Wed Jan 6 20:32:06 CET 2010


Alexander Clouter wrote:
> Steve Bertrand <steve at ibctech.ca> wrote:
>>>> The radacct table in the RADIUS database grows at an enormously fast
>>>> rate. Regardless of server resources, trying to search or perform
>>>> actions on this table can be a daunting task.
>>>   Yup.  In some cases, people are required to keep this data for months.
>> I have all radacct information back to 1999 readily available, and data
>> before that archived. It wasn't until ~2003 or so that I changed to
>> using SQL. It didn't take long to realize that the SQL server became
>> useless after only a few months.
>>
> I guess I am a 'small cheese' with only 2m rows in my authentication 
> table?  I am using PgSQL and have btree'd my timestamp columns, but it 
> still takes only 3.6s to pull 10k rows worth of just todays data.
> 
> I thought the point of index'ing your columns (sensibly) meant that the 
> size of the table was not meant to really be too much of a killer?

A single select isn't an issue:

select * from radacct where acctstoptime > '2010-01-01';
15633 rows in set (0.84 sec)

The problem I've noticed is in complex queries. For instance, in our
accounting system, when we display a plan for a user, it used to pull
out all radacct info for the user for an entire year, and display this
info as aggregates per each month.

This becomes a nightmare in cases where an ADSL user has a gateway that
disconnects automatically every five minutes, but auto reconnects right
after.

Another situation is if I want to mysqldump the radacct table. It takes
far less time to do this on a table with three months of data as opposed
to 12+.

So, what I do is:

- aggregate daily data into a separate table
- aggregate the daily aggregated data into a separate monthly table
- copy the radacct data to a new radacct-YYYYMM table monthly, then
remove that months data from the radacct table (generally, I keep three
months active)
- the accounting system displays the user month totals from the month
aggregated table, so it only has to select 12 rows. The month archive is
run daily

Steve




More information about the Freeradius-Users mailing list