Architecture Zabbix 1.8.11 de deux serveurs sous Ubuntu 12.04
Dans un environnement cloud amazon comment configurer un zabbix server avec le frontend php et un serveur mysql 5.5 avec le partitionnement des tables sur une ubuntu 12.04 afin d’avoir une architecture scalable et taillée pour de très forte charge. En effet les problèmes de performance de Zabbix sont liés à la base de données. Cela devient visible seulement quand vous avez des milliers d’items. Cela conduit à devoir gérer des tables énormes et il deviens impossible de faire le ménage dans l’historique (Housekeeper) ou des sauvegarde, d’ou le partitionnement de table en solution.
A Lire avant tout :
Cas Iscool Entertainement
Optimisation partitioning-tables
Procédures Mysql stockée pour tables mensuelles
Tips Mysql pour zabbix
Architecture Zabbix distribuée et scalable
Notice
Ceci n’est que le copier coller de notes de travail et en aucun cas une procédure à suivre au pied de la lettre en copiant collant.
Security group
Créer un security group monitor et les règles de sécurité.
ec2-add-group --region eu-west-1 Monitor l -d "Monitor" ec2-authorize Monitor -P tcp -p 22 -s 0.0.0.0/0 ec2-authorize Monitor -P tcp -p 3306 -u 727387230542 -o "Monitor" ec2-authorize Monitor -P tcp -p 10050 -u 727387230542 -o "Monitor" ec2-authorize Monitor -P udp -p 10050 -u 727387230542 -o "Monitor" ec2-authorize Monitor -P tcp -p 80 -u 727387230542 -o "Monitor"
Verifier le résultat de la configuration du security group.
ec2-describe-group Monitor GROUP sg-907282e6 727387230542 Monitor Monitor PERMISSION 727387230542 Monitor ALLOWS tcp 22 22 FROM CIDR 0.0.0.0/0 ingress PERMISSION 727387230542 Monitor ALLOWS tcp 3306 3306 FROM USER 727387230542 NAME Monitor ID sg-907282e6 ingress PERMISSION 727387230542 Monitor ALLOWS tcp 10050 10050 FROM USER 727387230542 NAME Monitor ID sg-907282e6 ingress PERMISSION 727387230542 Monitor ALLOWS udp 10050 10050 FROM USER 727387230542 NAME Monitor ID sg-907282e6 ingress PERMISSION 727387230542 Monitor ALLOWS tcp 80 80 FROM CIDR 0.0.0.0/0 ingress
Mysql
Lancer une instance m1.large avec un maximum de disque éphémère.
ec2-run-instances ami-094c666d -k infra-live -g Monitor -b "sdb=ephemeral0" -b "sdc=ephemeral1" -b "sdd=ephemeral2" -b "sde=ephemeral3" -t m1.large -z eu-west-1b -d hostname=monitorweb1
Vérifier le résultat du lancement d’instance
ec2-describe-instances i-abda3ce2 RESERVATION r-a2da48eb 727387230542 Monitor INSTANCE i-abda3ce2 ami-094c666d ec2-176-34-201-131.eu-west-1.compute.amazonaws.com ip-10-51-45-30.eu-west-1.compute.internal running infra-live 0 m1.large 2012-04-16T15:18:19+0000 eu-west-1b aki-62695816 monitoring-disabled 176.34.201.130 10.51.45.30 ebs paravirtual xen sg-907282e6 default BLOCKDEVICE /dev/sda1 vol-be4aeed6 2012-04-16T15:18:34.000Z true TAG instance i-abda3ce2 Name monitor-sql1
Ajouter un tag name à cette instance
ec2-create-tags i-abda3ce2 --tag Name=monitorweb1
Logger vous sur l’instance et installer Mysql 5.5 qui est disponible dans les dépôts ubuntu 12.04 . Bien noté l’ID et le mots de passe du super-administrateur mysql
apt-get install mysq-server maatkit
Modifier l’installation de Mysql
/etc/init.d/mysql stop cd /mnt/ rm -r lost+found/ mkdir mysql-bin chown mysql.mysql mysql-bin/
Effacer la base mysql par defaut
cd /var/lib/mysql rm -r *
Reconfigurer */etc/fstab* pour avoir /var/lib/mysql sur le disque non utilisé */dev/xvdc*
LABEL=cloudimg-rootfs / ext4 defaults 0 0 /dev/xvdb /mnt auto rw,suid,dev,exec,auto,nouser,async,noatime,nodiratime 0 2 /dev/xvdc /var/lib/mysql auto rw,suid,dev,exec,auto,nouser,async,noatime,nodiratime 0 2
Monter le filesytem
mount -a
Réinstaller la DB par défaut de Mysql
cd /var/lib/mysql/ mysql_install_db
Configurer mysql avec deux fichiers:
Le premier */etc/mysql/conf.d/zabbix.cnf* avec la configuration de Mysql standalone server spéciale m1.large
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr skip-external-locking # network binding bind-address = 0.0.0.0 skip-name-resolve # charsets # Deprecated default-character-set=utf8 # Deprecated default-collation=utf8 collation_server=utf8_general_ci character_set_server=utf8 skip-character-set-client-handshake # paths datadir = /var/lib/mysql tmpdir = /tmp # network connect_timeout = 5 interactive_timeout = 30 max_allowed_packet = 1M max_connect_errors = 4096 max_connections = 1200 wait_timeout = 30 # limits max_heap_table_size = table_open_cache = 8192 thread_cache_size = 512 #$processorcount * 2 thread_concurrency = 4 tmp_table_size = # logs log_error = /var/log/mysql/mysql-error.log log_queries_not_using_indexes = 1 log_warnings = 1 long_query_time = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log # query cache query_cache_limit = 1M query_cache_size = 128M query_cache_type = 1 # buffers key_buffer_size = 32M read_buffer_size = sort_buffer_size = 1M # other stuff event_scheduler = 1 # InnoDB storage engine config options default-storage-engine = InnoDB innodb_additional_mem_pool_size = 128M innodb_autoextend_increment = 64 innodb_buffer_pool_size = 3000M innodb_data_home_dir = /var/lib/mysql innodb_data_file_path = ibdata1:128M:autoextend innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_io_capacity = 300 innodb_log_buffer_size = innodb_log_file_size = 128M innodb_log_files_in_group = innodb_mirrored_log_groups = 1 innodb_max_dirty_pages_pct = 45 innodb_status_file = 0 innodb_support_xa = 1 innodb_thread_concurrency = 8 # experimental #innodb_stats_update_need_lock =
Le second */etc/mysql/conf.d/zabbix.local.cnf* avec la configuration de Mysql pour sa réplication Master/Slave
################################################################## # Ce fichier contient les parametres specifiques a la replication [mysqld] # conf commune a tous les slaves log_bin=/mnt/mysql-bin/mysql-bin.log expire_logs_days=10 max_binlog_size=100M sync_binlog=0 log-slave-updates replicate-same-server-id=0 auto_increment_increment=2 slave-skip-errors=1053 binlog_format=MIXED # fix pb relay-log dependant du hostname relay-log=mysqld-relay-bin relay-log-index=mysqld-relay-bin.index # Conf specifique a ce slave en particulier # chaque replicat doit avoir un server-id unique (1,2,3,4...) server-id=1 # role de l'offset: un replicat ne cree que des ids pairs, et l'autre que des ids impairs # ceci afin d'eviter au maximum les conflits # valeurs possibles: 2 (ids pairs) 1 (ids impairs) auto_increment_offset=1 # tout replicat autre que le master actif doit etre en read-only # autrement dit, seul le master actif peut traiter les ecritures # read-only
Installer le paquet mysql de zabbix
zabbix-server-mysql
Créé une AMI par sécurité
ec2-create-image -n Mysql55-U1204-m1large -d Mysql55-U1204--m1large-v1 i-abda3ce2
Zabbix Server et FrontEnd php
Lancer une instance c1.medium
ec2-run-instances ami-094c666d -k infra-live -g Monitor -b "sdb=ephemeral0" -b "sdc=ephemeral1" -b "sdd=ephemeral2" -b "sde=ephemeral3" -t c1.medium -z eu-west-1b -d hostname=monitorweb1 RESERVATION r-e06df1a9 727387230542 Monitor INSTANCE i-eb00e3a3 ami-094c666d pending infra-live 0 c1.medium 2012-04-18T13:13:06+0000 eu-west-1b aki-62695816 monitoring-disabled ebs paravirtual xen sg-907282e6 default
Donner un tag name à cette instance
ec2-create-tags i-eb00e3a3 --tag Name=monitor-web1
Installer les paquets suivant
apt-get instal libapache2-mod-php5 php-apc php5 php5-cli php5-common php5-gd php5-mysql zabbix-frontend-php zabbix-server-mysql bsd-mailx
Puis Deinstaller les paquets *mysql-server* qu’un dépendance à forcer à l’installation.
apt-get remove --purge mysql-server*
Configurer Apache avec les modules suivent. Les autres ne sont pas nécéssaire.
auth_basic.load authz_default.load authz_host.load deflate.conf deflate.load php5.conf php5.load status.conf status.load
Modifier le lancement du module deflate de apache
Pour une compression légère sur tout ce qui est texte ceci sur l’exemple du fichier */etc/apache2/mods-enabled/deflate.conf* suivant
AddOutputFilterByType DEFLATE text/html text/plain text/xml
AddOutputFilterByType DEFLATE text/xml
AddOutputFilterByType DEFLATE text/css
AddOutputFilterByType DEFLATE text/javascript
AddOutputFilterByType DEFLATE text/html
AddOutputFilterByType DEFLATE text/plain
AddOutputFilterByType DEFLATE application/xml
AddOutputFilterByType DEFLATE application/xhtml+xml
AddOutputFilterByType DEFLATE application/rss+xml
AddOutputFilterByType DEFLATE application/javascript
AddOutputFilterByType DEFLATE application/x-javascript
DeflateCompressionLevel 3
Configurer APC sur le modèle du fichier */etc/php5/conf.d/apc.ini* suivant
extension=apc.so apc.enabled="1" apc.shm_size="64M" apc.num_files_hint="100" apc.user_entries_hint="0" apc.ttl="0" apc.gc_ttl="3601" apc.stat="1" apc.slam_defense="0" apc.write_lock="1"
Configurer frontend php pour utiliser le serveur mysql précédement créé sur le modèle du fichier */etc/zabbix/dbconfig.php* suivant
Configurer zabbix serveur pour utiliser le serveur mysql précédement créé sur le modèle du fichier */etc/zabbix/zabbix_server.conf* suivant
LogFile=/var/log/zabbix-server/zabbix_server.log PidFile=/var/run/zabbix/zabbix_server.pid DBHost=monitor-sql1.DOMAINENAME.COM DBName=zabbix DBUser=zabbix DBPassword=password AlertScriptsPath=/etc/zabbix/alert.d/ FpingLocation=/usr/bin/fping Fping6Location=/usr/bin/fping6
Configurer la base de donnée avec le partitionnement des tables
Configurer les droits et password de root mysql même si c’est déjà fait
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Créer et configurer les droits et password de l’utilisation zabbix
GRANT ALL PRIVILEGES ON *.* TO 'zabbix'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO 'zabbix'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Modification de la DB zabbix
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;
Partitionner les tables journalières de la DB zabbix
ALTER TABLE events PARTITION BY RANGE( clock ) (
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")));
ALTER TABLE acknowledges PARTITION BY RANGE( clock ) (
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")));
ALTER TABLE alerts PARTITION BY RANGE( clock ) (
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")));
ALTER TABLE auditlog PARTITION BY RANGE( clock ) (
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")));
ALTER TABLE service_alarms PARTITION BY RANGE( clock ) (
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")));
ALTER TABLE trends PARTITION BY RANGE( clock ) (
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")));
ALTER TABLE trends_uint PARTITION BY RANGE( clock ) (
PARTITION p201204 VALUES LESS THAN (UNIX_TIMESTAMP("2012-05-01 00:00:00")));
Partitionner les tables mensuelles de la DB zabbix
ALTER TABLE history_uint PARTITION BY RANGE( clock ) (
PARTITION p20110419 VALUES LESS THAN (UNIX_TIMESTAMP("2011-04-20 00:00:00")));
ALTER TABLE history PARTITION BY RANGE( clock ) (
PARTITION p20110419 VALUES LESS THAN (UNIX_TIMESTAMP("2011-04-20 00:00:00")));
ALTER TABLE history_log PARTITION BY RANGE( clock ) (
PARTITION p20110419 VALUES LESS THAN (UNIX_TIMESTAMP("2011-04-20 00:00:00")));
ALTER TABLE history_str PARTITION BY RANGE( clock ) (
PARTITION p20110419 VALUES LESS THAN (UNIX_TIMESTAMP("2011-04-20 00:00:00")));
ALTER TABLE history_str PARTITION BY RANGE( clock ) (
PARTITION p20110419 VALUES LESS THAN (UNIX_TIMESTAMP("2011-04-20 00:00:00")));
ALTER TABLE history_text PARTITION BY RANGE( clock ) (
PARTITION p20110419 VALUES LESS THAN (UNIX_TIMESTAMP("2011-04-20 00:00:00")));
Enregistrer les procedures stockées
Procédure pour tables quotidiennes
/**************************************************************
MySQL Auto Partitioning Procedure for Zabbix 1.8
http://zabbixzone.com/zabbix/partitioning-tables/
Author: Ricardo Santos (rsantos at gmail.com)
Version: 20110518
**************************************************************/
DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_partitions` //
CREATE PROCEDURE `zabbix`.`create_zabbix_partitions` ()
BEGIN
CALL zabbix.create_next_partitions("zabbix","history");
CALL zabbix.create_next_partitions("zabbix","history_log");
CALL zabbix.create_next_partitions("zabbix","history_str");
CALL zabbix.create_next_partitions("zabbix","history_text");
CALL zabbix.create_next_partitions("zabbix","history_uint");
CALL zabbix.drop_old_partitions("zabbix","history");
CALL zabbix.drop_old_partitions("zabbix","history_log");
CALL zabbix.drop_old_partitions("zabbix","history_str");
CALL zabbix.drop_old_partitions("zabbix","history_text");
CALL zabbix.drop_old_partitions("zabbix","history_uint");
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totaldays = 7;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i 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( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totaldays THEN
LEAVE createloop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @mindays = 3;
SET @maxdays = @mindays+4;
SET @i = @maxdays;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i DAY);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m%d' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i
Procédure pour tables mensuelles
DELIMITER //
DROP PROCEDURE IF EXISTS `zabbix`.`create_zabbix_monthly_partitions` //
CREATE PROCEDURE `zabbix`.`create_zabbix_monthly_partitions` ()
BEGIN
CALL zabbix.create_next_monthly_partitions("zabbix","acknowledges");
CALL zabbix.create_next_monthly_partitions("zabbix","alerts");
CALL zabbix.create_next_monthly_partitions("zabbix","auditlog");
CALL zabbix.create_next_monthly_partitions("zabbix","events");
CALL zabbix.create_next_monthly_partitions("zabbix","service_alarms");
CALL zabbix.create_next_monthly_partitions("zabbix","trends");
CALL zabbix.create_next_monthly_partitions("zabbix","trends_uint");
CALL zabbix.drop_old_monthly_partitions("zabbix","acknowledges");
CALL zabbix.drop_old_monthly_partitions("zabbix","alerts");
CALL zabbix.drop_old_monthly_partitions("zabbix","auditlog");
CALL zabbix.drop_old_monthly_partitions("zabbix","events");
CALL zabbix.drop_old_monthly_partitions("zabbix","service_alarms");
CALL zabbix.drop_old_monthly_partitions("zabbix","trends");
CALL zabbix.drop_old_monthly_partitions("zabbix","trends_uint");
END //
DROP PROCEDURE IF EXISTS `zabbix`.`create_next_monthly_partitions` //
CREATE PROCEDURE `zabbix`.`create_next_monthly_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE NEXTCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @totalmonths = 3;
SET @i = 1;
createloop: LOOP
SET NEXTCLOCK = DATE_ADD(NOW(),INTERVAL @i 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( SCHEMANAME, TABLENAME, PARTITIONNAME, CLOCK );
SET @i=@i+1;
IF @i > @totalmonths THEN
LEAVE createloop;
END IF;
END LOOP;
END //
DROP PROCEDURE IF EXISTS `zabbix`.`drop_old_monthly_partitions` //
CREATE PROCEDURE `zabbix`.`drop_old_monthly_partitions` (SCHEMANAME varchar(64), TABLENAME varchar(64))
BEGIN
DECLARE OLDCLOCK timestamp;
DECLARE PARTITIONNAME varchar(16);
DECLARE CLOCK int;
SET @minmonths = 12;
SET @maxmonths = @minmonths+3;
SET @i = @maxmonths;
droploop: LOOP
SET OLDCLOCK = DATE_SUB(NOW(),INTERVAL @i MONTH);
SET PARTITIONNAME = DATE_FORMAT( OLDCLOCK, 'p%Y%m' );
CALL zabbix.drop_partition( SCHEMANAME, TABLENAME, PARTITIONNAME );
SET @i=@i-1;
IF @i
Vérifier le bon enregistrements des procedures stockées
select name from mysql.proc; +----------------------------------+ | name | +----------------------------------+ | create_next_monthly_partitions | | create_next_partitions | | create_partition | | create_zabbix_monthly_partitions | | create_zabbix_partitions | | drop_old_monthly_partitions | | drop_old_partitions | | drop_partition | +----------------------------------+
Créé les cron journalié et mensuel de changement de table
Sur monitor-web1
mkdir -p /etc/zabbix/cron.d
Créer le fichier */etc/zabbix/cron.d/daily-housekeeping.sh*
#!/bin/bash MAILTO=karles@mimesis-republic.com tmpfile=/tmp/daily-housekeeping$$ date >$tmpfile /usr/bin/mysql --skip-column-names -B -h localhost -u zabbix -pzabbix zabbix -e "CALL create_zabbix_partitions();" >>$tmpfile 2>&1 /usr/bin/mail -s "[Zabbix] Daily MySql Partition Housekeepingg" $MAILTO
Créer le fichier */etc/cron.daily/zabbix-daily-housekeeping*
#!/bin/bash /etc/zabbix/cron.d/daily-housekeeping.sh
Créer le fichier */etc/zabbix/cron.d/monthly-housekeeping.sh*
#!/bin/bash MAILTO=karles@mimesis-republic.com tmpfile=/tmp/monthly-housekeeping$$ date >$tmpfile /usr/bin/mysql --skip-column-names -B -h monitor-sql1.DOMAINENAME.COM -u zabbix -ppass zabbix -e "CALL create_zabbix_monthly_partitions();" >>$tmpfile 2>&1 /usr//bin/mail -s "[Zabbix] Monthly MySql Partition Housekeeping" $MAILTO
Créer le fichier */etc/cron.monthly/zabbix-monthly-housekeeping*
#!/bin/bash /etc/zabbix/cron.d/monthly-housekeeping.sh
Lancer les deux cron manuellement pour finir l’installation.
Le 