Примерно год назад передо мной и моими коллегами была поставлена задача разобраться с использованием популярной системы мониторинга сетевой инфраструктуры — Zabbix. После изучения документации мы сразу же перешли к нагрузочному тестированию: хотели оценить с каким количеством параметров может работать Zabbix без заметных падений производительности. В качестве СУБД использовали только PostgreSQL.

В ходе тестов были выявлены некоторые архитектурные особенности разметки БД и поведения самой системы мониторинга, которые по умолчанию не позволяют выйти системе мониторинга на свою максимальную мощность работы. В результате были разработаны, проведены и апробированы некоторые оптимизационные мероприятия в основном в части настройки БД.

О результатах проделанной работы я и хочу поделиться в данной статье. Статья будет полезна как администраторам Zabbix, так и PostgreSQL DBA, а также всем желающим лучше понять и разобраться в популярной СУБД PosgreSQL.

Небольшой спойлер: на слабой машине при нагрузке в 200 тысяч параметров в минуту нам удалось снизить показатель CPU iowait с 20% до 2%, уменьшить время записи порциями в таблицы первичных данных в 250 раз и в таблицы агрегированных данных в 32 раза, уменьшить размер индексов в 5-10 раз и ускорить получение исторических выборок в некоторых случаях до 18 раз.

Нагрузочное тестирование


Нагрузочное тестирование проводилось по схеме: один сервер Zabbix, один активный Zabbix proxy, два агента. Каждый агент был настроен чтобы отдавать по 50 т. целочисленных и 50 т. строковых параметров в минуту (суммарно с двух агентов получается 200 т. параметров в минуту или по 3333 параметра в секунду). Для генерации параметров агента мы использовали плагин для Zabbix Для проверки того, какое максимальное количество параметров может генерировать агент, нужно использовать специальный скрипт от того же автора плагина zabbix_module_stress. Web-админка Zabbix имеет сложности с регистрацией больших шаблонов, поэтому мы разбили параметры на 20 шаблонов по 5 т. параметров (2500 числовых и 2500 строковых).

Скрипт генератора шаблонов для нагрузочного тестирования на python
import argparse

"""
Генерирует шаблоны для нагрузочного теста.
Были сгенерированы 20 шаблонов по 5000 параметров в каждом 
(по 2500 каждого вида: параметр echo, тип строка; параметр ping, тип число)
"""

TEMP_HEAD = """
<?xml version="1.0" encoding="UTF-8"?>
<zabbix_export>
    <version>2.0</version>
    <date>2015-08-17T23:15:01Z</date>
    <groups>
        <group>
            <name>Templates</name>
        </group>
    </groups>
    <templates>
        <template>
            <template>Template Zabbix Srv Stress {count} passive {char}</template>
            <name>Template Zabbix Srv Stress {count} passive {char}</name>
            <description/>
            <groups>
                <group>
                    <name>Templates</name>
                </group>
            </groups>
            <applications/>
            <items>
"""

TEMP_END = """</items>
            <discovery_rules/>
            <macros/>
            <templates/>
            <screens/>
        </template>
    </templates>
</zabbix_export>
"""

TEMP_ITEM = """<item>
                    <name>{k}</name>
                    <type>0</type>
                    <snmp_community/>
                    <multiplier>0</multiplier>
                    <snmp_oid/>
                    <key>{k}</key>
                    <delay>1m</delay>
                    <history>3</history>
                    <trends>365</trends>
                    <status>0</status>
                    <value_type>{t}</value_type>
                    <allowed_hosts/>
                    <units/>
                    <delta>0</delta>
                    <snmpv3_contextname/>
                    <snmpv3_securityname/>
                    <snmpv3_securitylevel>0</snmpv3_securitylevel>
                    <snmpv3_authprotocol>0</snmpv3_authprotocol>
                    <snmpv3_authpassphrase/>
                    <snmpv3_privprotocol>0</snmpv3_privprotocol>
                    <snmpv3_privpassphrase/>
                    <formula>1</formula>
                    <delay_flex/>
                    <params/>
                    <ipmi_sensor/>
                    <data_type>0</data_type>
                    <authtype>0</authtype>
                    <username/>
                    <password/>
                    <publickey/>
                    <privatekey/>
                    <port/>
                    <description/>
                    <inventory_link>0</inventory_link>
                    <applications/>
                    <valuemap/>
                    <logtimefmt/>
                </item>
"""

TMP_FNAME_DEFAULT = "Template_App_Zabbix_Server_Stress_{count}_passive_{char}.xml"

chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

if __name__ == "__main__":
    parser = argparse.ArgumentParser(
        description='Генератор шаблонов для стресс теста zabbix')
    parser.add_argument('--items', dest='items', type=int, default=1000,
                        help='Кол-во элементов данных (default: 1000)')
    parser.add_argument('--templates', dest='templates', type=int, default=1,
                        help=f'Кол-во шаблонов [1-{len(chars)}] (default: 1)')
    args = parser.parse_args()
    items_count = args.items
    tmps_count = args.templates
    if not (tmps_count >= 1 and tmps_count <= len(chars)):
        sys.exit(f"Templates must be in range 1 - {len(chars)}")
    for i in range(tmps_count):
        fname = TMP_FNAME_DEFAULT.format(count=items_count, char=chars[i])
        with open(fname, "w") as output:
            output.write(TEMP_HEAD.format(count=items_count, char=chars[i]))
            for k,t in [('stress.ping[{}-I-{:06d}]',3),
                        ('stress.echo[{}-S-{:06d}]',4)]:
                for j in range(int(items_count/2)):
                    output.write(TEMP_ITEM.format(k=k.format(chars[i],j),t=t))
            output.write(TEMP_END)


Метрика cpu iostat служит хорошим показателем производительности Zabbix — она отражает долю единицы времени, в течение которой процессор ожидает доступа к диску. Чем она выше — тем больше диск занят операциями чтения и записи, что косвенно влияет на ухудшение производительности системы мониторинга в целом. Т.е. это верный признак того, что с мониторингом что-то не в порядке. Кстати, на просторах сети довольно популярный вопрос «как убрать триггер iostat в Zabbix», так что это наболевшая тема, потому что существует множество причин повышения значения метрики iowait.

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



