Zabbix优化：数据库表分区-白红宇

在说数据库分表之前，先随便聊聊Zabbix的一些参数的优化吧，其实我也不是很懂，只是机器上了1500+，楞着头皮去调一下参数

首先是几个Poller的调整：

### Option: StartPollers （处理监控项的东西）

### Option: StartIPMIPollers （母鸡干什么用的，暂时没用，设为0）

### Option: StartPollersUnreachable （获取数据遇到不可到达时，交给这些进程处理）

### Option: StartHTTPPollers （监控HTTP，WEB那些东西用的，没用，设为0）

### Option: StartJavaPollers （监控JAVA专用进程，为毛就只有JAVA）

### Option: StartProxyPollers （处理代理的进程，暂时没用，设为0）

### Option: StartDiscoverers （处理自动发现的，没用，设为0）

### Option: StartPingers （如果用了ICMP PING那个模板的，这个值要调大一些）

把几个没用到的先关掉：StartHTTPPollers StartJavaPollers StartProxyPollers，设为0就可以了，然后可以观察ZabbixServer的图形来调整StartPollers和StartPollersUnreachable的数量：

要注意的是，例如StartPollers，虽然配置文件写是可用范围是0-1000，但这个1000是所有的进程的值，也就是说上面那些进程的总数不能超过1000，设的时候要注意一下这点。

关于Cache的调整：

### Option: CacheSize

### Option: CacheUpdateFrequency

### Option: HistoryCacheSize

### Option: TrendCacheSize

### Option: HistoryTextCacheSize

### Option: ValueCacheSize

关于内存的参数有这么多，都是要根据机器数量和item数量的增加而增加，这些内存的值不能大于系统内核的kernel.shmall这个值，否则申请不了内存程序启动不了

如果日志出现连续一大片地出现这些信息：

Zabbix agent item "vfs.fs.size[/boot,free]" on host "192.168.1.100" failed: first network error, wait for 15 seconds

resuming Zabbix agent checks on host "192.168.1.100": connection restored

说明你的poller不够，需要再加大poller，如果加大poller还是这样则需要减少监控的item，或者用proxy来做分布式监控了

机器数量多的时候，mysql里面的history表就会越来越大，虽然zabbix本身有删除功能（就是那个housekeeper的功能），但这东西太影响性能，所以网上的做法都是关闭这个东西，用mysql的表分区功能来实现清理历史数据还可以提升mysql的性能

一、先修改两张表的结构：

                               
Alter
table
history_text 
drop
primary
key
, 
add
index
(id), 
drop
index
history_text_2, 
add
index
history_text_2 (itemid, id)
          
        

                               
Alter
table
history_log 
drop
primary
key
, 
add
index
(id), 
drop
index
history_log_2, 
add
index
history_log_2 (itemid, id);
          
        

二、导入存储过程：

                               
DELIMITER $$
CREATE
PROCEDURE
`partition_create`(SCHEMANAME 
VARCHAR
(64), TABLENAME 
VARCHAR
(64), PARTITIONNAME 
VARCHAR
(64), CLOCK 
INT
)
BEGIN
        
/*
           
SCHEMANAME = The DB 
schema
in
which 
to
make changes
           
TABLENAME = The 
table
with
partitions 
to
potentially 
delete
           
PARTITIONNAME = The 
name
of
the partition 
to
create
        
*/
        
/*
           
Verify that the partition does 
not
already exist
        
*/         
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
                
/*
                   
1. Print a message indicating that a partition was created.
                   
2. 
Create
the SQL 
to
create
the partition.
                   
3. 
Execute
the SQL 
from
#2.
                
*/
                
