Monitoring/Zabbix

[Zabbix 4.4] 자빅스-MySQL 성능 튜닝 & MySQL 파티셔닝 (zabbix mysql tuning, zabbix busy housekeeper processes 100%, zabbix history write cache 100%, zabbix history syncer process 100%, zabbix disk i/o, zabbix mysql partitioning)

Nirsa 2020. 6. 23. 18:01
반응형

 

  • 자빅스-MySQL 성능 튜닝 & MySQL 파티셔닝 (zabbix mysql tuning, zabbix busy housekeeper processes 100%, zabbix history write cache 100%, zabbix history syncer process 100%, zabbix disk i/o, zabbix mysql partitioning)

성능 튜닝 및 mysql partitioning을 진행하게된 계기는 자빅스 서버 구축 후 history write cache, history syncer 프로세스가 증가하며 housekeeper가 실행되고, disk i/o 증가로 인해 자빅스 자체가 느려지면서 30분 단위로 발생/정상화되는 현상이 발생 하였습니다.

당장 모니터링에 무언가 문제가 생기거나 하지는 않았지만, 자빅스 웹페이지가 많이 느려지면서 발생 지속 시간이 조금씩 길어지기도 하고 disk i/o 부하로 인해 심하면 모니터링 불가능한 상황까지 예측되어 성능 튜닝 및 MySQL Partitioning을 진행하게 되었습니다.

housekeeper는 단일 프로세스로 평소 실행되지 않을 땐 0% 이지만, 프로세스가 실행되면 100% 으로 표시가 됩니다. housekeeper 프로세스는 데이터를 일정 기간 저장하다가 삭제할때가 되면 삭제를 해주는 프로세스로 생각하시면 됩니다.

 

이러한 문제를 해결하기 위해 history, trends housekeeper를 비활성화하고, history 관련 프로세스 및 disk i/o의 부하를 줄이고 안정화 시키기 위해 Zabbix와 MySQL 튜닝을 진행하기로 결정 했습니다.

구성 환경은 아래와 같으며 성능 튜닝 후 메모리는 약 75~80% 유지하며 사용중입니다. 환경에 맞춰 값을 바꿔서 사용 하시기 바랍니다.

  • CPUs : 6
  • Memory : 32GB
  • OS : CentOS 7.7 1908
  • Zabbix Version : 4.4
  • MySQL Version : 5.7

 

빨간색 : 최초 발생 및 1차 성능 튜닝 작업 / 파란색 : Zabbix, MySQL 성능 튜닝 및 MySQL Partitioning 작업 후
빨간색 : 최초 발생 및 1차 성능 튜닝 작업 / 파란색 : Zabbix, MySQL 성능 튜닝 및 MySQL Partitioning 작업 후
빨간색 : 최초 발생 및 1차 성능 튜닝 작업 / 파란색 : Zabbix, MySQL 성능 튜닝 및 MySQL Partitioning 작업 후

 

  • Zabbix & Mysql Tuning
  • /etc/zabbix/zabbix_server.conf

아래는 zabbix_server.conf에 추가한 내용들입니다. 몇몇 옵션들이 좀 과한감이 있기는해서 추후 테스트를 통해 조정해나갈 생각 입니다.

StartPollers=250
StartPollersUnreachable=60
StartTrappers=10
StartPingers=30
StartDiscoverers=70
MaxHousekeeperDelete=1500
CacheSize=1G
HistoryCacheSize=512M
HistoryIndexCacheSize=512M
TrendCacheSize=512M
ValueCacheSize=1G
Timeout=10
LogSlowQueries=3000

 

  • /etc/my.cnf

아래 옵션도 추후 조정해나갈 예정 입니다.

long_query_time=5
slow_query_log = 1
slow_query_log_file = /var/log/mysql_slow.log
query_cache_type=1

event_scheduler=ON

skip-character-set-client-handshake
key_buffer_size = 256M
max_allowed_packet = 200M
max_connections=500
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 512M
log_bin_trust_function_creators = TRUE
binlog_format=row
table_definition_cache = 20000
table_open_cache = 4000
table_open_cache_instances = 16
open_files_limit = 65536
transaction-isolation = READ-COMMITTED

innodb_locks_unsafe_for_binlog = 1
innodb_autoextend_increment = 256
innodb_buffer_pool_instances = 12
innodb_buffer_pool_size = 20G
innodb_concurrency_tickets = 5000
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_log_file_size = 512M
innodb_log_files_in_group = 8
innodb_old_blocks_time = 1000
innodb_open_files = 2048
innodb_stats_on_metadata = OFF
innodb_force_recovery = 0

skip-name-resolve
default_storage_engine = innodb
binlog-row-event-max-size = 8192
binlog-format = MIXED
character_set_server = utf8
collation_server = utf8_bin
expire_logs_days = 1
join_buffer_size = 262144
max_allowed_packet = 32M
max_connect_errors = 10000
max_connections = 5000
max_heap_table_size = 134217728
port = 3306
query_cache_type = 1
query_cache_size = 268435456
slow-query-log = ON
table_open_cache = 2048
thread_cache_size = 512
tmp_table_size = 134217728
user = mysql
wait_timeout = 86400

 

  • MySQL Partitioning

