Zabbix MySQL Database Partitioning 表分区优化

栏目: 数据库 · Mysql · 发布时间: 5年前

内容简介：Zabbix 默认会启用 housekeeping 功能用于清理 history/trend 等历史数据，当监控服务器数量增加，保留时间有要求的情况下，housekeeping 的清理策略就会造成 Zabbix Server 性能下降，比如查询历史监控数据等。Zabbix 官方的建议是直接在数据库按照时间唯独创建分区表并定时清理，好处自然就是减少 Zabbix Server 的负担提升 Zabbix 应用对于数据库读写性能。Zabbix 3.4 之后的版本增加了对 Elasticsearch 的支持，个人

Zabbix 默认会启用 housekeeping 功能用于清理 history/trend 等历史数据，当监控服务器数量增加，保留时间有要求的情况下，housekeeping 的清理策略就会造成 Zabbix Server 性能下降，比如查询历史监控数据等。Zabbix 官方的建议是直接在数据库按照时间唯独创建分区表并定时清理，好处自然就是减少 Zabbix Server 的负担提升 Zabbix 应用对于数据库读写性能。Zabbix 3.4 之后的版本增加了对 Elasticsearch 的支持，个人也非常推荐大家尝试。

Zabbix MySQL Database Partitioning 表分区优化禁用 housekeeping 提升历史数据清理性能

更新历史

2018 年 12 月 02 日 - 初稿

阅读原文 - https://wsgzao.github.io/post/zabbix-mysql-partition/

扩展阅读

MySQL Database Partitioning - https://www.zabbix.org/wiki/Docs/howto/mysql_partition

禁用 housekeeping

Zabbix 2.2+ 版本请直接通过 UI 界面禁用 housekeeping

Zabbix UI in the “Administration” -> “General” section. Make sure you select “Housekeeping” in the drop-down in the upper right.

Make sure that the checkboxes with the title “Enable internal housekeeping” are unchecked for both History and Trends.
Make sure that the checkboxes with the title “Override item <trend/history> period” are checked for both History and Trends.
Set the “Data storage period (in days)” box for history and trends to the amount of time you are retaining both. In the examples that were given, the correct values are 28 and 730 (as seen in the screenshot).

创建存储过程

Zabbix 数据库优化

目的: 快速清理历史数据，并减少数据存储容量

方法: 历史表使用分区表 (删除分区表速度快)

Zabbix 版本: 3.2 + 以上版本，其它版本需要创建 Index 索引，请参考官方文档

涉及表项: 存储不同类型 item 的历史数据，最终 1 小时或者 1 天等段时间的绘图数据从其中获取 history、history_log、history_str、history_text、history_uint，存储不同类型 item 的历史趋势数据，每隔一小时从历史数据中统计一次，并计算统计区间的平均值，最大值，最小值 trends、trends_uint

Alright, let’s go ahead and add the necessary stored procedures to the Zabbix database. These procedures are documented in the Documentation section if you want more details about how they work. This section is going to look a little messy, but don’t be intimidated. Just copy/paste each section of SQL code. Due to weirdness in how the output will look, I can’t really show the”mysql>”prompts here. As long as you see”Query OK, 0 rows affected (0.00 sec)”after running each section AND make sure the”DELIMITER ;”SQL actually runs (sometimes you’ll have to press ENTER), you’ll be fine.

# login mysql
mysql -uroot -p
zabbix

mysql> use zabbix;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show variables like '%dir%';
+-----------------------------------------+-------------------------------------+
| Variable_name                           | Value                               |
+-----------------------------------------+-------------------------------------+
| basedir                                 | /usr/                               |
| binlog_direct_non_transactional_updates | OFF                                 |
| character_sets_dir                      | /usr/share/percona-server/charsets/ |
| datadir                                 | /var/lib/mysql/                     |
| ignore_db_dirs                          |                                     |
| innodb_data_home_dir                    |                                     |
| innodb_log_group_home_dir               | ./                                  |
| innodb_max_dirty_pages_pct              | 75.000000                           |
| innodb_max_dirty_pages_pct_lwm          | 0.000000                            |
| innodb_tmpdir                           |                                     |
| innodb_undo_directory                   | ./                                  |
| lc_messages_dir                         | /usr/share/percona-server/          |
| plugin_dir                              | /usr/lib64/mysql/plugin/            |
| slave_load_tmpdir                       | /tmp                                |
| tmpdir                                  | /tmp                                |
+-----------------------------------------+-------------------------------------+
15 rows in set (0.07 sec)

mysql>
SELECT table_name AS "Tables",
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = 'zabbix'
ORDER BY (data_length + index_length) DESC;

+----------------------------+------------+
| Tables                     | Size in MB |
+----------------------------+------------+
| history                    |  545043.75 |
| history_uint               |   44729.66 |
| trends                     |   13500.41 |
| trends_uint                |    1666.66 |
| history_text               |     650.31 |