А вот какую картину для этой же метрики мы получили также в течение трёх дней в итоге после всех проделанных оптимизационных мероприятий, речь о которых будет идти ниже:



Как видно из графиков показатель cpu iowait упал практически с 20% до 2%, что косвенно ускорило время выполнения всех запросов на добавление и чтение данных. Теперь давайте разберёмся почему при стандартных настройках БД происходит падение общей производительности системы мониторинга и как это исправить.

Причины падения производительности Zabbix


При накоплении более 10 млн значений параметров в каждой таблице первичных данных было замечено, что быстродействие системы мониторинга резко падает, что связано со следующими причинами:

  • повышается метрика iowait для ЦП сервера свыше 20%, что свидетельствует о возрастании времени, в течении которого ЦП ожидает доступа к операциям чтения и записи диска
  • сильно раздуваются индексы таблиц, в которых хранятся данные мониторинга
  • повышается метрика общего использования (utilization) до 100% для диска с данными мониторинга, что свидетельствует о полной загруженности диска операциями чтения и записи
  • устаревшие значения не успевают удаляться из таблиц историй при очистке по расписанию housekeeper

Ситуация усугубляется в начале каждого часа, когда в добавок к этому высчитывается агрегированная почасовая статистика — при этом выполняется активное чтение и запись индексных страниц с диска, удаление устаревших данных из истории, что приводит всё к тому же результату — падение производительности БД и увеличение времени выполнения запросов (в пределе было отмечен запрос длительностью до 5ти минут!).

Небольшая справка по организации хранилища данных мониторинга в Zabbix. Он хранит первичные данные и агрегированные данные в разных таблицах, причём с разделением по типам параметров. Каждая таблица хранит поле itemid (неявная ссылка на зарегистрированный элемент данных в системе), временную метку регистрации значения clock в формате unix timestamp (миллисекунды в отдельном столбце) и значение в отдельном столбце (исключением является таблица логов, в ней больше полей — подобие журнала событий):
Имя таблицы Назначение Тип данных
history Первичные данные мониторинга numeric(16,4)
history_uint Первичные данные мониторинга numeric(20,0)
history_str Первичные данные мониторинга varchar(255)
history_text Первичные данные мониторинга text
history_logs Первичные данные мониторинга поля text и int
trends Агрегированные данные мониторинга numeric(16,4)
trends_uint Агрегированные данные мониторинга numeric(20,0)

Оптимизационные мероприятия


Для повышения производительности БД PostgreSQL были проведены различные оптимизационые мероприятия, основными из которых являются партиционирование и изменение индексов. Однако стоит упомянуть парой слов ещё о нескольких важных и полезных мероприятиях, способных ускорить работу любой БД под СУБД PostgreSQL.

Важное замечание. На момент сбора материала статьи нами использовался Zabbix версии 4.0, хотя сейчас уже вышла версия 4.2 и готовится к выходу версия 4.4. Почему об этом важно упомянуть? Потому что начиная с версии 4.2 Zabbix стал поддерживать специальное мощное расширение для работы с временными рядами TimescaleDB, но пока в экспериментальном режиме: при всех достоинствах использования этого расширения есть мнение, что некоторые запросы стали работать медленнее и имеются пока ещё не решённые проблемы производительности (будут решены в версии 4.4) — прочтите эту статью. В следующей статье планирую написать о результатах нагрузочного тестирования уже с использованием расширения TimescaleDB в сравнении с данным кейсом решений. Версия PostgreSQL использовалась 10, но вся приведённая информация актуальна и для 11 и 12 версий (ждём!).

Поэтому обо всём по порядку:

  • настройка конфигурационного файла с помощью утилиты pgtune
  • вынесение БД на отдельный физический диск
  • партиционирование таблиц истории с помощью pg_pathman
  • изменение типов индексов таблиц истории на brin (clock) и btree-gin (itemid)
  • сбор и анализ статистики выполнения запросов pg_stat_statements
  • настройка параметров мониторинга физических дисков
  • аппаратное улучшение производительности
  • создание распределённого кластера (материал за рамками данной статьи)


Настройка конфигурационного файла с помощью утилиты pgtune


На самом деле PostgreSQL — довольно легковесная СУБД. Её конфигурационный файл по умолчанию настроен так, чтобы, как говорит мой коллега, «работать даже на кофеварке», т.е. на весьма скромном железе. Поэтому обязательно нужно настраивать PostgreSQL под конфигурацию сервера, учитывая объём памяти, количество процессоров, тип предполагаемого использования БД, тип диска (HDD или SSD) и количество подключений.

Увы, не существует единой формулы настройки всех СУБД, но есть определённые правила и закономерности, подходящие для большинства конфигураций (более тонкая настройка — уже дело рук эксперта). Для упрощения жизни DBA была написана утилита pgtune, которая была дополнена web версией пользователем le0pard — автором интересной и полезной книги по администрированию PostgreSQL.

Пример запуска утилиты в консоли с указанием 100 подключений (у Zabbix требовательная Web админка) под тип приложения «Data warehouses»:

pgtune -i postgresql.conf -o new_postgresql.conf -T DW -c 100

Конфигурационные параметры, которые меняет утилита pgtune, с описанием назначения (значения приведены в качестве примера)
# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 8 GB
# CPUs num: 1
# Connections num: 100
# Data Storage: hdd

