• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

[翻译]——Zabbix: Partitioning MySQL / MariaDB database Tables in 5 min

互联网 diligentman 1个月前 (10-27) 20次浏览

 

前言:本文是对这篇博客Zabbix: Partitioning MySQL / MariaDB database Tables in 5 min的翻译,翻译如有不当的地方,敬请谅解,请尊重原创和翻译劳动成果,转载的时候请注明出处。谢谢!

 

英文地址:https://bestmonitoringtools.com/zabbix-partitioning-tables-on-mysql-database

 

在本教程中,我们将逐步学习如何使用分区脚本(partitioning script)在MySQL或MariaDB服务器上对Zabbix数据库(history和trends表)进行分区。

 

Zabbix从主机采集数据并将其存储在history和trends表中,Zabbix的history表中保存原始数据(Zabbix采集的每一个值),trends表中存储每小时内的合并数据,那些数据的平均值、最小值、最大值。

 

Zabbix的housekeeping进程负责删除trends表和history表中的旧数据。使用delete语句从数据库删除旧数据可能对数据库性能产生负面的性能影响。因此,我们中许多人都收到过令人讨厌的警报Zabbix housekeeper processes more than 75% busy

 

 

这个问题能够通过数据库分区方案轻松的解决。每小时或每一天为分区表创建一个分区,并在不需要它们的时候,删除这些分区。使用SQL删除分区比使用DELET语句删除数据更高效。

 

你可以将本教程用于任何Zabbix 3.0之后的版本(3.2、3.4、4.0、4.2、4.4、5.0、5.2等)

 

在开始之前,请备份你的zabbix数据库,但是如果是新安装的Zabbix Server,则无需备份。

 

步骤1:下载分区脚本进行分区

 

数据库服务器上下载并解压SQL脚本zbx_db_partitiong.sql

 

wget https://bestmonitoringtools.com/wp-content/uploads/2019/10/zbx_db_partitiong.tar.gz
 
tar -zxvf zbx_db_partitiong.tar.gz

 

脚本”zbx_db_partitiong.sql“中配置保留hisotry表中7天的数据和trends表中365天的数据——如果你可以接受这些设置,请转到步骤2.

 

如果你想修改trends表和history表中保留数据的天数,请打开zbx_db_partitiong.sql文件,如下图所示,修改设置并保存文件。

 

[翻译]——Zabbix: Partitioning MySQL / MariaDB database Tables in 5 min

 

 

步骤2:使用脚本创建分区的存储过程。

 

运行脚本的语法为mysql -u ‘<db_username>’ -p'<db_password>’ <zb_database_name> < zbx_db_partitiong.sql,现在用你Zabbix服务器的数据库名称、用户名和密码去创建这些存储过程。

 

mysql -u 'zabbix' -p'zabbixDBpass' zabbix < zbx_db_partitiong.sql

 

在新安装的Zabbix 服务器上,,脚本将非常快速的创建分区过程,但是在大型数据库上,此过程坑你持续数小时。

 

译者点评: 这里应该是作者的笔误或表达有误,不管zabbix数据库多大,创建这些存储过程都非常快,但是在大型的zabbix数据库上,运行这些维护分区(创建分区、删除分区)的存储过程脚本将非常慢。猜测是作者表达有误!

 

步骤3:创建crontab作业进行分区

 

注意:如果没有正确设置Crontab作业来定期创建新分区,Zabbix将会停止采集数据,并且在MySQL的日志文件中出现[Z3005] query failed: [1526] Table has no partition for value ….”这样的错误。

 

这一步是最重要的,因为必须定期有规律的删除分区和创建分区!您不必为此而烦恼,因为我们使用crontab作业完成这些任务。

 

使用命令sudo crontab -e打开crontab文件,在文件中的任何位置添加以下命令行,在每天03:30 AM对Zabbix数据库进行分区维护操作:

 

30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

 

译者点评: 这种使用账号密码明文的方式,其实不太安全,应该使用mysql_config_editor技术配置账号密码,避免在作业或脚本中使用数据库账号明文

 

30 03 * * * /usr/bin/mysql --login-path=zabbixdb zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

 

Crontab作业会执行脚本,通过存储过程维护分区(删除掉旧分区和创建新分区),并且将这些操作记录在日志““/tmp/CronDBpartitiong.log中。

 

