Zabbix MySQL Database Partitioning 表分区优化

前言

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:

1. partition_create - This will create a partition on the given table in the given schema.
2. partition_drop - This will drop partitions older than the given timestamp on the given table in the given schema.
3. 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.
4. 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再创建新的

1. login mysql zabbix
2. DROP PROCEDURE IF EXISTS partition_maintenance_all
3. 根据需要修改括号内第三列的时间，估计得改成45或者30了。每列的定义请参照最上面给的链接
4. 再手动跑下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/