maintenance of radacct table

[Alan DeKok]

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.