因此,如果你没有耐心等待作业运行,也可以从终端运行这个命令

 

root@dbserver:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');"
+-----------------------------------------------------------+
| msg |
+-----------------------------------------------------------+
| partition_create(zabbix,history,p201910150000,1571180400) |
+-----------------------------------------------------------+
+-----------------------------------------------------------+
...etc.

 

然后检查分区状态,如下所示:

 

root@dbserver:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "show create table historyG"
Table: history
Create Table: 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 COLLATE=utf8_bin
/*!50100 PARTITION BY RANGE (clock)
(PARTITION p201910140000 VALUES LESS THAN (1571094000) ENGINE = InnoDB,
PARTITION p201910150000 VALUES LESS THAN (1571180400) ENGINE = InnoDB,
PARTITION p201910160000 VALUES LESS THAN (1571266800) ENGINE = InnoDB) */

 

从上面输出结果中,你可以看到我们为history和trend表创建了3个分区。

 

译者点评:运行这些脚本,只会创建当前日期往后的三个分区,所以存在一些弊端,如果history等表中已经有大量历史数据,那么此时最靠前的一个分区将会变得无比巨大。

 

步骤4: 在Zabbix前端中配置Housekeeping

 

[翻译]——Zabbix: Partitioning MySQL / MariaDB database Tables in 5 min

 

如果图片没有表述清楚,请按照以下步骤在Zabbix前端上配置Housekeeping

 

转到Housekeeping部分:管理——>常规(或者是一般的翻译)——>Housekeeping

取消历史记录和趋势部分下启用内部整理(Enable internal housekeeping)的复选标记;

历史记录和趋势部分的替代项目趋势期(Override item trend period)上打勾;

历史记录和趋势部分下,为趋势和历史记录定义数据存储期的天数(必须与数据库分区中配置的天数相同-历史记录应为7天,趋势图应为365天,如果您未更改 脚本中的默认设置);

单击更新按钮。

 

步骤5: 修改分区设置(修改history和trends表中的天数)

 

有时候,你可能最初为history表和trends表设置了太长的时间,因此磁盘空间消耗的太快了,或者相反的情况,你没有为history和trends表配置足够的天数,那该怎么办呢?

 

你无需再次运行该脚本,只需创建一个新过程并将其设置为从crontab作业中运行即可。

 

 

a) 创建新的分区存储过程

 

连接到MySQL或MariaDB服务器:

 

mysql -u ‘zabbix’ -p’zabbixDBpass’

use zabbix;

 

创建一个新的存储过程,但是根据你的需要修改history和trends表保留的天数。我一般会为history表保留30天的数据,trends表保留400天的数据。

 

DELIMITER $$
 
CREATE PROCEDURE partition_maintenance_all_30and400(SCHEMA_NAME VARCHAR(32))
 
BEGIN
 
CALL partition_maintenance(SCHEMA_NAME, 'history', 30, 24, 3);
 
CALL partition_maintenance(SCHEMA_NAME, 'history_log', 30, 24, 3);
 
CALL partition_maintenance(SCHEMA_NAME, 'history_str', 30, 24, 3);
 
CALL partition_maintenance(SCHEMA_NAME, 'history_text', 30, 24, 3);
 
CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 30, 24, 3);
 
CALL partition_maintenance(SCHEMA_NAME, 'trends', 400, 24, 3);
 
CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 400, 24, 3);
 
END$$
 
DELIMITER ;

 

 

b)更新crontab作业

 

 用命令sudo crontab -e”打开crontab文件,注释掉旧的作业命令,并新增一个新的作业

 

# old procedure, still exists in the database so it can be used if needed
#
# 30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all('zabbix');" > /tmp/CronDBpartitiong.log 2>&1
30 03 * * * /usr/bin/mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CALL partition_maintenance_all_30and400('zabbix');" > /tmp/CronDBpartitiong.log 2>&1

 

 

 

步骤6:关于Zabbix分区脚本的信息

 

本指南中使用的Zabbix分区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', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'history_log', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'history_str', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'history_text', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'history_uint', 7, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'trends', 365, 24, 3);
                CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 365, 24, 3);
END$$
DELIMITER ;

你还需要更多信息吗? 请观看有关Zabbix的MySQL数据库分区的视频。

 

 

 


喜欢 (0)