Tạo phân vùng database Zabbix trên MySQL MariaDB

Trong bài viết này, chúng ta sẽ tìm hiểu từng bước tạo phân vùng database Zabbix trên MySQL MariaDB bằng cách sử dụng tập lệnh phân vùng.

Zabbix thu thập dữ liệu từ các máy chủ và lưu trữ chúng trong cơ sở dữ liệu bằng cách sử dụng bảng lịch sử và xu hướng (History and trends). Lịch sử Zabbix lưu giữ dữ liệu thô (từng giá trị mà Zabbix đã thu thập) và xu hướng lưu trữ dữ liệu tổng hợp hàng giờ được tính trung bình dưới dạng tối thiểu, trung bình và tối đa.

Tạo phân vùng database Zabbix trên MySQL MariaDB

Quy trình housekeeping của Zabbix chịu trách nhiệm xóa dữ liệu lịch sử và xu hướng cũ. Xóa dữ liệu cũ khỏi database bằng cách sử dụng truy vấn xóa SQL, hành động này tác động tiêu cực đến hiệu suất database, có thể sẽ nhận được thông báo “Zabbix housekeeper processes more than 75% busy” vì điều đó.

Vấn đề này có thể được giải quyết với việc phân vùng cơ sở dữ liệu. Phân vùng tạo ra các bảng cho mỗi giờ hoặc mỗi ngày và loại bỏ chúng khi không cần thiết. SQL DROP hiệu quả hơn câu lệnh DELETE.

Bạn có thể áp dụng hướng dẫn này cho bất kỳ phiên bản Zabbix nào.

Trước khi thực hiện các bước tạo phân vùng, nên backup Zabbix database, nếu cài đặt mới thì không cần backup.

Các bước tạo phân vùng database Zabbix trên MySQL MariaDB

Bước 1: Download SQL script để phân vùng

Tải xuống và giải nén file zbx_db_partitiong.sql trên Zabbix server

wget http://bestmonitoringtools.com/dl/zbx_db_partitiong.tar.gz
tar -zxvf zbx_db_partitiong.tar.gz

Mặc định “zbx_db_partitiong.sql” được định cấu hình để lưu lịch sử dữ liệu 7 ngày và 365 ngày dữ liệu xu hướng. Tuy nhiên bạn có thể thay đổi các thông số này bằng cách thay đổi cài đặt file như trong hình bên dưới và lưu lại file.

Tạo phân vùng database Zabbix trên MySQL MariaDB

Bước 2: Tạo phân vùng bằng SQL script

Cú pháp chạy script là : mysql -u ‘<db_username>’ -p'<db_password>’ <zb_database_name> < zbx_db_partitiong.sql

Bây giờ hãy chạy lệnh với db_username, database_name, db_password:

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

Bước 3: Chạy phân vùng tự động

Quản lý phân vùng tự động bằng cách sử dụng MySQL event scheduler

Mặc định MySQL event scheduler bị tắt, bạn cần bật lại bằng cách vào file cấu hình MySQL, tệp cấu hình MySQL ( 10_my_tweaks.cnf ) sẽ được đặt tại “ /etc/mysql/mariadb.conf.d/ ” hoặc “ /etc/my.cnf.d/ “ sửa lại câu lệnh:

[mysqld] 
event_scheduler = ON

Khởi động lại máy chủ SQL

sudo systemctl restart mysql

Lệnh kiểm tra MySQL event scheduler đã được bật hay chưa

root@dbserver:~ $ mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "SHOW VARIABLES LIKE 'event_scheduler';"
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+

Bây giờ chúng ta có thể tạo một event sẽ chạy thủ tục “ partition_maintenance_all ” 12 giờ một lần.

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "CREATE EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all('zabbix');"

Sau 12 giờ, hãy kiểm tra xem event đã được thực hiện thành công hay chưa bằng cách sử dụng lệnh

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "SELECT * FROM INFORMATION_SCHEMA.events\G"
EVENT_CATALOG: def
                   ...
CREATED: 2020-10-24 11:01:07
LAST_ALTERED: 2020-10-24 11:01:07
LAST_EXECUTED: 2020-10-24 11:43:07

Bước 4: Cấu hình Housekeeping trên giao diện web

Truy cập tới Administration -> General -> Housekeeping, đặt các giá trị như hình dưới

Tạo phân vùng database Zabbix trên MySQL MariaDB

Bạn đã hoàn thành! Hãy nhớ rằng phân vùng sẽ xóa lịch sử và bảng xu hướng theo những gì bạn đã cấu hình trong quy trình phân vùng.

Bước 5: Thay đổi cài đặt phân vùng

Trường hợp ban đầu bạn đặt quá nhiều ngày cho lịch sử và xu hướng cho database Zabbix, do đó dung lượng đĩa đầy quá nhanh. Hoặc bạn đã không cấu hình đủ ngày cho lịch sử hoặc xu hướng. Vậy phải làm sao để khắc phục những lỗi này?

Bạn chỉ cần tạo một thủ tục mới mà bạn sẽ chạy thay cho thủ tục cũ.
a. Tạo một thủ tục phân vùng mới

Kết nối với MySQL / MariaDB server:

mysql -u 'zabbix' -p'zabbixDBpass' zabbix

Tạo một thủ tục mới nhưng thay đổi số ngày cho xu hướng và lịch sử theo nhu cầu của bạn, chúng tôi sẽ đặt 30 ngày cho lịch sử và 400 ngày cho xu hướng :

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. Cập nhật MySQL event scheduler

Bây giờ chúng ta phải thay thế thủ tục cũ bằng thủ tục mới

mysql -u 'zabbix' -p'zabbixDBpass' zabbix -e "ALTER EVENT zbx_partitioning ON SCHEDULE EVERY 12 HOUR DO CALL partition_maintenance_all_30and400('zabbix');"

Bước 6: Thông tin về tập lệnh phân vùng Zabbix

Tập lệnh SQL phân vùng Zabbix được sử dụng trong hướng dẫn này chứa các thủ tục phân vùng sau:

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 ;

Quá trình các bước tạo phân vùng, thay đổi phân vùng database Zabbix trên MySQL MariaDB đã hoàn thành.

5/5 - (31 bình chọn)

Viết một bình luận