Managing the RADIUS database
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
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
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
- 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
More information about the Freeradius-Users