处理Zabbix前史数据库方法二---运用MySQL表分区51CTO博客 - 娱乐之横扫全球

处理Zabbix前史数据库方法二---运用MySQL表分区51CTO博客

2019年02月28日11时57分16秒 | 作者: 辰沛 | 标签: 分区,运用,数据 | 浏览: 110


一 运用场景描绘


在前面介绍能够通过创立新表然后导入一个月内的数据到新表,最终删去旧表的办法来处理历史数据。

可是这种办法十分消耗时间,至少是几个小时,一起也不有必要停掉zabbix server防止新的数据写入。关于需求全天不停地处理监控的运用来说,这种办法仍是不可取的。

咱们能够运用MySQL表分区来对history这种大表进行分区,可是必定要在数据量小的时分进行分区,当数据量到达好几十G设置几百G了仍是选用榜首种办法把数据整理了再作表分区



二 MySQL表分区相关知识点

MySQL的表分区不支撑外键。Zabbix2.0以上history和trend相关的表没有运用外键,因而能够运用分区。

MySQL表分区就是将一个大表在逻辑上切分红好几个物理分片。运用MySQL表分区有以下几个优点:

 在有些场景下能够明显添加查询功用,特别是关于那些重度运用的表假如是一个独自的分区或许好几个分区就能够明显添加查询功用,由于比起加载整张表的数据到内存,一个分区的数据和索引更简略加载到内存。检查zabbix数据的general日志,能够发现zabbix关于history相关的几张表调用是十分频频的,所以假如要优化zabbix的数据库要点要优化history这几张大表。

 假如查询或许更新主要是运用一个分区,那么功用提高就能够简略地通过次序拜访磁盘上的这个分区而不必运用索引和随机拜访整张表。

 批量刺进和删去履行的时分能够简略地删去或许添加分区,只需当创立分区的时分有计划的创立。ALTER TABLE操作也会很快

 

MySQL从5.1今后支撑表分区。MySQL5.6之前检查是否支撑表分区

mysql> show variables like have_partitioning;
+-+-+
| Variable_name     | Value |
+-+-+
| have_partitioning | YES   |
+-+-+
1 row in set (0.00 sec)

 MySQL5.6开端检查是否支撑表分区

 show plugins;


MySQL表分区类型

  Range partitioning

  依据取值规模将表区分红多块。每个独自分区的取值规模不能越界。例如依据日期分区或许依据其他摸个自定义字段分区。

  Other partitioning types

  其他的分区类型有hash,list和key。这儿zabbix的history类表时分运用range类型的表分区。



三 办理history类表的分区

这儿供给两种计划来办理分区:

  运用MySQL存储进程

  运用外部脚本

运用存储进程调试会比较费事,这儿引荐运用外部脚原本办理分区


PurposeData typeMaximum size
historyKeeps raw historyNumeric (float)double(16,4) - 999999999999.9999
history_uintKeeps raw historyNumeric (unsigned)bigint(20) - 264+1
history_strKeeps raw short string dataCharactervarchar(255) - 255
history_textKeeps raw long string dataTexttext - 65535
history_logKeeps raw log stringsLogtext - 65535
trendsKeeps reduced dataset (trends)Numeric (float)double(16,4) - 999999999999.9999
trends_uintKeeps reduced dataset (trends)Numeric (unsigned)bigint(20) - 264+1


数据类型是Character,Text,Log类型的的监控项是没有趋势数据的,就是在trends表中没有数据,假如要对history_str,history_text,history_log作表分区需求考虑这个问题。


Partitionning descisions

在履行为zabbix履行表分区之前有必要要考虑几个方面:

  1. 运用range partitioning就是运用依据规模的分区,一般是依据日期

  2. Housekeeper关于某些数据类型不在需求了。能够通过Administration->General->Housekeeping来封闭不需求的数据类型的housekeeping。比方封闭History类的housekeeping

  3. 监控项目装备中的History storage period (in days) 和Trend storage period (in days)将不在运用,由于老数据会依据规模整理掉。这两个值能够也应该被Administration->General->Housekeeping中设置的时间距离给重置。Housekeeping设置的时间距离应该匹配希望保存的表分区。

  4. 假如需求存储数据很长一段时间,可是磁盘空间有限,能够运用对过期的分区运用软链接。

  

