Netexpertise

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 archive older Freeradius 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 saying the truth?

Creating the New Archive 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 so it takes very little space. You can find a very good article about storage engines 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 (Check it matches last Freeradius radacct table version):

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. Only the storage engine is different.
Note You can check the archive engine is available on your installation with the following statement: SHOW ENGINES;.

Moving Accounting Data Across

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.
I suggest you split the table in multiple partitions (Archive engine supports partitioning) for faster queries and get the ability to delete older data based on years. Archive Freeradius accounting is fairly easy.

Tags: Accounting, Database, Freeradius, linux, Mysql, radius

Comments RSS