SELECT
CONCAT( 
"partition_create("
, 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
$$
DELIMITER ;
          
        

                               
DELIMITER $$
CREATE
PROCEDURE
`partition_drop`(SCHEMANAME 
VARCHAR
(64), TABLENAME 
VARCHAR
(64), DELETE_BELOW_PARTITION_DATE 
BIGINT
)
BEGIN
        
/*
           
SCHEMANAME = The DB 
schema
in
which 
to
make changes
           
TABLENAME = The 
table
with
partitions 
to
potentially 
delete
           
DELETE_BELOW_PARTITION_DATE = 
Delete
any
partitions 
with
names that are dates older than this one (yyyy-mm-dd)
        
*/
        
DECLARE
done 
INT
DEFAULT
FALSE
;        
DECLARE
drop_part_name 
VARCHAR
(16);
         
/*
           
Get a list 
of
all
the partitions that are older than the 
date
           
in
DELETE_BELOW_PARTITION_DATE.  
All
partitions are prefixed 
with
           
a 
"p"
, so use 
SUBSTRING
TO
get rid 
of
that 
character
.
        
*/
        
DECLARE
myCursor 
CURSOR
FOR
                
SELECT
partition_name                
FROM
information_schema.partitions                
WHERE
table_schema = SCHEMANAME 
AND
TABLE_NAME = TABLENAME 
AND
CAST
(
SUBSTRING
(partition_name 
FROM
2) 
AS
UNSIGNED) < DELETE_BELOW_PARTITION_DATE;        
DECLARE
CONTINUE
HANDLER 
FOR
NOT
FOUND 
SET
done = 
TRUE
;
         
/*
           
Create
the basics 
for
when
we need 
to
drop
the partition.  Also, 
create
           
@drop_partitions 
to
hold a comma-delimited list 
of
all
partitions that
           
should be deleted.
        
*/
        
SET
@alter_header = CONCAT(
"ALTER TABLE "
, SCHEMANAME, 
"."
, TABLENAME, 
" DROP PARTITION "
);        
SET
@drop_partitions = 
""
;
         
/*
           
Start looping through 
all
the partitions that are too old.
        
*/
        
OPEN
myCursor;
        
read_loop: LOOP
                
FETCH
myCursor 
INTO
drop_part_name;                IF done 
THEN
                        
LEAVE read_loop;                
END
IF;                
SET
@drop_partitions = IF(@drop_partitions = 
""
, drop_part_name, CONCAT(@drop_partitions, 
","
, drop_part_name));        
END
LOOP;        IF @drop_partitions != 
""
THEN
                
/*
                   
1. Build the SQL 
to
drop
all
the necessary partitions.
                   
2. Run the SQL 
to
drop
the partitions.
                   
3. Print 
out
the 
table
partitions that were deleted.
                
*/
                
SET
@full_sql = CONCAT(@alter_header, @drop_partitions, 
";"
);                
PREPARE
STMT 
FROM
@full_sql;                
EXECUTE
STMT;
                
DEALLOCATE
PREPARE
STMT;
                 
SELECT
CONCAT(SCHEMANAME, 
"."
, TABLENAME) 
AS
`
table
`, @drop_partitions 
AS
`partitions_deleted`;        
ELSE
                
/*
                   
No
partitions are being deleted, so print 
out
"N/A"
(
Not
applicable) 
to
indicate
                   
that 
no
changes were made.
                
*/
                
SELECT
CONCAT(SCHEMANAME, 
"."
, TABLENAME) 
AS
`
table
`, 
"N/A"
AS
`partitions_deleted`;        
END
IF;
END
$$
DELIMITER ;
          
        

                               
DELIMITER $$
CREATE
PROCEDURE
`partition_maintenance`(SCHEMA_NAME 
VARCHAR
(32), TABLE_NAME 
VARCHAR
(32), KEEP_DATA_DAYS 
INT
, HOURLY_INTERVAL 
INT
, CREATE_NEXT_INTERVALS 
INT
)
BEGIN
        
DECLARE
OLDER_THAN_PARTITION_DATE 
VARCHAR
(16);        
DECLARE
PARTITION_NAME 
VARCHAR
(16);        
DECLARE
LESS_THAN_TIMESTAMP 
INT
;        
DECLARE
CUR_TIME 
INT
;
         
CALL partition_verify(SCHEMA_NAME, TABLE_NAME, HOURLY_INTERVAL);        
SET
CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(NOW(), 
'%Y-%m-%d 00:00:00'
));        IF 
DATE
(NOW()) = 
'2014-04-01'
THEN
                
SET
CUR_TIME = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 
DAY
), 
'%Y-%m-%d 00:00:00'
));        
END
IF;        
SET
@__interval = 1;
        
create_loop: LOOP                IF @__interval > CREATE_NEXT_INTERVALS 
THEN
                        
LEAVE create_loop;                
END
IF;
                 
SET
LESS_THAN_TIMESTAMP = CUR_TIME + (HOURLY_INTERVAL * @__interval * 3600);                
SET
PARTITION_NAME = FROM_UNIXTIME(CUR_TIME + HOURLY_INTERVAL * (@__interval - 1) * 3600, 
'p%Y%m%d%H00'
);                CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);                
SET
@__interval=@__interval+1;        
END
LOOP;
         
SET
OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVAL KEEP_DATA_DAYS 
DAY
), 
'%Y%m%d0000'
);        CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);
 
END
$$
DELIMITER ;
          
        

                               
DELIMITER $$
CREATE
PROCEDURE
`partition_verify`(SCHEMANAME 
VARCHAR
(64), TABLENAME 
VARCHAR
(64), HOURLYINTERVAL 
INT
(11))
BEGIN
        
DECLARE
PARTITION_NAME 
VARCHAR
(16);        
DECLARE
RETROWS 
INT
(11);        
DECLARE
FUTURE_TIMESTAMP 
TIMESTAMP
;
         