mysql> show variables like have_symlink;

+-+-+

| Variable_name | Value |

+-+-+

| have_symlink  | YES   |

+-+-+

1 row in set (0.00 sec)

  可是不主张运用软链接功用,由于软衔接很难保证对任何表都作业正常。还有就是即便监控项目的housekeeping在页面封闭了,Zabbix server和web接口仍是会继续向housekeeper表写入housekeeping信息以供讲来运用。为了防止这个,能够设置

  

ALTER TABLE housekeeper ENGINE = BLACKHOLE;


A.运用MySQL存储进程和事情调度器进行分区

首要断定event scheduler敞开


mysql> SHOW GLOBAL VARIABLES LIKE event_scheduler;

+-+-+

| Variable_name   | Value |

+-+-+

| event_scheduler | ON    |

+-+-+

1 row in set (0.00 sec)

在/etc/my.cnf文中也要设置

event_scheduler=ON

Zabbix 2.2之后的版别只要几个和历史数据的大表主张分区history, history_uint, history_str, history_text, history_log, trends, trends_uint。

由于MySQL有关于运用仅有索引,主键等的内部约束。在开端分区之前需求更改一些索引

Zabbix2.2以及今后的版别

ALTER TABLE `history_log` DROP PRIMARY KEY, ADD INDEX `history_log_0` (`id`);

ALTER TABLE `history_log` DROP KEY `history_log_2`;

ALTER TABLE `history_text` DROP PRIMARY KEY, ADD INDEX `history_text_0` (`id`);

ALTER TABLE `history_text` DROP KEY `history_text_2`;



现在能够为每个表开端履行分区操作。由于分区操作通常是对现已存在的的历史数据进行分区

对每张表有必要指定分区从一个clock字段的最小值到当时时间的值。


SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_uint`;


mysql> SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_uint`;
+-+
| FROM_UNIXTIME(MIN(clock)) |
+-+
| 2016-04-30 00:00:01       |
+-+
1 row in set (44 min 7.58 sec)




mysql> SELECT FROM_UNIXTIME(MIN(clock)) FROM `history`;
+-+
| FROM_UNIXTIME(MIN(clock)) |
+-+
| 2016-04-30 00:00:01       |
+-+
1 row in set (26 min 9.16 sec)

mysql> SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_str`;
+-+
| FROM_UNIXTIME(MIN(clock)) |
+-+
| 2015-11-05 10:13:44       |
+-+
1 row in set (47.58 sec)


mysql> SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_text`;
+-+
| FROM_UNIXTIME(MIN(clock)) |
+-+
| 2016-04-30 00:00:26       |
+-+
1 row in set (0.17 sec)


mysql> SELECT FROM_UNIXTIME(MIN(clock)) FROM `trends`;
+-+
| FROM_UNIXTIME(MIN(clock)) |
+-+
| 2015-10-15 13:00:00       |
+-+
1 row in set (9 min 57.65 sec)




mysql> SELECT FROM_UNIXTIME(MIN(clock)) FROM `trends_uint`;
| FROM_UNIXTIME(MIN(clock)) |
+-+
| 2015-10-15 13:00:00       |
+-+
1 row in set (14 min 48.83 sec)





对一切要分区的表履行相同的查询操作

需求留意的是一个表一共的分区数量有约束,MySQL5.6.7之前是1024,MySQL5.6.7开端是8192

一张表要么悉数分区要么全不要分区