이 부분은 www.zabbix.org/wiki/Docs/howto/MySQL_Table_Partitioning_(variant) 에서 설명을 봐가면서 하시는걸 추천 드립니다. 저는 프로시저 방식으로 진행 하였습니다.

MySQL Partitioning 작업 전 확인해주어야할 내용들입니다. mysql -u root -p 를 이용해 mysql 콘솔로 진입 해주세요.

# MySQL 5.6 이전 파티션 기능 활성화 확인 (ACTIVE = 활성화)
SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'have_partitioning';

# MySQL 5.6+ 파티션 기능 활성화 확인 (ACTIVE = 활성화)
SELECT plugin_status FROM information_schema.plugins WHERE plugin_name = 'partition';

# MySQL blackhole engine 사용 가능 여부 확인, 일부 MySQL에서는 사용 불가능하다고 함
SHOW ENGINES\G

# 하우스키핑을 비활성화 하더라도 나중에 하우스 키퍼 테이블을 사용할 하우스 키핑 정보를 계속 기록하기 위해 필요
USE zabbix
ALTER TABLE housekeeper ENGINE = BLACKHOLE;

SHOW GLOBAL VARIABLES LIKE 'event_scheduler';
# OFF 일 경우 아래 명령어 저장, 일시 적용이므로 영구적용 필요 시 /etc/my.cnf 파일에 event_scheduler=ON 저장 필요
# 위의 my.cnf 파일 설정할때에 event_scheduler=ON 을 이미 작성했으므로
# 위 내용을 복붙했을 경우 아래 SET GLOBAL만 진행하시면 됩니다.
SET GLOBAL event_scheduler = ON;

 

# 그대로 복붙하시면 됩니다.
CREATE TABLE `manage_partitions` (
  `tablename` VARCHAR(64) NOT NULL COMMENT 'Table name',
  `period` VARCHAR(64) NOT NULL COMMENT 'Period - daily or monthly',
  `keep_history` INT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'For how many days or months to keep the partitions',
  `last_updated` DATETIME DEFAULT NULL COMMENT 'When a partition was added last time',
  `comments` VARCHAR(128) DEFAULT '1' COMMENT 'Comments',
  PRIMARY KEY (`tablename`)
) ENGINE=INNODB;

INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 30, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 30, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'day', 120, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 120, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'day', 120, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'month', 24, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'month', 24, now(), '');



## 필요한 파티션이 있는지 확인
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `create_next_partitions`$$
 
CREATE PROCEDURE `create_next_partitions`(IN_SCHEMANAME VARCHAR(64))
BEGIN
    DECLARE TABLENAME_TMP VARCHAR(64);
    DECLARE PERIOD_TMP VARCHAR(12);
    DECLARE DONE INT DEFAULT 0;
 
    DECLARE get_prt_tables CURSOR FOR
        SELECT `tablename`, `period`
            FROM manage_partitions;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
    OPEN get_prt_tables;
 
    loop_create_part: LOOP
        IF DONE THEN
            LEAVE loop_create_part;
        END IF;
 
        FETCH get_prt_tables INTO TABLENAME_TMP, PERIOD_TMP;
 
        CASE WHEN PERIOD_TMP = 'day' THEN
                    CALL `create_partition_by_day`(IN_SCHEMANAME, TABLENAME_TMP);
             WHEN PERIOD_TMP = 'month' THEN
                    CALL `create_partition_by_month`(IN_SCHEMANAME, TABLENAME_TMP);
             ELSE
            BEGIN
                            ITERATE loop_create_part;
            END;
        END CASE;
 
                UPDATE manage_partitions set last_updated = NOW() WHERE tablename = TABLENAME_TMP;
    END LOOP loop_create_part;
 
    CLOSE get_prt_tables;
END$$
 
DELIMITER ;


## 일별 파티션 생성
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
 
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE BEGINTIME TIMESTAMP;
        DECLARE ENDTIME INT UNSIGNED;
        DECLARE PARTITIONNAME VARCHAR(16);
        SET BEGINTIME = DATE(NOW()) + INTERVAL 1 DAY;
        SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m_%d' );
 
        SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 DAY);
 
        SELECT COUNT(*) INTO ROWS_CNT
                FROM information_schema.partitions
                WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
 
    IF ROWS_CNT = 0 THEN
                     SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                                ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
                PREPARE STMT FROM @SQL;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        ELSE
        SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
        END IF;
END$$
 
DELIMITER ;



## 월별 파티션 생성
DELIMITER $$

USE `zabbix`$$

DROP PROCEDURE IF EXISTS `create_partition_by_month`$$
 
CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE BEGINTIME TIMESTAMP;
        DECLARE ENDTIME INT UNSIGNED;
        DECLARE PARTITIONNAME VARCHAR(16);
        SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);
        SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m' );
 
        SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH);
 
        SELECT COUNT(*) INTO ROWS_CNT
                FROM information_schema.partitions
                WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
 
    IF ROWS_CNT = 0 THEN
                     SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                                ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
                PREPARE STMT FROM @SQL;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        ELSE
        SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
        END IF;
