zabbix数据库优化

参考文档：
https://www.zabbix.org/wiki/Docs/howto/mysql_partition
https://www.kancloud.cn/devops-centos/centos-linux-devops/375488

my.cnf的参数的调整要根据实际应用和长期使用中慢慢去调整，此处主要是创建四个存储过程：
create，drop，maintenance，verify去对zabbix几个大表去做处理，

大表如下：

创建脚本如下：

cat partition_call.sqlDELIMITER $$
CREATE PROCEDURE `partition_create`(SCHEMANAME varchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)
BEGIN/*SCHEMANAME = The DB schema in which to make changesTABLENAME = The table with partitions to potentially deletePARTITIONNAME = The name of the partition to create*//*Verify that the partition does not already exist*/DECLARE RETROWS INT;SELECT COUNT(1) INTO RETROWSFROM information_schema.partitionsWHERE 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 changesTABLENAME = The table with partitions to potentially deleteDELETE_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 datein DELETE_BELOW_PARTITION_DATE.  All partitions are prefixed witha "p", so use SUBSTRING TO get rid of that character.*/DECLARE myCursor CURSOR FORSELECT partition_nameFROM information_schema.partitionsWHERE 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 thatshould 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: LOOPFETCH myCursor INTO drop_part_name;IF done THENLEAVE 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 indicatethat 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)
BEGINDECLARE 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: LOOPIF @__interval > CREATE_NEXT_INTERVALS THENLEAVE 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) THENCALL 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))
BEGINDECLARE 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 RETROWSFROM information_schema.partitionsWHERE 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 querySET @__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 queryPREPARE STMT FROM @__PARTITION_SQL;EXECUTE STMT;DEALLOCATE PREPARE STMT;END IF;
END$$
DELIMITER ;

调用存储过程去清理，当然也可以用python去写脚本去处理，这边觉得shell更加简单直接

cat partition_all.sqlDELIMITER $$
CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
BEGINCALL partition_maintenance(SCHEMA_NAME, 'history', 90, 24, 14);CALL partition_maintenance(SCHEMA_NAME, 'history_log', 90, 24, 14);CALL partition_maintenance(SCHEMA_NAME, 'history_str', 90, 24, 14);CALL partition_maintenance(SCHEMA_NAME, 'history_text', 90, 24, 14);CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 90, 24, 14);CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);
END$$
DELIMITER ;

由于需要监控到每天的存储过程是否调用，有两个思路，一个是记录到日志，另外一个是邮件告警，我这边采用的是邮件通知，部署脚本如下

#!/bin/bash
mkdir -p /usr/local/msmtp/etc
cat > /root/.msmtprc << EOF
host mail.xxxx.com
tls off
auth plain
from zabbix@xxxx.com
user zabbix
password xxxxxxEOFcat > /root/.muttrc << EOF
set sendmail="/usr/local/msmtp/bin/msmtp"
set use_from=yes
set from=zabbix@xxxx.com
set envelope_from=yesEOFcat >  /usr/local/msmtp/etc/msmtprc  << EOF
defaults 
account zabbix
host mail.xxx.com
from zabbix@xxx.com       
auth login
port 25
tls off
user zabbix@xxx.com 
password  xxxxx
account default : zabbix
logfile /usr/local/msmtp/log/msmtp.logEOFmkdir -p /usr/local/msmtp/log
echo 'set sendmail="/usr/local/msmtp/bin/msmtp"' >>/etc/Muttrc
echo "set use_from=yes" >>/etc/Muttrc
echo 'set realname="zabbix@xxxx.com"' >>/etc/Muttrc
echo 'set editor="vim"' >>/etc/Muttrctar jxvf msmtp-1.4.30.tar.bz2
cd msmtp-1.4.30
./configure --prefix=/usr/local/msmtp
make
sleep 3 
make install
sleep 3
cd ..
tar zxvf mutt-1.5.21.tar.gz 
cd mutt-1.5.21
./configure --prefix=/usr/local/mutt
make
sleep 3
make install
sleep 3
cd ..
ln -s /usr/local/msmtp/bin/msmtp /usr/binecho  测试  | /usr/local/mutt/bin/mutt  -s  "测试"  xxx@xxxxx.com

计划任务
0 0 * * * sh /apps/sh/zabbix_partition/housekeeping.sh

cat housekeeping.sh

#!/bin/bash
MAILTO=xxx@xxxx.com
tmpfile=/tmp/housekeeping$$date >$tmpfile
/usr/bin/mysql -uzabbix -pxxxxx -hlocalhost zabbix -e "CALL partition_maintenance_all('zabbix');" >>$tmpfile 2>&1
/usr/local/mutt/bin/mutt -s "Zabbix MySql Partition Housekeeping" $MAILTO <$tmpfile
rm -f $tmpfile

此处的邮箱和密码按照自己的配置进行更改，往知悉，响应的软件包自行下载

最后要记得在全局配置中去修改housekeeping的时间来统一定义所有模版的history和trend，不然你得每个模版去配置，有点麻烦，过程如下：