# Zabbix > 3.2, history 30, Trends 300

#cat partition.sql
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_description >= CLOCK;

        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 OLD_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'));

        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');
                IF(PARTITION_NAME != OLD_PARTITION_NAME) THEN
                        CALL partition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);
                END IF;
                SET @__interval=@__interval+1;
                SET OLD_PARTITION_NAME = PARTITION_NAME;
        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 ;

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

There are four functions in the stored procedures:

partition_create - This will create a partition on the given table in the given schema.
partition_drop - This will drop partitions older than the given timestamp on the given table in the given schema.
partition_maintenance - This function is what is called by users. It is responsible for parsing the given parameters and then creating/dropping partitions as needed.
partition_verify - Checks if partitioning is enabled on the given table in the given schema. If it is not enable, it creates a single partition.

存储过程执行后将可以使用命令对想要分区的表进行表分区了，其中的参数我这里解释一下。

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

这是举例：

CALL partition_maintenance(zabbix, 'history_uint', 31, 24, 14);

zabbix_db_name：库名

table_name：表名

days_to_keep_data：保存多少天的数据

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

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

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

这里可以将上面四个存储过程保存为一个文件，导入到数据库中，文件我稍后将会放在附件中，这里使用的命令是

mysql -uzabbix -pzabbix zabbix < partition_call.sql

然后可以将 CALL 统一调用也做成一个文件

创建定时任务

cronjob 里的脚本包括了建新表和删除旧表，用 mysql 的 procedure 控制，删除旧表可以释放空间
想要修改短一点，需要修改 procedure partition_maintenance_all 里规定的时间
我的做法是 Drop 旧 procedure 再创建新的

login mysql zabbix
DROP PROCEDURE IF EXISTS partition_maintenance_all
根据需要修改括号内第三列的时间，估计得改成 45 或者 30 了。每列的定义请参照最上面给的链接
再手动跑下 cronjob 内的那个指令就好

[root@sg-gop-10-65-200-90 mysql]# grep -Ev '^$|#' /etc/zabbix/zabbix_server.conf
LogFile=/var/log/zabbix/zabbix_server.log
LogFileSize=0
PidFile=/var/run/zabbix/zabbix_server.pid
SocketDir=/var/run/zabbix
DBName=zabbix
DBUser=zabbix
DBPassword=zabbix
StartPollers=500
StartPingers=50
SNMPTrapperFile=/var/log/snmptrap/snmptrap.log
CacheSize=8G
TrendCacheSize=1G
Timeout=15
AlertScriptsPath=/usr/lib/zabbix/alertscripts
ExternalScripts=/usr/lib/zabbix/externalscripts
LogSlowQueries=3000

[root@sg-gop-10-65-200-90 percona-server.conf.d]# grep -Ev '^$|#' /etc/percona-server.conf.d/mysqld.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
max_connections=1000

# import partition.sql
mysql -u'zabbix' -p'zabbix' zabbix  < partition.sql
# run
nohup mysql -u'zabbix' -p'zabbix' 'zabbix' -e "CALL partition_maintenance_all('zabbix')" &> /root/partition.log&
tail -f /root/partition.log

# 查看过程逻辑 
show create procedure partition_maintenance_all \G;
# 删除存储过程 
drop procedure if exists partition_maintenance_all; 
# 查看存储过程 
show procedure status like 'partition_maintenance%' \G;
# 查看 
show create table history

# crontab
[root@sg-gop-10-65-200-90 wangao]# cat /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

15 3 * * * root bash /opt/sa_scripts/zabbix_partitioning.sh

# create script
[root@sg-gop-10-65-200-90 wangao]# cat /opt/sa_scripts/zabbix_partitioning.sh
#!/bin/bash

user='zabbix'
password='zabbix'
database='zabbix'

mysql -u${user} -p$password $database -e "CALL partition_maintenance_all('zabbix');"

# mod
chmod 755 /opt/sa_scripts/zabbix_partitioning.sh
service crond restart

参考文档

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

https://cloud.tencent.com/developer/article/1006301

https://wsgzao.github.io/post/zabbix-mysql-partition/

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。

码农书籍

引爆流行

[美] 马尔科姆·格拉德威尔 / 钱清、覃爱冬 / 中信出版社 / 2002-7 / 18.00元

马尔科姆·格拉德威尔以社会上突如其来的流行风潮研究为切入点，从一个全新的角度探索了控制科学和营销模式。他认为，思想、行为、信息以及产品常常会像传染病爆发一样，迅速传播蔓延。正如一个病人就能引起一场全城流感；如果个别工作人员对顾客大打出手，或几位涂鸦爱好者管不住自己，也能在地铁里掀起一场犯罪浪潮；一位满意而归的顾客还能让新开张的餐馆座无虚席。这些现象均属“社会流行潮”，它爆发的那一刻，即达到临界水平......一起来看看《引爆流行》这本书的介绍吧!

码农工具