Улучшение производительности Zabbix + PostgreSQL при помощи партиционирования и индексирования

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)

# 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, которые будут переработаны для будущего использования

# управление параллельными обработчиками запросов
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             # отключение ускоряет обычные операции, но может привести к повреждению или незаметной порче данных после сбоя системы

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

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

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

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

# общий список партиционированных таблиц, основное хранилище конфигурации:
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

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

- тип: 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)

/*
Скрипт позволяет полноценно использовать индекс 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);

/*
Переназначение индексов для таблиц первичных и агрегированных данных мониторинга
*/

-- удалим старые индексы
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);

CREATE EXTENSION pg_stat_statements;

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