Разбиение осуществляется через логическое разбиение одной большой таблицы на маленькие физические куски. Разбиение может дать несколько преимуществ:
- В определённых ситуациях, производительность запроса может быть очень существенно увеличена, особенно когда наиболее интенсивно используемая область таблицы является отдельным разбиением или небольшим количеством разбиений. Такое разбиение и его индексы лучше размещаются в памяти, чем индекс всей таблицы.
- Когда запросы или обновления используют большой процент одного разбиения, производительность может быть увеличена просто через более выгодный последовательный доступ по диску к этому разбиению, вместо использования индекса и случайного доступа на чтение всей таблицы. В нашем случае используются индексы типа B-Tree (itemid, clock), для них разбиение существенно улучшает производительность
- Массовые INSERT и DELETE могут быть выполнены через простое удаление или добавление разбиений, если это требование планируется при создании разбиения. Команда ALTER TABLE будет отрабатывать намного быстрее, чем любая команда массовой загрузки или удаления.
- В MySQL невозможно использовать табличные пространства для InnoDB таблиц. Получается одна папка - одна база данных. Таким образом, чтобы перенести файл секции таблицы нужно физически вручную скопировать её на другой носитель и затем сделать символическую ссылку на этот файл в папку базы данных.
Начиная с MySQL 5.6 имеется возможность указывать расположение табличного пространства. Однако имеются некоторые ограничения, о которых вы можете прочитатьздесь.
Эти выгоды обычно проявляют себя, только когда таблица будет очень большой. Выиграет таблица от применения разбиения или нет в конечном счёте зависит от приложения, но есть довольно простое правило, что это произойдёт, если размер этой таблицы превышает физическую память сервера баз данных.
В настоящий момент, 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)
В MySQL могут быть реализованы следующие виды разбиений:
- Диапазонное разбиение (RANGE)
Таблица разбивается по “диапазонам”, заданным ключевой колонкой или списком колонок без перекрывания диапазонов значений, предназначеных для разных разбиений. Например, это может быть диапазоны дат или диапазоны идентификаторов отдельных бизнес-объектов.
- Другие типы разбиения
Так же есть типы разбиения по HASH, LIST и KEY. Обсуждение этих типов секционирования выходит за рамки статьи, т.к. будем использовать только диапазонное разбиение (RANGE) таблиц. Аналогично не будем рассматривать разбиение секции (subpartitioning).
1 Структура таблиц истории в Zabbix
В Zabbix есть несколько таблиц предназначенных для разного рода истории. Зная предназначение таблиц можно рассчитать период, который необходимо хранить.
Ниже представлен список таблиц для хранения собранных данных с элементов данных:
| Назначение | Типы данных | Максимальный размер | |
|---|---|---|---|
| history | Хранение точной истории | Числовой (с плавающей точкой) | double(16,4) - 999999999999.9999 |
| history_uint | Хранение точной истории | Числовой (целое положительное) | bigint(20) - 264+1 |
| history_str | Хранение небольших строк | Символ | varchar(255) - 255 |
| history_text | Хранение достаточно длинных строк | Текст | text - 65535 |
| history_log | Хранение строк из журналов | Журнал | text - 65535 |
| trends | Хранение приближенных значений (тенденций) | Числовой (с плавающей точкой) | double(16,4) - 999999999999.9999 |
| trends_uint | Хранение приближенных значений (тенденций) | Числовой (целое положительное) | bigint(20) - 264+1 |
Данные собранные с элементов данных типов Символ, Журнал (лог) и Текстхранимые в таблицах history_str, history_log и history_text не имеют тенденций. Этот факт следует учитывать при секционировании этих таблиц
В таблицах trends и trends_uint данные хранятся периодами по 1 часу по каждому элементу данных. Каждая запись содержит в себе количество съемов данных точной истории, среднее значение элемента данных за час, минимальное значение элемента данных за час и максимальное значение элемента данных за час.
Так же в Zabbix имеются и другие таблицы для хранения данных не генерирумых элементами данных. Рассмотрим эти таблицы:
| Назначение | |
|---|---|
| acknowledges | Хранение сообщений подтверждений тех или иных событий |
| alerts | Хранение истории оповещений |
| auditlog | Хранение действий пользователей |
| events | Хранение событийных данных. Например сработка триггера, обнаружение нового устройства |
| service_alarms | Хранение изменений состояний ”Услуги IT” |
2 Разбиение в Zabbix
Для применения разбиения в Zabbix необходимо учесть некоторые особенности:
1. Для разбиения таблиц будет использоваться диапазонное разбиение.
2. Более не потребуется использование “housekeeper”. Эту функцию самоотчистки базы данных от устаревших данных истории и тенденций можно отключить в файле конфигурации Zabbix сервера ”zabbix_server.conf”.
3. Значения полей ”Хранить историю (дни)” и ”Хранить динамику изменений (дни)” у элементов данных более не важны, т.к. очистка устаревших данных происходит диапазоном, т.е. целой секцией.
4. Если же потребуется долговременное хранение данных, но есть ограничение по дисковому пространству, то возможно использовать символические ссылки для устаревших разбиений. Проверить наличие этой функции в MySQL можно командой:
mysql> show variables like 'have_symlink'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | have_symlink | YES | +---------------+-------+ 1 row in set (0.00 sec)
Крайне не рекомендуется пользоваться функцией символических ссылок. Символические ссылки могут работать некорректно с любыми типами таблиц, кромеMyISAM, Zabbix же использует InnoDB.
5. Даже с включенной опцией DisableHousekeeping=1 Zabbix сервер и веб-интерфейс будут писать информацию для будущей очистки базы в таблицу housekeeper. Чтобы этого избежать вы можете выставить ENGINE = Blackhole для этой таблицы:
ALTER TABLE housekeeper ENGINE = BLACKHOLE;
В некоторых сборах MySQL подсистемы BlackHole не имеется по умолчанию. Для того чтобы проверить имеется ли у вас эта подсистема выполните запрос SHOW ENGINES;
3 Секционирование
Ниже приводится описание автоматического секционирования таблиц. Будут приведены примеры секционирования на основе процедур и планировщика MySQL или с помощью скриптовой обвязки. На рисунке представлен пример секционирования по таблицам, вы же можете выбрать тип разбиения (по месяцам или по дням) по своему усмотрению:
3.1 Подготовка к секционированию
В случае если секционируемые таблицы большие, указанные в разделе SQL запросы могут выполняться достаточно долгое время!
1. Так как в MySQL имеются внутренние ограничения использование уникальных индексов, первичных ключей и т.п., то прежде чем собственно начать разбиение потребуется изменить некоторые индексы в базе данных Zabbix.
ALTER TABLE `acknowledges` DROP PRIMARY KEY, ADD KEY `acknowledges_0` (`acknowledgeid`); ALTER TABLE `alerts` DROP PRIMARY KEY, ADD KEY `alerts_0` (`alertid`); ALTER TABLE `auditlog` DROP PRIMARY KEY, ADD KEY `auditlog_0` (`auditid`); ALTER TABLE `events` DROP PRIMARY KEY, ADD KEY `events_0` (`eventid`); ALTER TABLE `service_alarms` DROP PRIMARY KEY, ADD KEY `service_alarms_0` (`servicealarmid`); ALTER TABLE `history_log` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`clock`); ALTER TABLE `history_log` DROP KEY `history_log_2`; ALTER TABLE `history_log` ADD UNIQUE INDEX `history_log_2`(`itemid`,`id`,`clock`); ALTER TABLE `history_text` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`clock`); ALTER TABLE `history_text` DROP KEY `history_text_2`; ALTER TABLE `history_text` ADD UNIQUE INDEX `history_text_2`(`itemid`,`id`,`clock`);
для версии 2.0 дополнительно выполните:
ALTER TABLE `acknowledges` DROP FOREIGN KEY `c_acknowledges_1`, DROP FOREIGN KEY `c_acknowledges_2`; 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`; ALTER TABLE `auditlog` DROP FOREIGN KEY `c_auditlog_1`; ALTER TABLE `service_alarms` DROP FOREIGN KEY `c_service_alarms_1`; ALTER TABLE `auditlog_details` DROP FOREIGN KEY `c_auditlog_details_1`;
2. Теперь следует определиться с начальной секцией у каждой таблицы. Требуется создать секции от момента минимального значения поля clock и до текущего момента (дня, месяца включительно) в секционируемых таблицах. Узнать какое минимальное значение времени в таблице можно запросом:
SELECT FROM_UNIXTIME(MIN(clock)) FROM `history_uint`;
3. Так же нужно заполнить таблицу ”manage_partitions” в соответствии с выбранным диапазоном разбиения и сроком хранения секций. Таблица должна быть уже создана.
INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('acknowledges', 'month', 6, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('alerts', 'month', 6, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('auditlog', 'month', 6, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('events', 'month', 6, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('service_alarms', 'month', 6, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history', 'day', 30, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_uint', 'day', 30, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_str', 'day', 120, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_text', 'day', 120, now(), ''); INSERT INTO manage_partitions (tablename, period, keep_history, last_updated, comments) VALUES ('history_log', 'day', 120, 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(), '');
Обратите внимание, что общее количество разбиений не может превышать 1024ввиду внутренних ограничений MySQL
4. Далее представлены примеры добавления секций для уже созданных таблиц.
Разбиение по дням:
ALTER TABLE `history_uint` PARTITION BY RANGE ( clock) (PARTITION p2011_10_23 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-24 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_10_24 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-25 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_10_25 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-26 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_10_26 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-27 00:00:00") div 1) ENGINE = InnoDB, ... PARTITION p2011_11_20 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-21 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_11_21 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-22 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_11_22 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-23 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_11_23 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-24 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_11_24 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-25 00:00:00") div 1) ENGINE = InnoDB);
Разбиение по месяцам:
ALTER TABLE `trends_uint` PARTITION BY RANGE ( clock) (PARTITION p2010_10 VALUES LESS THAN (UNIX_TIMESTAMP("2010-11-01 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2010_11 VALUES LESS THAN (UNIX_TIMESTAMP("2010-12-01 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2010_12 VALUES LESS THAN (UNIX_TIMESTAMP("2011-01-01 00:00:00") div 1) ENGINE = InnoDB, ... PARTITION p2011_08 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_09 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_10 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_11 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00") div 1) ENGINE = InnoDB, PARTITION p2011_12 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00") div 1) ENGINE = InnoDB);
3.2 Функции управления
Не спешите создавать/менять процедуры и правило планировщика. Для начала разберитесь как работает вся схема и только после этого приступайте.
3.2.1 Процедура проверки наличия требуемых секций
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 ;
3.2.2 Создание секции по дням
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 ;
3.2.3 Создание секций по месяцам
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 ;
3.2.4 Проверка наличия устаревших секций и удаление
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 ;
3.2.5 Удаление указанной секции
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 ;
3.2.6 Таблица настроек секционирования
В этой таблице будут хранится настройки секционирования для таблиц Zabbix.
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;
4 Управление разбиениями
Обычно список разбиений создаётся при начальном определении таблицы, которая изначально не планируется как статическая. Это означает, что впоследствии вы захотите удалить старые разбиения с данными и периодически добавлять разбиения с новыми данными. Одно из наиболее важных преимуществ разбиения состоит в том, что оно позволяет вместо перемещения больших объёмов данных, манипулировать структурой разбиений.
4.1 Планировщик (events)
По умолчанию в большинстве версий MySQL планировщик отключен. Чтобы его включить добавьте event_scheduler = 1; в файл конфигурации MySQL. Для избежания перезапуска MySQL следует выполнить далее команду SET GLOBAL event_scheduler = ON;
С помощью планировщика возможно автоматически запускать управление разбиениями (создание, удаление устаревших) в заданное время через заданный интервал.
В примере, управление разбиениями запускается ежедневно в 4 часа утра:
DELIMITER $$ CREATE EVENT IF NOT EXISTS `e_part_manage` ON SCHEDULE EVERY 1 DAY STARTS '2011-08-08 04:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Управление созданием и удалением секций' DO BEGIN CALL zabbix.drop_partitions('zabbix'); CALL zabbix.create_next_partitions('zabbix'); END$$ DELIMITER ;
Более подробно о создании события можно прочитать в разделе ”CREATE EVENT Syntax” на сайте MySQL.
4.2 Скриптовая обвязка
Кроме того, вместо планировщика MySQL возможно воспользоваться скриптом по управлению разбиениями. Его следует выполнять каждые сутки через cron. Скрипт поддерживает создание новых разбиений и удаление устаревших.
Следует учесть, что прежде чем добавлять скрипт в cron нужно будет настроить в нем периоды хранения и тип разбиений для каждой таблицы в соответствии с желаемым. Ниже приводится пример конфигурации из скрипта:
my $tables = { 'history' => { 'period' => 'day', 'keep_history' => '30'}, 'history_log' => { 'period' => 'day', 'keep_history' => '120'}, 'history_str' => { 'period' => 'day', 'keep_history' => '120'}, 'history_text' => { 'period' => 'day', 'keep_history' => '120'}, 'history_uint' => { 'period' => 'day', 'keep_history' => '120'}, 'trends' => { 'period' => 'month', 'keep_history' => '24'}, 'trends_uint' => { 'period' => 'month', 'keep_history' => '24'}, 'acknowledges' => { 'period' => 'month', 'keep_history' => '6'}, 'alerts' => { 'period' => 'month', 'keep_history' => '6'}, 'auditlog' => { 'period' => 'month', 'keep_history' => '6'}, 'events' => { 'period' => 'month', 'keep_history' => '6'}, 'service_alarms' => { 'period' => 'month', 'keep_history' => '6'}, };
Обратите внимание, что если задать значение 0 для ”keep_history”, то будет храниться только текущее разбиение, т.е. активное.
4.3 Создание секций
Для добавления нового разбиения можно воспользоваться следующей командой:
ALTER TABLE `history_uint` ADD PARTITION p2011_10_23 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-24 00:00:00") div 1) ENGINE = InnoDB;
4.4 Удаление устаревших секций
Для удаления устаревших данных нужно просто удалить секцию, в котором больше нет необходимости:
ALTER TABLE `history_uint` DROP PARTITION p2011_06;
Это гораздо быстрее, чем удаление миллионов записей из таблицы, потому что при этом не выполняется отдельное удаление каждой записи, однако помните, что удаление большой по размеру секции так же занимает некоторое время!
4.5 Ограничения
- Следует помнить, что при создании/добавления новых секций диапазоны разбиений не должны перекрывать друг друга, в противном случае попытка завершится ошибкой.
- В MySQL таблица разбивается либо вся, либо не разбивается вовсе. Из-за этого не должно оставаться записей которые не попадают ни в одну из создаваемых секций.
- При создании таблиц с большим количеством секций, Вы возможно столкнетесь с ошибками вида “Can't create/write to file”. Чтобы избежать подобных ситуаций увеличьте значение параметра ”open_files_limit” в файле конфигурации MySQL.
- Разбитые на секции таблицы не поддерживают внешние ключи (”FOREIGN KEYS”) ввиду внутренних ограничений MySQL. Именно по этой причине в разделе подготовки к секционированию внешние ключи были удалены.
- Все столбцы, используемые в выражении выделения разделов для разбитой на разделы таблицы, должны быть частью каждого уникального ключа, который таблица может иметь. Другими словами, каждый уникальный ключ на таблице должен использовать каждый столбец в выражении выделения разделов таблиц. Именно по этой причине в разделе подготовки к секционированию первичные ключи были удалены, а вместо них были созданые обычные индексы.
- Максимальное количество разбиений не может превышать 1024, включая подразбиения секций. Учитывайте, пожалуйста, этот момент при планировании разбиений.
- Существует еще множество ограничений при использовании секционирования обсуждение которых выходит за рамки данной статьи. Более подробно с ограничениями вы можете ознакомиться на сайте MySQL.
4.6 Рекомендации
- Используйте MySQL версии 5.5. Он более оптимизирован и стабилен при использовании секционированных таблиц.
- Используйте не в чистом виде InnoDB, а XtraDB. Этот engine входит в состав таких форков MySQL как MariaDB и Percona.
- TokuDB показал не самые лучшие результаты в плане выборки из таблиц этого engine (на очень больших объемах).
- Оптимизация, оптимизация и еще раз оптимизация. MySQL уже давненько стал не просто программой с возможностью хранения табличек аля Excel, поэтому и ему теперь требуется тщательная оптимизация параметров.
5 Известные проблемы
1. В случае использования скриптовой обвязки для управления разбиениями может получится, что следующие секции не будут созданы. Такая ситуация может возникнуть при недоступности базы данных в момент выполнения скрипта по cron.
2. Возможно стоит создавать сразу несколько разбиений "на будущее" для таблиц.
3. Добавлен ??div 1?? при создании разбиений. Сделано только лишь из-за MariaDB в которой начиная с версии 5.3 UNIX_TIMESTAMP возвращает нецелочисленное значение: https://lists.launchpad.net/maria-discuss/msg00682.html
4. Таблица ids - это узкое место Zabbix. Была введена для управления идентификаторами объектов в распределенном мониторинге. Частично будет упразднена в версии 2.2. Если у вас появляются сообщения вида:
[Z3005] query failed: [1205] Lock wait timeout exceeded; try restarting transaction [update ids set nextid=nextid+327 where nodeid=10 and table_name='events' and field_name='eventid']
то как вариант попытайтесь выставить тип этой таблицы как MyISAM. Если же у вас не настроен РМ на основе нод как таковой, то возможно попытаться выставить тип этой таблицы MEMORY.
Переход на другие типы таблицы ids не тестировался в полной мере. Этот пункт вы делаете на свой страх и риск. Если у вас уже есть опыт в смене типа таблицы, буду рад услышать результат.
Комментариев нет:
Отправить комментарий