Oct 11 2006

Archive Accounting in Freeradius

Published by at 12:00 am under Freeradius,Mysql




Logging Radius accounting into Mysql database can fill up a lot of space. Some performance issues will arise at some stage as well, especially if traffic reporting scripts are to be added to the overall system. That’s why we added a new table to log older accounting records.

Motivations

We can of course delete all records older than a certain amount of time. However, we work with ISPs and accounting can provide these 2 important features:
– Traffic reporting for customers
– Past information about IP addresses ownership
Not sure how long ISPs are supposed to keep this last information legally, but 3 months should be a good compromise. Why not just deleting data older than 3 months? It might be just personnal but I like to keep all data for future checks. Someone might complain that he’s been charged and hasn’t used the connection in the last 10 months. How would we check he’s right? We’re screwed basically…

Creating the new table

We start from a proper FreeRadius installation running with a Mysql database. Accounting is stored in the Radacct table. Mysql provides a storage engine that can handle large amount of data like logs. The ARCHIVE engine doesn’t support deletion nor indexing so queries would run slower obviously. This is a minor issue as we rarely want to access this table. On the other hand, data is compressed hence it takes very little space. You can find a very good article written by Robin Schumacher, MySQL’s Director of Product Management, at http://dev.mysql.com/tech-resources/articles/storage-engine.html. Here’s the table creation code:

CREATE TABLE `radacctold` (
  `RadAcctId` bigint(21) NOT NULL,
  `AcctSessionId` varchar(128) default NULL,
  `AcctUniqueId` varchar(32) NOT NULL default '',
  `UserName` varchar(64) NOT NULL default '',
  `Realm` varchar(64) default '',
  `NASIPAddress` varchar(15) NOT NULL default '',
  `NASPortId` int(12) default NULL,
  `NASPortType` varchar(32) default NULL,
  `AcctStartTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `AcctStopTime` datetime NOT NULL default '0000-00-00 00:00:00',
  `AcctSessionTime` int(12) default NULL,
  `AcctAuthentic` varchar(32) default NULL,
  `ConnectInfo_start` varchar(32) default NULL,
  `ConnectInfo_stop` varchar(32) default NULL,
  `AcctInputOctets` int(12) unsigned default NULL,
  `AcctOutputOctets` bigint(12) default NULL,
  `CalledStationId` varchar(50) NOT NULL default '',
  `CallingStationId` varchar(50) NOT NULL default '',
  `AcctTerminateCause` varchar(32) NOT NULL default '',
  `ServiceType` varchar(32) default NULL,
  `FramedProtocol` varchar(32) default NULL,
  `FramedIPAddress` varchar(15) NOT NULL default '',
  `AcctStartDelay` int(12) default NULL,
  `AcctStopDelay` int(12) default NULL
) ENGINE = ARCHIVE;

It is basically a copy of the original table with no index and no primary key. The storage engine differs.
Note Mysql 5.0 is required and it needs ARCHIVE storage support. It is included in pre-compiled packages on Mysql website.
Note You can check it’s available with the following statement: SHOW VARIABLES LIKE ‘have_archive’;. If missing, you might need to recompile Mysql.

Moving Data Accross

Second step is to migrate old data from the orginal to the previously created table. A simple stored procedure can do the job:

CREATE PROCEDURE radius.archive_acct()
BEGIN
  INSERT INTO radacctold
    SELECT * FROM radacct
    WHERE acctstoptime > 0
    AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
  DELETE FROM radacct
    WHERE acctstoptime > 0
    AND date(acctstarttime) < (CURDATE() - INTERVAL 3 MONTH);
END

Launch the procedure once a day and you’re sorted for a while. No maintenance needed and good performance remain. You can also modify it just to delete old records.
At the time of this writting Mysql5.1 is still in Beta version so we won’t use events to fire up the script. A simple cron job does the job in the meantime.


2 responses so far

2 Responses to “Archive Accounting in Freeradius”

  1. Tagefuetlyfalon 21 Nov 2008 at 9:14 pm

    I bet the main reason the police keep people away from a plane crash is they don?t want anybody walking in and lying down in the crash stuff, then, when somebody comes up, act like they just woke up and go, “What was that?!” (Jack Handey) 😛

  2. ezequielon 13 Nov 2012 at 12:28 am

    Take care of this statement: SHOW VARIABLES LIKE ‘have_archive’;

    For me it doesn’t work, if you copy/paste change the ” cause this are unicode diferent.

Comments RSS

Leave a Reply