mysql> ALTER TABLE `history_uint` PARTITION BY RANGE ( clock)
    -> (PARTITION p2016_04_30 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-01 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_01 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-02 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_02 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-03 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_03 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-04 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_04 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-05 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_05 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-06 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_06 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-07 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_07 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-08 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_08 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-09 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_09 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-10 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_10 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-11 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_11 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-12 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_12 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-13 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05_13 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-14 00:00:00")) ENGINE = InnoDB)
    -> ;




履行完成后能够检查分区状况

mysql> show create table history_uint;
++-+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
++-+
| history_uint | CREATE TABLE `history_uint` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT 0,
  `value` bigint(20) unsigned NOT NULL DEFAULT 0,
  `ns` int(11) NOT NULL DEFAULT 0,
  KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE ( clock)
(PARTITION p2016_04_30 VALUES LESS THAN (1462032000) ENGINE = InnoDB,
 PARTITION p2016_05_01 VALUES LESS THAN (1462118400) ENGINE = InnoDB,
 PARTITION p2016_05_02 VALUES LESS THAN (1462204800) ENGINE = InnoDB,
 PARTITION p2016_05_03 VALUES LESS THAN (1462291200) ENGINE = InnoDB,
 PARTITION p2016_05_04 VALUES LESS THAN (1462377600) ENGINE = InnoDB,
 PARTITION p2016_05_05 VALUES LESS THAN (1462464000) ENGINE = InnoDB,
 PARTITION p2016_05_06 VALUES LESS THAN (1462550400) ENGINE = InnoDB,
 PARTITION p2016_05_07 VALUES LESS THAN (1462636800) ENGINE = InnoDB,
 PARTITION p2016_05_08 VALUES LESS THAN (1462723200) ENGINE = InnoDB,
 PARTITION p2016_05_09 VALUES LESS THAN (1462809600) ENGINE = InnoDB,
 PARTITION p2016_05_10 VALUES LESS THAN (1462896000) ENGINE = InnoDB,
 PARTITION p2016_05_11 VALUES LESS THAN (1462982400) ENGINE = InnoDB,
 PARTITION p2016_05_12 VALUES LESS THAN (1463068800) ENGINE = InnoDB,
 PARTITION p2016_05_13 VALUES LESS THAN (1463155200) ENGINE = InnoDB,
 PARTITION p2016_05_14 VALUES LESS THAN (1463241600) ENGINE = InnoDB,
 PARTITION p2016_05_15 VALUES LESS THAN (1463328000) ENGINE = InnoDB) */ |
++-+
1 row in set (0.00 sec)



检查MySQL数据库目下下的表文件

# ls -lh|grep history_uint
-rw-rw 1 mysql mysql 8.5K May 16 00:50 history_uint.frm
-rw-rw 1 mysql mysql  240 May 16 00:50 history_uint.par
-rw-rw 1 mysql mysql 2.5G May 15 10:44 history_uint#P#p2016_04_30.ibd
-rw-rw 1 mysql mysql 2.5G May 15 10:54 history_uint#P#p2016_05_01.ibd
-rw-rw 1 mysql mysql 2.5G May 15 11:03 history_uint#P#p2016_05_02.ibd
-rw-rw 1 mysql mysql 2.5G May 15 11:13 history_uint#P#p2016_05_03.ibd
-rw-rw 1 mysql mysql 2.5G May 15 11:23 history_uint#P#p2016_05_04.ibd
-rw-rw 1 mysql mysql 2.3G May 15 11:31 history_uint#P#p2016_05_05.ibd
-rw-rw 1 mysql mysql 112K May 15 10:34 history_uint#P#p2016_05_06.ibd
-rw-rw 1 mysql mysql 972M May 15 11:35 history_uint#P#p2016_05_07.ibd
-rw-rw 1 mysql mysql 1.0G May 15 11:38 history_uint#P#p2016_05_08.ibd
-rw-rw 1 mysql mysql 2.6G May 15 11:48 history_uint#P#p2016_05_09.ibd
-rw-rw 1 mysql mysql 2.6G May 15 11:57 history_uint#P#p2016_05_10.ibd
-rw-rw 1 mysql mysql 2.6G May 15 12:07 history_uint#P#p2016_05_11.ibd
-rw-rw 1 mysql mysql 2.6G May 15 12:17 history_uint#P#p2016_05_12.ibd
-rw-rw 1 mysql mysql 2.6G May 15 12:27 history_uint#P#p2016_05_13.ibd
-rw-rw 1 mysql mysql 2.4G May 15 20:50 history_uint#P#p2016_05_14.ibd
-rw-rw 1 mysql mysql 696M May 16 01:23 history_uint#P#p2016_05_15.ibd
-rw-rw 1 mysql mysql 1.9G May 16 15:28 history_uint#P#p2016_05_16.ibd


能够看到通过分区后的表的数据库文件由原本打个ibd文件变成了依照日期区分的多个ibd文件,一起添加了一个par文件来存储分区信息。






然后顺次对history,history_log,history_str,history_text依照每天进行分区

对trends,trends_uint依照每个月进行分区

mysql> ALTER TABLE `trends_uint` PARTITION BY RANGE ( clock)
    -> (PARTITION p2015_10 VALUES LESS THAN (UNIX_TIMESTAMP("2015-11-01 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2015_11 VALUES LESS THAN (UNIX_TIMESTAMP("2015-12-01 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2015_12 VALUES LESS THAN (UNIX_TIMESTAMP("2016-01-01 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_01 VALUES LESS THAN (UNIX_TIMESTAMP("2016-02-01 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_02 VALUES LESS THAN (UNIX_TIMESTAMP("2016-03-01 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_03 VALUES LESS THAN (UNIX_TIMESTAMP("2016-04-01 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_04 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-01 00:00:00")) ENGINE = InnoDB,
    ->  PARTITION p2016_05 VALUES LESS THAN (UNIX_TIMESTAMP("2016-06-01 00:00:00")) ENGINE = InnoDB)
    -> ;






手动添加或许删去分区

MySQL 5.6之前

ALTER TABLE `history_uint` ADD PARTITION p2011_10_23 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-24 00:00:00") ENGINE = InnoDB;

ALTER TABLE `history_uint` DROP PARTITION p2011_06;


MySQL5.6之后

ALTER TABLE `history_uint` ADD PARTITION (PARTITION p2016_05_16 VALUES LESS THAN (UNIX_TIMESTAMP("2016-05-17 00:00:00")) ENGINE=InnoDB);

ALTER TABLE `history_uint` DROP PARTITION p2016_05_16;


假如在MySQL5.6上依照MySQL5.6之前的ADD PARTITION句子履行会报如下过错

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near p2016_05_16 VALUES LESS THAN (1463414400) ENGINE=InnoDB at line 1


运用存储进程来进行分区 Partitioning with stored procedurces

1.创立一个办理分区的表,这个表记载每张需求进行分区的表的数据保存多长时间

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES (history, day, 30, now(), );

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES (history_uint, day, 30, now(), );

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES (history_str, day, 120, now(), );

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES (history_text, day, 120, now(), );

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES (history_log, day, 120, now(), );

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES (trends, month, 24, now(), );

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES (trends_uint, month, 24, now(), );

Zabbix2.2之后的数据库只需求这几行


2.验证分区是否存在


DELIMITER $$

USE `zabbix`$$

DROP PROCEDURE IF EXISTS `create_next_partitions`$$

CREATE PROCEDURE `create_next_partitions`(IN_SCHEMANAME VARCHAR(64))

BEGIN

    DECLARE TABLENAME_TMP VARCHAR(64);
    DECLARE PERIOD_TMP VARCHAR(12);
    DECLARE DONE INT DEFAULT 0; 
    DECLARE get_prt_tables CURSOR FOR
        SELECT `tablename`, `period`
            FROM manage_partitions;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
    OPEN get_prt_tables; 
    loop_create_part: LOOP
        IF DONE THEN
            LEAVE loop_create_part;
        END IF; 
        FETCH get_prt_tables INTO TABLENAME_TMP, PERIOD_TMP; 
        CASE WHEN PERIOD_TMP = day THEN
                    CALL `create_partition_by_day`(IN_SCHEMANAME, TABLENAME_TMP);
             WHEN PERIOD_TMP = month THEN
                    CALL `create_partition_by_month`(IN_SCHEMANAME, TABLENAME_TMP);
             ELSE
            BEGIN
                            ITERATE loop_create_part;
            END;
        END CASE; 
                UPDATE manage_partitions set last_updated = NOW() WHERE tablename = TABLENAME_TMP;
    END LOOP loop_create_part; 

    CLOSE get_prt_tables;

END$$

DELIMITER ;



3.依据每天来创立表分区

DELIMITER $$ 

USE `zabbix`$$ 

DROP PROCEDURE IF EXISTS `create_partition_by_day`$$ 

CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))

BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE BEGINTIME TIMESTAMP;
        DECLARE ENDTIME INT UNSIGNED;
        DECLARE PARTITIONNAME VARCHAR(16);
        SET BEGINTIME = DATE(NOW()) + INTERVAL 1 DAY;
        SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, p%Y_%m_%d ); 
        SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 DAY); 
        SELECT COUNT(*) INTO ROWS_CNT
                FROM information_schema.partitions
                                     WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME; 
    IF ROWS_CNT = 0 THEN
                     SET @SQL = CONCAT( ALTER TABLE `, IN_SCHEMANAME, `.`, IN_TABLENAME, `,
                                 ADD PARTITION (PARTITION , PARTITIONNAME,  VALUES LESS THAN (, ENDTIME, )); );
                PREPARE STMT FROM @SQL;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        ELSE
                  SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
        END IF;

END$$ 

DELIMITER ;


4.依据每个月来设置表分区

DELIMITER $$

USE `zabbix`$$

DROP PROCEDURE IF EXISTS `create_partition_by_month`$$

CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))

BEGIN

    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE BEGINTIME TIMESTAMP;
        DECLARE ENDTIME INT UNSIGNED;
        DECLARE PARTITIONNAME VARCHAR(16);
        SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);
        SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, p%Y_%m ); 
        SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH); 
        SELECT COUNT(*) INTO ROWS_CNT
                FROM information_schema.partitions
                WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME; 
    IF ROWS_CNT = 0 THEN
                     SET @SQL = CONCAT( ALTER TABLE `, IN_SCHEMANAME, `.`, IN_TABLENAME, `,
                                 ADD PARTITION (PARTITION , PARTITIONNAME,  VALUES LESS THAN (, ENDTIME, )); );
                PREPARE STMT FROM @SQL;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        ELSE
        SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;

        END IF;