max_connections = 100               # максимальное количество одновременных подключений к БД
shared_buffers = 2GB                # объём памяти для различных буферов (в основном кэш блоков таблиц и индексов) в разделяемой памяти
effective_cache_size = 6GB          # максимальный размер требуемой памяти для выполнения запроса с использованием индексов
maintenance_work_mem = 512MB        # влияет на скорость выполнения операций VACUUM, ANALYZE, CREATE INDEX
checkpoint_completion_target = 0.7  # целевое время для завершения процедуры контрольной точки
wal_buffers = 16MB                  # количество памяти, используемое в Shared Memory для ведения транзакционных логов
default_statistics_target = 100     # объём статистики, собираемой командой ANALYZE - при увеличении оптимизатор строить запросы медленнее, но лучше
random_page_cost = 4                # условная стоимость индексного доступа к страницам данных - влияет на решение использовать индекс
effective_io_concurrency = 2        # количество асинхронных операций ввода/вывода, которое будет пытаться выполнить СУБД в отдельном сеансе
work_mem = 10485kB                  # объём памяти, который используется для операций сортировки и хеш-таблиц прежде, чем задействуются временные файлы на диске
min_wal_size = 1GB                  # ограничивает снизу число файлов WAL,  которые будут переработаны для будущего использования
max_wal_size = 2GB                  # ограничивает сверху число файлов WAL, которые будут переработаны для будущего использования

Некоторые полезные параметры конфигурации postgresql
# управление параллельными обработчиками запросов
max_worker_processes = 8            # максимальное число фоновых процессов - минимум по одному на БД
max_parallel_workers_per_gather = 4 # максимальное количество параллельных процессов в рамках одного запроса
max_parallel_workers = 8            # максимальное число рабочих процессов, которое система сможет поддерживать для параллельных операций

# настройки логирования (простой способ узнать о времени выполнения запросов без использования расширения pg_stat_statements)
log_min_duration_statement = 3000   # записывать в логи продолжительность выполнения всех команд, время работы которых >= указанной величины в мс
log_duration = off                  # записывать продолжительность каждой завершённой команды
log_statement = 'none'              # какие SQL-команды записывать в журнал, значения: none (отключено), ddl, mod и all (все команды)
debug_print_plan = off              # вывод дерева плана запроса для последующего анализа

# выжми из БД максимум и будь готов получить по щам при любом сбое (для самых отбитых, кто игнорирует существование ssd и распределённого кластера)
#fsync = off                        # физическая запись на диск изменений, отключение fsync даёт выигрыш в скорости, но может привести к необратимым сбоям
#synchronous_commit = off           # позволяет ответить клиенту ещё до попадания сведений о транзакции в WAL - почти безопасная альтернатива отключения fsync
#full_page_writes = off             # отключение ускоряет обычные операции, но может привести к повреждению или незаметной порче данных после сбоя системы

Вынесение БД на отдельный физический диск


Данный пункт не обязателен и скорее является переходным решением на пути к полноценному распределённому кластеру, но знать о такой возможности будет полезно. Для ускорения работы БД можно вынести её на отдельный диск. Мы смонтировали диск целиком в каталог base, где хранятся все БД PostgreSQL, но вообще можно сделать по-другому: создать новый tablesbase и вынести БД (или даже только её часть — таблицы первичных и агрегированных данных мониторинга) в этот tablesbase на отдельный диск.

Пример монтирования
Предварительно нужно отформатировать диск с файловой системой ext4 и подключить его к серверу. Монтировать диск для БД нужно с меткой noatime:

mount /dev/sdc1 /var/lib/pgsql/10/data/base -o noatime

Для постоянного монтирования нужно добавить в файл /etc/fstab строку:

# где UUID - идентификатор диска, можно посмотреть с помощью утилиты blkid
UUID=121efe29-70bf-410b-bc71-90704568ce3b /var/lib/pgsql/10/data/base ext4 defaults,noatime 0 0 


Партиционирование таблиц истории с помощью pg_pathman


Одна из проблем, с которой мы столкнулись при нагрузочном тестировании Zabbix — PostgreSQL не успевает удалять устаревшие данные из БД. С помощью партиционирования можно разбить таблицу на составные части, тем самым уменьшить размер индексов и составных частей супертаблицы, что положительно сказывается на быстродействии БД в целом.

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

1. ускорение удаления устаревших данных путём удаления целых таблиц

2. дробление индексов под каждую составную таблицу

Для партиционирования в PostgreSQL есть четыре механизма:

1. стандартный constraint_exclusion

2. расширение pg_partman (не путайте с pg_pathman)

3. расширение pg_pathman

4. вручную создавать и поддерживать партиции самим

Наиболее удобным, надёжным и оптимизированным решением для партиционирования, по нашему мнению, является расширение pg_pathman. При таком методе партиционирования планировщик запросов гибко определяет в каких партициях искать данные. Поговаривают, что в 12ой версии PostgreSQL будет отличное партиционирование уже «из коробки».

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

Установка и настройка партиционирования для PostgreSQL 10
Устанавливаем и настраиваем расширение pg_pathman из стандартного репозитория ОС (инструкцию по сборке последней версии расширения из исходников ищите в этом же репозитории на github):

yum install pg_pathman10
nano /var/pgsqldb/postgresql.conf
shared_preload_libraries = 'pg_pathman' # важно - здесь пишите pg_pathman последним в списке

Перезагружаем СУБД, создаём расширение для БД и выполняем настройку партиционирования (1 день для первичных данных мониторинга и 3 дня для агрегированных данных мониторинга — можно было сделать и по 1 дню):

systemctl restart postgresql-10.service
psql -d zabbix -U postgres
CREATE EXTENSION pg_pathman;
# настроим по одному дню для таблиц первичных данных мониторинга
# 1552424400 - начало отсчёта как unix timestamp, 86400 - секунд в сутках
select create_range_partitions('history', 'clock', 1552424400, 86400);
select create_range_partitions('history_uint', 'clock', 1552424400, 86400);
select create_range_partitions('history_text', 'clock', 1552424400, 86400);
select create_range_partitions('history_str', 'clock', 1552424400, 86400);
select create_range_partitions('history_log', 'clock', 1552424400, 86400);
# настроим по три дня для таблиц агрегированных данных мониторинга
# 1552424400 - начало отсчёта как unix timestamp, 259200 - секунд в трёх сутках
select create_range_partitions('trends', 'clock', 1545771600, 259200);  
select create_range_partitions('trends_uint', 'clock', 1545771600, 259200); 

