zabbix表分区优化

发表于 更新于 分类于 阅读次数：

Zabbix是一款非常优秀的开源监控系统。

MySQL存储过程定义

  • cat zabbixPartition.sql
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    # ======partition_create========================================================
    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 ;

    # ======partition_drop==========================================================
    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 ;


    # ======partition_maintenance===================================================
    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 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: 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');
                    IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN
                            CALL 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 ;

    # ======partition_verify========================================================
    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 ;

    #======partition_maintenance_all================================================
    DELIMITER $$
    CREATE PROCEDURE `partition_maintenance_all`(SCHEMA_NAME VARCHAR(32))
    BEGIN
                    CALL partition_maintenance(SCHEMA_NAME, 'history', 180, 24, 90);
                    CALL partition_maintenance(SCHEMA_NAME, 'history_log', 180, 24, 90);
                    CALL partition_maintenance(SCHEMA_NAME, 'history_str', 180, 24, 90);
                    CALL partition_maintenance(SCHEMA_NAME, 'history_text', 180, 24, 90);
                    CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 180, 24, 90);
                    CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 365);
                    CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 365);
    END$$
    DELIMITER ;
  • 参数说明
    • SCHEMA_NAME = 数据库
    • TABLE_NAME = 表
    • KEEP_DATA_DAYS = 要保留的分区的最大天数。所有超过此天数的分区将被删除。
    • HOURLY_INTERVAL = 分区的时间间隔(小时为单位)
    • CREATE_NEXT_INTERVALS = 创建的未来分区数

Housekeeper配置修改

1
2
3
"Administration" -> "General" -> "Housekeeping"
将History&Trends "Enable internal housekeeping"取消，勾选"Override item trend period",
将"Data storage period (in days)"的值改成和存储过程中变量KEEP_DATA_DAYS的值一致。

存储过程导入

1
/usr/bin/mysql -uroot -p zabbix < zabbixPartition.sql

crontab配置

1
00 01 01 * *  /usr/bin/mysql  -uroot -pPASSWD zabbix -e"CALL partition_maintenance_all('zabbix')" >/dev/null 2>&1

查看分区结果

1
2
mysql> use zabbix;
mysql> showcreate table history;

以上在zabbix3.0.2版本上执行通过，其它版本根据实际情况判断是否微调。。。

参考