END$$

DELIMITER ;



5.验证和删去老的分区


DELIMITER $$

USE `zabbix`$$

DROP PROCEDURE IF EXISTS `drop_partitions`$$ CREATE PROCEDURE `drop_partitions`(IN_SCHEMANAME VARCHAR(64))

BEGIN

    DECLARE TABLENAME_TMP VARCHAR(64);
    DECLARE PARTITIONNAME_TMP VARCHAR(64);
    DECLARE VALUES_LESS_TMP INT;
    DECLARE PERIOD_TMP VARCHAR(12);
    DECLARE KEEP_HISTORY_TMP INT;
    DECLARE KEEP_HISTORY_BEFORE INT;
    DECLARE DONE INT DEFAULT 0;
    DECLARE get_partitions CURSOR FOR
        SELECT p.`table_name`, p.`partition_name`, LTRIM(RTRIM(p.`partition_description`)), mp.`period`, mp.`keep_history`
            FROM information_schema.partitions p
            JOIN manage_partitions mp ON mp.tablename = p.table_name
            WHERE p.table_schema = IN_SCHEMANAME
            ORDER BY p.table_name, p.subpartition_ordinal_position; 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; 
    OPEN get_partitions; 
    loop_check_prt: LOOP
        IF DONE THEN
            LEAVE loop_check_prt;
        END IF; 
        FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
        CASE WHEN PERIOD_TMP = day THEN
                SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP DAY));
             WHEN PERIOD_TMP = month THEN
                SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP MONTH - INTERVAL DAY(NOW())-1 DAY));
             ELSE
            BEGIN
                ITERATE loop_check_prt;
            END;
        END CASE; 
        IF KEEP_HISTORY_BEFORE >= VALUES_LESS_TMP THEN
                CALL drop_old_partition(IN_SCHEMANAME, TABLENAME_TMP, PARTITIONNAME_TMP);
        END IF;
        END LOOP loop_check_prt; 

        CLOSE get_partitions;