Если в какой-то из таблиц ещё нет данных, то нужно при вызове функции create_range_partitions передать ещё один дополнительный аргумент p_count = 0_.

Полезные запросы для наблюдения и управления партиционированием:

# общий список партиционированных таблиц, основное хранилище конфигурации:
select * from pathman_config;
# представление со всеми существующими разделами, а также их родители и границы диапазонов:
select * from pathman_partition_list;
# дополнительные параметры, переопределяющие стандартное поведение pg_pathman:
select * from pathman_config_params;
# скопировать содержимое обратно в родительскую таблицу и удалить партиции:
select drop_partitions('table_name'::regclass, false);

Полезный скрипт для просмотра статистики по количеству и размеру партиций:
/* Статистика по количеству и размеру партиций */
SELECT 
nspname AS schemaname, relname, relkind, cast (reltuples as int),
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM 
pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE 
nspname NOT IN ('pg_catalog', 'information_schema') 
and (relname like 'history%' or relname like 'trends%') and relkind = 'r' 
-- and reltuples > 0
-- and pg_relation_size(C.oid) >= 0
ORDER BY schemaname, relname

Автонастройка удаления устаревших партиций (ахтунг - большая SQL функция)
Чтобы настроить автоудаление партиций, нужно создать функцию в БД
(широкий текст, поэтому пришлось убрать подсветку синтаксиса):

CREATE OR REPLACE FUNCTION public.delete_old_partitions(history_days integer, trends_days integer, str_days integer)
 RETURNS text
 LANGUAGE plpgsql
AS $function$
/*
Функция удаляет все партиции, старше, чем заданное количество дней:
history_days - для партиций history_x, history_uint_x
trends_days  - для партиций trends_x, trends_uint_x
str_days     - для партиций history_str_x, history_text_x, history_log_x
*/
declare clock_today_start         int;
declare clock_delete_less_history int  = 0;
declare clock_delete_less_trends  int  = 0;
declare clock_delete_less_strings int  = 0;
clock_delete_less                 int  = 0;
declare iterator                  int  = 0;
declare result_str                text = '';
declare buf_table_size            text;
declare buf_table_len             text;
declare partition_name            text;
declare clock_max                 text;
declare err_detail                text;
declare t_start                   timestamp = clock_timestamp();
declare t_end                     timestamp;
begin
    if $1 <= 0 then return 'ups, something wrong: history_days argument must be positive integer value'; end if;
    if $2 <= 0 then return 'ups, something wrong: trends_days argument must be positive integer value'; end if;
    if $3 <= 0 then return 'ups, something wrong: str_days argument must be positive integer value'; end if;
    clock_today_start = extract(epoch from date_trunc('day', now()))::int;
    clock_delete_less_history = extract(epoch from date_trunc('day', now()) - ($1::text || ' days')::interval)::int;
    clock_delete_less_trends  = extract(epoch from date_trunc('day', now()) - ($2::text || ' days')::interval)::int;
    clock_delete_less_strings = extract(epoch from date_trunc('day', now()) - ($3::text || ' days')::interval)::int;
    clock_delete_less = least(clock_delete_less_history, clock_delete_less_trends, clock_delete_less_strings);
    --raise notice 'clock_today_start % (%)', to_timestamp(clock_today_start), clock_today_start;
    --raise notice 'clock_delete_less_history % (%) % days', to_timestamp(clock_delete_less_history), clock_delete_less_history, $1;
    --raise notice 'clock_delete_less_trends  % (%) % days', to_timestamp(clock_delete_less_trends),  clock_delete_less_trends,  $2;
    --raise notice 'clock_delete_less_strings % (%) % days', to_timestamp(clock_delete_less_strings), clock_delete_less_strings, $3;
    for partition_name, clock_max in select partition, range_max from pathman_partition_list where 
    range_max::int <= greatest(clock_delete_less_history, clock_delete_less_trends, clock_delete_less_strings) and 
    (partition::text like 'history%' or partition::text like 'trends%') order by partition asc
    loop
        if (partition_name ~ 'history_uint_\d'  and clock_max::int <= clock_delete_less_history)
        or (partition_name ~ 'history_\d'       and clock_max::int <= clock_delete_less_history)
        or (partition_name ~ 'trends_\d'        and clock_max::int <= clock_delete_less_trends)
        or (partition_name ~ 'history_log_\d'   and clock_max::int <= clock_delete_less_strings)
        or (partition_name ~ 'history_str_\d'   and clock_max::int <= clock_delete_less_strings)
        or (partition_name ~ 'history_text_\d'  and clock_max::int <= clock_delete_less_strings)
        then 
            iterator = iterator + 1;
            raise notice '%', format('!!! delete %s %s', partition_name, clock_max);
            select max(reltuples::int), pg_size_pretty(sum(pg_relation_size(pg_class.oid))) as "size" from pg_class where relname like partition_name || '%' into strict buf_table_len, buf_table_size;
            if result_str != '' then result_str = result_str || ', '; end if;
            result_str = result_str || format('%s (dt < %s, len %s, %s)', partition_name, to_char(to_timestamp(clock_max::int), 'YYYY-MM-DD'), buf_table_len, buf_table_size);
            execute format('drop table if exists %s', partition_name);
        end if;
    end loop;
    if iterator = 0 then  result_str = format('there is no partitions to delete older, then %s date', to_char(to_timestamp(clock_delete_less), 'YYYY-MM-DD')); 
    else                  result_str = format('deleted %s partitions in %s seconds: ', iterator, trunc(extract(seconds from (clock_timestamp() - t_start))::numeric, 3)) || result_str;
    end if;
    --raise notice '%', result_str;
    return result_str;
exception when others then
   get stacked diagnostics err_detail = PG_EXCEPTION_CONTEXT;
   return format('ups, something wrong: %s [err code %s], %s', sqlerrm, sqlstate, err_detail);
end; 
$function$;

