MySQL Accounting records advice

[Fajar A. Nugraha]

On Wed, May 21, 2014 at 10:23 PM, Mike Poole <mpoole at pavlovmedia.com> wrote:
> Using FreeRADIUS 2.1.12 with MySQL 5.5.
>
> Not a freeradius request per se but just wanted to know if others have ideas on accounting records in sql.
>
> We've a mid-size ISP and recently turned on accounting writes to SQL for CALEA.  The table has grown enormously and now dwarfs our other databases (20+ GB in about three months).
>
> Has anyone run into this and did you solve it?  I'm thinking we should be archiving the table and clearing it out on a regular basis

Correct. Mysql's partitioning can make it easy to clear out the data
if you partition it by month (i.e. the
almost-instaneous-drop-partition vs
several-minutes-to-hours-delete-of-millions-of-rows)

> either to a file share or mysql  ARCHIVE engine db.

That depends on your company policy. On some places, deleting old data
would be sufficient as there are already billing data (processed from
summary of acct records) stored elsewhere with different retention
policy.

Instead of archive engine though, I'd recommend tokudb with its
built-in gzip compression.

-- 
Fajar