Разбиение (секционирование) в бд zabbix MySQL

Когда количество устройств в zabbix перевалило за 1000, могут возникнуть проблемы с производиетльностью этой великолепной системы мониторинга. Один из вариантов решения проблемы - партиционирование таблиц в базе данных(Mysql).

Как это работает и преимущества разбиения нужно читать здесь https://www.zabbix.com/wiki/non-english/ru/partitioning_in_mysql (по факту эта стать я для тех у кого нет данных в базе, то есть партиционирование после чистой инсталляции) , не буду копипастить. Сразу опишу перевод zabbix на партиции, с учетом того, что  в zabbix уже есть данные.

1.

В настоящий момент, MySQL поддерживает разбиение “из коробки”. Поддержка секционирования начинается с версии MySQL 5.1, если у вас установлена версия более ранняя, то придется MySQL придется обновить.

Так же следует учесть, что сам MySQL сервер должен быть собран с поддержкой секционирования. Проверить наличие можно командой:

mysql> show variables like 'have_partitioning';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set 0.00 sec

 

 

На офсайте предлагается секционировать следующие таблицы, проведя мониторинг использования таблиц, пришел к выводу, что и в моем случае этот список актуален.

 

Вот эти таблицы с делением по дням:

  • history

  • history_log

  • history_str

  • history_text

  • history_uint

и с делением по месяцам:

  • acknowledges

  • alerts

  • auditlog

  • events

  • service_alarms

  • trends

  • trends_uint

2. Сначала выполним бекапы,на всякий случай трех типов(для секционирования, если уже система мониторинга была в работе нам нужен третий тип):

  • полностью всю базу
# mysqldump -u root -p zabbix > zabbix.sql
  • полностью все таблицы, подлежащие секционированию
# mysqldump -u root -p zabbix history > history.sql
 
# mysqldump -u root -p zabbix history_uint > history_uint.sql
 
# mysqldump -u root -p zabbix history_log > history_log.sql
 
# mysqldump -u root -p zabbix history_str > history_str.sql
 
# mysqldump -u root -p zabbix history_text > history_text.sql
 
# mysqldump -u root -p zabbix acknowledges > acknowledges.sql
 
# mysqldump -u root -p zabbix alerts > alerts.sql
 
# mysqldump -u root -p zabbix auditlog > auditlog.sql
 
# mysqldump -u root -p zabbix events > events.sql
 
# mysqldump -u root -p zabbix trends > trends.sql
 
# mysqldump -u root -p zabbix trends_uint > trends_uint.sql
 
# mysqldump -u root -p zabbix service_alarms > service_alarms.sql
 
# mysqldump -u root -p zabbix housekeeper > housekeeper.sql

 

  • и тот самый третий тип без ф-ции create
# mysqldump -u root -p --no-create-info --lock-tables zabbix history > historyh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix history_uint > history_uinth.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix history_log > history_logh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix history_str > history_strh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix history_text > history_texth.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix acknowledges > acknowledgesh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix alerts > alertsh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix auditlog > auditlogh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix events > eventsh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix trends > trendsh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix trends_uint > trends_uinth.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix service_alarms > service_alarmsh.sql
 
# mysqldump -u root -p --no-create-info --lock-tables zabbix housekeeper > housekeeperh.sql

 

Далее останавливаем zabbix демон, и отключаем веб интерфейс(например остановкой аппача).

3.

В конфиге zabbix сервера отключаем Housekeeper

DisableHousekeeping=1

 

4.

Создаем таблицу настроек секционирования. В этой таблице будут хранится настройки секционирования для таблиц Zabbix.

mysql> use zabbix;
 
mysql> SHOW CREATE TABLE `manage_partitions`;
 
CREATE TABLE `manage_partitions` (
  `tablename` VARCHAR(64) NOT NULL COMMENT 'Имя секционируемой таблицы',
  `period` VARCHAR(64) NOT NULL COMMENT 'Период секционирования: day или month',
  `keep_history` INT(3) UNSIGNED NOT NULL DEFAULT '1' COMMENT 'Количество дней или месяцев хранения секций',
  `last_updated` DATETIME DEFAULT NULL COMMENT 'Время последнего добавления секции',
  `comments` VARCHAR(128) DEFAULT '1' COMMENT 'Комментарии',
  PRIMARY KEY (`tablename`)
) ENGINE=INNODB;

 