Для автоматического вызова функции автоочистки партиций нужно создать один элемент данных для хоста сервера zabbix типа «Монитор БД» со следующими настройками:

- тип: database monitor
- имя: delete_old_history_partitions
- ключ: db.odbc.select[delete_old_history_partitions, zabbix]
- sql выражение: select delete_old_partitions(3, 30, 30);
# здесь в качестве параметров вызова функции delete_old_partitions указывается время хранения в днях 
# для числовых значений, агрегированных числовых значений и строковых значений
- тип данных: Текст
- интервал обновления: 0
- пользовательский интервал: по расписанию в h2
- период хранения истории: 90 дней
- группа элементов данных: Database

В результате будем получать статистику по очисткам партиций примерно такого вида:

2019-09-16 02:00:00, deleted 3 partitions in 0.024 seconds: trends_78 (dt < 2019-08-17, len 1, 48 kB), history_193 (dt < 2019-09-13, len 85343, 9448 kB), history_uint_186 (dt < 2019-09-13, len 27969, 3480 kB)

Важно! После настройки автоудаления партиций через элемент данных и пользовательскую функцию нужно выключить очистку истории и трендов в планиовщике задач housekeeper Zabbix: через пункт zabbix меню выбираем «Администрирование» -> «Общее» -> справа в углу выбираем из списка «Очистка истории» -> отключить все чекбоксы у разделов «История» и «Динамика изменений».

Изменение типов индексов таблиц истории на brin (clock) и btree-gin (itemid)


Отдельное спасибо пользователю erogov за прекрасный цикл обзорных статей про индексы PostgreSQL. Да и вообще всей команде PostgresPRO. Под впечатлением этих статей мы поигрались с разными типами индексов на таблицах данных мониторинга и пришли к выводу какие типы индексов на каких полях дадут максимальный прирост производительности.

Было замечено, что на всех таблицах данных мониторинга по умолчанию создаётся составной индекс btree(itemid, clock) — он быстрый для поиска, особенно для монотонно упорядоченных значений, но сильно «пухнет» на диске, когда данных много — более 10 млн.

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

В ходе тестирования различных индексов было выявлено наиболее удачное сочетание индексов: индекс brin на поле clock и индекс btree-gin на поле itemid для всех таблиц данных мониторинга.

Индекс brin идеально подходит для монотонно возрастающих данных, таких как временная метка факта какого-либо события, т.е. для временных рядов. А индекс btree-gin — это по сути gin индекс над стандартными типами данных, что в целом намного быстрее классического индекса btree т.к. gin индекс не перестраивается в ходе добавления новых значений, а лишь дополняется ими. Индекс btree-gin ставится как расширение к PostgreSQL.

Сравнение скорости выполнения выборок для этой стратегии индексирования и для индексов в БД Zabbix по умолчанию приведён ниже. В ходе нагрузочных тестов мы накопили данные за три дня по трём партициям:
Имя партиции Количество строк в МЛН Размер в МБ
history_uint_1 81.3 4119
history_uint_2 74.9 4426
history_uint_3 100.7 5387

Для оценки результатов выполнялись три вида запросов:

  1. для одного конкретного параметра itemid данные за последний месяц, по факту три последних дня (всего 1660 записей)

    explain analyze select * from history_uint where itemid = 313300
    and clock >= extract (epoch from '2019-03-09 00:00:00'::timestamp)::int
    and clock <= extract (epoch from '2019-04-09 12:00:00'::timestamp)::int;
    
  2. для одного конкретного параметра данные за 12 часов одного дня (всего 649 записей)

    explain analyze select * from history_text where itemid = 310650
    and clock >= extract (epoch from '2019-04-09 00:00:00'::timestamp)::int
    and clock <= extract (epoch from '2019-04-09 12:00:00'::timestamp)::int;
    
  3. для одного конкретного параметра данные за один час (всего 61 запись):

    explain analyze select count(*) from history_text where itemid = 336540
    and clock >= extract (epoch from '2019-04-08 11:00:00'::timestamp)::int
    and clock <= extract (epoch from '2019-04-08 12:00:00'::timestamp)::int;
    

Результаты теста были сведены в таблицу ниже:
тип индекса размер в МБ* запрос 1** в мс запрос 2** в мс запрос 3** в мс
btree (clock, itemid) 14741 7154.3 2205.3 1860.4
brin(clock),
btree-gin (itemid)
0.42 и 1329 2958.2 1820.4 102.1
* размер в МБ указан суммарно для трёх партиций
** запрос типа 1 — данные за 3 дня, запрос типа 2 — данные за 12 часов, запрос типа 3 — данные за один час

Из сравнительной таблицы видно, что для больших таблиц данных с количеством записей более 100 млн чётко прослеживается, что изменение стандартного составного индекса btree на два индекса brin и btree-gin благотворно отразилось на уменьшении размера индексов и ускорении времени выполнения запросов.

Эффективность индексирования и партиционирования показана ниже на примере запроса добавления новых записей в таблицы history_uint и trends_uint (добавления происходят в среднем по 2000 значений за запрос).
Таблица Среднее время запроса до улучшений, мс Среднее время запроса после улучшений, мс
trends_uint 2201.48 8.72
trends_uint 1997.27 62.16

Обобщая результаты тестов различных конфигураций индексов для таблиц данных мониторинга системы zabbix можно сказать, что подобное изменение стандартного индекса для таблиц данных мониторинга системы zabbix положительно сказывается на быстродействии системы в целом, что сильнее всего ощущается при накоплении объёмов данных в размере от 10 млн. Также не стоит забывать о косвенном эффекте «разбухания» стандартного btree индекса по умолчанию — частые перестроения многогигобайтного индекса приводит к сильной загрузке жёсткого диска (метрика utilization), что в конечном итоге повышает время операций с диском и время ожидания доступа к диску со стороны CPU (метрика iowait).

Но, чтобы индекс btree-gin мог работать с типом данных bigint (in8), которым является столбец itemid, нужно выполнить регистрацию семейства операторов типа bigint для индекса btree-gin.

