Instalación y configuración de un sistema de monitoreo Zabbix. (Parte V – Final)

webmaster
LINUXSOFTWARE
Zabbix

Liberar espacio en Zabbix

Si estás usando Zabbix durante un largo periodo de tiempo, te habrás dado cuenta que el tamaño de la BBDD se incrementa más y más diariamente. Podemos borrar los datos antiguos de la BBDD para así liberar espacio:

  1. Login en MySQL: $ mysql -u root -p
  2. Selecciona la BBDD que usa Zabbix: $ USE zabbix
  3. Ejecuta las siguientes sentencias:
delete FROM alerts where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM acknowledges clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM events where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM history where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM history_uint where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM history_str where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM history_text where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM history_log where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM trends where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));
delete FROM trends_uint where clock < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 5 DAY));

truncate table history;
OPTIMIZE TABLE history;
commit;

Si necesitas que sea mayor a 5 días, simplemente cambia ese valor.

Si necesitamos eliminar un intervalo de días podemos realizar estas consultas:

SET @history_interval = 7;
SET @trends_interval = 90;

DELETE FROM alerts WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM acknowledges WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM events WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);

DELETE FROM history WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_str WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_text WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);
DELETE FROM history_log WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@history_interval * 24 * 60 * 60);

DELETE FROM trends WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60);
DELETE FROM trends_uint WHERE (UNIX_TIMESTAMP(NOW()) - clock) > (@trends_interval * 24 * 60 * 60);

TRUNCATE TABLE history;
TRUNCATE TABLE history_str;
TRUNCATE TABLE history_uint;
TRUNCATE TABLE history_log;

PURGE BINARY LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 7 DAY);
commit;

delete FROM alerts where age(to_timestamp(alerts.clock)) > interval '7 days';
delete FROM acknowledges where age(to_timestamp(acknowledges.clock)) > interval '7 days';
delete FROM events where age(to_timestamp(events.clock)) > interval '7 days';
delete FROM history where age(to_timestamp(history.clock)) > interval '7 days';
delete FROM history_uint where age(to_timestamp(history_uint.clock)) > interval '7 days' ;
delete FROM history_str  where age(to_timestamp(history_str.clock)) > interval '7 days' ;
delete FROM history_text where age(to_timestamp(history_text.clock)) > interval '7 days' ;
delete FROM history_log where age(to_timestamp(history_log.clock)) > interval '7 days' ;
delete FROM trends where age(to_timestamp(trends.clock)) > interval '7 days';
delete FROM trends_uint where age(to_timestamp(trends_uint.clock)) > interval '7 days' ;

delete from history where itemid not in (select itemid from items where status='0');
delete from history_uint where itemid not in (select itemid from items where status='0');
delete from history_str where itemid not in (select itemid from items where status='0');
delete from history_text where itemid not in (select itemid from items where status='0');
delete from history_log where itemid not in (select itemid from items where status='0');
delete from trends where itemid not in (select itemid from items where status='0');
delete from trends_uint where itemid not in (select itemid from items where status='0');
commit;

Todo esto lo podemos automatizar mediante «triggers» en Zabbix, para que se libere el espacio cuando sea menor al que definamos, y así evitar que se nos llene el disco.

Entradas relacionadas