Zabbix性能调优:MySQL表分区

  sre

注意:5.1版本的mysql不支持分表(其实是支持的,需要重新编译mysql,加上参数–with-plugins=partition(支持分表功能))。
#查看记录数
select table_name,table_rows from information_schema.tables
where table_schema="zabbix"
ORDER BY table_rows desc;

#停Zabbix服务,大表备份:
CREATE table history like history_bak;
CREATE table history_uint like history_uint_bak;
RENAME table history to history_bak;
RENAME table history_uint to history_uint_bak;

#执行分表脚本
脚本地址为https://github.com/itnihao/zabbixdbpartitioning
修改脚本,只对history_uint,history这两个大表执行分区操作。
执行完成后,会生成以下6个存过:
create_next_monthly_partitions #增加月分区
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totalmonths = 3;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m' );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 MONTH),'%Y-%m-01 00:00:00'));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totalmonths THEN
LEAVE createloop;
END IF;
END LOOP;
END

create_next_partitions
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totaldays = 7;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( NEXTCLOCK, 'p%Y%m%d' );
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1 DAY),'%Y-%m-%d 00:00:00'));
CALL zabbix.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totaldays THEN
LEAVE createloop;
END IF;
END LOOP;
END

create_partition
BEGIN
DECLARE RETROWS int;
SELECT COUNT(1) INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME;

IF RETROWS = 0 THEN
SELECT CONCAT( "create_partition(", 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

create_zabbix_partitions
BEGIN
CALL zabbix.create_next_partitions("zabbix","history");
CALL zabbix.drop_old_partitions("zabbix","history");
CALL zabbix.create_next_partitions("zabbix","history_str");
CALL zabbix.drop_old_partitions("zabbix","history_str");
CALL zabbix.create_next_partitions("zabbix","history_uint");
CALL zabbix.drop_old_partitions("zabbix","history_uint");

CALL zabbix.create_next_monthly_partitions("zabbix","trends");
CALL zabbix.drop_old_monthly_partitions("zabbix","trends");
CALL zabbix.create_next_monthly_partitions("zabbix","trends_uint");
CALL zabbix.drop_old_monthly_partitions("zabbix","trends_uint");
END

drop_old_monthly_partitions
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @minmonths = 12;
SET @maxmonths = @minmonths+24;
SET @i = @maxmonths;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @minmonths THEN LEAVE droploop; END IF; END LOOP; END

drop_old_partitions
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @mindays = 90;
SET @maxdays = @mindays+4;
SET @i = @maxdays;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i <= @mindays THEN LEAVE droploop; END IF; END LOOP; END

drop_partition
BEGIN
DECLARE RETROWS int;
SELECT COUNT(1) INTO RETROWS
FROM information_schema.partitions
WHERE table_schema = SCHEMANAME AND table_name = TABLENAME AND partition_name = PARTITIONNAME;

IF RETROWS = 1 THEN
SELECT CONCAT( "drop_partition(", SCHEMANAME, ",", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME,
' DROP PARTITION ', PARTITIONNAME, ';' );
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END

新建脚本/opt/BackupShell/create_zabbix_partitions.sh,内容为:

#!/bin/bash

DBHost="192.168.1.2"
DBPort="3307"
DBUser=root
DBPasswd="xxx"
DB=zabbix
DATE=(date +%y%m%d%H%M%S)
tmpfile="/opt/logs/mysql/create_zabbix_partitions_"
DATE".log"

function smail(){
Subject=(date +%y%m%d)"zabbix_partition_report"
mailcontent=
(cat 2)
sendcontent="ZGV2b3BzQG5ldGlubi5uZXQ="
smtp="smtp.netinn.net 25"
smtp_domain="netinn.net"
FROM="devops@netinn.net"
RCPTTO=
1
username_base64="ZGV2b3BzQG5ldGlubi5uZXQ="
password_base64="VGVzdDEyMzQ1Ng=="

( for i in "ehlo smtp_domain" "AUTH LOGIN" "username_base64" "$password_base64" "MAIL FROM:<$FROM>" "RCPT TO:<$RCPTTO>" "DATA";do
echo $i
sleep 4
done
echo "Subject:<$Subject>"
echo "From:<$FROM>"
echo "To:<$RCPTTO>"
echo "<$sendcontent>"
echo "."
sleep 2
echo "quit" )|telnet smtp
}

/usr/bin/mysql --skip-column-names -B -hDBHost -P DBPort -uDBUser -pDBPasswdDB -e "CALL create_zabbix_partitions();" >>tmpfile 2>&1
today_log=
(ls /opt/logs/mysql -t |head -1)
smail xxx@xxx.com $today_log;

增加crontable,每日凌晨执行分表:
00 00 * * * /opt/BackupShell/create_zabbix_partitions.sh

执行分表程序后,查看建表语句:
show create table history;
出现分区信息即表明分区成功:
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=latin1
/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p20170226 VALUES LESS THAN (1488124800) ENGINE = InnoDB,
PARTITION p20170227 VALUES LESS THAN (1488211200) ENGINE = InnoDB,
PARTITION p20170228 VALUES LESS THAN (1488297600) ENGINE = InnoDB,
PARTITION p20170301 VALUES LESS THAN (1488384000) ENGINE = InnoDB,
PARTITION p20170302 VALUES LESS THAN (1488470400) ENGINE = InnoDB,
***
PARTITION p20170603 VALUES LESS THAN (1496505600) ENGINE = InnoDB) */

运行正常后可删掉备份数据。

LEAVE A COMMENT

Captcha Code