Регистрация семейства операторов типа bigint для индекса btree-gin
/*
Скрипт позволяет полноценно использовать индекс gin на типах данных biginteger и integer без явного приведения типов.
Проблема - в индексе gin отсутствует семейство операторов для int2, int4, int8,
поэтому при построении индекса на поле типа bigint он не используется в запросе, если указанное число меньше bigint (<= 2147483647)
Чтобы создать индекс с поддержкой нового семейства операторов intger_ops, нужно выполнить:
create index on tablename using gin(columnname int8_family_ops) with (fastupdate = false);
*/

-- не забываем поставить само расширение для btree_gin
CREATE EXTENSION btree_gin;

CREATE OPERATOR FAMILY integer_ops using gin;

CREATE OPERATOR CLASS int4_family_ops
FOR TYPE int4 USING gin FAMILY integer_ops
AS
    OPERATOR        1       <,
    OPERATOR        2       <=,
    OPERATOR        3       =,
    OPERATOR        4       >=,
    OPERATOR        5       >,
    FUNCTION        1       btint4cmp(int4,int4),
    FUNCTION        2       gin_extract_value_int4(int4, internal),
    FUNCTION        3       gin_extract_query_int4(int4, internal, int2, internal, internal),
    FUNCTION        4       gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
    FUNCTION        5       gin_compare_prefix_int4(int4,int4,int2, internal),
STORAGE         int4;

CREATE OPERATOR CLASS int8_family_ops
FOR TYPE int8 USING gin FAMILY integer_ops
AS
    OPERATOR        1       <,
    OPERATOR        2       <=,
    OPERATOR        3       =,
    OPERATOR        4       >=,
    OPERATOR        5       >,
    FUNCTION        1       btint8cmp(int8,int8),
    FUNCTION        2       gin_extract_value_int8(int8, internal),
    FUNCTION        3       gin_extract_query_int8(int8, internal, int2, internal, internal),
    FUNCTION        4       gin_btree_consistent(internal, int2, anyelement, int4, internal, internal),
    FUNCTION        5       gin_compare_prefix_int8(int8,int8,int2, internal),
STORAGE         int8;

ALTER OPERATOR FAMILY integer_ops USING gin add
  OPERATOR 1 <(int4,int8),
  OPERATOR 2 <=(int4,int8),
  OPERATOR 3 =(int4,int8),
  OPERATOR 4 >=(int4,int8),
  OPERATOR 5 >(int4,int8);

ALTER OPERATOR FAMILY integer_ops USING gin add
  OPERATOR 1 <(int8,int4),
  OPERATOR 2 <=(int8,int4),
  OPERATOR 3 =(int8,int4),
  OPERATOR 4 >=(int8,int4),
  OPERATOR 5 >(int8,int4);


Этот скрипт переразмечает все индексы в БД PostgreSQL для Zabbix с конфигурации по умолчанию на оптимальную конфигурацию, описанную выше.
/*
Переназначение индексов для таблиц первичных и агрегированных данных мониторинга
*/

-- удалим старые индексы
drop index history_1;
drop index history_uint_1;
drop index history_str_1;
drop index history_text_1;
drop index history_log_1;
-- на таблицах агрегированных данных индексы были созданы как уникальные PK 
-- (это совсем не требуется, мы только теряем время на проверку уникальности при вставке)
alter table trends drop constraint trends_pk;
alter table trends_uint drop constraint trends_uint_pk;

-- создадим новые индексы типа bree-gin на поля itemid в таблицах данных мониторинга
-- поддержка применения btree-gin для bigint описана в комментарии к посту по ссылке
-- https://habr.com/ru/company/postgrespro/blog/340978/#comment_10545932
-- предварительно ставим расширение create extension btree_gin;
create index on history      using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_uint using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_str  using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_text using gin(itemid int8_family_ops) with (fastupdate = false);
create index on history_log  using gin(itemid int8_family_ops) with (fastupdate = false);
create index on trends       using gin(itemid int8_family_ops) with (fastupdate = false);
create index on trends_uint  using gin(itemid int8_family_ops) with (fastupdate = false);

-- создадим новые индексы типа bree-gin на поля itemid в таблицах данных мониторинга
-- размер зоны для индекса brin выбран стардартный в 128 страниц, но на больших данных
-- в таблицах первичных данных мониторинга могут быть эффективны бОльшие размеры зон,
-- методика подбора описана в посте https://habr.com/ru/company/postgrespro/blog/346460/
create index on history      using brin(clock) with (pages_per_range = 128);
create index on history_uint using brin(clock) with (pages_per_range = 128);
create index on history_str  using brin(clock) with (pages_per_range = 128);
create index on history_text using brin(clock) with (pages_per_range = 128);
create index on history_log  using brin(clock) with (pages_per_range = 128);
create index on trends       using brin(clock) with (pages_per_range = 128);
create index on trends_uint  using brin(clock) with (pages_per_range = 128);


Для индекса brin для нашего объёма данных при интенсивности в 100 т. параметров в минуту (100 т. в history и 100 т. в history_uint) было замечено, что на таблицах первичных данных мониторинга индекс при размере зоны в 512 страниц работает в два раза быстрее, чем при стандартном размере в 128 страниц, но это индивидуально и зависит от размера таблиц и конфигурации сервера. В любом случае индекс brin занимает очень мало места, а вот скорость его работы может быть чуть-чуть увеличена с помощью тонкой настройки размера зоны, но при условии, что интенсивность потока данных не сильно меняется.

В качестве итога стоит отметить, что существует ограничение, связанное с архитектурой самого Zabbix: на вкладке «Последние данные» собираются по два последних значения для каждого параметра с учётом фильтрации. По каждому параметру значения запрашиваются в БД отдельно. Поэтому чем больше таких параметров будет отобрано, тем дольше будет выполняться запрос. Наиболее быстро последние данные ищутся, когда на таблицах history установлен индекс btree(itemid, clock desc) именно с обратной сортировкой по времени, но при этом сам индекс конечно «пухнет» на диске и в целом косвенно замедляет работу с БД, что вызывает проблему, описанную выше.

