Managing the RADIUS database

[Steve Bertrand]

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