Секционирование (партицирование) БД 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

Правим конфиг /var/lib/pgsql/data/postgresql.conf, добавив такие строки в секцию -Shared Library Preloading-:

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;

Копируем данные из старых таблиц в новые секционированные

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;

Удаляем старые таблицы:

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;