Поэтому есть три выхода из положения:

  1. выполнить описанные выше манипуляции с индексами и стараться не отбирать во вкладке «Последние данные» значения одновременно для более чем 100 параметров (т.е. смириться с тем, что данные на вкладке «Последние данные» будут появляться немного медленнее)
  2. переделать механизм внутри сервера Zabbix так, чтобы последние два значения для всех параметров писались по триггеру ещё в отдельную таблицу с двумя последними значениями, откуда и возвращались бы на запросы для отрисовки вкладки «Последние данные»
  3. оставить индексы так, как они есть по умолчанию, и ограничиться только партиционированием чтобы получать довольно большие выборки на вкладке «Последние данные» одновременно по множеству параметров (однако было замечено, что у web-сервера Zabbix всё равно есть ограничение на количество одновременно отображаемых значений параметров на вкладке «Последние данные» — так, при попытке отобразить 5000 значений БД вычислила результат, но сервер не смог подготовить web-страницу и отобразить такой большой объём данных).

Сбор и анализ статистики выполнения запросов pg_stat_statements


Pg_stat_statements — расширение для сбора статистики выполнения запросов в рамках всего сервера. Преимущество данного расширения в том, что ему не требуется собирать и парсить логи PostgreSQL.

Использование расширения pg_stat_statements
Установка расширения через psql:

CREATE EXTENSION pg_stat_statements;

Настройки для расширения в конфигурационном файле postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000 # максимальное количество sql запросов, которое будет храниться расширением (удаляются записи с наименьшим количеством вызовов);
pg_stat_statements.track = all # all - все вызовы (в том числе в функциях), top - только вызовы клиент/сервер, none - отключить сбор
pg_stat_statements.save = true # сохранять статистику между перезапусками СУБД

Обнулить статистику:

SELECT pg_stat_statements_reset();

Полезный запрос для просмотра среднего времени выполнения операций на таблицах:
select substring(query from '[^(]*') as query_sub, 
sum(calls) as calls, avg(mean_time) as mean_time
from pg_stat_statements
where
query ~ 'insert into' or query ~ 'update trends'
group by substring(query from '[^(]*')
order by calls desc

Настройка параметров мониторинга физических дисков


Для мониторинга жёстких дисков в Zabbix из коробки предусмотрены только параметры vfs.dev.read и vfs.dev.write. Эти параметры не дают информации о загруженности дисков. Полезными критериями для поиска проблем с производительностью жёстких дисков являются показатели коэффициента загруженности utilization, время выполнения запроса await и загруженность очереди запросов к диску.

Как правило высокая загруженность диска коррелирует с высоким iowait самого cpu и с ростом времени выполнения sql запросов, что и было установлено при нагрузочном тестировании zabbix сервера со стандартной конфигурацией без партиционирования и без настройки альтернативных индексов. Добавить эти параметры мониторинга жёстких дисков можно с помощью следующих действий, которые были подсмотрены в статье у товарища lesovsky и улучшены: теперь параметры iostat собираются отдельно по каждому диску в json временный параметр, откуда по настройках постобработки уже раскладываются в конечные параметры мониторинга.

Пока Pull request ожидает на рассмотрении, вы можете попробовать развернуть мониторинг параметров дисков по подробной инструкции через мой fork.

После всех описанных действий можно добавить на главную панель мониторинга сервера Zabbix кастомный график с iowait cpu и параметрами utiliztion для системного диска и диска с БД (если они разные). Результат может выглядеть так (sda — основной диск, sdc — диск с БД):



Аппаратное улучшение производительности


После настройки СУБД, индексирования и партиционирования можно приступить к вертикальному масштабированию — улучшить аппаратные характеристики сервера: добавить оперативной памяти, поменять накопители на твёрдотельные и добавить процессорных ядер. Это гарантированный прирост производительности, но лучше это сделать только после программной оптимизации.

Создание распределённого кластера


После умеренного вертикального масштабирования нужно приступать к горизонтальному — создавать распределённый кластер: делать либо шардирование, либо репликации мастер-слейв. Но это уже отдельная тема и материал отдельной статьи (как слепить кластер из говна и палок), как и сравнение вышеописанного методики оптимизации БД Zabbix с использованием pg_pathman и индексирования с методикой применения расширения TimescaleDB.

А пока остаётся надеяться, что материал данной статьи оказался полезным и познавательным!