END$$

DELIMITER ;



6.删去设定的分区

DELIMITER $$

USE `zabbix`$$

DROP PROCEDURE IF EXISTS `drop_old_partition`$$ CREATE PROCEDURE `drop_old_partition`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64), IN_PARTITIONNAME VARCHAR(64))

BEGIN

    DECLARE ROWS_CNT INT UNSIGNED; 
        SELECT COUNT(*) INTO ROWS_CNT
                FROM information_schema.partitions
                WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = IN_PARTITIONNAME; 
    IF ROWS_CNT = 1 THEN
                     SET @SQL = CONCAT( ALTER TABLE `, IN_SCHEMANAME, `.`, IN_TABLENAME, `,
                                 DROP PARTITION , IN_PARTITIONNAME, ; );
                PREPARE STMT FROM @SQL;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        ELSE
        SELECT CONCAT("partition `", IN_PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME, "` not exists") AS result;

        END IF;

END$$

DELIMITER ;


7.设置事情调度器


DELIMITER $$

USE `zabbix`$$


CREATE EVENT IF NOT EXISTS `e_part_manage`
       ON SCHEDULE EVERY 1 DAY
       STARTS 2011-08-08 04:00:00
       ON COMPLETION PRESERVE
       ENABLE
       COMMENT Creating and dropping partitions
       DO BEGIN
            CALL zabbix.drop_partitions(zabbix);
            CALL zabbix.create_next_partitions(zabbix);

       END$$

