O particionamento no MySQL é uma técnica que divide grandes tabelas em partes menores chamadas de partições. Isso pode melhorar o desempenho e a manutenção de grandes volumes de dados, como no caso do Zabbix, um sistema de monitoramento de rede.
Aqui está o código SQL para criar tabelas de gerenciamento de partições e procedimentos armazenados para automatizar a criação e exclusão de partições no MySQL:
USE `zabbix`;
CREATE TABLE IF NOT EXISTS `manage_partitions` (
`tablename` VARCHAR(64) NOT NULL COMMENT 'Table name',
`period` VARCHAR(64) NOT NULL COMMENT 'Period - daily or monthly',
`keep_history` INT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'For how many days or months to keep the partitions',
`last_updated` DATETIME DEFAULT NULL COMMENT 'When a partition was added last time',
`comments` VARCHAR(128) DEFAULT '1' COMMENT 'Comments',
PRIMARY KEY (`tablename`)
) ENGINE=INNODB;
CREATE TABLE IF NOT EXISTS `manage_partitions_history` (
`schema_name` varchar(64) NOT NULL COMMENT 'Zabbix schema name',
`table_name` varchar(64) NOT NULL COMMENT 'Zabbix table name',
`table_partition_name` varchar(64) NOT NULL COMMENT 'Zabbix table partition name',
`partition_action` varchar(64) NOT NULL COMMENT 'Zabbix table partition action',
`partition_action_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When a partition was added or dropped'
) ENGINE=InnoDB;
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'day', 90, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'month', 24, now(), '');
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'month', 24, now(), '');
DROP PROCEDURE IF EXISTS `create_next_partitions`;
DROP PROCEDURE IF EXISTS `create_partition_by_day`;
DROP PROCEDURE IF EXISTS `create_partition_by_month`;
DROP PROCEDURE IF EXISTS `drop_partitions`;
DROP PROCEDURE IF EXISTS `drop_old_partition`;
DROP EVENT IF EXISTS `e_zbx_part_mgmt`;
DELIMITER $$
CREATE PROCEDURE `create_next_partitions`(IN_SCHEMANAME VARCHAR(64))
BEGIN
DECLARE TABLENAME_TMP VARCHAR(64);
DECLARE PERIOD_TMP VARCHAR(12);
DECLARE DONE INT DEFAULT 0;
DECLARE get_prt_tables CURSOR FOR
SELECT `tablename`, `period`
FROM manage_partitions;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN get_prt_tables;
loop_create_part: LOOP
IF DONE THEN
LEAVE loop_create_part;
END IF;
FETCH get_prt_tables INTO TABLENAME_TMP, PERIOD_TMP;
CASE
WHEN PERIOD_TMP = 'day' THEN
CALL `create_partition_by_day`(IN_SCHEMANAME, TABLENAME_TMP);
WHEN PERIOD_TMP = 'month' THEN
CALL `create_partition_by_month`(IN_SCHEMANAME, TABLENAME_TMP);
ELSE
BEGIN
ITERATE loop_create_part;
END;
END CASE;
UPDATE manage_partitions set last_updated = NOW() WHERE tablename = TABLENAME_TMP;
END LOOP loop_create_part;
CLOSE get_prt_tables;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
DECLARE BEGINTIME TIMESTAMP;
DECLARE ENDTIME INT UNSIGNED;
DECLARE PART_ACTION VARCHAR(12);
DECLARE PARTITIONNAME VARCHAR(16);
DECLARE ROWS_CNT INT UNSIGNED;
SET BEGINTIME = DATE(NOW()) + INTERVAL 1 DAY;
SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 DAY);
SET PART_ACTION = 'ADD';
SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m_%d' );
SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME
AND table_name = IN_TABLENAME
AND partition_name = PARTITIONNAME;
IF ROWS_CNT = 0 THEN
SET @SQL = CONCAT('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', PARTITIONNAME,'","', PART_ACTION,'");');
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
DECLARE BEGINTIME TIMESTAMP;
DECLARE ENDTIME INT UNSIGNED;
DECLARE PART_ACTION VARCHAR(12);
DECLARE PARTITIONNAME VARCHAR(16);
DECLARE ROWS_CNT INT UNSIGNED;
SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);
SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH);
SET PART_ACTION = 'ADD';
SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m' );
SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME
AND table_name = IN_TABLENAME
AND partition_name = PARTITIONNAME;
IF ROWS_CNT = 0 THEN
SET @SQL = CONCAT('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', PARTITIONNAME,'","', PART_ACTION,'");');
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', ENDTIME, '));' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `drop_partitions`(IN_SCHEMANAME VARCHAR(64))
BEGIN
DECLARE TABLENAME_TMP VARCHAR(64);
DECLARE PARTITIONNAME_TMP VARCHAR(64);
DECLARE VALUES_LESS_TMP INT;
DECLARE PERIOD_TMP VARCHAR(12);
DECLARE KEEP_HISTORY_TMP INT;
DECLARE KEEP_HISTORY_BEFORE INT;
DECLARE DONE INT DEFAULT 0;
DECLARE get_partitions CURSOR FOR
SELECT p.`table_name`, p.`partition_name`, LTRIM(RTRIM(p.`partition_description`)), mp.`period`, mp.`keep_history`
FROM information_schema.partitions p
JOIN manage_partitions mp ON mp.tablename = p.table_name
WHERE p.table_schema = IN_SCHEMANAME
ORDER BY p.table_name, p.subpartition_ordinal_position;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN get_partitions;
loop_check_prt: LOOP
IF DONE THEN
LEAVE loop_check_prt;
END IF;
FETCH get_partitions INTO TABLENAME_TMP, PARTITIONNAME_TMP, VALUES_LESS_TMP, PERIOD_TMP, KEEP_HISTORY_TMP;
CASE
WHEN PERIOD_TMP = 'day' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP DAY));
WHEN PERIOD_TMP = 'month' THEN
SET KEEP_HISTORY_BEFORE = UNIX_TIMESTAMP(DATE(NOW() - INTERVAL KEEP_HISTORY_TMP MONTH - INTERVAL DAY(NOW())-1 DAY));
ELSE
BEGIN
ITERATE loop_check_prt;
END;
END CASE;
IF KEEP_HISTORY_BEFORE >= VALUES_LESS_TMP THEN
CALL drop_old_partition(IN_SCHEMANAME, TABLENAME_TMP, PARTITIONNAME_TMP);
END IF;
END LOOP loop_check_prt;
CLOSE get_partitions;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `drop_old_partition`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64), IN_PARTITIONNAME VARCHAR(64))
BEGIN
DECLARE PART_ACTION VARCHAR(12);
DECLARE PART_ACTION_DATE INT;
DECLARE ROWS_CNT INT UNSIGNED;
SELECT COUNT(*) INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME
AND table_name = IN_TABLENAME
AND partition_name = IN_PARTITIONNAME;
SET PART_ACTION = 'DROP';
IF ROWS_CNT = 1 THEN
SET @SQL = CONCAT('INSERT INTO `manage_partitions_history`(`schema_name`,`table_name`,`table_partition_name`,`partition_action`) VALUES ("', IN_SCHEMANAME,'","', IN_TABLENAME,'","', IN_PARTITIONNAME,'","', PART_ACTION,'");');
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',' DROP PARTITION ', IN_PARTITIONNAME, ';' );
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", IN_PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME, "` does not exist") AS result;
END IF;
END$$
DELIMITER ;
DELIMITER $$
CREATE EVENT `e_zbx_part_mgmt`
ON SCHEDULE EVERY 1 DAY STARTS '2018-01-01 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating and dropping partitions'
DO BEGIN
CALL zabbix.drop_partitions('zabbix');
CALL zabbix.create_next_partitions('zabbix');
END$$
DELIMITER ;
Em seguida, edite o arquivo /etc/my.cnf e adicione a linha abaixo:
[mysqld]
event_scheduler=ON
Retirado do site: https://raw.githubusercontent.com/bakink/mysql/master/partitioning
0 Comentários