溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶(hù)服務(wù)條款》
您好，登錄后才能下訂單哦！
一 應(yīng)用場(chǎng)景描述
http://john88wang.blog.51cto.com/2165294/1770582
在前面介紹可以通過(guò)創(chuàng)建新表然后導(dǎo)入一個(gè)月內(nèi)的數(shù)據(jù)到新表，最后刪除舊表的方法來(lái)處理歷史數(shù)據(jù)。
但是這種方式非常耗費(fèi)時(shí)間，至少是幾個(gè)小時(shí)，同時(shí)也不必須停掉zabbix server防止新的數(shù)據(jù)寫(xiě)入。對(duì)于需要全天不停地處理監(jiān)控的應(yīng)用來(lái)說(shuō)，這種方法還是不可取的。
我們可以使用MySQL表分區(qū)來(lái)對(duì)history這種大表進(jìn)行分區(qū)，但是一定要在數(shù)據(jù)量小的時(shí)候進(jìn)行分區(qū)，當(dāng)數(shù)據(jù)量達(dá)到好幾十G設(shè)置幾百G了還是采用第一種方法把數(shù)據(jù)清理了再作表分區(qū)
二 MySQL表分區(qū)相關(guān)知識(shí)點(diǎn)
MySQL的表分區(qū)不支持外鍵。Zabbix2.0以上history和trend相關(guān)的表沒(méi)有使用外鍵，因此可以使用分區(qū)。
MySQL表分區(qū)就是將一個(gè)大表在邏輯上切分成好幾個(gè)物理分片。使用MySQL表分區(qū)有以下幾個(gè)好處：
在有些場(chǎng)景下可以明顯增加查詢(xún)性能，特別是對(duì)于那些重度使用的表如果是一個(gè)單獨(dú)的分區(qū)或者好幾個(gè)分區(qū)就可以明顯增加查詢(xún)性能，因?yàn)楸绕鸺虞d整張表的數(shù)據(jù)到內(nèi)存，一個(gè)分區(qū)的數(shù)據(jù)和索引更容易加載到內(nèi)存。查看zabbix數(shù)據(jù)的general日志，可以發(fā)現(xiàn)zabbix對(duì)于history相關(guān)的幾張表調(diào)用是非常頻繁的，所以如果要優(yōu)化zabbix的數(shù)據(jù)庫(kù)重點(diǎn)要優(yōu)化history這幾張大表。
如果查詢(xún)或者更新主要是使用一個(gè)分區(qū)，那么性能提升就可以簡(jiǎn)單地通過(guò)順序訪(fǎng)問(wèn)磁盤(pán)上的這個(gè)分區(qū)而不用使用索引和隨機(jī)訪(fǎng)問(wèn)整張表。
批量插入和刪除執(zhí)行的時(shí)候可以簡(jiǎn)單地刪除或者增加分區(qū)，只要當(dāng)創(chuàng)建分區(qū)的時(shí)候有計(jì)劃的創(chuàng)建。ALTER TABLE操作也會(huì)很快
MySQL從5.1以后支持表分區(qū)。MySQL5.6之前查看是否支持表分區(qū)
mysql> show variables like 'have_partitioning'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in set (0.00 sec)
MySQL5.6開(kāi)始查看是否支持表分區(qū)
show plugins;
MySQL表分區(qū)類(lèi)型
Range partitioning
根據(jù)取值范圍將表劃分成多塊。每個(gè)單獨(dú)分區(qū)的取值范圍不能越界。例如根據(jù)日期分區(qū)或者根據(jù)其他摸個(gè)自定義字段分區(qū)。
Other partitioning types
其他的分區(qū)類(lèi)型有hash，list和key。這里zabbix的history類(lèi)表時(shí)候使用range類(lèi)型的表分區(qū)。
三 管理history類(lèi)表的分區(qū)
這里提供兩種方案來(lái)管理分區(qū)：
使用MySQL存儲(chǔ)過(guò)程
使用外部腳本
使用存儲(chǔ)過(guò)程調(diào)試會(huì)比較麻煩，這里推薦使用外部腳本來(lái)管理分區(qū)
| Purpose | Data type | Maximum size | |
| history | Keeps raw history | Numeric (float) | double(16,4) - 999999999999.9999 |
| history_uint | Keeps raw history | Numeric (unsigned) | bigint(20) - 264+1 |
| history_str | Keeps raw short string data | Character | varchar(255) - 255 |
| history_text | Keeps raw long string data | Text | text - 65535 |
| history_log | Keeps raw log strings | Log | text - 65535 |
| trends | Keeps reduced dataset (trends) | Numeric (float) | double(16,4) - 999999999999.9999 |
| trends_uint | Keeps reduced dataset (trends) | Numeric (unsigned) | bigint(20) - 264+1 |
數(shù)據(jù)類(lèi)型是Character,Text,Log類(lèi)型的的監(jiān)控項(xiàng)是沒(méi)有趨勢(shì)數(shù)據(jù)的，就是在trends表中沒(méi)有數(shù)據(jù)，如果要對(duì)history_str,history_text,history_log作表分區(qū)需要考慮這個(gè)問(wèn)題。
Partitionning descisions
在執(zhí)行為zabbix執(zhí)行表分區(qū)之前必須要考慮幾個(gè)方面：
使用range partitioning就是使用基于范圍的分區(qū)，一般是基于日期
Housekeeper對(duì)于某些數(shù)據(jù)類(lèi)型不在需要了?？梢酝ㄟ^(guò)Administration->General->Housekeeping來(lái)關(guān)閉不需要的數(shù)據(jù)類(lèi)型的housekeeping。比如關(guān)閉History類(lèi)的housekeeping
監(jiān)控項(xiàng)目配置中的History storage period (in days) 和Trend storage period (in days)將不在使用，因?yàn)槔蠑?shù)據(jù)會(huì)根據(jù)范圍清理掉。這兩個(gè)值可以也應(yīng)該被Administration->General->Housekeeping中設(shè)置的時(shí)間間隔給重置。Housekeeping設(shè)置的時(shí)間間隔應(yīng)該匹配期望保留的表分區(qū)。
如果需要存儲(chǔ)數(shù)據(jù)很長(zhǎng)一段時(shí)間，但是磁盤(pán)空間有限，可以利用對(duì)過(guò)期的分區(qū)使用軟鏈接。
mysql> show variables like 'have_symlink';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink | YES |
+---------------+-------+
1 row in set (0.00 sec)
但是不建議使用軟鏈接功能，因?yàn)檐涍B接很難保證對(duì)任何表都工作正常。還有就是即使監(jiān)控項(xiàng)目的housekeeping在頁(yè)面關(guān)閉了，Zabbix server和web接口還是會(huì)持續(xù)向housekeeper表寫(xiě)入housekeeping信息以供講來(lái)使用。為了避免這個(gè)，可以設(shè)置
ALTER TABLE housekeeper ENGINE = BLACKHOLE;
A.使用MySQL存儲(chǔ)過(guò)程和事件調(diào)度器進(jìn)行分區(qū)
首先確定event scheduler開(kāi)啟
mysql> SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)
在/etc/my.cnf文中也要設(shè)置
event_scheduler=ON
Zabbix 2.2之后的版本只有幾個(gè)和歷史數(shù)據(jù)的大表建議分區(qū)history, history_uint, history_str, history_text, history_log, trends, trends_uint。
由于MySQL有關(guān)于使用唯一索引，主鍵等的內(nèi)部限制。在開(kāi)始分區(qū)之前需要更改一些索引
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`;
現(xiàn)在可以為每個(gè)表開(kāi)始執(zhí)行分區(qū)操作。因?yàn)榉謪^(qū)操作通常是對(duì)已經(jīng)存在的的歷史數(shù)據(jù)進(jìn)行分區(qū)
對(duì)每張表必須指定分區(qū)從一個(gè)clock字段的最小值到當(dāng)前時(shí)刻的值。
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)
對(duì)所有要分區(qū)的表執(zhí)行相同的查詢(xún)操作
需要注意的是一個(gè)表總共的分區(qū)數(shù)量有限制，MySQL5.6.7之前是1024,MySQL5.6.7開(kāi)始是8192
一張表要么全部分區(qū)要么全不要分區(qū)
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)
-> ;執(zhí)行完成后可以查看分區(qū)情況
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數(shù)據(jù)庫(kù)目下下的表文件
# 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
可以看到經(jīng)過(guò)分區(qū)后的表的數(shù)據(jù)庫(kù)文件由原來(lái)打個(gè)ibd文件變成了按照日期劃分的多個(gè)ibd文件，同時(shí)增加了一個(gè)par文件來(lái)存儲(chǔ)分區(qū)信息。
然后依次對(duì)history,history_log,history_str,history_text按照每天進(jìn)行分區(qū)
對(duì)trends,trends_uint按照每個(gè)月進(jìn)行分區(qū)
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)
-> ;手動(dòng)增加或者刪除分區(qū)
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語(yǔ)句執(zhí)行會(huì)報(bào)如下錯(cuò)誤
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
使用存儲(chǔ)過(guò)程來(lái)進(jìn)行分區(qū) Partitioning with stored procedurces
1.創(chuàng)建一個(gè)管理分區(qū)的表，這個(gè)表記錄每張需要進(jìn)行分區(qū)的表的數(shù)據(jù)保留多長(zhǎng)時(shí)間
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之后的數(shù)據(jù)庫(kù)只需要這幾行
2.驗(yàn)證分區(qū)是否存在
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.根據(jù)每天來(lái)創(chuàng)建表分區(qū)
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.根據(jù)每個(gè)月來(lái)設(shè)置表分區(qū)
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.驗(yàn)證和刪除老的分區(qū)
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.刪除設(shè)定的分區(qū)
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.設(shè)置事件調(diào)度器
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.使用外部腳本來(lái)執(zhí)行表分區(qū)
使用外部腳本來(lái)執(zhí)行分區(qū)比使用存儲(chǔ)過(guò)程簡(jiǎn)單，也便于排錯(cuò)。
腳本中注意MySQL的版本號(hào)
#!/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)");
}執(zhí)行的時(shí)候可能報(bào)錯(cuò)
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
執(zhí)行perl zabbix_mysql_partition.pl
這個(gè)腳本是把日志寫(xiě)入到syslog的，可以/var/log/messages查看
然后放到crontab中執(zhí)行
0 23 * * * /usr/bin/perl /opt/script/zabbix_mysql_partition.pl
四 總結(jié)
使用分區(qū)考慮事項(xiàng)
當(dāng)創(chuàng)建增加新的分區(qū)時(shí)，確保分區(qū)范圍沒(méi)有越界，要不然會(huì)返回錯(cuò)誤
一個(gè)MySQL表要么完全被分區(qū)，要么一點(diǎn)也不要被分區(qū)。
當(dāng)嘗試對(duì)一個(gè)表進(jìn)行大量分區(qū)時(shí)，增大open_files_limit的值
被分區(qū)的表都不支持外鍵，在進(jìn)行分區(qū)之前需要?jiǎng)h除外鍵
被分區(qū)的表不支持查詢(xún)緩存
使用分區(qū)建議
使用MySQL5.5或者以后版本。這些版本對(duì)表分區(qū)進(jìn)行了優(yōu)化，運(yùn)行更穩(wěn)定。
可以考慮使用XtraDB,而不是純粹的InnoDB.XtraDB包含在MariaDB和Percona中
TokuDB不太適合Zabbix，執(zhí)行查詢(xún)表的時(shí)候似乎運(yùn)行不佳
優(yōu)化，優(yōu)化，再優(yōu)化，對(duì)配置參數(shù)進(jìn)行執(zhí)行調(diào)整
參考文檔：
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
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