Fév 21 2007

Archiver l’accounting sous Mysql

Publié par à 12:00 sous Freeradius,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 de les archiver.

 

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

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 Mysql 5.0 est nécessaire et doit être compilé avec le support pour le moteur "ARCHIVE". Il est inclus dans les packages pré-compilés sur le site Mysql.
Note Vous pouvez vérifier s’il est disponible grâce à la commande suivante: SHOW VARIABLES LIKE ‘have_archive’;. S’il est manquant, Mysql doit être recompilé avec le support adéquate.

 

Transfer des données

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.


No responses yet

Comments RSS

Leave a Reply