Комментарии (15)


  1. denaspireone
    24.09.2019 09:03

    протестируйте еще и на стенде с glaber.io но только 1 сервер используйте и без проксей, и без жестких оптимизаций субд


    1. viking_unet Автор
      24.09.2019 14:02

      Спасибо за отсылку к Glaber, обязательно попробуем! ClickHouse конечно обеспечит высокую производительность, давно шли разговоры про поддержку ClickHouse для Zabbix. В недалёком будущем может и сам Zabbix станет поддерживать ClickHouse. А может в 12 версии PostgreSQL появится поддержка внешних подключаемых источников данных, в числе которых тоже окажется ClickHouse. Поживём — увидим) Сейчас учёка в Glaber хранится тоже в ClickHouse? Нет ли сильных расхождений по документации с Zabbix?


      1. denaspireone
        24.09.2019 15:21

        http://t.me/glaber_group
        задайте вопрос в нашей группе и тегните http://t.me/makurov


  1. MagicGTS
    24.09.2019 13:05

    После подключения www.timescale.com стало резко лучше, партицирование стало не так сильно и надо


    1. viking_unet Автор
      24.09.2019 14:05

      Конечно так и должно быть, просто сам Zabbix заявляет, что они ещё не в полной мере подружили TimescaleDB со внутренним механизмом формирования SQL запросов сервера Zabbix, обещают в версии 4.4 окончательно задействовать весь потенциал tsdb. В tsdb и так уже есть партиционирование — деление гипертаблиц на чанки. Обязательно разверну тот же самый мониторинг, но с tsdb и сравню эти подходы. На момент написания этой статьи была только версия Zabbix 4.0 без поддержки tsdb — дело было весной, просто только сейчас дошли руки систематизировать материал и поделиться с общественностью. Ведь в основном статья про способы оптимизации БД PostgreSQL — то, что может пригодиться большинству новичков и даже некоторым бывалым DBA.


  1. orynkanov
    24.09.2019 13:18

    А как обновляться? Этот момент меня останавливает — zabbix сервер при запуске сам изменяет базу и сможет ли провести обновление?.. Посоветуйте алгоритм действий.


    1. viking_unet Автор
      24.09.2019 13:30

      Если вы имеете ввиду обновление чисто БД без обновления версии самого Zabbix, то алгоритм примерно такой:


      1. удаляете старые индексы на таблицах данных мониторинга и создаёте новые индексы нужного типа, как описано в статье (можно не весь скрипт разом, а по одной таблице — посмотрите как пойдёт процесс по скорости, постройте пару выборок до и после чтобы сравнить)
      2. настраиваете партиционирование так же по одной таблице, в статье описаны нужные функции
      3. отключаете удаление истории в админке через hausekeeper
      4. регистрируете хранимую функцию автоудаления устаревших партиций delete_old_partitions и регистрируете специальный элемент данных для хоста zabbix сервера типа "Мониторинг БД" с параметрами, как описаны в статье.

      Сам сервер Zabbix при этом не должен ничего почувствовать — все изменения происходят только в БД и для него работа с партиционированными таблицами будет происходить как раньше, т.е. с теми же именами таблиц родителей (history, hostory_uint, trends, trends_uint и т.д.). Но нужно учитывать вашу нагрузку по данным мониторинга, если у вас она экстримальная, то возможно стоит делать обновление более "нежным" и в другом порядке: удалить индексы, выполнить партиционирование, добавить новые индексы (только при этом новые индексы придётся создавать руками для каждой партиции — поэтому я бы всё таки сначала поменял индексы, а уже потом выполнил партиционирование). В любом случае лучше делать это в часы наименьшей нагрузки, например, в 2 часа звёздной субботней ночью :D


      1. orynkanov
        24.09.2019 13:35

        Спасибо за подробный ответ! Но интересует как раз таки обновление Zabbix-server — например с 4.2 на 4.4.


        1. viking_unet Автор
          24.09.2019 13:57

          Тогда это больше вопрос по самому Zabbix — в документации есть общая инструкция для обновления компонентов между версиями:
          https://www.zabbix.com/documentation/devel/ru/manual/installation/upgrade/sources
          Версия 4.4 ещё в разработке) Я бы посоветовал вам сравнить файлы разметки БД для версии с которой собираетесь обновляться и версию, на которую собираетесь переходить. Внимательно изучите разделы "Что нового" между этими версиями. В версиях 4.х БД PostgreSQL вряд ли меняется — единственное нововведение, это появление расширения TimescaleDB, но и его установка вообще дело добровольное.


  1. SAAE
    24.09.2019 15:47

    Еще копеечку внесу:
    Вы вынелси каталог БД на отдельный диск. Далее можно и wal-файлы складывать на отдельный диск. Даже обычного SATA должно хватить на много т.к. производится последовательная запись.


  1. EvgeniyNuAfanasievich
    25.09.2019 09:44

    Здравствуйте! Можете примерно ввести в курс дела понимания термина экстримальные нагрузки. К примеру у нас вертетится zabbix 4.2 на bl460 схд vnx5300 кажется на виртуалки vmware 2ядра 8gb. Сколько для него узлов сети (snmp) и агентских (winserver) будут критической нагрузкой. Сейчас просто подключено далеко не всё, процентов(!) 10 всех желаемых хостов железяк на мониторинг. Передэодически вылазит уже предупреждение овер75% CPU in use.
    P.s. ставил готовый appliance там вроде MySQL.
    Не очень разбираюсь во всех этих кишках БД, уж извините.


  1. viking_unet Автор
    25.09.2019 10:07

    Экстремальные нагрузки по моему ИМХО — от 10к параметров в секунду. Наш тест держит 3333 в секунду и это для него не предел. У вас виртуалка — сразу потеря мощности, видимо развернули образ с Ubuntu с сайта Zabbix, он только «чтобы посмотреть». Разверните нормальный CentOS или Debian, сразу разница будет. Как опрашиваете хосты — активный опрос тоже минус к скорости. Хороший вариант — почти все параметры хостов пассивные, приходят от активного прокси, прокси уже активно спрашивает параметры хостов (если есть активные агенты — ещё лучше, тем более если NAT). Сейчас для хорошей производительности Zabbix нужно либо настраивать PostgreSQL + tsdb, либо разворачивать glaber.io с ClickHouse, как написал товарищ denaspireone в первом комментарии, который кстати является разработчиком) Мы с Zabbix разобрались не за один день, причём до сих пор поверхностно, так что дерзайте! Стратегии разворачивания, успешные кейсы и практики — известны и описаны в сети, моя статья больше про PostgreSQL, на самом деле :)


    1. EvgeniyNuAfanasievich
      25.09.2019 11:13

      А где параметры запросов в секунду увидеть?
      За идею с проксями спс. Natа нет.
      В очередях всегда по нулями.
      Насчёт того, что centos прям полетит по сравнению с Ubuntu server терзают сомнения.


      1. viking_unet Автор
        25.09.2019 11:23

        На главном dashboard (панели) Zabbix server health график Values processed per second (снизу слева).


        Насчёт того, что centos прям полетит по сравнению с Ubuntu server терзают сомнения.

        здесь я имел ввиду только то, что не стоит разворачивать сервер под виртуалкой.


        1. EvgeniyNuAfanasievich
          25.09.2019 12:02

          спс!!! нашёл колеблется около 100-120 :-)
          Насчёт виртуалки теперь уже понял. Но делая всё это я не осознавал, как оно что грузит и не видел никаких best_practice на сайте заббикса или где либо.