maintenance of radacct table

[bbnlradius]

Alan,

Thank you very much for helping.
I wanted to keep around 2 to 3 months data, hence the daily partition to 
drop the old data.
But we are not sure how the app is working here. what table does it refer.

We are an isp here in Bangalore,India, have around 70k users with around 
30k concurrent users logging in to the db.
We are using DMA Softlab software which takes care of the billing.
Since I am new to the radius and its app culture, I am not sure how it 
works, if I make any changes to the db, scared that it would mess up the 
app side and the customers billing scenario.

I see that the softlab software has a maintenance script which talks 
about creating temp tables called radacct1,rm_radacct1, they are 
summarising data,populating it and then updating the data in rm_users table.
But in this script the rm_radacct table does not even have any data to 
populate, I am worried to run the script on prod.
If by any chance any one of you have worked in this similar env, please 
do help me out as to how to get this resolved.


+--------------+------------+------------+--------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | TABLE_ROWS | 
AVG_ROW_LENGTH |
+--------------+------------+------------+--------+------------+----------------+
| radius1      | radacct    | BASE TABLE | InnoDB |   38951178 
|            456 |
| radius1      | rm_radacct | BASE TABLE | InnoDB |          0 
|              0 |
+--------------+------------+------------+--------+------------+----------------+




# THIS SCRIPTS COMES WITH ABSOLUTELY NO WARRANTY! USE IT ON YOUR OWN RISK!
# ALWAYS CREATE A FULL DATABASE BACKUP BEFORE EXECUTING THIS SCRIPT!
#
# This SQL script provides data deletion functionality for FreeRadius 
accounting tables
#
# Removal of old accounting data is neccessary if radacct table stores 
too many records
# and the login process is slown down due to a high load of MySQL server
#
# Copyright 2004-2010, DMA Softlab LLC

# year to delete
# !!! EDIT THE @year PARAMETER ONLY !!!

SET @year = "2009";

# create a temporary radacct table

CREATE TABLE radacct1 (
   `username` varchar(64) NOT NULL default '',
   `acctsessiontime` int(12) default NULL,
   `acctinputoctets` bigint(20) default NULL,
   `acctoutputoctets` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# cumulate datas from the previous years from radacct -> radacct1

SET @yearlike = CONCAT(@year, "%");
SET @cumuldatetime = CONCAT(@year, "-12-31 23:59:59");

INSERT INTO radacct1 (username, acctsessiontime, acctinputoctets, 
acctoutputoctets)
SELECT username, SUM(acctsessiontime), SUM(acctinputoctets), 
SUM(acctoutputoctets)
FROM radacct
WHERE acctstarttime LIKE @yearlike
GROUP BY username;

# delete all data from radacct in the specified year

DELETE FROM radacct WHERE acctstarttime LIKE @yearlike;

# create a temporary rm_radacct table

CREATE TABLE rm_radacct1 (
   `username` varchar(64) NOT NULL default '',
   `acctsessiontime` int(12) default NULL,
   `ulbytes` bigint(20) default NULL,
   `dlbytes` bigint(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

# cumulate datas from the previous years from rm_radacct -> rm_radacct1

SET @yearlike = CONCAT(@year, "%");
SET @cumuldatetime = CONCAT(@year, "-12-31 23:59:59");

INSERT INTO rm_radacct1 (username, acctsessiontime, ulbytes, dlbytes)
SELECT username, SUM(acctsessiontime), SUM(ulbytes), SUM(dlbytes)
FROM rm_radacct
WHERE acctstarttime LIKE @yearlike
GROUP BY username;

# delete all data from rm_radacct in the specified year

DELETE FROM rm_radacct WHERE acctstarttime LIKE @yearlike;

# substract the deleted values (radacct1) from radacct

UPDATE rm_users, radacct1
SET rm_users.uptimelimit = rm_users.uptimelimit - radacct1.acctsessiontime,
     rm_users.uplimit     = rm_users.uplimit     - radacct1.acctinputoctets,
     rm_users.downlimit   = rm_users.downlimit   - 
radacct1.acctoutputoctets,
     rm_users.comblimit   = rm_users.comblimit   - 
radacct1.acctoutputoctets - radacct1.acctinputoctets
WHERE rm_users.username = radacct1.username;

# substract the deleted values (rm_radacct1) from radacct

UPDATE rm_users, rm_radacct1
SET rm_users.uptimelimit = rm_users.uptimelimit + 
rm_radacct1.acctsessiontime,
     rm_users.uplimit     = rm_users.uplimit     + rm_radacct1.ulbytes,
     rm_users.downlimit   = rm_users.downlimit   + rm_radacct1.dlbytes,
     rm_users.comblimit   = rm_users.comblimit   + rm_radacct1.dlbytes + 
rm_radacct1.ulbytes
WHERE rm_users.username = rm_radacct1.username;

# drop temporary table

DROP TABLE `radacct1`;
DROP TABLE `rm_radacct1`;









On Sunday 05 March 2017 07:17 PM, Alan DeKok wrote:
> On Mar 4, 2017, at 11:57 PM, bbnlradius <bbnlradius at bbnl.co.in> wrote:
>>  From many days we are seeing major performance issue on the radius database.
>> Seeing radacct table getting bombarded with high hits of update statement.
>> The radacct table has been daily partitioned and has around 6 months of data. The total rows is
>>     Data_length: 17732239360
>>    Index_length: 27330428928
>>       Data_free: 1644167168
>> Auto_increment: 131859985
>    That's a lot.  It explains why the database is slow.
>
>> I need to drop the old data by dropping the partitions, but we are seeing that the gb limit goes hay wire, when we do this.
>    FreeRADIUS doesn't do data limit enforcement by default.  So... the problem is in your local configuration.
>
>    You don't explain what you're doing, which would be good.  But you seem to be doing data limit enforcement based on *months* of traffic, not days.  Which means keeping months of traffic that slows down the DB.  And then when you delete the rows, the traffic summary is gone, and breaks the data limit calculations.
>
>> Even when the table was not partitioned we saw the same issue.
>> Can somebody please do help me out as to how to update the right data when the radacct table data is dropped.
>    As with many issues like this, the solution is a good database design.
>
>    Decide what you want the database to do, and then create schemas and queries to meet that need.  Summarize the data, so there's a separate table which contains the bandwidth data you need.  Make that table different from the radacct data.
>
>    i.e. create a "data limit' table, with one row per user per month.  The table should contain columns (user, date, data used). That will cause it to me much smaller than the radacct data (fewer columns).  Then, once a month, use a script to summarize the radacct data into that new table.  You can then safely delete the old radacct data.  Because the new table still has the information you need.
>
>    You'll also need to update the SQL queries to check this new table, too.
>
>    Again, this is largely a database design issue, and has little to do with FreeRADIUS.  You've done something to your database which makes it big and slow.  No amount of poking FreeRADIUS will fix your database design.
>
>    Alan DeKok.
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html