#昊鼎王五：如何对zabbix进行性能优化(MYSQL分区脚本)？
#引子：
有读者问博主，zabbix用了好久，数据量大，发现越来越慢，怎么办？
博主回答：这个时候就要考虑对zabbix进行性能优化了，今天将“MYSQL分区脚本”实现zabbix性能优化
#MYSQL分区脚本
##Zabbix : mise en place du partitionnement MySQL
Dans le cas où vous monitorez un nombre important de serveurs et d’items avec Zabbix, le partitionnement des tables SQL est une solution viable afin d’optimiser les performances de votre serveur. Le principe est de scinder horizontalement chaque table en divisant l’ensemble des enregistrements en sous-ensembles plus légers, optimisant ainsi les traitements. Les partitions sont considérées en interne comme de nouvelles tables et bénéficient donc de leurs propres index. Lors des requêtes, MySQL consulte uniquement les partitions nécessaires (un EXPLAIN PARTITION d’une requête confirme ce point). Plusieurs types de partitionnement existent, le plus simple probablement à comprendre étant celui par plage (range).
Dans le cas d’une base de données Zabbix, le partitionnement par range s’effectue au niveau du champ clock des tables. Certaines tables sont à partitionner par jour (history) et d’autres par mois ( trends). Le gain direct consiste en une souplesse de suppression des données, limitées dans ce cas à la suppression des partitions les plus anciennes. Le processus de HouseKeeping de Zabbix, visant à nettoyer toutes les tables par un DELETE sur chaque item, n’est donc plus nécessaire. Gain associé : il est possible d’augmenter la période de rétention des trends.
Voici la procédure de partitionnement des tables Zabbix.
###Modification des clés primaires
Le partitionnement par range nécessite certaines modifications au niveaux des clés de certaines tables :
ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledgedid` (`acknowledgeid`);
ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alertid` (`alertid`);
ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditid` (`auditid`);
ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `eventid` (`eventid`);
ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `servicealarmid` (`servicealarmid`);
ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_log` DROP KEY `history_log_2`;
ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`itemid`,`id`,`clock`);
ALTER TABLE `history_text` DROP KEY `history_text_2`;
###Ajout des partitions sur les tables mensuelles
Les tables mensuelles sont :
* acknowledges
* alerts
* auditlog
* events
* service_alarms
* trends
* trends_uint
Conservant 12 mois de données, il faut créer pour chaque table 12 partitions + 1 pour le mois suivant le temps de création. A chaque début de mois, une nouvelle partition sera à ajouter (pour le mois suivant) et la plus ancienne est à supprimer.
Sur chacune de ces tables, les partitions sont créées de cette fa?on (au 31/01/2013) :
ALTER TABLE `acknowledges` PARTITION BY RANGE(clock) (
PARTITION p201201 VALUES LESS THAN (UNIX_TIMESTAMP("2012-02-01 00:00:00")),
PARTITION p201202 VALUES LESS THAN (UNIX_TIMESTAMP("2012-03-01 00:00:00")),
PARTITION p201203 VALUES LESS THAN (UNIX_TIMESTAMP("2012-04-01 00:00:00")),
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")),
PARTITION p201205 VALUES LESS THAN (UNIX_TIMESTAMP("2012-06-01 00:00:00")),
PARTITION p201206 VALUES LESS THAN (UNIX_TIMESTAMP("2012-07-01 00:00:00")),
PARTITION p201207 VALUES LESS THAN (UNIX_TIMESTAMP("2012-08-01 00:00:00")),
PARTITION p201208 VALUES LESS THAN (UNIX_TIMESTAMP("2012-09-01 00:00:00")),
PARTITION p201209 VALUES LESS THAN (UNIX_TIMESTAMP("2012-10-01 00:00:00")),
PARTITION p201210 VALUES LESS THAN (UNIX_TIMESTAMP("2012-11-01 00:00:00")),
PARTITION p201211 VALUES LESS THAN (UNIX_TIMESTAMP("2012-12-01 00:00:00")),
PARTITION p201212 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-01 00:00:00")),
PARTITION p201301 VALUES LESS THAN (UNIX_TIMESTAMP("2013-02-01 00:00:00"))
);
###Ajout des partitions sur les tables journalières
Les tables journalières sont :
* history
* history_log
* history_str
* history_text
* history_uint
L’historique étant de 7 jours, chaque table nécessite 7 partitions + 1 pour le jour suivant le temps de création. Chaque jour, une nouvelle partition sera également à ajouter, la plus ancienne à supprimer.
Sur chacune de ces tables, les partitions sont créées de cette fa?on (au 31/01/2013) :
ALTER TABLE `history` PARTITION BY RANGE(clock) (
PARTITION p20130124 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-25 00:00:00")),
PARTITION p20130125 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-26 00:00:00")),
PARTITION p20130126 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-27 00:00:00")),
PARTITION p20130127 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-28 00:00:00")),
PARTITION p20130128 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-29 00:00:00")),
PARTITION p20130129 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-30 00:00:00")),
PARTITION p20130130 VALUES LESS THAN (UNIX_TIMESTAMP("2013-01-31 00:00:00")),
PARTITION p20130131 VALUES LESS THAN (UNIX_TIMESTAMP("2013-02-01 00:00:00"))
);
###Automatisation de la rotation des partitions
La rotation des partitions peut être effectuée par des triggers SQL. Ceux-si seront alors appelés par script cron.
DELIMITER //
CREATE PROCEDURE `zabbix`.`cron_monthly` ()
BEGIN
CALL zabbix.create_monthly_partition("acknowledges");
CALL zabbix.create_monthly_partition("alerts");
CALL zabbix.create_monthly_partition("auditlog");
CALL zabbix.create_monthly_partition("events");
CALL zabbix.create_monthly_partition("service_alarms");
CALL zabbix.create_monthly_partition("trends");
CALL zabbix.create_monthly_partition("trends_uint");
CALL zabbix.drop_monthly_partition("acknowledges");
CALL zabbix.drop_monthly_partition("alerts");
CALL zabbix.drop_monthly_partition("auditlog");
CALL zabbix.drop_monthly_partition("events");
CALL zabbix.drop_monthly_partition("service_alarms");
CALL zabbix.drop_monthly_partition("trends");
CALL zabbix.drop_monthly_partition("trends_uint");
END //
CREATE PROCEDURE `zabbix`.`cron_daily` ()
BEGIN
CALL zabbix.create_daily_partition("history");
CALL zabbix.create_daily_partition("history_log");
CALL zabbix.create_daily_partition("history_str");
CALL zabbix.create_daily_partition("history_text");
CALL zabbix.create_daily_partition("history_uint");
CALL zabbix.drop_daily_partition("history");
CALL zabbix.drop_daily_partition("history_log");
CALL zabbix.drop_daily_partition("history_str");
CALL zabbix.drop_daily_partition("history_text");
CALL zabbix.drop_daily_partition("history_uint");
END //
CREATE PROCEDURE `zabbix`.`create_monthly_partition` (TABLENAME VARCHAR(64))
BEGIN
DECLARE NEXTCLOCK TIMESTAMP;
DECLARE PARTITIONNAME VARCHAR(16);
DECLARE CLOCK INT;
SET NEXTCLOCK = DATE_ADD(NOW(), INTERVAL 1 MONTH);
SET PARTITIONNAME = DATE_FORMAT(NEXTCLOCK, 'p%Y%m');
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NEXTCLOCK, INTERVAL 1 MONTH), '%Y-%m-01 00:00:00'));
CALL zabbix.create_partition(TABLENAME, PARTITIONNAME, CLOCK);
END //
CREATE PROCEDURE `zabbix`.`drop_monthly_partition` (TABLENAME VARCHAR(64))
BEGIN
DECLARE OLDCLOCK TIMESTAMP;
DECLARE PARTITIONNAME VARCHAR(16);
DECLARE CLOCK INT;
SET @maxdays = 365;
SET @old = @maxdays+1;
SET OLDCLOCK = DATE_SUB(NOW(), INTERVAL @old DAY);
SET PARTITIONNAME = DATE_FORMAT(OLDCLOCK, 'p%Y%m');
CALL zabbix.drop_partition(TABLENAME, PARTITIONNAME);
END //
CREATE PROCEDURE `zabbix`.`create_daily_partition` (TABLENAME VARCHAR(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME VARCHAR(16);
DECLARE CLOCK INT;
SET NEXTCLOCK = DATE_ADD(NOW(), INTERVAL 1 DAY);
SET PARTITIONNAME = DATE_FORMAT(NEXTCLOCK, 'p%Y%m%d');
SET CLOCK = UNIX_TIMESTAMP(DATE_FORMAT(DATE_ADD(NEXTCLOCK, INTERVAL 1 DAY), '%Y-%m-%d 00:00:00'));
CALL zabbix.create_partition(TABLENAME, PARTITIONNAME, CLOCK);
END //
CREATE PROCEDURE `zabbix`.`drop_daily_partition` (TABLENAME VARCHAR(64))
BEGIN
DECLARE OLDCLOCK TIMESTAMP;
DECLARE PARTITIONNAME VARCHAR(16);
DECLARE CLOCK INT;
SET @maxdays = 7;
SET @old = @maxdays+1;
SET OLDCLOCK = DATE_SUB(NOW(), INTERVAL @old DAY);
SET PARTITIONNAME = DATE_FORMAT(OLDCLOCK, 'p%Y%m%d');
CALL zabbix.drop_partition(TABLENAME, PARTITIONNAME);
END //
CREATE PROCEDURE `zabbix`.`create_partition` (TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64), CLOCK INT)
BEGIN
DECLARE RETROWS INT;
SELECT COUNT(1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = 'zabbix' AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;
IF RETROWS = 0 THEN
SELECT CONCAT("create_partition(", PARTITIONNAME, ",", CLOCK, ")") AS msg;
SET @sql = CONCAT('ALTER TABLE `zabbix`.`', TABLENAME, '`', ' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', CLOCK, '));');
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END //
CREATE PROCEDURE `zabbix`.`drop_partition` (TABLENAME VARCHAR(64), PARTITIONNAME VARCHAR(64))
BEGIN
DECLARE RETROWS INT;
SELECT COUNT(1) INTO RETROWS
FROM `information_schema`.`partitions`
WHERE `table_schema` = 'zabbix' AND `table_name` = TABLENAME AND `partition_name` = PARTITIONNAME;
IF RETROWS = 1 THEN
SELECT CONCAT( "drop_partition(", TABLENAME, ",", PARTITIONNAME, ")" ) AS msg;
SET @sql = CONCAT( 'ALTER TABLE `zabbix`.`', TABLENAME, '`',' DROP PARTITION ', PARTITIONNAME, ';' );
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
END //
DELIMITER ;
Deux script à planifier en cron vont appeller ces triggers; le premier à chaque début de mois et le second chaque jour :
root@zabbix:~# crontab -l
0 1 1 * * /root/scripts/zabbix_monthly.sh
0 1 * * * /root/scripts/zabbix_daily.sh
root@zabbix:~# cat /root/scripts/zabbix_monthly.sh
#!/bin/sh
mysql -B -h localhost -u root -psecret zabbix -e "CALL cron_monthly();"
root@zabbix:~# cat /root/scripts/zabbix_daily.sh
#!/bin/sh
mysql -B -h localhost -u root -psecret zabbix -e "CALL cron_daily();"
Pour finaliser, le process HouseKeeper de Zabbix est à désactiver (option de configuration DisableHousekeeping).
Articles associés :
1. Zabbix : monitoring Nginx / PHP-FPM / MySQL
其他参考：
http://blog.hbis.fr/2013/01/31/zabbix-mysql_partitioning/
好了，聪明如你，知道了如何对zabbix进行性能优化(MYSQL分区脚本)，是不是很欢喜 _
还有其他问题的可以在评论区留言或者扫码加博主获取资源或者提问。
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
