Zabbix 数据库在没有使用分区分表功能，默认使用Housekeeping（管家功能）进行删除历史数据和趋势历史记录，如果zabbix数据库使用了分区分表功能需要把Housekeeping（管理功能）关闭。Housekeeping功能监控数据量少可以使用，但监控数据量多每次执行删除旧数据会降低MySQL数据库性能，并且还会产生很多空间碎片。经常会出现警报" Zabbix housekeeper processes more than 75% busy"的告警。（zabbix_server.conf配置文件两个参数进行历史记录数据删除：间隔多久删除一次，默认单位小时HousekeepingFrequency=1，一次删除多少数据，默认单位行MaxHousekeeperDelete=5000）。
历史数据和趋势数据（history和trends表）
历史数据和趋势数据是Zabbix系统中存储所采集的监控项目数据的两种存储方式，分别为history和trends 表；
历史数据
Zabbix系统针对每个监控项在每次采集时所收集到的数据，这个数据保存Zabbix系统数据库的历史表中。因为是每次所采集到的数据都保存在历史表中，所以如果监控项的更新间隔越小，则在固定时间内所保存到历史表中的数据就越多。所以在我们监控的主机的数量较多的时候，zabbix系统每台产生的数量是非常庞大的，这对数据库是一种负担。因此建议对数据库进行分表或尽量减小历史数据的保留天数，以免给数据库系统带来很大的压力。
趋势数据
趋势数据的趋势方式是对应监控项目的历史数据在一个小时内的平均值、最大值、最小值以及这一个小时内该监控项目所采集到的数据的个数。所以不管一个监控项目的更新间隔是多少，它所对应的趋势数据在数据库中的记录都只有一条（每小时）。更新间隔越小，仅可能导致数据历史数据增大，而不会影响该监控项目在趋势表里的记录条数的。
注意
- 如果监控项目的“保留历史数据(天)”配置项被设置成0时，则数据库历史表中仅保留该监控项目所采集的最后一条数据，其它历史数据将数据将不会被会保留。而且，引用该监控项目的触发器也只能使用该项目所采集的最后数据。因此，此时如果在触发器里引用该项目时使用max、avg、min等函数据时将没有意义。
- 如果监控项目的“保留趋势数据(天)”配置项被设置成0时，则该项目在系统数据库的趋势表里将不保留任何数据。
历史数据存储表
history 存储信息类型为浮点数的监控项历史数据
history_log 存储信息类型为日志的监控项历史数据
history_str 存储信息类型为字符的监控项历史数据
history_text 存储信息类型为文本的监控项历史数据
history_uint 存储信息类型为数字(无正负)的监控项历史数据
趋势数据存储表
trends存储信息类型为浮点数的监控项趋势数据
trends_uint 存储信息类型为数字(无正负)的监控项趋势数据
清空 zabbix 数据库历史数据
代码语言：javascript复制vim truncate.sql
truncate table history;
optimize table history;
truncate table history_str;
optimize table history_str;
truncate table history_log;
optimize table history_log;
truncate table history_text;
optimize table history_text;
truncate table history_uint;
optimize table history_uint;
truncate table trends;
optimize table trends;
truncate table trends_uint;
optimize table trends_uint;
导入 truncate.sql 语句
代码语言：javascript复制mysql -uroot -p zabbix
(root@localhost)[zabbix]> source /opt/truncate.sql
分表脚本文末社区链接中下载
代码语言：javascript复制DELIMITER $$
CREATE PROCEDURE `partition_create`(SCHEMANAME VARCHAR(64), TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)
BEGIN
/*
SCHEMANAME = The DB schema in which to make changes
TABLENAME = The table with partitions to potentially delete
PARTITIONNAME = The name of the partition to create
*/
/*
Verify that the partition does not already exist
*/
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
/*
1. Print a message indicating that a partition was created.
2. Create the SQL to create the partition.
3. Execute the SQL from #2.
*/
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
/*
SCHEMANAME = The DB schema in which to make changes
TABLENAME = The table with partitions to potentially delete
DELETE_BELOW_PARTITION_DATE = Delete any partitions with names that are dates older than this one (yyyy-mm-dd)
*/
DECLARE done INT DEFAULT FALSE;
DECLARE drop_part_name VARCHAR(16);
/*
Get a list of all the partitions that are older than the date
in DELETE_BELOW_PARTITION_DATE. All partitions are prefixed with
a "p", so use SUBSTRING TO get rid of that character.
*/
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;
/*
Create the basics for when we need to drop the partition. Also, create
@drop_partitions to hold a comma-delimited list of all partitions that
should be deleted.
*/
SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME, ".", TABLENAME, " DROP PARTITION ");
SET @drop_partitions = "";
/*
Start looping through all the partitions that are too old.
*/
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
/*
1. Build the SQL to drop all the necessary partitions.
2. Run the SQL to drop the partitions.
3. Print out the table partitions that were deleted.
*/
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
/*
No partitions are being deleted, so print out "N/A" (Not applicable) to indicate
that no changes were made.
*/
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 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');
CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
SET @__interval=@__interval 1;
END LOOP;
SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS DAY), '%Y%m�000');
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;
/*
* Check if any partitions exist for the given SCHEMANAME.TABLENAME.
*/
SELECT COUNT(1) INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND TABLE_NAME = TABLENAME AND partition_name IS NULL;
/*
* If partitions do not exist, go ahead and partition the table
*/
IF RETROWS = 1 THEN
/*
* Take the current date at 00:00:00 and add HOURLYINTERVAL to it. This is the timestamp below which we will store values.
* We begin partitioning based on the beginning of a day. This is because we don't want to generate a random partition
* that won't necessarily fall in line with the desired partition naming (ie: if the hour interval is 24 hours, we could
* end up creating a partition now named "p201403270600" when all other partitions will be like "p201403280000").
*/
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', 33, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 33, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 33, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 33, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 367, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 368, 720, 12);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 368, 168, 12);
DELIMITER ;其中需要修改的地方
代码语言：javascript复制CALL partition_maintenance(SCHEMA_NAME, 'history', 33, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 33, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 33, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 33, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 367, 24, 14);
CALL partition_maintenance(SCHEMA_NAME, 'trends', 368, 720, 12);
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 368, 168, 12);
举例
('history', 33, 24, 14);
history :
33：历史数据保留时间 单位天
24：每多长时间分表 单位小时
14：每次运行脚本分多少张表 单位张
注意:分表将根据在分表过程中配置的内容删除历史表和趋势表。例如，如果已配置保留 7 天的历史记录，则分区将在第 8 天开始删除历史记录。之后，每天删除一张历史表，使数据库始终有7天的历史数据。趋势数据也是如此，如果配置保留 365 天的趋势数据，则只有在 365 天后才会开始删除旧的趋势表。
使用 SQL 脚本创建分区过程
代码语言：javascript复制mysql -u zabbix -p zabbix < partition.sql注意: SQL脚本在新的 Zabbix 安装上非常快速地创建 MySQL 分区程序，但在大型数据库上，可能会持续数小时，建议尽早对数据库进行分表操作。
手动运行
代码语言：javascript复制mysql -uzabbix -pPASSWORD zabbix -e "CALL partition_maintenance_all('zabbix')" &>/var/log/partition.log查看 mysql 分表情况
代码语言：javascript复制mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "show create table historyG"
mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "show create table history_uintG"
mysql 存储目录下查看分表情况
代码语言：javascript复制cd /mariadb-data/mysql/zabbix
ls -alh
计划任务 crontab
代码语言：javascript复制crontab -e
01 01 * * * /usr/bin/mysql -uzabbix -pPASSWORD zabbix -e"CALL partition_maintenance_all('zabbix')" &>/var/log/partition.logZabbix 前端关闭管家功能（housekeeping）
Zabbix 社区数据库分区链接
https://bestmonitoringtools.com/zabbix-partitioning-tables-on-mysql-database/