DELIMITER ;





B.运用外部脚原本履行表分区

运用外部脚原本履行分区比运用存储进程简略,也便于排错。

脚本中留意MySQL的版别号


#!/usr/bin/perl

use strict;
use Data::Dumper;
use DBI;
use Sys::Syslog qw(:standard :macros);
use DateTime;
use POSIX qw(strftime);

openlog("mysql_zbx_part", "ndelay,pid", LOG_LOCAL0);

my $db_schema = zabbix;
my $dsn = DBI:mysql:.$db_schema.:mysql_socket=/var/lib/mysql/mysql.sock;
my $db_user_name = zbx_srv;
my $db_password = <password here>;
my $tables = {  history => { period => day, keep_history => 30},
                history_log => { period => day, keep_history => 30},
                history_str => { period => day, keep_history => 30},
                history_text => { period => day, keep_history => 30},
                history_uint => { period => day, keep_history => 30},
                trends => { period => month, keep_history => 2},
                trends_uint => { period => month, keep_history => 2},

# comment next 5 lines if you partition zabbix database starting from 2.2
# they usually used for zabbix database before 2.2

#               acknowledges => { period => month, keep_history => 23},
#               alerts => { period => month, keep_history => 6},
#               auditlog => { period => month, keep_history => 24},
#               events => { period => month, keep_history => 12},
#               service_alarms => { period => month, keep_history => 6},
                };
my $amount_partitions = 10;

my $curr_tz = Asia/Shanghai;

my $part_tables;

my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

unless ( check_have_partition() ) {
        print "Your installation of MySQL does not support table partitioning.\n";
        syslog(LOG_CRIT, Your installation of MySQL does not support table partitioning.);
        exit 1;
}

