zabbix4.0 之mysql优化(Zabbix分区表)
zabbix4.0 之mysql优化(Zabbix分区表)
2019.05.27 17:41:11字数 677阅读 2,496
zabbix最大的瓶颈不在zabbix服务，而是mysql数据库的压力上，优化mysql其实就是优化zabbix的配置了。
zabbix数据库常见的优化处理方法有两种：
- 清空数据库中history,history_uint,trends_uint中的数据（这种方式非常耗费时间）
- 使用MySQL表分区来对history这种大表进行分区，但是一定要在数据量小的时候进行分区，当数据量达到好几十G设置几百G了还是采用第一种方法把数据清理了再作表分区
分表操作如下：
相关知识点：
MySQL的表分区不支持外键。Zabbix2.0以上history和trend相关的表没有使用外键，因此可以使用分区。
MySQL表分区就是将一个大表在逻辑上切分成好几个物理分片。使用MySQL表分区有以下几个好处：
1.在有些场景下可以明显增加查询性能，特别是对于那些重度使用的表如果是一个单独的分区或者好几个分区就可以明显增加查询性能，因为比起加载整张表的数据到内存，一个分区的数据和索引更容易加载到内存。查看zabbix数据的general日志，可以发现zabbix对于history相关的几张表调用是非常频繁的，所以如果要优化zabbix的数据库重点要优化history这几张大表。
2.如果查询或者更新主要是使用一个分区，那么性能提升就可以简单地通过顺序访问磁盘上的这个分区而不用使用索引和随机访问整张表。
3. 批量插入和删除执行的时候可以简单地删除或者增加分区，只要当创建分区的时候有计划的创建。ALTER TABLE操作也会很快
4.Housekeeper对于某些数据类型不在需要了。可以通过Administration->General->Housekeeping来关闭不需要的数据类型的housekeeping。比如关闭History类的housekeeping
5.当创建增加新的分区时，确保分区范围没有越界，要不然会返回错误
一个MySQL表要么完全被分区，要么一点也不要被分区。
当尝试对一个表进行大量分区时，增大open_files_limit的值
被分区的表都不支持外键，在进行分区之前需要删除外键
被分区的表不支持查询缓存
1. 查看表占用空间情况
select table_name, (data_length+index_length)/1024/1024 as total_mb, table_rows from information_schema.tables where table_schema='zabbix';
image.png
2.Zabbix大表：history，history_log，history_str，history_text，history_uint，trends，trends_uint
共有四个存储过程
partition_create - 这将在给定模式中的给定表上创建一个分区。
partition_drop - 这将删除给定模式中给定表上给定时间戳的分区。
partition_maintenance - 此功能是用户调用的。它负责解析给定的参数，然后根据需要创建/删除分区。
partition_verify - 检查给定模式中给定表上是否启用了分区。如果没有启用，它将创建一个单独的分区。
具体的脚本如下：
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