zabbix mysql優化 my.cnf_Mysql優化之Zabbix分區優化

使用zabbix最大的瓶頸在于資料庫，維護好zabbix的資料存儲，告警，就能很好地應用zabbix去建構監控系統。目前zabbix的資料主要存儲在history和trends的2個表中，随着時間的推移，這兩個表變得非常大，性能會非常差，影響監控的使用。對MySQL進行調優，能夠極大的提升Zabbix的性能，本文采用對MySQL進行分區的方法進行調優。

原理

對zabbix中的history和trends等表進行分區，按日期進行分區，每天一個，共保留90天分區。

操作詳細步驟

操作影響： 可以線上操作，MySQL的讀寫變慢，Zabbix性能變慢，影響時間根據資料的小而變化，一般在2個小時左右。

第一步

登入zabbix server的資料庫，統一MySQL的配置

cat > /etc/my.cnf<

[mysqld]

datadir=/data/mysql

socket=/var/lib/mysql/mysql.sock

default-storage-engine = innodb

collation-server = utf8_general_ci

init-connect = 'SET NAMES utf8'

character-set-server = utf8

symbolic-links=0

max_connections=4096

innodb_buffer_pool_size=12G

max_allowed_packet = 32M

join_buffer_size=2M

sort_buffer_size=2M

query_cache_size = 64M

query_cache_limit = 4M

thread_concurrency = 8

table_open_cache=1024

innodb_flush_log_at_trx_commit = 0

long_query_time = 1

log-slow-queries =/data/mysql/mysql-slow.log

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

#[mysql]

#socket=/data/mysql/mysql.sock

#

# include all files from the config directory

#

!includedir /etc/my.cnf.d

EOF

注意：一定要修改innodb_buffer_pool_size=實體記憶體的1/3

第二步

先确認zabbix的版本，本操作zabbix的版本一定要大于3.2.0。小于3.2的版本不能安裝此操作，線上預設是zabbix-3.2.6。

a、 導入存儲過程

#cat partition.sql

DELIMITER $$

CREATE PROCEDURE `partition_create`(SCHEMANAMEvarchar(64), TABLENAME varchar(64), PARTITIONNAME varchar(64), CLOCK int)

BEGIN

DECLARE RETROWS INT;

SELECT COUNT(1) INTO RETROWS

FROM information_schema.partitions

WHERE table_schema = SCHEMANAME AND table_name = TABLENAME ANDpartition_description >= CLOCK;

IF RETROWS = 0 THEN

SELECT CONCAT( "partition_create(", SCHEMANAME, ",",TABLENAME, ",", PARTITIONNAME, ",", CLOCK, ")" )AS msg;

SET @sql = CONCAT( 'ALTER TABLE ', SCHEMANAME, '.', TABLENAME, ' ADDPARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));' );

PREPARE STMT FROM @sql;

EXECUTE STMT;

DEALLOCATE PREPARE STMT;

END IF;

END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE `partition_drop`(SCHEMANAMEVARCHAR(64), TABLENAME VARCHAR(64), DELETE_BELOW_PARTITION_DATE BIGINT)

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE drop_part_name VARCHAR(16);

DECLARE myCursor CURSOR FOR

SELECT partition_name

FROM information_schema.partitions

WHERE table_schema = SCHEMANAME AND table_name = TABLENAME ANDCAST(SUBSTRING(partition_name FROM 2) AS UNSIGNED)

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

SET @alter_header = CONCAT("ALTER TABLE ", SCHEMANAME,".", TABLENAME, " DROP PARTITION ");

SET @drop_partitions = "";

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

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

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

CALLpartition_create(SCHEMA_NAME, TABLE_NAME, PARTITION_NAME, LESS_THAN_TIMESTAMP);

END IF;

SET @[email protected]__interval+1;

SET OLD_PARTITION_NAME = PARTITION_NAME;

END LOOP;

SET OLDER_THAN_PARTITION_DATE=DATE_FORMAT(DATE_SUB(NOW(), INTERVALKEEP_DATA_DAYS DAY), '%Y%m%d0000');

CALL partition_drop(SCHEMA_NAME, TABLE_NAME, OLDER_THAN_PARTITION_DATE);

END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE `partition_verify`(SCHEMANAMEVARCHAR(64), TABLENAME VARCHAR(64), HOURLYINTERVAL INT(11))

BEGIN

DECLARE PARTITION_NAME VARCHAR(16);

DECLARE RETROWS INT(11);

DECLARE FUTURE_TIMESTAMP TIMESTAMP;

SELECT COUNT(1) INTO RETROWS

FROM information_schema.partitions

WHERE table_schema = SCHEMANAME AND table_name = TABLENAME ANDpartition_name IS NULL;

IFRETROWS = 1 THEN

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', 90, 24, 14);

CALL partition_maintenance(SCHEMA_NAME, 'history_log', 90, 24, 14);

CALL partition_maintenance(SCHEMA_NAME, 'history_str', 90, 24, 14);

CALL partition_maintenance(SCHEMA_NAME, 'history_text', 90, 24, 14);

CALLpartition_maintenance(SCHEMA_NAME, 'history_uint', 90, 24, 14);

CALL partition_maintenance(SCHEMA_NAME, 'trends', 730, 24, 14);

CALL partition_maintenance(SCHEMA_NAME, 'trends_uint', 730, 24, 14);

END$$

DELIMITER ;

上面内容包含了建立分區的存儲過程，将上面内容複制到partition.sql中,然後執行如下：

mysql -uzabbix -pzabbix zabbix < partition.sql

b、 添加crontable,每天執行01點01分執行，如下：

crontab -l > crontab.txt

cat >> crontab.txt <

#zabbix partition_maintenance

01 01 * * * mysql -uzabbix -pzabbix zabbix -e"CALL partition_maintenance_all('zabbix')" &>/dev/null

EOF

cat crontab.txt |crontab

注意： mysql的zabbix使用者的密碼部分按照實際環境配置

c、首先執行一次(由于首次執行的時間較長，請使用nohup執行)，如下：

nohup mysql -uzabbix -pzabbix zabbix -e "CALLpartition_maintenance_all('zabbix')" &> /root/partition.log&

注意：觀察/root/partition.log的輸出

d、 檢視結果

登入mysql,檢視history等表, 如下：

MariaDB [zabbix]> showcreate table history

| history | 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

|

發現了大量PARTITION字段，說明配置正确。注意觀察Mysql的Slow Query,一般到執行操作的第二天，Slow Query幾乎就會有了，此時Zabbix的Dashboard響應速度應該非常流暢了。