my $sth = $dbh->prepare(qq{SELECT table_name, partition_name, lower(partition_method) as partition_method,
                                        rtrim(ltrim(partition_expression)) as partition_expression,
                                        partition_description, table_rows
                                FROM information_schema.partitions
                                WHERE partition_name IS NOT NULL AND table_schema = ?});
$sth->execute($db_schema);

while (my $row =  $sth->fetchrow_hashref()) {
        $part_tables->{$row->{table_name}}->{$row->{partition_name}} = $row;
}

$sth->finish();

foreach my $key (sort keys %{$tables}) {
        unless (defined($part_tables->{$key})) {
                syslog(LOG_ERR, Partitioning for ".$key." is not found! The table might be not partitioned.);
                next;
        }

        create_next_partition($key, $part_tables->{$key}, $tables->{$key}->{period});
        remove_old_partitions($key, $part_tables->{$key}, $tables->{$key}->{period}, $tables->{$key}->{keep_history})
}

delete_old_data();

$dbh->disconnect();

sub check_have_partition {
        my $result = 0;
# MySQL 5.5
        my $sth = $dbh->prepare(qq{SELECT variable_value FROM information_schema.global_variables WHERE variable_name = have_partitioning});
# MySQL 5.6
        #my $sth = $dbh->prepare(qq{SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = partition});

        $sth->execute();

        my $row = $sth->fetchrow_array();

        $sth->finish();

# MySQL 5.5
        return 1 if $row eq YES;
# MySQL 5.6
        #return 1 if $row eq ACTIVE;
}

sub create_next_partition {
        my $table_name = shift;
        my $table_part = shift;
        my $period = shift;

        for (my $curr_part = 0; $curr_part < $amount_partitions; $curr_part++) {
                my $next_name = name_next_part($tables->{$table_name}->{period}, $curr_part);
                my $found = 0;

                foreach my $partition (sort keys %{$table_part}) {
                        if ($next_name eq $partition) {
                                syslog(LOG_INFO, "Next partition for $table_name table has already been created. It is $next_name");
                                $found = 1;
                        }
                }

                if ( $found  0 ) {
                        syslog(LOG_INFO, "Creating a partition for $table_name table ($next_name)");
                        my $query = ALTER TABLE ."$db_schema.$table_name". ADD PARTITION (PARTITION .$next_name.
                                                 VALUES less than (UNIX_TIMESTAMP(".date_next_part($tables->{$table_name}->{period}, $curr_part).") div 1));
                        syslog(LOG_DEBUG, $query);
                        $dbh->do($query);
                }
        }
}
sub remove_old_partitions {
        my $table_name = shift;
        my $table_part = shift;
        my $period = shift;
        my $keep_history = shift;
        
        my $curr_date = DateTime->now;
        $curr_date->set_time_zone( $curr_tz );
        
        if ( $period eq day ) {
                $curr_date->add(days => -$keep_history);
                $curr_date->add(hours => -$curr_date->strftime(%H));
                $curr_date->add(minutes => -$curr_date->strftime(%M));
                $curr_date->add(seconds => -$curr_date->strftime(%S));
        }       
        elsif ( $period eq week ) {
        }
        elsif ( $period eq month ) {
                $curr_date->add(months => -$keep_history);
                
                $curr_date->add(days => -$curr_date->strftime(%d)+1);
                $curr_date->add(hours => -$curr_date->strftime(%H));
                $curr_date->add(minutes => -$curr_date->strftime(%M));
                $curr_date->add(seconds => -$curr_date->strftime(%S));
        }       
        
        foreach my $partition (sort keys %{$table_part}) {
                if ($table_part->{$partition}->{partition_description} <= $curr_date->epoch) {
                        syslog(LOG_INFO, "Removing old $partition partition from $table_name table");
                        
                        my $query = "ALTER TABLE $db_schema.$table_name DROP PARTITION $partition";
                        
                        syslog(LOG_DEBUG, $query);
                        $dbh->do($query); 
                }       
        }       
}       

