title: cut/optimize radacct table (FreeRADIUS)
body: <p>I trying to optimize radacct table which grows up to 91628239 rows and growing. I decided cut a oldest part of table and putting it to archive db. I need to:</p>
  
  <ol>
  <li>save the auto_increment in radacct</li>
  <li>make a cut when radius is working</li>
  <li>leave all rows with active session</li>
  <li>move all rows with closed session to radacct in archive db</li>
  </ol>
  
  <p>I starting make a procedure for this</p>
  
  
  
  <pre class="lang-sql prettyprint-override"><code>BEGIN
  /**
   clean up radacct table procedure
  */
  
  -- create fresh radacct table same as old radacct and same auto_increment value
  CREATE TABLE db5.radacct_fresh LIKE db5.radacct;
  SELECT @my_auto_increment:=auto_increment FROM information_schema.tables WHERE table_name='radacct' AND table_schema='db5';
  SET @query = CONCAT("ALTER TABLE db5.radacct_fresh auto_increment = ", @my_auto_increment);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
  -- copy active sessions to fresh table
  INSERT INTO radacct_fresh SELECT * FROM radacct WHERE acctstoptime IS NULL;
  
  -- move radacct to db5h
  SET @query = CONCAT('ALTER TABLE db5.radacct RENAME db5h.radacct_', UNIX_TIMESTAMP());
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  END
  </code></pre>
  
  <p>I can't stop radius server to make this steps. How can I do this smoothly for radius?
  Maybe I'm moving wrong way in this optimization at all?</p>
  
  <p>UPDATE
  This script is working for my needs</p>
  
  <pre class="lang-sql prettyprint-override"><code>/*
  clean up radacct table procedure
  */
  
  -- create fresh radacct table same as old radacct and same auto_increment value
  DROP TABLE IF EXISTS db5.radacct_fresh;
  CREATE TABLE db5.radacct_fresh LIKE db5.radacct;
  
  LOCK TABLES db5.radacct WRITE, db5.radacct_fresh WRITE;
  -- make auto_increment same as in radacct
  SELECT @my_auto_increment:=auto_increment FROM information_schema.tables WHERE table_name='radacct' AND table_schema='db5';
  SET @query = CONCAT("ALTER TABLE db5.radacct_fresh auto_increment = ", @my_auto_increment);
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
  -- copy active sessions to fresh table
  INSERT INTO db5.radacct_fresh SELECT * FROM radacct WHERE acctstoptime IS NULL;
  
  -- move radacct to db5H
  SET @query = CONCAT('ALTER TABLE db5.radacct RENAME db5H.radacct_', UNIX_TIMESTAMP());
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  
  -- make fresh table as main
  ALTER TABLE db5.radacct_fresh RENAME db5.radacct;
  
  UNLOCK TABLES;
  </code></pre>
  
  <p>UPDATE</p>
  
  <p>I've made this scheme for radius</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE DEFINER=`root`@`%` PROCEDURE `archive_radacct`(AST int(11), ASEST int(12), AINOCT bigint(20), AOUTOCT bigint(20), ATERMC varchar(32), ASTD int(12), CIS varchar(50), ASESID varchar(32), UN varchar(64), NASIP int(11))
  BEGIN
  UPDATE db5.radacct SET
  acctstoptime = AST,
  acctsessiontime = ASEST,
  acctinputoctets = AINOCT,
  acctoutputoctets =AOUTOCT,
  acctterminatecause =ATERMC,
  acctstopdelay =ASTD,
  connectinfo_stop = CIS
  WHERE acctsessionid = ASESID
  AND username = UN
  AND nasipaddress = NASIP;
  
  DELETE FROM db5.radacct WHERE
  acctsessionid = ASESID
  AND username = UN
  AND nasipaddress = NASIP;
  END
  CREATE TRIGGER archive_radacct_row BEFOR DELETE ON db5.radacct FOR EACH ROW BEGIN
  INSERT INTO db5H.radacct SELECT * FROM db5.radacct WHERE radacctid = OLD.radacctid
  END
  </code></pre>
  
  <p>Radius CALL archive radacct when receiving Acct-stop. I'm testing this scheme in one of my production radius server. I'm trying to find out is it more heavy load for DB.</p>