Hi Jonathan, Yes I tried deleting a months worth of data earlier, locked the table, and had to kill the query! Maybe easier to truncate during planned downtime, and then run sql from cron keeping enough days of data. Thanks for your help. Kind regards, Sophie -- Sophie Loewenthal System Engineer ITOPS / Trimble Transport & Logistics GSM:+32.471.900703 On 18-Nov-15 4:13 PM, Jonathan Gazeley wrote:

Hi Sophie,

If an Accounting-Stop record comes in and the Accounting-Start record can't be found, it just inserts the Accounting-Stop record anyway.

For what it's worth, we have a daily cron job that runs this SQL query to clear rows older than 3 months out from radius.radacct so it never gets too big. We don't stop/start radiusd when doing this.

delete from radius.radacct where acctstoptime < date_sub(now(), interval 3 month);

If your table has already reached 11 million rows then that giant query might take a long time to complete depending on the speed of your database server so you might prefer to delete old rows manually in batches to begin with (use the LIMIT keyword in your query), and then enable the cron job. Or just truncate the table, as you suggested.

Cheers, Jonathan

On 18/11/15 15:02, Sophie Loewenthal wrote:

...

Hi,

Our radacct table in mysql reached 11 million rows and we don't need this information.

Would it be safe if I stopped FreeRadius, mysql: truncate radius.radacct; started FreeRadius?

How would this affect the active sessions with Radius (e.g those who had accounting records sent back after the table had been truncated )?

Versions: freeradius-mysql-2.1.12-1.el6.x86_64 freeradius-utils-2.1.12-1.el6.x86_64 freeradius-2.1.12-1.el6.x86_64

Kind regards, Sophie

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

We use Percona with INNODB with one file per table. Still locked! On 18-Nov-15 6:12 PM, Alan Buxey wrote:

hi,

Yes I tried deleting a months worth of data earlier, locked the table, and had to kill the query!

dont use MyISAM - use InnoDB as a minimum.

...

Maybe easier to truncate during planned downtime, and then run sql from cron keeping enough days of data. yes, out of hours/quiet time crontab entry to do the required housekeeping...deleting a days worth not too bad.... eg anything older than, say 90 days...big hit the first time its run, not so the next day

....then plan migration to postgresql ;-)

alan

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html