sub name_next_part {
        my $period = shift;
        my $curr_part = shift;

        my $name_template;

        my $curr_date = DateTime->now;
        $curr_date->set_time_zone( $curr_tz );

        if ( $period eq day ) {
                my $curr_date = $curr_date->truncate( to => day );
                $curr_date->add(days => 1 + $curr_part);

                $name_template = $curr_date->strftime(p%Y_%m_%d);
        }
        elsif ($period eq week) {
                my $curr_date = $curr_date->truncate( to => week );
                $curr_date->add(days => 7 * $curr_part);

                $name_template = $curr_date->strftime(p%Y_%m_w%W);
        }
        elsif ($period eq month) {
                my $curr_date = $curr_date->truncate( to => month );
                $curr_date->add(months => 1 + $curr_part);

                $name_template = $curr_date->strftime(p%Y_%m);
        }

        return $name_template;
}

sub date_next_part {
        my $period = shift;
        my $curr_part = shift;

        my $period_date;

        my $curr_date = DateTime->now;
        $curr_date->set_time_zone( $curr_tz );

        if ( $period eq day ) {
                my $curr_date = $curr_date->truncate( to => day );
                $curr_date->add(days => 2 + $curr_part);
                $period_date = $curr_date->strftime(%Y-%m-%d);
        }
        elsif ($period eq week) {
                my $curr_date = $curr_date->truncate( to => week );
                $curr_date->add(days => 7 * $curr_part + 1);
                $period_date = $curr_date->strftime(%Y-%m-%d);
        }
        elsif ($period eq month) {
                my $curr_date = $curr_date->truncate( to => month );
                $curr_date->add(months => 2 + $curr_part);

                $period_date = $curr_date->strftime(%Y-%m-%d);
        }

        return $period_date;
}

sub delete_old_data {
        $dbh->do("DELETE FROM sessions WHERE lastaccess < UNIX_TIMESTAMP(NOW() - INTERVAL 1 MONTH)");
        $dbh->do("TRUNCATE housekeeper");
        $dbh->do("DELETE FROM auditlog_details WHERE NOT EXISTS (SELECT NULL FROM auditlog WHERE auditlog.auditid = auditlog_details.auditid)");
}





履行的时分或许报错

Cant locate DateTime.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at zabbix_mysql_partition.pl line 7.
BEGIN failedcompilation aborted at zabbix_mysql_partition.pl line 7.


解决办法:

yum -y install perl-DateTime


履行perl zabbix_mysql_partition.pl 

这个脚本是把日志写入到syslog的,能够/var/log/messages检查

然后放到crontab中履行

0 23 * * *   /usr/bin/perl   /opt/script/zabbix_mysql_partition.pl







四 总结


运用分区考虑事项

  当创立添加新的分区时,保证分区规模没有越界,要不然会回来过错

  一个MySQL表要么彻底被分区,要么一点也不要被分区。

  当测验对一个表进行很多分区时,增大open_files_limit的值

  被分区的表都不支撑外键,在进行分区之前需求删去外键

  被分区的表不支撑查询缓存

  


运用分区主张

  运用MySQL5.5或许今后版别。这些版别对表分区进行了优化,运转更安稳。

  能够考虑运用XtraDB,而不是朴实的InnoDB.XtraDB包含在MariaDB和Percona中

  TokuDB不太合适Zabbix,履行查询表的时分好像运转欠安

  优化,优化,再优化,对装备参数进行履行调整





参阅文档:

https://www.zabbix.org/wiki/Docs/howto/mysql_partitioning

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

http://dev.mysql.com/doc/refman/5.6/en/symbolic-links-to-tables.html

http://dev.mysql.com/doc/refman/5.6/en/blackhole-storage-engine.html



版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表娱乐之横扫全球立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章