jueves, 14 de febrero de 2013

Particionando tablas en Zabbix 2.0

Tras usar durante varios años en producción Zabbix 1.8 estamos migrando la plataforma a Zabbix 2.0 para realizar una limpieza de equipos, refinado de plantillas, etc.

Una de las cosas que deben hacerse para mejorar el rendimiento de la base de datos es deshabilitar el housekeeping integrado en Zabbix y realizar el borrado de datos usando particiones en las tablas históricas.

Si usamos una guía genérica de particionamiento de tablas basado en Zabbix 1.8 no nos funcionará debido al uso de claves foráneas en algunas tablas. Por desgracia, a día de hoy, MySQL (ni Percona) soporta claves foráneas al mismo tiempo que particionamiento de tablas, por lo que la solución que propondremos aquí eliminará de forma permanente dichas relaciones.

Primer paso: Eliminar claves foráneas

# Tabla "acknowledges"
ALTER TABLE `acknowledges`
 DROP FOREIGN KEY `c_acknowledges_2`,
 DROP FOREIGN KEY `c_acknowledges_1`;

# Tabla "alerts"
ALTER TABLE `alerts`
 DROP FOREIGN KEY `c_alerts_4`,
 DROP FOREIGN KEY `c_alerts_1`,
 DROP FOREIGN KEY `c_alerts_2`,
 DROP FOREIGN KEY `c_alerts_3`;

# Tabla "auditlog"
ALTER TABLE `auditlog`
 DROP FOREIGN KEY `c_auditlog_1`;

# Tabla "auditlog_details"
ALTER TABLE `auditlog_details`
 DROP FOREIGN KEY `c_auditlog_details_1`;

# Tabla "service_alarms"
ALTER TABLE `service_alarms`
 DROP FOREIGN KEY `c_service_alarms_1`;

Segundo paso: Cambio de claves primarias

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`;

Tercer paso: Obtener fechas iniciales para particiones

SET @SEMANA= DATE_ADD(NOW(), INTERVAL 1 WEEK);
SET @F_SEMANA = DATE_FORMAT(@SEMANA, '%Y-%m-%d');
SET @P_SEMANA= CONCAT('p', DATE_FORMAT(@SEMANA, '%x%v'));
SELECT @F_SEMANA, @P_SEMANA;
SET @DIA= DATE_ADD(NOW(), INTERVAL 1 DAY);
SET @F_DIA = DATE_FORMAT(@DIA, '%Y-%m-%d');
SET @P_DIA= CONCAT('p', DATE_FORMAT(@DIA, '%Y%m%d'));
SELECT @F_DIA, @P_DIA;

Cuarto paso: Crear particiones semanales

# Formato de fecha PHP "oW"/'%x%v'
ALTER TABLE `acknowledges` PARTITION BY RANGE( clock ) ( PARTITION pYYYYss VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `alerts` PARTITION BY RANGE( clock ) ( PARTITION pYYYYss VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `events` PARTITION BY RANGE( clock ) ( PARTITION pYYYYss VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `auditlog` PARTITION BY RANGE( clock ) ( PARTITION pYYYYss VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `events` PARTITION BY RANGE( clock ) ( PARTITION pYYYYss VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `service_alarms` PARTITION BY RANGE( clock ) ( PARTITION pYYYYss VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `trends` PARTITION BY RANGE( clock ) ( PARTITION pYYYYss VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `trends_uint` PARTITION BY RANGE( clock ) ( PARTITION pYYYYss VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );

ALTER TABLE `acknowledges` PARTITION BY RANGE( clock ) ( PARTITION p201308 VALUES LESS THAN (UNIX_TIMESTAMP("2013-02-21 00:00:00")) );

Quinto paso: Crear particiones diarias

ALTER TABLE `history` PARTITION BY RANGE( clock ) ( PARTITION pYYYYmmdd VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `history_log` PARTITION BY RANGE( clock ) ( PARTITION pYYYYmmdd VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `history_str` PARTITION BY RANGE( clock ) ( PARTITION pYYYYmmdd VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `history_text` PARTITION BY RANGE( clock ) ( PARTITION pYYYYmmdd VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );
ALTER TABLE `history_uint` PARTITION BY RANGE( clock ) ( PARTITION pYYYYmmdd VALUES LESS THAN (UNIX_TIMESTAMP("XXXXXXXXXX 00:00:00")) );

ALTER TABLE `history` PARTITION BY RANGE( clock ) ( PARTITION p20130215 VALUES LESS THAN (UNIX_TIMESTAMP("2013-02-15 00:00:00")) );

No hay comentarios:

Publicar un comentario