在做zabbix的性能优化时，有时候在db的数据量比较大的时候，需要对表进行partition操作，这样可以在数据查询减少用时。并且由于使用了partition，我们可以自己实现历史数据的删除操作，这样就可以禁用zabbix的housekeeping功能。
简单的说下再2.0.x版本的zabbix中进行partition的操作：
1.备份数据，如果使用proxy的结构的话，调整ProxyOfflineBuffer,加大数据在offline情况的缓存时间，这样在partition调整完后数据会自动补充。
确定需要partition的表，并更改表结构（使用clock进行parttition，partition key必须是primarykey的一部分），涉及的表主要是存储历史相关数据的表：history,history_uint,history_text,history_log,history_str
表结构更改：
|
1
2
3
4
|
ALTER
TABLE
`history_log`
DROP
PRIMARY
KEY
,
ADD
PRIMARY
KEY
(`itemid`,`id`,`clock`);
ALTER
TABLE
`history_log`
DROP
KEY
`history_log_2`;
ALTER
TABLE
`history_text`
DROP
PRIMARY
KEY
,
ADD
PRIMARY
KEY
(`itemid`,`id`,`clock`);
ALTER
TABLE
`history_text`
DROP
KEY
`history_text_2`;
|
剩下的history_str,history,history_uint 3个表不需要做更改。
2.按clock进行分区
例子：
|
1
2
3
4
5
6
7
8
|
ALTER
TABLE
`history_uint` PARTITION
BY
RANGE( clock ) (
PARTITION p20140101
VALUES
LESS THAN (UNIX_TIMESTAMP(
"2014-01-02 00:00:00"
)),
PARTITION p20140102
VALUES
LESS THAN (UNIX_TIMESTAMP(
"2014-01-03 00:00:00"
)),
PARTITION p20140103
VALUES
LESS THAN (UNIX_TIMESTAMP(
"2014-01-04 00:00:00"
)),
PARTITION p20140104
VALUES
LESS THAN (UNIX_TIMESTAMP(
"2014-01-05 00:00:00"
)),
PARTITION p20140105
VALUES
LESS THAN (UNIX_TIMESTAMP(
"2014-01-06 00:00:00"
)),
PARTITION p20140106
VALUES
LESS THAN (UNIX_TIMESTAMP(
"2014-01-07 00:00:00"
))
);
|
把需要分区的表都进行相同的操作。
3.设置存储规则，并使用cronjob来实现自动partition操作
|
1
|
mysql -u xxx -pxxx xxx < ./partition.sql
|
其中partition.sql的内容如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
DELIMITER //
DROP
PROCEDURE
IF EXISTS `zabbix_server`.`create_zabbix_partitions` //
CREATE
PROCEDURE
`zabbix_server`.`create_zabbix_partitions` ()
BEGIN
CALL zabbix_server.create_next_partitions(
"zabbix_server"
,
"history"
);
CALL zabbix_server.create_next_partitions(
"zabbix_server"
,
"history_log"
);
CALL zabbix_server.create_next_partitions(
"zabbix_server"
,
"history_str"
);
CALL zabbix_server.create_next_partitions(
"zabbix_server"
,
"history_text"
);
CALL zabbix_server.create_next_partitions(
"zabbix_server"
,
"history_uint"
);
CALL zabbix_server.drop_old_partitions(
"zabbix_server"
,
"history"
);
CALL zabbix_server.drop_old_partitions(
"zabbix_server"
,
"history_log"
);
CALL zabbix_server.drop_old_partitions(
"zabbix_server"
,
"history_str"
);
CALL zabbix_server.drop_old_partitions(
"zabbix_server"
,
"history_text"
);
CALL zabbix_server.drop_old_partitions(
"zabbix_server"
,
"history_uint"
);
END
//
DROP
PROCEDURE
IF EXISTS `zabbix_server`.`create_next_partitions` //
CREATE
PROCEDURE
`zabbix_server`.`create_next_partitions` (SCHEMANAME
varchar
(64), TABLENAME
varchar
(64))
BEGIN
DECLARE
NEXTCLOCK
timestamp
;
DECLARE
PARTITIONNAME
varchar
(16);
DECLARE
CLOCK
int
;
SET
@totaldays = 7;
SET
@i = 1;
createloop: LOOP
SET
NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i
DAY
);
SET
PARTITIONNAME = DATE_FORMAT( NEXTCLOCK,
'p%Y%m%d'
);
SET
CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD( NEXTCLOCK ,INTERVAL 1
DAY
),
'%Y-%m-%d 00:00:00'
));
CALL zabbix_server.create_partition( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET
@i=@i+1;
IF @i > @totaldays
THEN
LEAVE createloop;
END
IF;
END
LOOP;
END
//
DROP
PROCEDURE
IF EXISTS `zabbix_server`.`drop_old_partitions` //
CREATE
PROCEDURE
`zabbix_server`.`drop_old_partitions` (SCHEMANAME
varchar
(64), TABLENAME
varchar
(64))
BEGIN
DECLARE
OLDCLOCK
timestamp
;
DECLARE
PARTITIONNAME
varchar
(16);
DECLARE
CLOCK
int
;
SET
@mindays = 30;
SET
@maxdays = @mindays+4;
SET
@i = @maxdays;
droploop: LOOP
SET
OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i
DAY
);
SET
PARTITIONNAME = DATE_FORMAT( OLDCLOCK,
'p%Y%m%d'
);
CALL zabbix_server.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET
@i=@i-1;
IF @i <= @mindays
THEN
LEAVE droploop;
END
IF;
END
LOOP;
END
//
DROP
PROCEDURE
IF EXISTS `zabbix_server`.`create_partition` //
CREATE
PROCEDURE
`zabbix_server`.`create_partition` (SCHEMANAME
varchar
(64), TABLENAME
varchar
(64), PARTITIONNAME
varchar
(64), CLOCK
int
)
BEGIN
DECLARE
RETROWS
int
;
SELECT
COUNT
(1)
INTO
RETROWS
FROM
`information_schema`.`partitions`
WHERE
`table_schema` = SCHEMANAME
AND
`table_name` = TABLENAME
AND
`partition_name` = PARTITIONNAME;
IF RETROWS = 0
THEN
SELECT
CONCAT(
"create_partition("
, SCHEMANAME,
","
, TABLENAME,
","
, PARTITIONNAME,
","
, CLOCK,
")"
)
AS
msg;
SET
@sql = CONCAT(
'ALTER TABLE `'
, SCHEMANAME,
'`.`'
, TABLENAME,
'`'
,
' ADD PARTITION (PARTITION '
, PARTITIONNAME,
' VALUES LESS THAN ('
, CLOCK,
'));'
);
PREPARE
STMT
FROM
@sql;
EXECUTE
STMT;
DEALLOCATE
PREPARE
STMT;
END
IF;
END
//
DROP
PROCEDURE
IF EXISTS `zabbix_server`.`drop_partition` //
CREATE
PROCEDURE
`zabbix_server`.`drop_partition` (SCHEMANAME
varchar
(64), TABLENAME
varchar
(64), PARTITIONNAME
varchar
(64))
BEGIN
DECLARE
RETROWS
int
;
SELECT
COUNT
(1)
INTO
RETROWS
FROM
`information_schema`.`partitions`
WHERE
`table_schema` = SCHEMANAME
AND
`table_name` = TABLENAME
AND
`partition_name` = PARTITIONNAME;
IF RETROWS = 1
THEN
SELECT
CONCAT(
"drop_partition("
, SCHEMANAME,
","
, TABLENAME,
","
, PARTITIONNAME,
")"
)
AS
msg;
SET
@sql = CONCAT(
'ALTER TABLE `'
, SCHEMANAME,
'`.`'
, TABLENAME,
'`'
,
' DROP PARTITION '
, PARTITIONNAME,
';'
);
PREPARE
STMT
FROM
@sql;
EXECUTE
STMT;
DEALLOCATE
PREPARE
STMT;
END
IF;
END
//
DELIMITER ;
|
查看存储过程：
|
1
2
|
select
name
from
mysql.proc;
show
create
procedure
create_partition;
|
cronjob形式的调用：
|
1
|
mysql -B -xxx -pxxx xxx -e
"CALL create_zabbix_partitions();"
|
4.测试partition的结果：
1)查看表结构
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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
/*!50100 PARTITION
BY
RANGE ( clock)
(PARTITION p20140101
VALUES
LESS THAN (1388592000) ENGINE = InnoDB,
PARTITION p20140102
VALUES
LESS THAN (1388678400) ENGINE = InnoDB,
PARTITION p20140103
VALUES
LESS THAN (1388764800) ENGINE = InnoDB,
PARTITION p20140104
VALUES
LESS THAN (1388851200) ENGINE = InnoDB,
PARTITION p20140105
VALUES
LESS THAN (1388937600) ENGINE = InnoDB,
PARTITION p20140106
VALUES
LESS THAN (1389024000) ENGINE = InnoDB,
PARTITION p20140107
VALUES
LESS THAN (1389110400) ENGINE = InnoDB,
PARTITION p20140108
VALUES
LESS THAN (1389196800) ENGINE = InnoDB,
PARTITION p20140109
VALUES
LESS THAN (1389283200) ENGINE = InnoDB,
PARTITION p20140110
VALUES
LESS THAN (1389369600) ENGINE = InnoDB) */
|
2)
explain
查看执行计划
explain partitions xxx
5.关闭housekeeping，并验证host update percent的情况
1)DisableHousekeeping=1
2)
|
1
|
select
b.hostname ,c.ip,a.update_percent
as
uppercent
from
(
select
b.hostid,ROUND(IFNULL(a.aa,0)*100/b.bb,2)
as
update_percent
from
(
select
hostid,
count
(*)
as
aa
from
items
where
lastclock > UNIX_TIMESTAMP()-1800
and
delay < 900
and
hostid
in
(
select
hostid
from
hosts
where
status=0)
and
status = 0
group
by
hostid ) a
RIGHT
JOIN
(
select
hostid,
count
(*)
as
bb
from
items
where
delay < 900
and
status = 0
and
hostid
in
(
select
hostid
from
hosts
where
status=0)
group
by
hostid) b
ON
a.hostid=b.hostid)a,(
select
hostid,
lower
(host)
as
hostname
from
hosts
where
status=0)b, (
select
hostid,ip
from
interface
where
type=
'1'
)c
where
a.hostid=b.hostid
and
b.hostid=c.hostid
order
by
uppercent;
|
本文转自菜菜光 51CTO博客，原文链接：http://blog.51cto.com/caiguangguang/1354093，如需转载请自行联系原作者
