Zabbix从主机收集数据,并使用历史记录和趋势表将其存储在数据库中。Zabbix历史记录保留原始数据(Zabbix收集的每个值),趋势存储合并的每小时数据,平均为最小值,平均值和最大值。
Zabbix的内务管理流程负责删除旧的趋势和历史数据。使用 SQL 删除查询从数据库中删除旧数据可能会对数据库性能产生负面影响。因此,我们中的许多人都收到了烦人的警报“”。Zabbix housekeeper processes more than 75% busy
这个问题可以通过数据库分区轻松解决。分区为每小时或每天创建表,并在不再需要时删除它们。SQL DROP 比 DELETE 语句更有效。
在我们继续之前,请备份Zabbix数据库,但如果安装是新的,则不需要备份。
在数据库服务器上下载并解压缩 SQL 脚本 “”(使用 wget 或 curl 工具):zbx_db_partitiong.sql
curl -O https://bestmonitoringtools.com/dl/zbx_db_partitiong.tar.gz tar -zxvf zbx_db_partitiong.tar.gz
脚本 “” 配置为保留 7 天的历史数据和 365 天的趋势数据 – 如果您可以接受这些设置,请转到步骤 2。zbx_db_partitiong.sql
但是,如果要更改趋势或历史记录的天数,请打开文件“zbx_db_partitiong.sql”,更改如下图所示的设置,然后保存文件。
步骤 2:使用 SQL 脚本创建分区过程
运行脚本的语法是“mysql -u ‘
现在,使用您的Zabbix数据库名称,用户名和密码运行它以创建分区过程:
mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix < zbx_db_partitiong.sql
脚本将在新的Zabbix安装上非常快速地创建MySQL分区过程,但在大型数据库上,这可能会持续数小时。
步骤 3:自动运行分区过程
我们已经创建了分区过程,但在我们运行它们之前,它们不会执行任何操作!
此步骤是最重要的,因为必须使用分区过程定期(每天)删除和创建分区!
别担心,您不必手动执行此操作。我们可以使用两种工具来完成此类任务:MySQL 事件调度程序或 Crontab – 选择您喜欢的任何工具。
配置 MySQL 事件调度程序或 Crontab 时要小心。如果您配置错误,Zabbix将停止收集数据!您会注意到,通过空图形和Zabbix日志文件中的错误“[Z3005]查询失败:[1526]表没有值…的分区”。
选项 1:使用 MySQL 事件调度程序自动管理分区(推荐)
默认情况下,MySQL 事件调度程序处于禁用状态。您需要通过在MySQL配置文件中的“[mysqld]”行之后设置“event_scheduler=ON”来启用它。
[mysqld]
event_scheduler = ON
进行更改后,请重新启动MySQL服务器以使设置生效!
sudo systemctl restart mysql
好!应该启用MySQL事件调度程序,让我们使用以下命令进行检查:
root@dbserver:~ $ mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix -e “SHOW VARIABLES LIKE ‘event_scheduler’;”
±----------------±------+
| Variable_name | Value |
±----------------±------+
| event_scheduler | ON |
±----------------±------+
现在我们可以创建一个事件,该事件将每 12 小时运行一次过程“partition_maintenance_all”。
mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix -e “CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all(‘zabbix’);”
12 小时后,使用以下命令检查事件是否已成功执行。
mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix -e “SELECT * FROM INFORMATION_SCHEMA.events\G”
选项 2:使用 Crontab 自动管理分区
如果您无法使用MySQL事件调度程序,Crontab是一个不错的选择。使用命令“sudo crontab -e”打开crontab文件,并通过在文件中的任何位置添加以下行来添加用于对Zabbix MySQL数据库进行分区的作业(每天凌晨03:30):
30 03 * * * /usr/bin/mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix -e “CALL partition_maintenance_all(‘zabbix’);” > /tmp/CronDBpartitiong.log 2>&1
保存并关闭文件。
Cron 将每天执行操作(删除旧表并创建新表)并将所有内容记录在文件 “” 中。/tmp/CronDBpartitiong.log
但是,如果您不想等待,请立即从终端运行命令:
root@dbserver:~ $ mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix -e “CALL partition_maintenance_all(‘zabbix’);” ±----------------------------------------------------------+ | msg | ±----------------------------------------------------------+ | partition_create(zabbix,history,p201910150000,1571180400) | ±----------------------------------------------------------+ ±----------------------------------------------------------+
并在之后检查分区状态:
root@dbserver:~ $ mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix -e “show create table history\G” Table: history Create Table: CREATE TABLE history ( itemid bigint(20) unsigned NOT NULL, clock int(11) NOT NULL DEFAULT ‘0’, value double(16,4) NOT NULL DEFAULT ‘0.0000’, ns int(11) NOT NULL DEFAULT ‘0’, KEY history_1 (itemid,clock) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
正如您在输出中看到的,我们为历史记录表创建了 3 个分区。
步骤4:在Zabbix前端配置内务管理
在Zabbix前端配置内务管理,如下图所示。
如果图片不言自明,以下是在Zabbix前端配置内务管理的步骤:
- 导航到“家政”部分:“管理”→“常规”→“家政”;
- 从“历史记录和趋势”部分下的“启用内部内务管理”中删除复选标记;
- 在“历史记录和趋势”部分下的“_覆盖项目趋势周期”_上打勾;
- 在“历史和趋势”部分下为趋势和历史记录定义“数据存储期”的天数(必须与数据库分区中配置的天数相同 – 如果尚未更改脚本中的默认设置,则历史记录应为 7 天,趋势应为 365 天);
- 单击“更新”按钮。
大功告成!请记住,分区将根据您在分区过程中配置的内容删除历史记录和趋势表。例如,如果已配置为保留 7 天的历史记录,则分区将在第 8 天开始删除历史记录。之后,它将每天删除一个历史记录表,以便数据库始终具有 7 天的历史数据。趋势数据也是如此,如果您配置为保留 365 天的趋势数据,则只有在 365 天后,它才会开始删除旧的趋势表。
步骤 5:更改分区设置(历史记录和趋势的天数)
有时,您最初可能会为Zabbix数据库设置太多的历史记录和趋势天数,因此磁盘空间填满得太快。或者相反的情况,您没有为历史记录或趋势配置足够的天数。那怎么办?
您无需再次运行脚本,只需创建一个将运行的新过程而不是旧过程即可。
a) 创建新的分区过程
连接到 MySQL/MariaDB 服务器:
mysql -u ‘zabbix’ -p’zabbixDBpass’ zabbix
创建一个新过程,但根据您的需要更改趋势和历史记录的天数,我将为历史记录设置 30 天,为趋势设置 400 天:
DELIMITER CREATEPROCEDUREpartitio n m aintenanc e a l l 3 0and400(SCHEM A N AMEVARCHAR(32))BEGINCALLpartitio n m aintenance(SCHEM A N AME , ′ histor y ′ ,30,24,3);CALLpartitio n m aintenance(SCHEM A N AME , ′ histor y l o g ′ ,30,24,3);CALLpartitio n m aintenance(SCHEM A N AME , ′ histor y s t r ′ ,30,24,3);CALLpartitio n m aintenance(SCHEM A N AME , ′ histor y t ex t ′ ,30,24,3);CALLpartitio n m aintenance(SCHEM A N AME , ′ histor y u in t ′ ,30,24,3);CALLpartitio n m aintenance(SCHEM A N AME , ′ trend s ′ ,400,24,3);CALLpartitio n m aintenance(SCHEM A N AME , ′ trend s u in t ′ ,400,24,3);END CREATE PROCEDURE partition_maintenance_all_30and400(SCHEMA_NAME VARCHAR(32)) BEGIN CALL partition_maintenance(SCHEMA_NAME, 'history', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_log', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_str', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_text', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 30, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'trends', 400, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 400, 24, 3); END CREATEPROCEDUREpartitionmaintenanceall30and400(SCHEMANAMEVARCHAR(32))BEGINCALLpartitionmaintenance(SCHEMANAME,′history′,30,24,3);CALLpartitionmaintenance(SCHEMANAME,′historylog′,30,24,3);CALLpartitionmaintenance(SCHEMANAME,′historystr′,30,24,3);CALLpartitionmaintenance(SCHEMANAME,′historytext′,30,24,3);CALLpartitionmaintenance(SCHEMANAME,′historyuint′,30,24,3);CALLpartitionmaintenance(SCHEMANAME,′trends′,400,24,3);CALLpartitionmaintenance(SCHEMANAME,′trendsuint′,400,24,3);END DELIMITER ;
b) 更新 MySQL 事件调度程序或 Crontab
我们已经在上一步中创建了分区过程,但它尚未激活!现在我们必须用新程序替换旧过程,该过程将定期删除和添加分区。选择以下两个选项之一,具体取决于您在Zabbix实例上配置的内容。
选项 1:更新 MySQL 事件调度程序
如果按照本教程创建了事件计划程序,请使用此命令将旧过程替换为新过程。
mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "ALTER EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all_30and400('zabbix');"
选项 2:更新 Crontab
对于那些使用 Crontab 的用户,使用命令“sudo crontab -e”打开 crontab 文件,注释掉旧的过程作业,然后添加一个新
# old procedure, still exists in the database so it can be used if needed # 30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1 30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all_30and400('zabbix');" > /tmp/CronDBpartitiong.log 2>&1
保存更改并退出 Crontab。
第 6 步:有关的信息 Zabbix 分区脚本
本指南中使用的Zabbix分区SQL脚本包含以下分区过程:
DELIMITER $$ CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int) BEGIN DECLARE RETROWS INT; SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_description >= CLOCK; IF RETROWS = 0 THEN SELECT CONCAT( "partition_create(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" ) AS msg; SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' ); PREPARE STMT FROM @sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `partition_drop`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT) BEGIN DECLARE done INT DEFAULT FALSE; DECLARE drop_part_name VARCHAR(16); DECLARE myCursor CURSOR FOR SELECT partition_name FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND CAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED) < DELETE_BELOW_PARTITION_DATE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION "); SET @drop_partitions = ""; OPEN myCursor; read_loop: LOOP FETCH myCursor INTO drop_part_name; IF done THEN LEAVE read_loop; END IF; SET @drop_partitions = IF(@drop_partitions = "", drop_part_name, CONCAT(@drop_partitions, ",", drop_part_name)); END LOOP; IF @drop_partitions != "" THEN SET @full_sql = CONCAT(@alter_header, @drop_partitions, ";"); PREPARE STMT FROM @full_sql; EXECUTE STMT; DEALLOCATE PREPARE STMT; SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, @drop_partitions AS `partitions_deleted`; ELSE SELECT CONCAT(SCHEMANAME, ".", TABLENAME) AS `table`, "N/A" AS `partitions_deleted`; END IF; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `partition_maintenance`(SCHEMA_NAME VARCHAR(32), TABLE_NAME VARCHAR(32), KEEP_DATA_DAYS INT, HOURLY_INTERVAL INT, CREATE_NEXT_INTERVALS INT) BEGIN DECLARE OLDER_THAN_PARTITION_DATE VARCHAR(16); DECLARE PARTITION_NAME VARCHAR(16); DECLARE OLD_PARTITION_NAME VARCHAR(16); DECLARE LESS_THAN_TIMESTAMP INT; DECLARE CUR_TIME INT; CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL); SET CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00')); SET @__interval = 1; create_loop: LOOP IF @__interval > CREATE_NEXT_INTERVALS THEN LEAVE create_loop; END IF; SET LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600); SET PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 'p%Y%m%d%H00'); IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP); END IF; SET @__interval=@__interval+1; SET OLD_PARTITION_NAME = PARTITION_NAME; END LOOP; SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m%d0000'); CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE); END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `partition_verify`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11)) BEGIN DECLARE PARTITION_NAME VARCHAR(16); DECLARE RETROWS INT(11); DECLARE FUTURE_TIMESTAMP TIMESTAMP; SELECT COUNT(1) INTO RETROWS FROM information_schema.partitions WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name IS NULL; IF RETROWS = 1 THEN SET FUTURE_TIMESTAMP = TIMESTAMPADD(HOUR, HOURLYINTERVAL, CONCAT(CURDATE(), " ", '00:00:00')); SET PARTITION_NAME = DATE_FORMAT(CURDATE(), 'p%Y%m%d%H00'); -- Create the partitioning query SET @__PARTITION_SQL = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " PARTITION BY RANGE(`clock`)"); SET @__PARTITION_SQL = CONCAT(@__PARTITION_SQL, "(PARTITION ", PARTITION_NAME, " VALUES LESS THAN (", UNIX_TIMESTAMP(FUTURE_TIMESTAMP), "));"); -- Run the partitioning query PREPARE STMT FROM @__PARTITION_SQL; EXECUTE STMT; DEALLOCATE PREPARE STMT; END IF; END$$ DELIMITER ; DELIMITER $$ CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32)) BEGIN CALL partition_maintenance(SCHEMA_NAME, 'history', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_log', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_str', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_text', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 7, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'trends', 365, 24, 3); CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 365, 24, 3); END$$ DELIMITER ;
来源地址:https://blog.csdn.net/xuhongshu/article/details/128898204