Zabbix性能调优：MySQL表分区

注意：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 -h $DBHost -P $DBPort -u $DBUser -p$DBPasswd $DB -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) */

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