Fév 21 2007
Archiver la Table Accounting Freeradius sous Mysql
Enregistrer les sessions Radius, notamment accounting dans une base Mysql peut générer des tables de très grosse taille. Des problèmes de performance vont apparaître après un certain temps, surtout si des scripts sont créés pour générer des rapports sur le trafic utilisé par les clients. C’est pourquoi j’ai ajouté une nouvelle table pour enregistrer les vieilles données, afin d’archiver la table accounting de Freeradius.
Motivations
Je pourrais bien sûr effacer tous les enregistrements dépassant un certain temps. Toutefois, ces enregistrements peuvent fournir des informations importantes, notamment pour les FAI:
– Rapport sur le trafic des clients
– Les logs des propriétaires d’une adresse IP
Je ne suis pas sûr combien de temps les FAI sont censés garder ce type d’information légallement, mais je pense que 3 mois est un bon compromis. Pourquoi ne pas effacer les données qui ont plus de 3 mois? C’est peut-être personnel mais j’aime conserver toutes les données pour de futures vérifications. Des clients pourraient se plaindre avoir été facturés pour une connexion qu’ils n’ont pas utilisée pendant 10 mois par exemple. Comment vérifier si cela est vrai? Nous en serions dans l’incapacité évidemment…
Créer la Nouvelle Table d’Archivage
Nous partons d’une installation de FreeRadius avec une base Mysql comme backend. Les enregistrements pour l’accounting sont sauvés dans la table Radacct. Mysql propose un moteur « storage » qui permet de stoker des quantités importantes de données comme les logs. Le moteur ARCHIVE ne supporte ni la suppression ni l’indexage donc les requêtes sont forcément plus lentes à exécuter. C’est un problème mineur puisque cette table ne sera accédée que très rarement. Par contre, les données sont compressés automatiquement par le moteur pour un gain d’espace évident. Vous trouverez un article très intéressant écrit par Robin Schumacher, le directeur management de produits MySQL, à http://dev.mysql.com/tech-resources/articles/storage-engine.html. Voici le code pour la création de la table:
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;
C’est quasiment une copie de la table originale sans les indexes et la clé primaire. Seul le moteur diffère.
Note Vous pouvez vérifier que le moteur archive est disponible grâce à la commande suivante: SHOW ENGINES’;.
Transfert de la Table Accounting
La seconde étape est de migrer les vieilles données de la table originale vers celle précédemment créée. Une simple procédure interne permet d’effectuer cette tâche:
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
Il suffit d’exécuter cette procédure une fois par jour et vous êtes sortis d’affaire. Aucune maintenance n’est nécessaire et les bonnes performances de la base sont maintenues. Vous pouvez également modifier cette procédure pour effacer les vieux enregistrements sur une base journalière.
Au moment où cet article a été écrit, Mysql5.1 est toujours en version Beta, c’est pourquoi je n’utilise pas les fonctions évènementielles pour lancer le script. Une simple ligne dans cron fera le même travail en attendant.
Je suggère de segmenter la table en différentes partitions (Le moteur Archive supporte le partitionnement) pour des requêtes plus rapides et avoir la possibilité de supprimer les données par année. Archiver la table accounting Freeradius est simple et rapide.