针对Zabbix库中history相关大表分区优化实践

1.对原有的history 相关表进行改造,建立分区表,这样下面的脚本才能更好的执行分区;

2.由于原表有主键,所以,需要删除主键,创建普通索引;

3.具体的表结构创建语句如下:(这的最后一个分区,一定得是 脚本执行当天的日期,不然,会少建分区)
CREATE TABLE `history` (
`itemid` BIGINT(20) UNSIGNED NOT NULL,
`clock` INT(11) NOT NULL DEFAULT ‘0’,
`value` DOUBLE(16,4) NOT NULL DEFAULT ‘0.0000’,
`ns` INT(11) NOT NULL DEFAULT ‘0’,
KEY `history_1` (`itemid`,`clock`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(
PARTITION p20141214 VALUES LESS THAN (1418572800) ENGINE = INNODB,
PARTITION p20141215 VALUES LESS THAN (1418659200) ENGINE = INNODB
);

CREATE TABLE `history_log` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`itemid` BIGINT(20) UNSIGNED NOT NULL,
`clock` INT(11) NOT NULL DEFAULT ‘0’,
`timestamp` INT(11) NOT NULL DEFAULT ‘0’,
`source` VARCHAR(64) NOT NULL DEFAULT ”,
`severity` INT(11) NOT NULL DEFAULT ‘0’,
`value` TEXT NOT NULL,
`logeventid` INT(11) NOT NULL DEFAULT ‘0’,
`ns` INT(11) NOT NULL DEFAULT ‘0’,
KEY parimary_id(`id`),
KEY `history_log_2` (`itemid`,`id`),
KEY `history_log_1` (`itemid`,`clock`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(
PARTITION p20141214 VALUES LESS THAN (1418572800) ENGINE = INNODB,
PARTITION p20141215 VALUES LESS THAN (1418659200) ENGINE = INNODB
);

CREATE TABLE `history_str` (
`itemid` BIGINT(20) UNSIGNED NOT NULL,
`clock` INT(11) NOT NULL DEFAULT ‘0’,
`value` VARCHAR(255) NOT NULL DEFAULT ”,
`ns` INT(11) NOT NULL DEFAULT ‘0’,
KEY `history_str_1` (`itemid`,`clock`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(
PARTITION p20141214 VALUES LESS THAN (1418572800) ENGINE = INNODB,
PARTITION p20141215 VALUES LESS THAN (1418659200) ENGINE = INNODB
);

CREATE TABLE `history_text` (
`id` BIGINT(20) UNSIGNED NOT NULL,
`itemid` BIGINT(20) UNSIGNED NOT NULL,
`clock` INT(11) NOT NULL DEFAULT ‘0’,
`value` TEXT NOT NULL,
`ns` INT(11) NOT NULL DEFAULT ‘0’,
KEY parimary_id(`id`),
KEY `history_text_2` (`itemid`,`id`),
KEY `history_text_1` (`itemid`,`clock`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
PARTITION BY RANGE(clock)
(
PARTITION p20141214 VALUES LESS THAN (1418572800) ENGINE = INNODB,
PARTITION p20141215 VALUES LESS THAN (1418659200) ENGINE = INNODB
);

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
PARTITION BY RANGE(clock)
(
PARTITION p20141214 VALUES LESS THAN (1418572800) ENGINE = INNODB,
PARTITION p20141215 VALUES LESS THAN (1418659200) ENGINE = INNODB
);

4.下面是创建脚本:
定时执行的:(每周日12点执行一次)
0 0 * * 0 bash /home/michael/zabbix_partition.sh
#!/bin/bash
# 参考: https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-autopartitioning.sql
# 功能: 放入定时任务,为zabbix创建和删除分区

MY_HOST=”数据库服务器”
MY_USER=”数据库用户名”
MY_PASS=”数据库密码”
MY_DB=”数据库库名”
# 创建分区时的最大天数
MAX_DAYS=14

# 删除分区时保留的天数
MIN_DAYS=7

function MySQLStatQuery() {
if [ -z “$1” ]; then
echo “ERROR: [MySQLStatQuery] sql empty.”
else
mysql -s –skip-column-names -h${MY_HOST} -u${MY_USER} -p${MY_PASS} ${MY_DB} -e “$1″
fi
}

function CreatePartition() {
local schemaName=”$1″
local tableName=”$2″
local partitionName=”$3″
local clock=”$4″

local sql=”SELECT count(1) FROM information_schema.partitions WHERE table_schema = ‘${schemaName}’ AND table_name = ‘${tableName}’ AND partition_name = ‘${partitionName}’;”
local partitionExist=$(MySQLStatQuery “$sql”)

if [ $partitionExist -eq 0 ]; then
echo “INFO: [CreatePartition] schema_name=’$schemaName’ table_name=’$tableName’ partition_name=’$partitionName’ clock=’$clock'”
sql=”ALTER TABLE ${schemaName}.${tableName} ADD PARTITION ( PARTITION ${partitionName} VALUES LESS THAN (UNIX_TIMESTAMP(‘${clock}’)));”
MySQLStatQuery “$sql”
fi
}

function DropPartition() {
local schemaName=”$1″
local tableName=”$2″
local partitionName=”$3″

local sql=”SELECT count(1) FROM information_schema.partitions WHERE table_schema = ‘${schemaName}’ AND table_name = ‘${tableName}’ AND partition_name = ‘${partitionName}’;”
local partitionExist=$(MySQLStatQuery “$sql”)

if [ $partitionExist -eq 1 ]; then
echo “INFO: [DropPartition] schema_name=’$schemaName’ table_name=’$tableName’ partition_name=’$partitionName'”
sql=”ALTER TABLE ${schemaName}.${tableName} DROP PARTITION ${partitionName};”
MySQLStatQuery “$sql”
fi
}

function CreateNextPartition() {
local schemaName=”$1″
local tableName=”$2″

local nextClock=””
local partitionName=””
local clock=””

local i=1
while true; do
nextClock=$(date +%Y%m%d –date “$i days”)
partitionName=”p$nextClock”
i=$(expr $i + 1)
clock=”$(date ‘+%Y-%m-%d 00:00:00’ –date “$i days”)”
CreatePartition “$schemaName” “$tableName” “$partitionName” “$clock”
[ $i -gt $MAX_DAYS ] && break
done
}

function DropOldPartition() {
local schemaName=”$1″
local tableName=”$2″

local oldClock=””
local partitionName=””

local i=$MAX_DAYS
while true; do
oldClock=$(date +%Y%m%d –date “-$i days”)
partitionName=”p$oldClock”
DropPartition “$schemaName” “$tableName” “$partitionName”
i=$(expr $i – 1)
[ $i -lt $MIN_DAYS ] && break
done
}

function main() {
# 需要分区的表,库名默认为zabbix
local tables=(history history_log history_str history_text history_uint)

local tableName=””

for tableName in ${tables[@]}; do
CreateNextPartition ‘zabbix’ “$tableName”
done

for tableName in ${tables[@]}; do
DropOldPartition ‘zabbix’ “$tableName”
done
}

main $*

Author: Michael

关注数据分析,挖掘数据金矿。

One thought on “针对Zabbix库中history相关大表分区优化实践”

Leave a Reply

Your email address will not be published. Required fields are marked *