Система мониторинга Zabbix - Партицирование таблиц MySQL

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

Предупреждаю, перед тем как использовать приведенные в статье скрипты, делайте полный бекап базы. Вы используйте всю информацию и скрипты на свой ​​страх и риск!

Я предлагаю отключить чистильщик и разделить некоторые таблицы по времени.

Основа идеи

Разбиваем основные таблицы на две группы:

1. Дневные (самые быстрорастущие таблицы)

• history
• history_log
• history_str
• history_text
• history_uint

2. Месячные (ну и название блин, в общем чуть менее нагруженные таблицы)

• acknowledges
• alerts
• auditlog
• events
• service_alarms
• trends
• trends_unit

Подготовка таблиц

Для начала проведем кое какие изменения с первичными ключами:

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

Установка месячных разделов

Данный шаг надо проделать для каждой из таблиц. В этом примере мы создадим ежемесячные разделы, для событий с мая 2011 до декабря 2011 года:

ALTER TABLE 'events' PARTITION BY RANGE(clock) (
PARTITION p201105 VALUES LESS THAN (UNIX_TIMESTAMP("2011-06-01 00:00:00")),
PARTITION p201106 VALUES LESS THAN (UNIX_TIMESTAMP("2011-07-01 00:00:00")),
PARTITION p201107 VALUES LESS THAN (UNIX_TIMESTAMP("2011-08-01 00:00:00")),
PARTITION p201108 VALUES LESS THAN (UNIX_TIMESTAMP("2011-09-01 00:00:00")),
PARTITION p201109 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-01 00:00:00")),
PARTITION p201110 VALUES LESS THAN (UNIX_TIMESTAMP("2011-11-01 00:00:00")),
PARTITION p201111 VALUES LESS THAN (UNIX_TIMESTAMP("2011-12-01 00:00:00")),
PARTITION p201112 VALUES LESS THAN (UNIX_TIMESTAMP("2012-01-01 00:00:00"))
);

Установка дневных разделов

Проделываем данный шаг для каждой из дневных таблиц:

В этом примере мы создадим ежедневные разделы, для значений таблицы history_uint с 15 мая до 22 мая:

ALTER TABLE `history_uint` PARTITION BY RANGE( clock ) (
PARTITION p20110515 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-16 00:00:00")),
PARTITION p20110516 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-17 00:00:00")),
PARTITION p20110517 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-18 00:00:00")),
PARTITION p20110518 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-19 00:00:00")),
PARTITION p20110519 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-20 00:00:00")),
PARTITION p20110520 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-21 00:00:00")),
PARTITION p20110521 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-22 00:00:00")),
PARTITION p20110522 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-23 00:00:00"))
);

Вот как управлять разделами вручную:

Добавляем раздел

ALTER TABLE `history_uint` ADD PARTITION (
PARTITION p20110523 VALUES LESS THAN (UNIX_TIMESTAMP("2011-05-24 00:00:00"))
);

Удаляем раздел

ALTER TABLE `history_uint` DROP PARTITION p20110515;

Далее выкладываю скрипт который чистит дневные разделы, оставляя только три последних:

https://github.com/xsbr/zabbixzone/blob/master/zabbix-mysql-autopartitioning.sql

Вот это не забудьте добавить в cron для автоматического запуска:

mysql -B -h localhost -u zabbix -pPASSWORD zabbix -e "CALL create_zabbix_partitions();"