Так же нужно заполнить таблицу ”manage_partitions” в соответствии с выбранным диапазоном разбиения и сроком хранения секций.

mysql> use zabbix;
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('acknowledges', 'month', 6, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('alerts', 'month', 6, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('auditlog', 'month', 6, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('events', 'month', 6, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('service_alarms', 'month', 6, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 30, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 30, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'day', 120, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 120, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'day', 120, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends', 'month', 24, now(), '');
 
mysql> INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('trends_uint', 'month', 24, now(), '');

5.

Изменяем движок таблицы housekeeper.

mysql> use zabbix;
 
mysql> ALTER TABLE housekeeper ENGINE = BLACKHOLE;

 

Даже с включенной опцией DisableHousekeeping=1 Zabbix сервер и веб-интерфейс будут писать информацию для будущей очистки базы в таблицу housekeeper. Чтобы этого избежать вы можете выставить ENGINE =Blackhole для этой таблицы.

The BLACKHOLE storage engine acts as a “black hole” that accepts data but throws it away and does not store it.

В некоторых сборах MySQL подсистемы BlackHole не имеется по умолчанию. Для того чтобы проверить имеется ли у вас эта подсистема выполните запрос SHOW ENGINES;

6.

Очищаем выше обозначенные таблицы от данных.

mysql> use zabbix;
 
mysql> truncate table history;
 
mysql> truncate table history_uint;
 
mysql> truncate table history_text;
 
mysql> truncate table history_str;
 
mysql> truncate table history_log;
 
mysql> truncate table acknowledges;
 
mysql> truncate table alerts;
 
mysql> truncate table auditlog;----------только после пункта 7
 
-------------
 
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`zabbix`.`auditlog_details`, CONSTRAINT `c_auditlog_details_1` FOREIGN KEY (`auditid`) REFERENCES `zabbix`.`auditlog` (`auditid`))
 
------------
 
mysql> truncate table events;------------только после пункта 7
 
-----------------
 
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`zabbix`.`acknowledges`, CONSTRAINT `c_acknowledges_2` FOREIGN KEY (`eventid`) REFERENCES `zabbix`.`events` (`eventid`))
 
-------------
 
mysql> truncate table service_alarms;
 
mysql> truncate table trends;
 
mysql> truncate table trends_uint;

7.

 Так как в MySQL имеются внутренние ограничения использование уникальных индексов, первичных ключей и т.п., то прежде чем собственно начать разбиение потребуется изменить некоторые индексы в базе данных Zabbix.

mysql> use zabbix;
 
mysql>  ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledges_0` (`acknowledgeid`);
 
 
mysql>  ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alerts_0` (`alertid`);
 
 
mysql>  ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditlog_0` (`auditid`);
 
 
mysql>  ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `events_0` (`eventid`);
 
 
mysql>  ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `service_alarms_0` (`servicealarmid`);
 
 
mysql>  ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`clock`);
 
 
mysql>  ALTER TABLE `history_log` DROP KEY `history_log_2`;
 
 
mysql>  ALTER TABLE `history_log` ADD UNIQUE INDEX `history_log_2`(`itemid`,`id`,`clock`);
 
 
mysql>   ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`clock`);
 
 
mysql>   ALTER TABLE `history_text` DROP KEY `history_text_2`;
 
 
mysql>   ALTER TABLE `history_text` ADD UNIQUE INDEX `history_text_2`(`itemid`,`id`,`clock`);

 

для версии 2.0 дополнительно выполните:

mysql> ALTER TABLE `acknowledges` DROP FOREIGN KEY `c_acknowledges_1`, DROP FOREIGN KEY `c_acknowledges_2`;
mysql> ALTER TABLE `alerts` DROP FOREIGN KEY `c_alerts_1`, DROP FOREIGN KEY `c_alerts_2`, DROP FOREIGN KEY `c_alerts_3`, DROP FOREIGN KEY `c_alerts_4`;
mysql> ALTER TABLE `auditlog` DROP FOREIGN KEY `c_auditlog_1`;
mysql> ALTER TABLE `service_alarms` DROP FOREIGN KEY `c_service_alarms_1`;
mysql> ALTER TABLE `auditlog_details` DROP FOREIGN KEY `c_auditlog_details_1`;

 

8. Выполняем секционирование.

PARTITION p2014_02_01 — название партиции,

(UNIX_TIMESTAMP("2014-02-02 00:00:00") div 1) - начало следующей партиции

Создаем количество партиций, в соответствии с временным интервалом в пределах которого мы хотим сохранить уже существующие наши данные(если секционирование делаем не на «чистом» недавно установленным zabbix).

Допустим у нас данные собирались с 02.02.2014 секционирование мы выполняем 13.02.2014. Значит создадим партиции начиная с 02.02.2014 по 14.02.2014(про запас, если не успеем сделать в один день). Аналогичным образом поступаем и с таблицами, секционируемыми по месяцам.

 

  • По дням
mysql> ALTER TABLE `history_uint` PARTITION BY RANGE ( clock)
(PARTITION p2014_02_01 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-02 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-03 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_03 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-04 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_04 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-05 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_05 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-06 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_06 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-07 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_07 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-08 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_08 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-09 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_09 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-10 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_10 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-11 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_11 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-12 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_12 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-13 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_13 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-14 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_14 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-15 00:00:00") div 1) ENGINE = InnoDB);
 
 
mysql> ALTER TABLE `history` PARTITION BY RANGE ( clock)
(PARTITION p2014_02_01 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-02 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-03 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_03 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-04 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_04 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-05 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_05 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-06 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_06 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-07 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_07 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-08 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_08 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-09 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_09 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-10 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_10 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-11 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_11 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-12 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_12 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-13 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_13 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-14 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_14 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-15 00:00:00") div 1) ENGINE = InnoDB);
 
 
mysql> ALTER TABLE `history_text` PARTITION BY RANGE ( clock)
(PARTITION p2014_02_01 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-02 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-03 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_03 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-04 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_04 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-05 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_05 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-06 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_06 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-07 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_07 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-08 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_08 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-09 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_09 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-10 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_10 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-11 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_11 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-12 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_12 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-13 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_13 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-14 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_14 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-15 00:00:00") div 1) ENGINE = InnoDB);
 
 
mysql> ALTER TABLE `history_str` PARTITION BY RANGE ( clock)
(PARTITION p2014_02_01 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-02 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-03 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_03 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-04 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_04 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-05 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_05 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-06 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_06 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-07 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_07 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-08 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_08 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-09 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_09 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-10 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_10 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-11 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_11 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-12 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_12 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-13 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_13 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-14 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_14 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-15 00:00:00") div 1) ENGINE = InnoDB);
 
mysql> ALTER TABLE `history_log` PARTITION BY RANGE ( clock)
(PARTITION p2014_02_01 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-02 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-03 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_03 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-04 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_04 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-05 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_05 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-06 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_06 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-07 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_07 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-08 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_08 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-09 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_09 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-10 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_10 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-11 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_11 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-12 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_12 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-13 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_13 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-14 00:00:00") div 1) ENGINE = InnoDB,
 PARTITION p2014_02_14 VALUES LESS THAN (UNIX_TIMESTAMP("2014-02-15 00:00:00") div 1) ENGINE = InnoDB);
  • По месяцам
mysql> ALTER TABLE `trends_uint` PARTITION BY RANGE ( clock)
(PARTITION p2014_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-03-01 00:00:00") div 1) ENGINE = InnoDB);
 
 
mysql> ALTER TABLE `trends` PARTITION BY RANGE ( clock)
(PARTITION p2014_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-03-01 00:00:00") div 1) ENGINE = InnoDB);
 
mysql> ALTER TABLE `acknowledges` PARTITION BY RANGE ( clock)
(PARTITION p2014_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-03-01 00:00:00") div 1) ENGINE = InnoDB);
 
mysql> ALTER TABLE `alerts` PARTITION BY RANGE ( clock)
(PARTITION p2014_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-03-01 00:00:00") div 1) ENGINE = InnoDB);
 
mysql> ALTER TABLE `auditlog` PARTITION BY RANGE ( clock)
(PARTITION p2014_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-03-01 00:00:00") div 1) ENGINE = InnoDB);
 
mysql> ALTER TABLE `events` PARTITION BY RANGE ( clock)
(PARTITION p2014_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-03-01 00:00:00") div 1) ENGINE = InnoDB);
 
mysql> ALTER TABLE `service_alarms` PARTITION BY RANGE ( clock)
(PARTITION p2014_02 VALUES LESS THAN (UNIX_TIMESTAMP("2014-03-01 00:00:00") div 1) ENGINE = InnoDB);

9. Восстанавливаем данные, в партиционированные таблицы

    # mysql -u root -p zabbix < historyh.sql 
    # mysql -u root -p zabbix < history_uinth.sql
    # mysql -u root -p zabbix < history_texth.sql 
    # mysql -u root -p zabbix < history_logh.sql
    # mysql -u root -p zabbix < history_strh.sql 
    # mysql -u root -p zabbix < acknowledgesh.sql 
    # mysql -u root -p zabbix < alertsh.sql 
    # mysql -u root -p zabbix < auditlogh.sql
    # mysql -u root -p zabbix < eventsh.sql
    # mysql -u root -p zabbix < service_alarmsh.sql
    # mysql -u root -p zabbix < trendsh.sql 
    # mysql -u root -p zabbix < trends_uinth.sql 

10.

В этом пункте добавляем хранимые процедуры для автоматического создания новых партиций и удаления старых(для автоматизации  п8. )

  • Процедура проверки наличия требуемых секций

 

mysql> 
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `create_next_partitions`$$
 
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 ;
  • Создание секции по дням

 

mysql> 
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
 
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE BEGINTIME TIMESTAMP;
        DECLARE ENDTIME INT UNSIGNED;
        DECLARE PARTITIONNAME VARCHAR(16);
        SET BEGINTIME = DATE(NOW()) + INTERVAL 1 DAY;
        SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m_%d' );
 
        SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 DAY) div 1;
 
        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( '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 ;
  • Создание секций по месяцам

 

mysql> 
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `create_partition_by_month`$$
 
CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE BEGINTIME TIMESTAMP;
        DECLARE ENDTIME INT UNSIGNED;
        DECLARE PARTITIONNAME VARCHAR(16);
        SET BEGINTIME = DATE(NOW() - INTERVAL DAY(NOW()) DAY + INTERVAL 1 DAY + INTERVAL 1 MONTH);
        SET PARTITIONNAME = DATE_FORMAT( BEGINTIME, 'p%Y_%m' );
 
        SET ENDTIME = UNIX_TIMESTAMP(BEGINTIME + INTERVAL 1 MONTH) div 1;
 
        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( '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 ;
  •  Проверка наличия устаревших секций и удаление

 

mysql> 
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `drop_partitions`$$
 
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 ;
  • Удаление указанной секции

 

mysql> 
DELIMITER $$
 
USE `zabbix`$$
 
DROP PROCEDURE IF EXISTS `drop_old_partition`$$
 
CREATE PROCEDURE `drop_old_partition`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64), IN_PARTITIONNAME VARCHAR(64))
BEGIN
    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;
 
    IF ROWS_CNT = 1 THEN
                     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, "` not exists") AS result;
        END IF;
END$$
 
 
DELIMITER ;

11.

 Управление разбиениями, планировщик  (events)

По умолчанию в большинстве версий MySQL планировщик отключен. Чтобы его включить добавьте event_scheduler = 1; в файл конфигурации MySQL. Для избежания перезапуска MySQL следует выполнить далее команду SET GLOBAL event_scheduler = ON;

 

mysql> 
DELIMITER $$
USE `zabbix` $$
CREATE EVENT `e_part_manage`
       ON SCHEDULE EVERY 1 DAY
       STARTS '2014-02-11 04:00:00'
       ON COMPLETION PRESERVE
       ENABLE
       COMMENT 'Управление созданием и удалением секций'
       DO BEGIN
            CALL zabbix.drop_partitions('zabbix');
            CALL zabbix.create_next_partitions('zabbix');
       END$$
 
DELIMITER ;

 

 

Вуаля, все готово.

Запускаем аппач, запускаем демон zabbix, смотрим логи.

Категории:

Комментарии

А я херанул (truncate) самую большую таблицу на 80 гигов с точными данными (history_uint), а другие прям на живую разрезал (alter table), все что старше текущего дня в одну патицию, сегодняшний день в другую, а далее мускул сам создал новые патиции.

Одно хреново, таблицы резались всю ночь.