END$$
 
DELIMITER ;


## 오래된 파티션 확인 및 삭제
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `drop_partitions`$$
 
CREATE PROCEDURE `drop_partitions`(IN_SCHEMANAME VARCHAR(64))
BEGIN
    DECLARE TABLENAME_TMP VARCHAR(64);
    DECLARE PARTITIONNAME_TMP VARCHAR(64);
    DECLARE VALUES_LESS_TMP INT;
    DECLARE PERIOD_TMP VARCHAR(12);
    DECLARE KEEP_HISTORY_TMP INT;
    DECLARE KEEP_HISTORY_BEFORE INT;
    DECLARE DONE INT DEFAULT 0;
    DECLARE get_partitions CURSOR FOR
        SELECT p.`table_name`, p.`partition_name`, LTRIM(RTRIM(p.`partition_description`)), mp.`period`, mp.`keep_history`
            FROM information_schema.partitions p
            JOIN manage_partitions mp ON mp.tablename = p.table_name
            WHERE p.table_schema = IN_SCHEMANAME
            ORDER BY p.table_name, p.subpartition_ordinal_position;
 
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
 
    OPEN get_partitions;
 
    loop_check_prt: LOOP
        IF DONE THEN
            LEAVE loop_check_prt;
        END IF;
 
        FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
        CASE WHEN PERIOD_TMP = 'day' THEN
                SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP DAY));
             WHEN PERIOD_TMP = 'month' THEN
                SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP MONTH - INTERVAL DAY(NOW())-1 DAY));
             ELSE
            BEGIN
                ITERATE loop_check_prt;
            END;
        END CASE;
 
        IF KEEP_HISTORY_BEFORE >= VALUES_LESS_TMP THEN
                CALL drop_old_partition(IN_SCHEMANAME, TABLENAME_TMP, PARTITIONNAME_TMP);
        END IF;
        END LOOP loop_check_prt;
 
        CLOSE get_partitions;
END$$
 
DELIMITER ;


## 지정된 파티션 삭제
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `drop_old_partition`$$
 
CREATE PROCEDURE `drop_old_partition`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64), IN_PARTITIONNAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
 
        SELECT COUNT(*) INTO ROWS_CNT
                FROM information_schema.partitions
                WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = IN_PARTITIONNAME;
 
    IF ROWS_CNT = 1 THEN
                     SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
                                ' DROP PARTITION ', IN_PARTITIONNAME, ';' );
                PREPARE STMT FROM @SQL;
                EXECUTE STMT;
                DEALLOCATE PREPARE STMT;
        ELSE
        SELECT CONCAT("partition `", IN_PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME, "` not exists") AS result;
        END IF;
END$$
 
DELIMITER ;


## 이벤트 스케줄러(작업 예약)
DELIMITER $$
 
USE `zabbix`$$

CREATE EVENT IF NOT EXISTS `e_part_manage`
       ON SCHEDULE EVERY 1 DAY
       STARTS '2020-06-24 04:00:00'
       ON COMPLETION PRESERVE
       ENABLE
       COMMENT 'Creating and dropping partitions'
       DO BEGIN
            CALL zabbix.drop_partitions('zabbix');
            CALL zabbix.create_next_partitions('zabbix');
       END$$
DELIMITER ;

 

  • Housekeeper 종료
  • 자빅스 웹페이지 → Administration → General  

 

  • 우측에서 Housekeeping 선택

 

  • 아래와 같이 체크박스 선택 후 기간을 작성 해줍니다.

 

반응형
저작자표시 비영리 동일조건

'Monitoring > Zabbix' 카테고리의 다른 글

[Zabbix 5.0] 자빅스 5.0 에러 (psql: FATAL: Ident authentication failed for user "zabbix")  (0) 2020.06.24
[Zabbix 5.0] 자빅스 5.0 설치 방법 (zabbix php 7.2, zabbix postgresql 12, zabbix 5.0 install, centos7 zabbix 5.0 install)  (0) 2020.06.24
[Zabbix 4.4] 자빅스 포트 다운 시 서비스 자동 실행 (zabbix action remotecommand)  (3) 2020.06.17
[Zabbix 4.4] 자빅스 가져온 그래프 이미지를 이용해 파워포인트 매크로 제작하기 (보고서 자동화, ppt macro insert image)  (0) 2020.06.17
[Zabbix 4.4] 자빅스 그래프 이미지 파일 수집 (zabbix export graph image)  (0) 2020.06.16

'Monitoring/Zabbix'의 다른글

  • 현재글[Zabbix 4.4] 자빅스-MySQL 성능 튜닝 & MySQL 파티셔닝 (zabbix mysql tuning, zabbix busy housekeeper processes 100%, zabbix history write cache 100%, zabbix history syncer process 100%, zabbix disk i/o, zabbix mysql partitioning)

관련글

티스토리툴바