我试图优化radacct表，该表最多可以增长到91628239行。我决定切割表的最旧部分并将其保存到存档数据库中。我需要：
将自动增量保存在radacct中
半径工作时进行切割
使所有行保持活动会话
将所有关闭会话的行移至存档数据库中的radacct
我开始为此做一个程序
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
我无法停止Radius服务器执行此步骤。我如何才能顺利完成半径调整？
也许我在优化过程中完全走错了方向？
更新
该脚本可以满足我的需求
/*
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;
更新
我已经为半径制定了这个方案
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
收到Acct-stop时，Radius CALL存档radacct。我正在我的一个生产半径服务器中测试此方案。我试图找出它是否是数据库更重的负载。
最佳答案
更好的方法是在将acctstoptime设置为非NULL值的更新上触发，从而将受影响的行移至历史表。
然后，FreeRADIUS继续使用活动表中的行集。