处理Zabbix历史数据库办法二---使用MySQL表分区-编程学习网

一应用场景描述

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存储过程

使用外部脚本

使用存储过程调试会比较麻烦，这里推荐使用外部脚本来管理分区

	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) - 2⁶⁴+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) - 2⁶⁴+1

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

Partitionning descisions

在执行为zabbix执行表分区之前必须要考虑几个方面：

使用range partitioning就是使用基于范围的分区，一般是基于日期
Housekeeper对于某些数据类型不在需要了。可以通过Administration->General->Housekeeping来关闭不需要的数据类型的housekeeping。比如关闭History类的housekeeping
监控项目配置中的History storage period (in days) 和Trend storage period (in days)将不在使用，因为老数据会根据范围清理掉。这两个值可以也应该被Administration->General->Housekeeping中设置的时间间隔给重置。Housekeeping设置的时间间隔应该匹配期望保留的表分区。
如果需要存储数据很长一段时间，但是磁盘空间有限，可以利用对过期的分区使用软链接。

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
 |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
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

文章详情

处理Zabbix历史数据库办法二---使用MySQL表分区

软考中级精品资料免费领

相关文章

猜你喜欢

处理Zabbix历史数据库办法二---使用MySQL表分区

处理Zabbix历史数据库办法一

处理Zabbixl历史数据库解决办法三---使用MySQL中间件为Zabbix数据库扩容

mysql数据处理采用的手段：分片分区分库分表