maintenance of radacct table
Hi, We are running a free radius version 2.2.0. It is talking to a mariadb database having 32 gb ram with 24 core cpu. 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 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. 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. I saw a script from my colleague who had a maintenance script for radacct, it is using rm_radacct table too, but i see no rows in this table. Is this what supposed to be? Please do help me out as to how to do maintenance of the radacct table. thanks in advance Arun
On Mar 4, 2017, at 11:57 PM, bbnlradius <bbnlradius@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.
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@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
On Mar 5, 2017, at 2:22 PM, bbnlradius <bbnlradius@bbnl.co.in> wrote:
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.
All of the FreeRADIUS configuration is available in text files in the /etc/raddb directory. The Wiki contains more documentation. It's all public.
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 suggested you add a table. That won't break the RADIUS server. Changing the RADIUS configuration may be an issue, but that's what test systems are for. Do the changes in a test system to see if they work. And nothing beats *understanding* how the system works. That takes time. There's no short-cut here.
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 you don't know what the script does, you shouldn't run it.
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.
I gave specific technical suggestions for how to fix it. The main one being you have to understand the system you're running, and what changes you're making. We can't do that for you. Alan DeKok.
Again thanks in taking your precious time in helping me out. Will talk to the dev team and see how they plan on going with the maintenance. An observation done and seeking help. I see that every few mins 2 tables keep getting created every few mins. rm_onlinecm1 rm_wlan1 These tables are getting created as myiasm engine, Could you please do let me know if you have come across these creations, if yes, can u please do let me know which file has this script so that i can change it to innodb. I see the following info in the mysql.conf file. # If you want both stop and start records logged to the # same SQL table, leave this as is. If you want them in # different tables, put the start table in acct_table1 # and stop table in acct_table2 acct_table1 = "radacct" acct_table2 = "radacct"-------------------------------> If i change this to another table name, will we face any issue, will it help in a better performance? # Allow for storing data after authentication postauth_table = "radpostauth"-----------------------> I do not see any data in this table, are we missing anything, should anything be enabled to see the data. authcheck_table = "radcheck" authreply_table = "radreply"-----------------------> I do not see any data in this table, are we missing anything, should anything be enabled to see the data. groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply" # Table to keep group info usergroup_table = "radusergroup" # If set to 'yes' (default) we read the group tables # If set to 'no' the user MUST have Fall-Through = Yes in the radreply table # read_groups = yes # Remove stale session if checkrad does not see a double login deletestalesessions = yes On Monday 06 March 2017 01:14 AM, Alan DeKok wrote:
On Mar 5, 2017, at 2:22 PM, bbnlradius <bbnlradius@bbnl.co.in> wrote:
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. All of the FreeRADIUS configuration is available in text files in the /etc/raddb directory. The Wiki contains more documentation. It's all public.
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 suggested you add a table. That won't break the RADIUS server. Changing the RADIUS configuration may be an issue, but that's what test systems are for. Do the changes in a test system to see if they work.
And nothing beats *understanding* how the system works. That takes time. There's no short-cut here.
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 you don't know what the script does, you shouldn't run it.
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. I gave specific technical suggestions for how to fix it. The main one being you have to understand the system you're running, and what changes you're making. We can't do that for you.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Mar 5, 2017, at 3:15 PM, bbnlradius <bbnlradius@bbnl.co.in> wrote:
An observation done and seeking help. I see that every few mins 2 tables keep getting created every few mins. rm_onlinecm1 rm_wlan1 These tables are getting created as myiasm engine, Could you please do let me know if you have come across these creations, if yes, can u please do let me know which file has this script so that i can change it to innodb.
Those tables are not part of FreeRADIUS.
I see the following info in the mysql.conf file.
# If you want both stop and start records logged to the # same SQL table, leave this as is. If you want them in # different tables, put the start table in acct_table1 # and stop table in acct_table2 acct_table1 = "radacct" acct_table2 = "radacct"-------------------------------> If i change this to another table name, will we face any issue, will it help in a better performance?
It won't help I gave you my advice. Alan DeKok.
In my case what i am doing is as follow: I am using triggers on insert and update of the radacct table to update another table with download quota and upload quota, so that i only check the data limit of a user on a separate table which contains only 1 record per user, also i am running a cronjob script which copy the radacct sessions which has an acctstoptime not null to another table and then delete them from the radacct table, so that the radacct table will always contain the online users. i am not checking for data limit of a user on real time,so that the freeradius will only insert radacct sessions, and i am running a script every few minutes to get all users who exceeded there limit and make the action provided by the FUP rules and updating the radusergroup with the new speed. Its working perfectly without any problems!
On Mar 5, 2017, at 10:15 PM, bbnlradius <bbnlradius@bbnl.co.in> wrote:
Again thanks in taking your precious time in helping me out. Will talk to the dev team and see how they plan on going with the maintenance.
An observation done and seeking help. I see that every few mins 2 tables keep getting created every few mins. rm_onlinecm1 rm_wlan1 These tables are getting created as myiasm engine, Could you please do let me know if you have come across these creations, if yes, can u please do let me know which file has this script so that i can change it to innodb.
I see the following info in the mysql.conf file.
# If you want both stop and start records logged to the # same SQL table, leave this as is. If you want them in # different tables, put the start table in acct_table1 # and stop table in acct_table2 acct_table1 = "radacct" acct_table2 = "radacct"-------------------------------> If i change this to another table name, will we face any issue, will it help in a better performance?
# Allow for storing data after authentication postauth_table = "radpostauth"-----------------------> I do not see any data in this table, are we missing anything, should anything be enabled to see the data.
authcheck_table = "radcheck" authreply_table = "radreply"-----------------------> I do not see any data in this table, are we missing anything, should anything be enabled to see the data.
groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply"
# Table to keep group info usergroup_table = "radusergroup"
# If set to 'yes' (default) we read the group tables # If set to 'no' the user MUST have Fall-Through = Yes in the radreply table # read_groups = yes
# Remove stale session if checkrad does not see a double login deletestalesessions = yes
On Monday 06 March 2017 01:14 AM, Alan DeKok wrote:
On Mar 5, 2017, at 2:22 PM, bbnlradius <bbnlradius@bbnl.co.in> wrote: 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. All of the FreeRADIUS configuration is available in text files in the /etc/raddb directory. The Wiki contains more documentation. It's all public.
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 suggested you add a table. That won't break the RADIUS server. Changing the RADIUS configuration may be an issue, but that's what test systems are for. Do the changes in a test system to see if they work.
And nothing beats *understanding* how the system works. That takes time. There's no short-cut here.
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 you don't know what the script does, you shouldn't run it.
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. I gave specific technical suggestions for how to fix it. The main one being you have to understand the system you're running, and what changes you're making. We can't do that for you.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi Khouzam might help many people to share these scripts? On Sun, Mar 5, 2017 at 11:03 PM, khouzam yaghi <khouzam.yaghi@gmail.com> wrote:
In my case what i am doing is as follow:
I am using triggers on insert and update of the radacct table to update another table with download quota and upload quota, so that i only check the data limit of a user on a separate table which contains only 1 record per user, also i am running a cronjob script which copy the radacct sessions which has an acctstoptime not null to another table and then delete them from the radacct table, so that the radacct table will always contain the online users. i am not checking for data limit of a user on real time,so that the freeradius will only insert radacct sessions, and i am running a script every few minutes to get all users who exceeded there limit and make the action provided by the FUP rules and updating the radusergroup with the new speed.
Its working perfectly without any problems!
On Mar 5, 2017, at 10:15 PM, bbnlradius <bbnlradius@bbnl.co.in> wrote:
Again thanks in taking your precious time in helping me out. Will talk to the dev team and see how they plan on going with the maintenance.
An observation done and seeking help. I see that every few mins 2 tables keep getting created every few mins. rm_onlinecm1 rm_wlan1 These tables are getting created as myiasm engine, Could you please do let me know if you have come across these creations, if yes, can u please do let me know which file has this script so that i can change it to innodb.
I see the following info in the mysql.conf file.
# If you want both stop and start records logged to the # same SQL table, leave this as is. If you want them in # different tables, put the start table in acct_table1 # and stop table in acct_table2 acct_table1 = "radacct" acct_table2 = "radacct"-------------------------------> If i change this to another table name, will we face any issue, will it help in a better performance?
# Allow for storing data after authentication postauth_table = "radpostauth"-----------------------> I do not see any data in this table, are we missing anything, should anything be enabled to see the data.
authcheck_table = "radcheck" authreply_table = "radreply"-----------------------> I do not see any data in this table, are we missing anything, should anything be enabled to see the data.
groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply"
# Table to keep group info usergroup_table = "radusergroup"
# If set to 'yes' (default) we read the group tables # If set to 'no' the user MUST have Fall-Through = Yes in the radreply table # read_groups = yes
# Remove stale session if checkrad does not see a double login deletestalesessions = yes
On Monday 06 March 2017 01:14 AM, Alan DeKok wrote:
On Mar 5, 2017, at 2:22 PM, bbnlradius <bbnlradius@bbnl.co.in> wrote: 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. All of the FreeRADIUS configuration is available in text files in the /etc/raddb directory. The Wiki contains more documentation. It's all public.
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 suggested you add a table. That won't break the RADIUS server. Changing the RADIUS configuration may be an issue, but that's what test systems are for. Do the changes in a test system to see if they work.
And nothing beats *understanding* how the system works. That takes time. There's no short-cut here.
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 you don't know what the script does, you shouldn't run it.
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. I gave specific technical suggestions for how to fix it. The main one being you have to understand the system you're running, and what changes you're making. We can't do that for you.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/ list/users.html
participants (4)
-
Alan DeKok -
bbnlradius -
Jonathan -
khouzam yaghi