Syed Jahanzaib Personal Blog to Share Knowledge !

January 15, 2018

Mikrotik with Freeradius/mySQL – Trimming & Archiving RADACCT # Part-8

Filed under: freeradius — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 2:40 PM

fre


FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s>

FREERADIUS WITH MIKROTIK – Part #2  – COA

FREERADIUS WITH MIKROTIK – Part #3 – Expiration

FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding

FREERADIUS WITH MIKROTIK – Part #5 – Stale Sessions

FREERADIUS  WITH MIKROTIK – Part # 6 – External Auth Script & RADPOSTAUTH

FREERADIUS WITH MIKROTIK – Part #7  – Quota Limit

FREERADIUS WITH MIKROTIK – Part #8  – RADACCT – Trimming & Archiving < You are here


Personnel Note:

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not an ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal. And with the right search, reading, understanding logic’s, you can do all on your own. I strongly encourage to read the FR mailing list and Google

OS: Ubuntu 16.04.3 LTS / 64bit


Disclaimer:
There are some other neat-to-Perfect methods like archive users usage data from radacct table to the archive data , per user ONE row only.  This will take much lesser size , but I need to track some data therefore I duplicated the whole table. Else you can use TRIGGER on RADACCT too which can update other user table for the usage only. All depend son the scenario and requirements.



Scenario:

slow data.jpg

Radius accounting is stored in radacct table which contains the user usage related data . Over the period of time this data can take lot of space as time passes. It can also create performance related issues as well if you donot take care of it like slow or timeout queries because of hundreds of thousands of entries.

I received complains from few networks that radius is giving timeout because they were installed few years ago and there radacct table have grown enormously in size thus resulting ins slow queries and timeout in authentication specially in Radius Manager which uses external auth script to validate the user request.

Solution:

  • Use SSD disks (or RAID10), they are more performance oriented storage,
  • Adding more RAM is plus point for any database server.

We will create another table named  radacct_archive  and move accounting data older then 6 months from the radacct table to radacct_archive table.

This way queries will work faster dueto less data in radacct. Also we will delete data older then 12 months from `radacct_archive` table too so that it may not grow large later.

First we will clone the radacct table structure into new radacct_archive table.

# This is one time step.
mysql -uroot -pzaib1234 -s -e "use radius; create table radacct_archive LIKE radacct;"

Now you can create a bash script and schedule it to run DAILY in less busy timings like 5 am in the morning.

# --- Copy data from CURRENT radacct table to new radacct_archive table (for archive purposes)
mysql -uroot -pSQLPASS -s -e "use radius; INSERT INTO radacct_archive SELECT * FROM radacct WHERE acctstoptime > 0 AND date(acctstarttime) < (CURDATE() - INTERVAL 6 MONTH);"

# --- Now Delete data from CURRENT RADACCT table so that it should remain fit and smart ins size
mysql -uroot -pSQLPASS -s -e "use radius; DELETE FROM radacct WHERE acctstoptime > 0 AND date(acctstarttime) < (CURDATE() - INTERVAL 6 MONTH);"

# --- Now Delete data from RADACCT_ARCHIVE table so that it should not grow either more then we required i.e 1 Year - one year archived data is enough IMO
mysql -uroot -pSQLPASS -s -e "use radius; DELETE FROM radacct_archive WHERE date(acctstarttime) < (CURDATE() - INTERVAL 12 MONTH);"

Regard’s
Syed Jahanzaib

1 Comment »

  1. […] FREERADIUS WITH MIKROTIK – Part #8  – RADACCT Trimming & Archiving  […]

    Like

    Pingback by Mikrotik with Freeradius/mySQL # Part-1 | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 15, 2018 @ 2:42 PM


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: