Секционирование (партицирование) БД Zabbix на СУБД PostgreSQL
Секционирование (партицирование) БД Zabbix на СУБД PostgreSQL
Подробно назначение и область применение секционирования таблиц описано на https://postgrespro.ru/docs/postgresql/11/ddl-partitioning
Здесь мы рассмотрим практическое применение секционирования таблиц для базы данных Zabbix.
Особенности хранения данных в Zabbix
Основной поток данных пишется в таблицы: history, history_log, history_str, history_text, history_uint, trends и trends_uint. Для очистки устаревших данных вызывается внутренний обработчик housekeeper, который уже формирует запросы DELETE к СУБД. Для систем с большим количеством опрашиваемых хостов и отслеживаемых метрик часто складывается ситуация, когда housekeeper не успевает быстро отработать удаление данных, и система уходит в состояние перманентно высокого iowait. Именно для решения этой проблемы будет применено секционирование таблиц.
В отличии от перечисленных на https://zabbix.org/wiki/Category:Table_partitioning способов секционирования на основе самописных хранимых процедур и триггеров, мы воспользуемся встроенным, начиная с версии 10, в PostgreSQL секционированием.
Установка для новой системы
Устанавливаем сервер СУБД:
# apt-get install postgresql11-contrib postgresql11-server postgresql11-pg_partman
где postgresql11-pg_partman - серверное расширение PostgreSQL упрощающее управление и сопровождение данных в секционированных таблицах
Проводим установку Zabbix по инструкции.
Останавливаем все процессы Zabbix, с целью недопущения неконтролируемого изменения БД в процессе работы над нет.
systemctl stop zabbix_pgsql.service
shared_preload_libraries = 'pg_stat_statements, pg_partman_bgw' # (change requires restart) ... enable_partition_pruning = on pg_partman_bgw.interval = 3600 pg_partman_bgw.role = 'zabbix' pg_partman_bgw.dbname = 'zabbix' pg_partman_bgw.analyze = off pg_partman_bgw.jobmon = on
где: pg_partman_bgw.role - имя пользователя в PostgreSQL, с которым происходит подключение к БД сервера Zabbix; pg_partman_bgw.dbname - название БД, к которой подключается Zabbix. И перезапускаем PostgreSQL.
Подключаемся к СУБД суперпользователем, создаем схему partman, создаем расширение pg_partman, задаем права для пользователя zabbix на схему и расширение:
$ psql -U postgres zabbix GRANT ALL PRIVILEGES ON DATABASE zabbix to zabbix; CREATE SCHEMA partman AUTHORIZATION "zabbix"; CREATE EXTENSION pg_partman schema partman; GRANT ALL ON SCHEMA partman TO zabbix; GRANT ALL ON ALL TABLES IN SCHEMA partman TO zabbix; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO zabbix; GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO zabbix;
Подключаемся с СУБД пользователем zabbix:
$ psql -U zabbix zabbix
Удаляем изменяемые таблицы:
DROP TABLE history; DROP TABLE history_log; DROP TABLE history_str; DROP TABLE history_text; DROP TABLE history_uint; DROP TABLE trends; DROP TABLE trends_uint;
Процесс housekeeper будет все равно формировать очереди на удаление и вносить их в таблицу housekeeper. Чтобы не засорять БД лишними данными создаем процедуру заглушку и триггер на таблицу:
CREATE OR REPLACE FUNCTION housekeeper_blackhole()
RETURNS trigger AS
$func$
BEGIN
RETURN NULL;
END
$func$ LANGUAGE plpgsql;
CREATE TRIGGER housekeeper_blackhole
BEFORE INSERT ON housekeeper
FOR EACH ROW
EXECUTE PROCEDURE housekeeper_blackhole();
DELETE FROM housekeeper;
Создаем таблицы с возможностью секционирования:
CREATE TABLE history (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value numeric(16,4) DEFAULT '0.0000' NOT NULL,
ns integer DEFAULT '0' NOT NULL
) PARTITION BY RANGE (clock);
CREATE INDEX history_1 ON history (itemid,clock);
CREATE TABLE history_uint (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value numeric(20) DEFAULT '0' NOT NULL,
ns integer DEFAULT '0' NOT NULL
) PARTITION BY RANGE (clock);
CREATE INDEX history_uint_1 ON history_uint (itemid,clock);
CREATE TABLE history_str (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value varchar(255) DEFAULT '' NOT NULL,
ns integer DEFAULT '0' NOT NULL
) PARTITION BY RANGE (clock);
CREATE INDEX history_str_1 ON history_str (itemid,clock);
CREATE TABLE history_log (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
timestamp integer DEFAULT '0' NOT NULL,
source varchar(64) DEFAULT '' NOT NULL,
severity integer DEFAULT '0' NOT NULL,
value text DEFAULT '' NOT NULL,
logeventid integer DEFAULT '0' NOT NULL,
ns integer DEFAULT '0' NOT NULL
) PARTITION BY RANGE (clock);
CREATE INDEX history_log_1 ON history_log (itemid,clock);
CREATE TABLE history_text (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
value text DEFAULT '' NOT NULL,
ns integer DEFAULT '0' NOT NULL
) PARTITION BY RANGE (clock);
CREATE INDEX history_text_1 ON history_text (itemid,clock);
CREATE TABLE trends (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
num integer DEFAULT '0' NOT NULL,
value_min numeric(16,4) DEFAULT '0.0000' NOT NULL,
value_avg numeric(16,4) DEFAULT '0.0000' NOT NULL,
value_max numeric(16,4) DEFAULT '0.0000' NOT NULL,
PRIMARY KEY (itemid,clock)
) PARTITION BY RANGE (clock);
CREATE TABLE trends_uint (
itemid bigint NOT NULL,
clock integer DEFAULT '0' NOT NULL,
num integer DEFAULT '0' NOT NULL,
value_min numeric(20) DEFAULT '0' NOT NULL,
value_avg numeric(20) DEFAULT '0' NOT NULL,
value_max numeric(20) DEFAULT '0' NOT NULL,
PRIMARY KEY (itemid,clock)
) PARTITION BY RANGE (clock);
Активируем секционирование для таблиц:
SELECT partman.create_parent('public.history', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.history_uint', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.history_str', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.history_text', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.history_log', 'clock', 'native', 'daily', null, 7, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.trends', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
SELECT partman.create_parent('public.trends_uint', 'clock', 'native', 'monthly', null, 12, 'on', null, true, 'seconds');
Для таблиц history* задаем тип секционирования daily (ежедневный), с предварительным созданием 7 секций; для таблиц trends секционирование monthly, с предварительным созданием 12 секций.
Задаем глубину хранения данных в секциях (для history* 8 дней, для trends* 13 месяцев) и автоматическое обслуживание таблиц:
UPDATE partman.part_config set retention = '8 day', retention_keep_table = false, retention_keep_index = false,
automatic_maintenance = 'on' WHERE parent_table = 'public.history';
UPDATE partman.part_config set retention = '8 day', retention_keep_table = false, retention_keep_index = false,
automatic_maintenance = 'on' WHERE parent_table = 'public.history_uint';
UPDATE partman.part_config set retention = '8 day', retention_keep_table = false, retention_keep_index = false,
automatic_maintenance = 'on' WHERE parent_table = 'public.history_str';
UPDATE partman.part_config set retention = '8 day', retention_keep_table = false, retention_keep_index = false,
automatic_maintenance = 'on' WHERE parent_table = 'public.history_text';
UPDATE partman.part_config set retention = '8 day', retention_keep_table = false, retention_keep_index = false,
automatic_maintenance = 'on' WHERE parent_table = 'public.history_log';
UPDATE partman.part_config set retention = '13 month', retention_keep_table = false, retention_keep_index = false,
automatic_maintenance = 'on' WHERE parent_table = 'public.trends';
UPDATE partman.part_config set retention = '13 month', retention_keep_table = false, retention_keep_index = false,
automatic_maintenance = 'on' WHERE parent_table = 'public.trends_uint';
Запускаем обслуживание секций таблиц:
SELECT partman.run_maintenance('public.history');
SELECT partman.run_maintenance('public.history_uint');
SELECT partman.run_maintenance('public.history_str');
SELECT partman.run_maintenance('public.history_text');
SELECT partman.run_maintenance('public.history_log');
SELECT partman.run_maintenance('public.trends');
SELECT partman.run_maintenance('public.trends_uint');
Отключаем автоматический анализатор для секционированных таблиц, так как в противном случае производится блокировка таблицы на время анализа
SELECT partman.run_maintenance(p_analyze := false);
Отключение внутренней очистки истории Zabbix
В веб-интерфейсе Zabbix перейти на: Администрирование -> Общие. В выпадающем списке выбрать пункт "Очистка истории", и в появившемся окне снять все галки, какие там были установлены.
Миграция на существующей системе
Требования: PostgreSQL должен быть версии 11 и новее.
Установка дополнительных пакетов:
Крайне желательно предварительно снять резервную копию БД!
# apt-get install postgresql11-contrib postgresql11-pg_partman
где postgresql11-pg_partman - серверное расширение PostgreSQL упрощающее управление и сопровождение данных в секционированных таблицах
Правим конфиг /var/lib/pgsql/data/postgresql.conf, добавив такие строки:
shared_preload_libraries = 'pg_stat_statements, pg_partman_bgw' # (change requires restart) ... enable_partition_pruning = on pg_partman_bgw.interval = 3600 pg_partman_bgw.role = 'zabbix' pg_partman_bgw.dbname = 'zabbix' pg_partman_bgw.analyze = off pg_partman_bgw.jobmon = on
где: pg_partman_bgw.role - имя пользователя в PostgreSQL, с которым происходит подключение к БД сервера Zabbix; pg_partman_bgw.dbname - название БД, к которой подключается Zabbix. И перезапускаем PostgreSQL.
Подключаемся к СУБД суперпользователем, создаем схему partman, создаем расширение pg_partman, задаем права для пользователя zabbix на схему и расширение:
$ psql -U postgres zabbix GRANT ALL PRIVILEGES ON DATABASE zabbix to zabbix; CREATE SCHEMA partman AUTHORIZATION "zabbix"; CREATE EXTENSION pg_partman schema partman; GRANT ALL ON SCHEMA partman TO zabbix; GRANT ALL ON ALL TABLES IN SCHEMA partman TO zabbix; GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA partman TO zabbix; GRANT EXECUTE ON ALL PROCEDURES IN SCHEMA partman TO zabbix;
Подключаемся с СУБД пользователем zabbix:
$ psql -U zabbix zabbix
Процесс housekeeper будет все равно формировать очереди на удаление и вносить их в таблицу housekeeper. Чтобы не засорять БД лишними данными создаем процедуру заглушку и триггер на таблицу:
CREATE OR REPLACE FUNCTION housekeeper_blackhole()
RETURNS trigger AS
$func$
BEGIN
RETURN NULL;
END
$func$ LANGUAGE plpgsql;
CREATE TRIGGER housekeeper_blackhole
BEFORE INSERT ON housekeeper
FOR EACH ROW
EXECUTE PROCEDURE housekeeper_blackhole();
DELETE FROM housekeeper;
Переименовываем таблицы, подлежащие секционированию:
ALTER TABLE history RENAME TO history_temp; ALTER TABLE history_log RENAME TO history_log_temp; ALTER TABLE history_str RENAME TO history_str_temp; ALTER TABLE history_text RENAME TO history_text_temp; ALTER TABLE history_uint RENAME TO history_uint_temp; ALTER TABLE trends RENAME TO trends_temp; ALTER TABLE trends_uint RENAME TO trends_uint_temp; DROP INDEX history_1; DROP INDEX history_log_1; DROP INDEX history_str_1; DROP INDEX history_text_1; DROP INDEX history_uint_1; DROP INDEX trends_pkey; DROP INDEX trends_uint_pkey;
Выполняем действия, описанные в п.1.2.6 и 1.2.7
Копируем данные из старых таблиц в новые секционированные
INSERT INTO history SELECT * FROM history_temp; INSERT INTO history_uint SELECT * FROM history_uint_temp; INSERT INTO history_str SELECT * FROM history_str_temp; INSERT INTO history_log SELECT * FROM history_log_temp; INSERT INTO history_text SELECT * FROM history_text_temp; INSERT INTO trends SELECT * FROM trends_temp; INSERT INTO trends_uint SELECT * FROM trends_uint_temp;
Выполняем действия, описанные в пунктах c 1.2.8 по 1.2.11
Удаляем старые таблицы:
DROP TABLE history_temp; DROP TABLE history_uint_temp; DROP TABLE history_str_temp; DROP TABLE history_log_temp; DROP TABLE history_text_temp; DROP TABLE trends_temp; DROP TABLE trends_uint_temp;