/*
         
* 
Check
if 
any
partitions exist 
for
the given SCHEMANAME.TABLENAME.
         
*/
        
SELECT
COUNT
(1) 
INTO
RETROWS        
FROM
information_schema.partitions        
WHERE
table_schema = SCHEMANAME 
AND
TABLE_NAME = TABLENAME 
AND
partition_name 
IS
NULL
;
         
/*
         
* If partitions do 
not
exist, go ahead 
and
partition the 
table
         
*/
        
IF RETROWS = 1 
THEN
                
/*
                 
* Take the 
current
date
at
00:00:00 
and
add
HOURLYINTERVAL 
to
it.  This 
is
the 
timestamp
below which we will store 
values
.
                 
* We 
begin
partitioning based 
on
the beginning 
of
a 
day
.  This 
is
because we don
't want to generate a random partition
                 
* that won'
t necessarily fall 
in
line 
with
the desired partition naming (ie: if the 
hour
interval 
is
24 hours, we could
                 
* 
end
up creating a partition now named 
"p201403270600"
when
all
other partitions will be 
like
"p201403280000"
).
                 
*/
                
SET
FUTURE_TIMESTAMP = TIMESTAMPADD(
HOUR
, HOURLYINTERVAL, CONCAT(CURDATE(), 
" "
, 
'00:00:00'
));                
SET
PARTITION_NAME = DATE_FORMAT(CURDATE(), 
'p%Y%m%d%H00'
);
                 
-- Create the partitioning query
                
SET
@__PARTITION_SQL = CONCAT(
"ALTER TABLE "
, SCHEMANAME, 
"."
, TABLENAME, 
" PARTITION BY RANGE(`clock`)"
);                
SET
@__PARTITION_SQL = CONCAT(@__PARTITION_SQL, 
"(PARTITION "
, PARTITION_NAME, 
" VALUES LESS THAN ("
, UNIX_TIMESTAMP(FUTURE_TIMESTAMP), 
"));"
);
                 
-- Run the partitioning query
                
PREPARE
STMT 
FROM
@__PARTITION_SQL;                
EXECUTE
STMT;
                
DEALLOCATE
PREPARE
STMT;        
END
IF;
END
$$
DELIMITER ;
          
        

可以将这四个存储过程写成一个SQL文件直接导入：

                               
/usr/local/mysql/bin/mysql
-uroot -p
'123456'
zabbix < partition_call.sql

三、使用存储过程：

                               
mysql> CALL partition_maintenance(
'<zabbix_db_name>'
, 
'<table_name>'
, <days_to_keep_data>, <hourly_interval>, <num_future_intervals_to_create>)
          
        

例（不要急着直接执行，后面统一运行）：

                               
mysql> CALL partition_maintenance(
'zabbix'
, 
'history'
, 7, 24, 7);
          
        

zabbix_db_name：库名

table_name：表名

days_to_keep_data：保存多少天的数据

hourly_interval：每隔多久生成一个分区

num_future_intervals_to_create：本次一共生成多少个分区

这个例子就是history表最多保存7天的数据，每隔24小时生成一个分区，这次一共生成7个分区

下面这个存储过程就是统一调用：

                               
DELIMITER $$
CREATE
PROCEDURE
`partition_maintenance_all`(SCHEMA_NAME 
VARCHAR
(32))
BEGIN
                
CALL partition_maintenance(SCHEMA_NAME, 
'history'
, 28, 24, 14);
                
CALL partition_maintenance(SCHEMA_NAME, 
'history_log'
, 28, 24, 14);
                
CALL partition_maintenance(SCHEMA_NAME, 
'history_str'
, 28, 24, 14);
                
CALL partition_maintenance(SCHEMA_NAME, 
'history_text'
, 28, 24, 14);
                
CALL partition_maintenance(SCHEMA_NAME, 
'history_uint'
, 28, 24, 14);
                
CALL partition_maintenance(SCHEMA_NAME, 
'trends'
, 730, 24, 14);
                
CALL partition_maintenance(SCHEMA_NAME, 
'trends_uint'
, 730, 24, 14);
END
$$
DELIMITER ;
          
        

保存成文件，再次导入存储过程：

                               
/usr/local/mysql/bin/mysql
-uroot -p
'123456'
zabbix < partition_all.sql

以后只需要调用这个存储过程就可以了，每天执行一次：

                               
mysql -uzabbix -pzabbix zabbix -e 
"CALL partition_maintenance_all('zabbix');"

四、最后关掉Zabbix的HouseKeeper功能：

最后附上两个存储过程的文件~

参考文章：

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

本文转自运维笔记博客51CTO博客，原文链接http://blog.51cto.com/lihuipeng/1561221如需转载请自行联系原作者

lihuipeng