Just more to this as I was in a bit of a hurry this morning… Accounting data - needs to be managed. It is very unefficient to sit with a accounting table with millions and millions of records, as radius needs to run select queries against that data, that can potentially take to long to query.
Create two more tables for yourself…
mysql> DESCRIBE RadiusAcctTotals;
+-----------------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+------------+----------------+
| TotAcctId | bigint(21) | NO | PRI | NULL | auto_increment |
| UserName | varchar(64) | NO | MUL | NULL | |
| AcctDate | date | NO | MUL | 0000-00-00 | |
| ConnNum | bigint(12) | YES | | NULL | |
| ConnTotDuration | bigint(12) | YES | | NULL | |
| ConnMaxDuration | bigint(12) | YES | | NULL | |
| ConnMinDuration | bigint(12) | YES | | NULL | |
| InputOctets | bigint(12) | YES | | NULL | |
| OutputOctets | bigint(12) | YES | | NULL | |
+-----------------+-------------+------+-----+------------+----------------+
9 rows in set (0.02 sec)
mysql> DESCRIBE RadiusAcctMonthlyTotals;
+-----------------+-------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+------------+----------------+
| MTotAcctId | bigint(21) | NO | PRI | NULL | auto_increment |
| UserName | varchar(64) | NO | MUL | NULL | |
| AcctDate | date | NO | MUL | 0000-00-00 | |
| ConnNum | bigint(12) | YES | | NULL | |
| ConnTotDuration | bigint(12) | YES | | NULL | |
| ConnMaxDuration | bigint(12) | YES | | NULL | |
| ConnMinDuration | bigint(12) | YES | | NULL | |
| InputOctets | bigint(12) | YES | | NULL | |
| OutputOctets | bigint(12) | YES | | NULL | |
+-----------------+-------------+------+-----+------------+----------------+
9 rows in set (0.01 sec)
As part of your daily maintenance run, move all accounting data from the Radius Accounting table that is not active (has a acctstoptime) and older than a couple of days, to the RadiusAcctTotals tables. Then, delete the old records from the radius accounting table. Thus, your main accounting table used my radius is small, compact, and won’t give you any issues like this.
A couple of steps are done here:
- Delete entries older than 90 days, as these would be in AcctMonthlyTotals already
DELETE FROM RadiusAcctTotals WHERE AcctDate = '" . $Date_Start . "'
- Copy the days closed Accounting Data from the default Radius Accounting tables, into a summarised version of our own Accounting Data (We do this on a per user basis, rather than a per session bases which means less records)
INSERT INTO RadiusAcctTotals (UserName, AcctDate, ConnNum, ConnTotDuration, ConnMaxDuration, ConnMinDuration, InputOctets, OutputOctets) SELECT UserName, '" . $Date_Small_Start . "',COUNT(RadAcctId), SUM(AcctSessionTime), MAX(AcctSessionTime), MIN(AcctSessionTime), SUM(AcctInputOctets), SUM(AcctOutputOctets) FROM RadiusAccounting WHERE AcctStopTime >= '" . $Date_Start . "' AND AcctStopTime < '" . $Date_End . "' AND UserName LIKE '%@%' GROUP BY UserName
- Delete the Accounting Data from the Radius Accounting tables that we just copied to our own summarised Accounting Tables
DELETE FROM RadiusAccounting WHERE AcctStopTime >= '" . $Date_Start . "' AND AcctStopTime < '" . $Date_End . "' AND UserName LIKE '%@%'
At the end of the month, I normally run a query against my daily populated accounting table, and create monthly totals for my users in the monthly accounting table. At the end of the day, I can keep 100 years (if I want) worth of accounting data based on a per month usage with zero impact on my radius server…
At the end of the month, we run amongst others:
- Move a summarised (we now move from daily to monthly) accounting data from our own accounting tables to the monthly tables
INSERT INTO RadiusAcctMonthlyTotals (UserName, AcctDate, ConnNum, ConnTotDuration, ConnMaxDuration, ConnMinDuration, InputOctets, OutputOctets) SELECT UserName, '" . $Date_Start . "', SUM(ConnNum), SUM(ConnTotDuration), MAX(ConnMaxDuration), MIN(ConnMinDuration), SUM(InputOctets), SUM(OutputOctets) FROM RadiusAcctTotals WHERE AcctDate >= '" . $Date_Start . "' AND AcctDate <= '" . $Date_End . "' GROUP BY UserName
- Delete our daily stats the stats that we have in our monthly table
DELETE FROM RadiusAcctTotals WHERE AcctDate >= '" . $Date_Start . "' AND AcctDate <= '" . $Date_End . "'
You can make your own system as you desire obviously, and you won’t be able to simply copy & paste mine 
but you get the idea. Data needs to be managed, you can’t just dump things in a database and expect them to work forever. The basic idea is that you have as little as possible records in the accounting tables used by radius, but how you achieve that can obviously be done in many, many ways - some which might suit you, some which may not.
–
C
