中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

處理Zabbix歷史數據庫辦法二---使用MySQL表分區

發布時間：2020-06-03 10:57:51 來源：網絡 閱讀：19892 作者：自由linux 欄目：數據庫


一 應用場景描述


http://john88wang.blog.51cto.com/2165294/1770582

在前面介紹可以通過創建新表然后導入一個月內的數據到新表，最后刪除舊表的方法來處理歷史數據。

但是這種方式非常耗費時間，至少是幾個小時，同時也不必須停掉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)");
}





執行的時候可能報錯

Can't 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 failed--compilation 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



向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
曲阜市| 梁平县| 溆浦县| 乌拉特后旗| 镇江市| 监利县| 闽清县| 崇义县| 通河县| 昌黎县| 措勤县| 如东县| 台中县| 遂宁市| 建昌县| 星子县| 桑日县| 积石山| 通辽市| 合江县| 新化县| 莱西市| 峨边| 丹东市| 新干县| 应城市| 刚察县| 鸡东县| 滦平县| 云林县| 旬邑县| 资源县| 衡南县| 宁城县| 潼关县| 茂名市| 蒙山县| 林口县| 武威市| 青田县| 双柏县|