Postgresql

В прошлом году популярный сервис мониторинга Zabbix представил Agent 2, призванный сократить число TCP-подключений и обеспечить удобную расширяемость за счёт плагинов на Golang.

Меня зовут Даша, и я один из разработчиков плагина мониторинга PostgreSQL для Zabbix Agent 2. В этой статье я расскажу об основных фишках использования Zabbix Agent 2 для мониторинга PostgreSQL, о принципе работы плагина, дам советы по его настройке, а также объясню на примере, как кастомизировать плагин.

Как появился плагин мониторинга PostgreSQL для Zabbix Agent 2?

В 2019 году Zabbix анонсировал выпуск нового Zabbix Agent 2. Он написан с нуля на Golang. Для мониторинга каждого приложения требуется отдельный плагин. Мы в Postgres Professional решили, что это отличная возможность применить наш многолетний опыт использования Zabbix для мониторинга PostgeSQL, и написали модуль мониторинга для Agent 2.

Как устроен мониторинг СУБД в Zabbix?

Начнём с небольшого введения в схему работы мониторинга Zabbix для новичков.

Интересную нам сейчас структуру можно разбить на две составляющие:

1. Zabbix Server, который хранит и собирает данные.

2. Агенты, которые устанавливаются на наблюдаемых объектах и собирают данные.

Для мониторинга каждого приложения в Zabbix Server требуется шаблон - XML-файл. В нём указаны ключи метрик (уникальные ID) и параметры их обработки.

Zabbix Agent 2 призван дать пользователю инструмент мониторинга из коробки, быстро и легко настраиваемый, а также с хорошей расширяемостью.

Как же работает PostgreSQL плагин для Zabbix Agent 2?

Есть основная функция, в которой по уникальному ключу вызываются обработчики для каждой метрики. Обработчик (handler) служит для сбора данных. Это файл, в котором указывается и выполняется SQL-запрос для получения одной или нескольких метрик. Результаты выполнения запроса записываются в переменную, которая относится к типу int, float или string. Если результат должен содержать значения сразу нескольких метрик, то он будет преобразован в JSON ещё на стадии получения запроса. Полученные результаты Zabbix Agent 2 периодически отдаёт Zabbix Server.

Плагин и обработчики находятся вот в этой папке: /plugins/postgres

Какими возможностями обладает модуль мониторинга PostgreSQL для Zabbix Agent 2?

• Поддержка постоянного подключения к PostgreSQL.

• Мониторинг нескольких экземпляров (instances) PostgreSQL одновременно.

• Опции контроля и проверки метрик в реальном времени через командную строку.

• Конфигурирование плагина через общий файл конфигурации агента.

• Сохранение состояния между проверками.

• Довольно простая кастомизация сбора существующих метрик.

• Возможность писать новые плагины под свои требования.

К плагину есть официальный шаблон, который можно скачать по ссылке.

В нем есть базовые триггеры и комплексный экран, на котором отображается комбинация нескольких графиков. Всего плагин собирает более 95 метрик. Полный список всех метрик также можно найти по ссылке выше.

В веб-интерфейсе Zabbix Server можно редактировать шаблон и его составляющие для своих нужд. Что именно можно настроить?

1. Изменить интервал сбора метрики.

2. Добавить триггер для метрики.

3. Добавить макрос или отредактировать существующий.

Как установить и использовать PostgreSQL-плагин для Zabbix Agent 2?

1. Создаем пользователя PostgreSQL для мониторинга:

CREATE USER 'zbx_monitor' IDENTIFIED BY '<password>';GRANT EXECUTE ON FUNCTION pg_catalog.pg_ls_dir(text) TO zbx_monitor;GRANT EXECUTE ON FUNCTION pg_catalog.pg_stat_file(text) TO zbx_monitor;

2. Редактируем pg_hba.conf, чтобы разрешить подключение от Zabbix Agent 2:

# TYPE DATABASE USER ADDRESS METHOD
host all zbx_monitor 127.0.0.1 md5

Больше информации о pg_hba.conf по ссылке.

Теперь остаётся задать параметры подключения к PostgreSQL для Zabbix Agent 2. Это можно сделать двумя способами:

• использовать макросы для параметров подключения,

• создать сессию.

Первый способ немного проще. Его достаточно, если нужно настроить мониторинг одного экземпляра PostgreSQL:

1. В шаблоне редактируем макрос {$PG.URI} , в котором указывается путь к PostgreSQL в формате <protocol(host:port)>.

2. Задаем макрос с именем пользователя и паролем ({$PG.USER} and {$PG.PASSWORD}). Так же можно указать макрос {$PG.DBNAME}. Этот параметр опционален для большинства метрик - если он не задан в ключе, то будет использовано имя базы, указанное в конфигурационном файле агента.

В шаблоне эти макросы уже указаны в параметрах всех ключей. Обратите внимание, что порядок указания параметров в ключе фиксирован.

Второй способ позволяет задавать параметры подключения к нескольким экземплярам PostgreSQL:

1. Задаём параметры подключения для сессии в конфигурационном файле zabbix_agent2.conf в секции плагина Postgres: Postgres.Sessions.<Session_name>.URI,Postgres.Sessions.<Session_name>.User,Postgres.Sessions.<Session_name>.Password. Здесь вместо <Session_name> нужно указать уникальное имя новой сессии.

   

2. Создаём макрос с именем сессии в шаблоне {$PG.<Session_name>}.

3. Указываем макрос как единственный параметр для метрик в шаблоне.

Рассмотрим, как использовать плагин для сбора дополнительных метрик на примере добавления метрики uptime.

Для этого надо создать новый обработчик с запросом и добавить его ключ в основную функцию.

1. Создаем файл для получения новой метрики:

zabbix/src/go/plugins/postgres/handler_uptime.go

Подключаем пакет postgres и указываем ключ (ключи) метрик:

package postgresconst (keyPostgresUptime = "pgsql.uptime")

2. Объявляем обработчик (handler) c запросом, а так же переменную uptime, куда будет записан результат:

func uptimeHandler(ctx context.Context, conn PostgresClient,                    _ string, _ map[string]string, _ ...string) (interface{}, error){var uptime float64query := `SELECT date_part('epoch', now() - pg_postmaster_start_time());

3.Выполняем запрос, проверяем, возникла ли ошибка. Если все ОК, возвращаем переменную uptime с результатом.

row, err := conn.QueryRow(ctx, query)if err != nil {...}err = row.Scan(&uptime)if err != nil {...}return uptime, nil

4. Регистрируем ключ новой метрики:

var metrics = metric.MetricSet{....,keyPostgresUptime: metric.New("Returns uptime.",[]*metric.Param{paramURI, paramUsername, paramPassword,paramDatabase}, false),}

Собираем агент!

Новый функционал

В версии Zabbix 5.2 появилась возможность вычислять метрики, собирая результаты пользовательских запросо из отдельных SQL-файлов. При этом можно создавать даже динамические запросы. В этой версии также обновлена архитектура модуля и исправлены мелкие ошибки.Продемонстрируем, как добавлять кастомные метрики через SQL - файл на примере простого запроса с одним параметром:

1. Создадим SQL-файл с запросом.

   $touch custom1.sql$echo SELECT id FROM my_table WHERE id=$1; > custom1.sql
   

   Тут в $1 будет передан параметр при выполнении запроса.

2. В zabbix_agent2.conf заполним параметр Plugins.Postgres.CustomQueriesPath, указав путь к директории с SQL-файлом.

   Plugins.Postgres.CustomQueriesPath=/path/to/the/file

3. В шаблоне для ключа pgsql.query.custom укажем имя SQL-файла и добавим дополнительные параметры для запроса, т.е. тот, который заменит $1. Стоит отметить, что для названия SQL - файла и для параметров можно также создавать макросы в шаблоне.

Дополнительные материалы

Презентации Zabbix Online Meetup, который проходил 19 июня

Статья Вадима Ипатова - одного из разработчиков Zabbix Agent 2

Шаблон для плагина мониторинга PostgreSQL

Zabbix Git для тех, кто хочет видеть больше реальных примеров и посмотреть на все SQL-запросы для получения метрик

Видео доклада на PGConf.Online 2021 "Обзор новой функциональности и настройка Zabbix Agent 2 для мониторинга PostgreSQL"

Остались вопросы?

Все вопросы можно задавать в комментариях.

О чем эта статья и кому адресована?

С SQL работают почти все, но даже опытные разработчики иногда не могут ответить на простой вопрос. Каким образом СУБД выполняет самый обычный INNER JOIN?

С другой стороны - разработчики на C# или других ООП языках часто воспринимают СУБД как всего лишь хранилище. И размещать какие-то бизнес-правила в SQL - плохо. В противовес им создаются библиотеки вродеLinq2Db(не путаем сLinq2Sql- совершенно разные авторы и разные библиотеки). При ее использовании весь код пишется на C# и вы получаете все преимущества типизированного языка. Но это формальность. Затем этот код транслируется на SQL и выполняется на стороне СУБД.

Для того чтобы лучше разобраться как работает одинаковый код на SQL и на C# мы попробуем реализовать одно и то же на первом и на втором, а затем разберем как это работает. Если вы хорошо знаете что такоеNested Loop,Merge Join,Hash Join- вам скорее всего имеет смысл прочитать статью по диагонали. А вот если не знаете - статья для вас должна быть полезной.

Работа с несколькими коллекциями

Предположим, что у нас есть некоторый сервисный центр по техническому обслуживанию автомобилей - станция технического обслуживания (СТО). Есть две сущности:Person- клиенты сервисного центра иVisit- конкретное посещение данного центра.Personкроме идентификатора содержит имя, фамилию и статус активности (например, если клиент поменял машину на другую марку - он переводится в статус не активного и уже не будет в ближайшем времени посещать нас).Visitкроме идентификатора содержит в себе ссылку на клиента, дату визита и сумму, которую заплатил клиент за этот визит. Все вышеперечисленное можно было бы оформить с помощью следующих классов на C# для самого простейшего случая:

internal sealed class Person{    internal int Id { get; set; }    internal string FirstName { get; set; }    internal string LastName { get; set; }    internal bool IsActive { get; set; }}internal sealed class Visit{    internal int Id { get; set; }    internal int PersonId { get; set; }    internal DateTime Date { get; set; }    internal decimal Spent { get; set; }}// ...internal Person[] persons = new Person[];internal Visit[] visits = new Visit[];// ...

В базе данных (в дальнейшем мы будем использоватьPostgreSQL) для двух этих сущностей есть две таблицы с аналогичными полями:

create table public.visit(    id integer,    person_id integer,    visit_datetime timestamp without time zone,    spent money) tablespace pg_default;create table public.person(    id integer,    first_name character varying(100) COLLATE pg_catalog."default",    last_name character varying(100) COLLATE pg_catalog."default",    is_active boolean) tablespace pg_default;

Исходный код для данной статьи находитсяздесь. Если у вас есть какие-то замечания - можете сразу править.

Пусть наша задача сводится к написанию простейшего бизнес-правила -найти общую сумму выручки за 2020 год и ранее, которую принесли клиенты, находящиеся сейчас в активном статусе. Как можно реализовать решение такой простой задачи?

Nested Loop

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

public decimal NestedLoop(){    decimal result = 0;    var upperLimit = new DateTime(2020, 12, 31);    foreach (var person in persons)    {        if (person.IsActive == false)        {            continue;        }                foreach (var visit in visits)        {            if (person.Id == visit.PersonId && visit.Date <= upperLimit)            {                result += visit.Spent;            }        }    }    return result;}

Эта идея анимирована ниже:

Алгоритм очень простой, не потребляет дополнительной памяти. Но затратность егоO(N), что будет сказываться на большом числе элементов - чем их больше, тем больше телодвижений необходимо совершить.

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

select setseed(0.777);delete from public.person;insert into public.person(id, first_name, last_name, is_active)select row_number() over () as id,substr(md5(random()::text), 1, 10) as first_name,substr(md5(random()::text), 1, 10) as last_name,((row_number() over ()) % 5 = 0) as is_activefrom generate_series(1, 5000);/*<-- 5000 это число клиентов*/delete from public.visit;insert into public.visit(id, person_id, visit_datetime, spent)select row_number() over () as id,(random()*5000)::integer as person_id, /*<-- 5000 это число клиентов*/DATE '2020-01-01' + (random() * 500)::integer as visit_datetime,(random()*10000)::integer as spentfrom generate_series(1, 10000); /* 10000 - это общее число визитов в СТО*/

В данном случае число клиентов CTOPравно 5000, число их визитовV- 10000. Дата визита, а также сам факт визита для клиента генерируются случайным образом из указанных диапазонов. Признак активности клиента выставляется для каждого пятого. В итоге мы получаем некоторый тестовый набор данных, приближенный к реальному. Для тестового набора нам интересна характеристика - число клиентов и посещений. Или(P,V)равное в нашем случае(5000, 10000). Для этого тестового набора мы сделаем следующее: выгрузим его в обьекты C# и с помощью цикла в цикле (Nested Loop) посчитаем суммарные траты наших посетителей. Как это определено в постановке задачи. На моем компьютере получаем приблизительно20.040 миллисекунд, затраченное на подсчет. При этом время получение данных из БД составило все те же самые20.27 миллисекунд. Что в сумме дает около40 миллисекунд. Посмотрим на время выполнения SQL запроса на тех же данных.

select sum(v.spent) from public.visit v                    join public.person p on p.id = v.person_idwhere v.visit_datetime <= '2020-12-31' and p.is_active = True

Все на том же компьютере получилось порядка2.1 миллисекундына все. И кода заметно меньше. Т.е. в 10 раз быстрее самого метода, не считая логики по извлечению данных из БД и их материализации на стороне приложения.

Merge Join

Разница в скорости работы в 20 раз наталкивает на размышления. Скорее всего Nested Loop не очень нам подходити мы должны найти что-то получше. И есть такой алгоритм НазываетсяMerge JoinилиSort-Merge Join. Общая суть в том, что мы сортируем два списка по ключу на основе которого происходит соединение. И делаем проход всего в один цикл. Инкрементируем индекс и если значения в двух списках совпали - добавляем их в результат. Если в левом списке идентификатор больше, чем в правом - увеличиваем индекс массива только для правой части. Если, наоборот, в левом списке идентификатор меньше, то увеличиваем индекс левого массива. Затратность такого алгоритмаO(N*log(N)).

Результат работы такой реализации радует глаз -1.4 миллисекундыв C#. Правда данные из базы данных еще нужно извлечь. А это все те же самые дополнительные20 миллисекунд. Но если вы извлекаете данные из БД, а затем выполняете несколько обработок, то недостаток постепенно нивелируется. Но можно ли подсчитать заданную сумму еще быстрее? Можно!Hash Joinпоможет нам в этом.

Hash Join

Этот алгоритм подходит для больших массивов данных. Его идея проста. Для каждого из списков считается хэш ключа, далее этот хэш используется для того, чтобы выполнить сам Join. Детально можно посмотреть в видео:

Видео работы Hash Join (на англ. языке)

Затратность алгоритмаO(N). В .NET стандартный Linq метод как раз его и реализует. В реляционных СУБД часто используются модификации этого алгоритма (Grace hash join,Hybrid hash join) - суть которых сводится к работе в условиях ограниченной оперативной памяти. Замер скорости работы в C# показывает, что этот алгоритм еще быстрее и выполняется за0.9 миллисекунды.

Динамический выбор алгоритма

Отлично! Похоже мы нашли универсальный алгоритм, который самый быстрый. Нужно просто использовать его всегда и не беспокоиться более об этом вопросе. Но если мы учтем еще и расход памяти все станет немного сложнее. Для Nested Loop - память не нужна, Merge Join - нужна только для сортировки (если она будет). Для Hash Join - нужна оперативная память.

Оказывается расход памяти - это еще не все. В зависимости от общего числа элементов в массивах скорость работы разных алгоритмов ведет себя по-разному. Проверим для меньшего числа элементов (P, V) равному (50, 100). И ситуация переворачивается на диаметрально противоположную:Nested Loopсамый быстрый -2.202 микросекунды, Merge Join -4.715 микросекунды, Hash Join -7.638 микросекунды. Зависимость скорости работы каждого алгоритма можно представить таким графиком:

Для нашего примера можно провести серию экспериментов на C# и получить следующую таблицу:

А что если узнать значения X1 и X2 и динамически выбирать алгоритм в зависимости от его значения для данных коллекций? К сожалению не все так просто. Наша текущая реализация исходит из статичности коллекции. Что нужно сделать, чтобы вставить еще один визит за 2020 год? В массив в коде на C#. В массив фиксированного размера он, очевидно, не поместится. Нужно выделять новый массив размером на один элемент больше. Скопировать туда все данные, вставлять новый элемент. Понятно, что это дорого. Как насчет того, чтобы заменить Array на List? Уже лучше, т.к. он предоставляет все необходимое API. Как минимум удобно, но если посмотреть на его реализацию - под капотом используется все тот же массив. Только резервируется памяти больше чем надо С запасом. Для нас это означает лишние траты памяти. LinkedList? Здесь должно быть все нормально. Давайте поменяем коллекцию и посмотрим что из этого получится.

Время выполнения не только изменилось. Сама кривая стала более крутой и с числом элементов время растет:

Таким образом мы приходим к понимаю, что время доступа к каждому конкретному элементу коллекции крайне важно. Одно из главных преимуществ реляционных СУБД в том, что они всегда готовы к добавлению новых данных в любой диапазон. При этом это добавление произойдет максимально эффективным образом - не будет релокации всего диапазона данных или т.п. Кроме того данные СУБД часто хранится в одном файле - таблицы и их данные. Если утрировать, то здесь также используется связанный список. В случае с PostgreSQL данные представлены в страницах (page), внутри страницы располагаются кортежи данных (tuples). В общих чертах вы можете себе это увидеть на картинках ниже. А если захотите узнать больше деталей, то ниже также есть и ссылка.

Более детально описано в первоисточникеЗдесь

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

Более детально описано в первоисточникеЗдесь.

В оперативную память попадают страницы, а попадают они вbuffer poolчерезbuffer manager. Все это сказывается на стоимости доступа к каждому конкретному значению таблицы. Вне зависимости от того что используетсяNested Loop,Merge JoinилиHash Join. Другой вопрос, что в зависимости от алгоритма число обращений может отличаться в разы. Поэтому реляционные СУБД подходят динамически к выбору алгоритма в каждом конкретном запросе и строят план запроса (Query Plan).

Сравним для большого числа элементов насколько будет отличаться время обработки с одним и тем же алгоритмом в БД и на C#. (P, V) будет равно (50000, 100000). В коде на C# загрузка данных из БД занимает145.13 миллисекунд. Дополнительно к этому выполнение самой логики сNested Loopна основе обычного массива -305.38 миллисекунд,Hash Join-36.59 миллисекунд. Для того чтобы проверить в СУБД такую же реализацию мы будем использовать такой скрипт:

set enable_hashjoin to 'off';--Заставляем БД использовать Nested Loopset enable_mergejoin to 'off';set enable_material to 'off';select sum(v.spent) from public.visit vjoin public.person p on p.id = v.person_idwhere v.visit_datetime <= '2020-12-31' and p.is_active = True

На аналогичных данных в БД сNested Loopзапрос выполнится за11247.022 миллисекунд. Что может говорить о сильно большем времени доступа к каждому конкретному элементу:

Но СУБД приходится заставлять работать так, чтобы она использовалаNested Loop. Изменим наш скрипт таким образом:

set enable_hashjoin to 'on';set enable_mergejoin to 'on';set enable_material to 'on';select sum(v.spent) from public.visit vjoin public.person p on p.id = v.person_idwhere v.visit_datetime <= '2020-12-31' and p.is_active = True

По-умолчанию для такого объема данных будет, конечно выбранHash Join:

И мы видим, что время выполнение составило25.806 миллисекунды, что сопоставимо по скорости с реализацией на C# и даже немного быстрее.

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

Выводы

На примере простейшей задачи мы в общих чертах разобрали как работает типичная реляционная СУБД при реализации JOIN. Сравнивать коллекции C# и SQL не очень корректно, за внешней схожестью скрывается серьезное различие в предназначении. Реляционная СУБД призвана обеспечитьконкурентный доступ к данным максимально эффективным способом(при этом подразумевается, что сами данные могут постоянно модифицироваться). Кроме того, данные могут не помещаться в оперативную память и частично храниться на диске.

Более того, СУБД обязана обеспечить сохранность данных на постоянном носителе - одно из основных ее предназначений. При этом на получение данных СУБД динамически выбирает алгоритм, наиболее эффективный в данном случае. В C# аналагичных библиотек или реализаций просто нет И это показательно, т.к. лишь свидетельствует об отсутствии такой необходимости. Linq метод Join реализуетHash Join, который потенциально тратит больше оперативной памяти, но это просто не берется в расчет. Т.к. мало кого интересует применительно к решаемым задачам.

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

Описание проблемы

Не раз наша команда в Каруне сталкивались с задачей, связанной с хранением и использованием IP-адресов в базе данных. Предположим, что есть типичная задача: необходимо парсить огромное количество диапазонов адресов (~300k) сизвестного ресурса, а далее определять страну по IP-адресу клиента. Кажется, ничего особенного. Это довольно просто решается любым ниже описанным способом при малых нагрузках. Но если у нас тысячи пользователей, или наш сервис является прокси перед всеми остальными? В этом случае не хочется быть бутылочным горлышком и приходится бороться за каждую долю секунды.

Немного про адресацию

Существует 2 типа адресации в сети

INET (Классовая адресация IP-сетей) архитектура сетевой адресации, которая использовалась в Интернете в период с 1981 по 1993 годы. Была вытеснена бесклассовой адресацией ввиду плохой гибкости и неэкономичного использования адресного пространства.

CIDR (Classless Inter-Domain Routing,Бесклассовая адресация) современный метод IP-адресации, при которой количество адресов в сети определяется маской подсети.

Диапазон адресов записывается в видеaddress/y, гдеy число бит маски подсети. Например, /28 означает, что 28 разряда IP-адреса отводятся под номер сети, а остальные 4 разряда полного адреса под адреса хостов этой сети, адрес этой сети и широковещательный адрес сети.

Например, запись192.168.5.0/24означает диапазон адресов от192.168.5.1до192.168.5.254, а также192.168.5.0 адрес сети и192.168.5.255 широковещательный адрес сети.

Типы inet и cidr по умолчанию

PostgreSQL предоставляет 2 типа по умолчанию для хранения IP-адресов и диапазонов:inetиcidr. Существует путаница между официальными названиями классовой и бесклассовой адресации и типамиinet/cidr.

Типinetсодержит адрес узла, а также может содержать подсеть. Вводимое значение должно иметь форматaddress/y. Если компонентyотсутствует, то маска сети считается равной 32 (для IPv4), так что это значение будет представлять один узел.

Типcidrсодержит определение сети IPv4 (или IPv6). Вводимое значение также имеет форматaddress/y. Но еслиyкомпонент отсутствует, то сеть вычисляется по старой классовой схеме нумерации сетей (INET).

Существенным отличием этих двух типов является в том, чтоinetпринимает значения с ненулевыми битами справа от маски сети, аcidrнет. Если у вас сетевая маска /8, то типcidrтребует, чтобы все 24 крайних правых бита были равны нулю,inetне имеет этого требования. Например,255.0.0.2/8будет ошибочным дляcidrт.к. справа от маски255.0.0.0имеются ненулевые значения (цифра 2 в последнем разряде адреса).255.128.128.7/24, 255.255.255.255/31 тоже ошибочны, а вот для типаinetявляются валидными.

А может уже померим что-нибудь?

Выполним несколько предварительных настроек на локальной машине (MacBook 16, 2019 2,6 GHz 6-Core Intel Core i7). Создадим таблицу и добавим индекс для поля с IP-адресом:

CREATE INDEX ON ip_ranges USING GIST (ip_range inet_ops);

Попробуем выполнить большое количество запросов (1.000.000) определения вхождения в диапазон IP-адреса клиента с помощью цикла:

DO$$DECLARE  i RECORD;BEGIN FOR i IN 1..1000000 LOOP  PERFORM country_id FROM ip_ranges WHERE ip_range >>= {random_ip}; end loop;END;$$;

и посчитаем среднее время определения адреса.

Волшебный ip4r

Существует способ и более быстродействующий использование расширенияip4r, позволяющее значительно сократить время определения страны пользователя.

Расширение гарантирует, что умеет в индексы лучше, чем встроенные типы PostgreSQL. И указывает на низкую производительность дефолтных типов даже в новых версиях СУБД. Кроме того, говорит о перегруженности дефолтных типов.

После тех же самых проверок получили значительное снижение времени определения страны пользователя до38 мкс.

Серебряная пуля (или нет?)

Если вдруг вы используетеnginx, то для него естьgeo модуль, позволяющий определять по IP-адресу нужный параметр. Создадим сервис черезdocker-compose.yml:

version: '3.7'services:  web:    image: nginx:latest    volumes:      - ./nginx.conf:/etc/nginx/nginx.conf      - ./GeoIP.dat:/var/geo/GeoIP.dat      - ./geo.conf:/var/geo/geo.conf    ports:      - "8080:80"    environment:      - NGINX_PORT=80

Конфигnginx:

http {        ...    geo $geo {        default        NONE;        include        /var/geo/geo.conf;    }    geoip_country /var/geo/GeoIP.dat;        ...    server {        ...        location / {            ...            add_header Geo-By-File $geo;            add_header Geo-By-Binary $geoip_country_code;        }    }}

Мы можем получать гео клиента, через переменную$geo, предварительно сгенерировав файлgeo.confтипа:

128.0.0.0/1 US;...

Или скачать бинарный файлGeoIP.datи использовать его без генерации, получая гео через переменные ($geoip_country_code).

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

Выводы

Стоит отметить, что измерения производились в клиенте PostgreSQL и свели к минимуму оверхед языка программирования. Если вы боретесь за милисекунды, то стоит учесть и этот факт.

Конечно, все представленные типы позволяют делать не только операцию поиска вхождения в диапазон (она была выбрана как наиболее популярная в нашей команде). А также операциипересечения диапазонов, сравнения и т.д. Буду благодарен, если кто-то напишет об опыте использования других операций и их поведении для разных типов.

В случае если вам не нужно хорошее быстродействие, мало клиентов, вы не боретесь за доли секунды, то вам подойдут типы по умолчаниюinetилиcidr, различие между которыми находятся в рамках статистической погрешности. Расширениеip4rпозволит сократить время в ~20 раз.

Постановка задачи

В рамках проекта Фото-Географического Атласа России (photogeomap.ru) мы собрали ряд фотографий различных ландшафтов страны. Многие из них сделаны в достаточно труднодоступных местах. Именно эту труднодоступность на качественном уровне мы и хотим оценить для каждой точки (фотографии)/

Индекс недоступности

Характеристика, позволяющая косвенно, на качественном уровне оценить сложность попасть к точку съемки данного кадра. Поскольку не нашлось готовых решений по расчету такой характеристики. Решено было создать свою. Назовем ее индекс недоступности (ИН).

От чего он зависит? Очевидно, что от:

• удаленности от дорог доступных для транспорта

• удаленности от пеших троп и дорог пригодных только для пешего передвижения

• удаленности от водных путей сообщения

Источник данных для анализа

Единственным доступным источником векторных данных для такого анализа близости нам видится OSM.

Спорные моменты и допущения

Сразу опишу все допущения принятые нами

1. Все расчеты проведены с использованием данных OSM со всеми содержащимися в них огрехами и неточностями.
   На карте могут быть отображены НЕ ВСЕ тропы и не все дороги. Степень валидности существующих объектов также не обсуждалась.

2. Все расчеты расстояний проведены не в лучшей для этого проекции но с учетом что нас интересуют довольно небольшие расстояния искажениями пренебрежём. Тем более что в финале все количественные характеристики все равно переводятся в качественные.

3. При анализе близости от водных объектов не учитывалась их связность и судоходность. Что в общем случае конечно неверно, но мы сознательно вводим это допущение.

Подготовка данных OSM

1. Данные OSM на территорию РФ, полученные через https://download.geofabrik.de загружены в СУБД Postgres (c ext. PostGIS).
Основные нужные нам для анализа объекты расположены в таблице planet_osm_line.
Не забываем индексировать нужные нам поля (в случае дорог это поле highway)

2. Готовим дороги и тропы. Созданы materialize view для автодороги и тропинок из класса planet_osm_line.

Дороги - select * from planet_osm_line where highway is not null and highway != track (выбраны все типы дорог из данных OSM вне зависимости от типа и качества покрытия) ошибки неверного назначения тегов проигнорированы..

Тропы - select * from planet_osm_line where highway is not null and highway = track (выбраны тропинки)

На полученных m.view - тоже создаем индексы на нужное поле. Работать будет легче.

3. Готовим реки. Создаем materialize view для линейных рек и площадных водных объектов
Теги по которым можно выбрать реки смотрим ТУТ

Краткий анализ что у нас есть по рекам вообще -

--------------------------------
SELECT t.waterway , count (t.waterway) as cnt FROM public.osm_rivers_l as t where t.waterway is not null group by t.waterway order by cnt desc
---------------------------------

Реки (линейные)
select * from planet_osm_line where waterway is not null

Реки (площадные)
select * from planet_osm_polygon where water is not null

Расчет удаления от точек съемки

На этом этапе мы собственно считаем расстояния от наших точек (фото) до дорог, троп и рек.
Выполнить эту процедуру можно в любом настольном ГИС приложении , например в QGIS . В принципе, такой расчет можно провести в самом PostGIS, не вылезая из БД. Но я не программист и мне лень изучать и делать с нуля то, что я могу быстро за 10 мин сделать в той среде где работаю ежедневно (в GIS)

Определение расстояний от точек съемки до дорог - пишем в поле Road_dist и троп - Track_dist считаем все сразу в километрах! Определяем расстояние от линейных и площадных рек. Берем минимальное из пары (ближайший водные объект, неважно какой геометрии) и пишем в поле River_dist

Итак у нас есть поля с записанными в них расточениями. Собственно на этом использование данных OSM завершается, но не весь процесс..

Методика расчета

Теперь у нас все готово, и мы начинаем считать сам ИН.
Сначала мы переводим количественные показатели в качественные характеристики.

1. Введены градации расстояний (поля Road_cat и Track_cat) и присвоены значения весового коэффициента для удаленности от автодорог и троп (Road_cst и Track_cst)

Track_cst считается только для объектов с удаление от дорог более 5 км иначе принимается 0

2. Введены градации расстояний (River_cat) и присвоены значения весового коэффициента для удаленности от рек (River_cst) для объектов с удаление более 10 км от любых дорог и троп , иначе принимается 0

3. Вводим характеристику отражающую доступ только с акватории северных морей.
Поле Sea_cst весовой коэффициент по доступу с северных морей для объектов, расположенных в Арктике и на побережье северных морей (выбраны по карте визуальное их не много). В основном все объекты приняты за 0, кроме пары десятков.

4. Все 4 поля *_cst суммированы в INDEX_IMP
смысл у него примерно такой - чем он выше --тем тяжелее добраться к точке.

менее 3 - относительно легко доступная точка (можно ии приехать на машине или относительно недалеко прийти пешком

в районое первого десятка приехать на машине и хорошо прогуляться.

второй десяток уровень автономной экспедиции.

более 30 очень сложно доступная точка - только с морских судов и пр пр радости

Собственно - все. Мы посчитали искомую характеристику и теперь можем расклассифицировать точки на карте по ней (для наглядности) или просто указать ее в атрибутах.

Бесспорно, интерфейс Gravitee представляет достаточно наглядные и удобные средства визуализации работы шлюзов Gravitee. Но в любом случае, возникает потребность предоставить доступ к этим инструментам службе мониторинга, владельцам или потребителям API и при этом они могут находится вне закрытого контура, в котором расположен менеджер API. Да и иметь всю доступную информацию по различным API на одном экране всегда удобнее.
Видеть происходящее на шлюзах, при этом не вдаваясь в особенности пользовательского интерфейса Gravitee, а администраторам - не тратить время на создание пользователей и разделение ролей и привилегий внутри Gravitee.
На Хабре уже была пара статей посвященных APIM Gravitee, тут и тут. По этому, в своей заметке, буду подразумевать, что читатель уже знаком с процессом установки/настройки APIM Gravitee и Grafana, рассмотрю только процесс настройки их интеграции.

Почему нельзя пойти простым путём?

По умолчанию, хранилищем для аналитики Gravitee является ElasticSearch. Информация накапливается в четырёх различных индексах, с посуточной разбивкой:

• gravitee-request-YYYY.MM.DD - здесь хранится информация по каждому запросу (аналог access.log в nginx). Это наша основная цель;

• gravitee-log-YYYY.MM.DD - здесь уже хранится более подробная информация о запросе (при условии, что включена отладка, см. рисунок ниже). А именно полные заголовки запросов и ответов, а также полезная нагрузка. В зависимости от настроек, логироваться может как обмен между потребителем и шлюзом, так и/или шлюзом и поставщиком API;

  Экран включения/отключения расширенного логирования

• gravitee-monitor-YYYY.MM.DD - этот нас не интересует;

• gravitee-health-YYYY.MM.DD - этот нас не интересует.

И казалось бы, что может быть проще: подключай ElasticSearch в качестве источника данных в Grafana и визуализируй, но не всё так просто.
Во первых, в индексе хранятся только идентификаторы объектов, т.е. человеко-читаемых имён поставщиков и потребителей, вы там не увидите. Во вторых, получить полную информацию соединив данные из двух источников непосредственно в интерфейсе Grafana, крайне проблематично. Gravitee хранит информацию о настройках и статистику своей работы в разных местах. Настройки, в MongoDB или PostgreSQL, по сути статическая информация. Таким образом в одном месте у нас (в терминах Grafana) - таблица, в другом - временной ряд.

B как же быть?

Большим преимуществом СУБД PostgreSQL является богатый набор расширений для работы с внешними источниками данных, в том числе и с ElasticSearch (тут). Благодаря этому интеграция сводится к тому, что Grafana общается с единственным источником данных - СУБД PostgreSQL, которая в свою очередь получает данные из ElasticSearch и обогащает их информацией и делает читаемой для администратора или любого другого бенефициара.
Схематически это будет выглядеть следующим образом (рисунок ниже).

Ну что же, за дело!

Все ниже описанные действия актуальны для следующей конфигурации: CentOS 7, APIM Gravitee 3.6, СУБД PostgreSQL 11, ElasticSearch 7.+

Начнём с интеграции PostgreSQL и ElasticSearch. Сам процесс интеграции достаточно прост и делится на следующие шаги:

1. Устанавливаем расширение multicorn11 и если не установлен pip, то ставим и его:

   yum install multicorn11 python3-pip
   

2. Далее из pip-репозитория, устанавливаем библиотеку python3 для работы с ElasticSearch:

   pip3 install pg_es_fdw
   

3. Далее, переходим к настройке PostgreSQL. Подключаемся целевой БД и добавляем расширение multicorn и подключаем необходимую библиотеку:

   GRANT USAGE on FOREIGN DATA WRAPPER multicorn TO gatewaytest;GRANT USAGE ON FOREIGN SERVER multicorn_es TO gatewaytest;
   

    CREATE EXTENSION multicorn; CREATE SERVER multicorn_es FOREIGN DATA WRAPPER multicorn  OPTIONS (wrapper 'pg_es_fdw.ElasticsearchFDW');
   

4. Выдаём права, непривилегированному пользователю. В нашем случае это logreader:

   GRANT USAGE on FOREIGN DATA WRAPPER multicorn TO logreader;GRANT USAGE ON FOREIGN SERVER multicorn_es TO logreader;
   

5. Для удобства, создадим отдельную схему logging, владельцем которой будет наш пользователь logreader:

   CREATE SCHEMA logging AUTHORIZATION logreader;
   

6. Создадим родительскую таблицу, к которой мы будем подключать новые индексы и удалять не актуальные:

   CREATE TABLE logging.requests (  id varchar(36),  "@timestamp" timestamp with time zone,  api varchar(36),  "api-response-time" int,  application varchar(36),  custom json,  endpoint text,  gateway varchar(36),  "local-address" varchar(16),  method int,  path text,  plan varchar(36),  "proxy-latency" int,  "remote-address" varchar(16),  "request-content-length" int,  "response-content-length" int,  "response-time" int,  sort text,  status int,  subscription varchar(36),  uri text,  query TEXT,  score NUMERIC) PARTITION BY RANGE("@timestamp");
   

   Конечно же, индексы содержат больше полей, но для решения текущей задачи, приведенных выше - достаточно.

7. Для подключения и отключения индексов, создадим небольшой shell-скрипт и будем запускать его раз в сутки через cron:

   #!/bin/shNEWPART=${1:-$(date +'%Y.%m.%d')}OLDPART=$(date --date='14 days ago' +'%Y.%m.%d')curl http://gateway.corp/testpsql gateway -U logreader -c "CREATE FOREIGN TABLE logging.\"requests_${NEWPART}\"PARTITION OF logging.requests   FOR VALUES FROM ('${NEWPART} 00:00:00') TO ('${NEWPART} 23:59:59')SERVER multicorn_esOPTIONS (host 'els-host',  port '9200',  index 'gravitee-request-${NEWPART}',  rowid_column 'id',  query_column 'query',  query_dsl 'false',    score_column 'score',  sort_column 'sort',  refresh 'false',  complete_returning 'false',  timeout '20',  username 'elastic-ro',  password 'Sup3rS3cr3tP@ssw0rd');"    psql gateway -U gatewaydev -c "drop foreign table logging.\"requests_${OLDPART}\""
   

   Немного пояснений:

   • NEWPART - текущая дата, для формирования имени партиции , при подключении нового индекса из ElasticSearch;

   • OLDPART - дата истекшего, неактуально индекса, здесь это 14 дней (определяется исходя из настроек ES Curator). Удалять партиции, ссылающиеся на несуществующие - обязательно. В противном случае запросы, к родительской таблице, будут прерываться с ошибками;

   • Вызов 'curl http://gateway.corp/test', необходим для того, что бы создавался индекс текущего дня, так как он создаётся в момент первого обращения к любому поставщику API. Если его не создать, то это будет приводить к ошибке, описанной выше. Такая проблема больше актуальна для тестовых стендов и стендов разработки;

   • Затем, создаём партицию на индекс текущего дня;

   • И на последнем шаге - удаляем неактуальный индекс.

   • Проверяем что всё работает

     TABLE logging.requests LIMIT 1;
     

     Если всё правильно, то должны получить похожий результат

   -[ RECORD 1 ]-----------+-------------------------------------id                      | 55efea8a-9c91-4a61-afea-8a9c917a6133@timestamp              | 2021-05-16 00:00:02.025+03api                     | 9db39338-1019-453c-b393-381019f53c72api-response-time       | 0application             | 1custom                  | {}endpoint                | gateway                 | 7804bc6c-2b72-497f-84bc-6c2b72897fa9local-address           | 10.15.79.29method                  | 3path                    | plan                    | proxy-latency           | 2remote-address          | 10.15.79.27request-content-length  | 0response-content-length | 49response-time           | 2sort                    | status                  | 401subscription            | uri                     | /testquery                   | score                   | 1.0
   

Рисуем графики

И вот мы подошли к тому, ради чего всё и делалось - визуализируем статистику Gravitee. Благодаря тому, что для доступа к аналитике используется единая точка входа, а именно СУБД PostgreSQL, это даёт дополнительные возможности. Например, выводить статическую информацию: количество поставщиков, количество потребителей и их статусы; количество и состояние подписок; параметры конфигурации для поставщика и многое другое, наряду с динамическими данными.
В том числе хотелось бы отметить, что у поставщиков и потребителей имеется раздел Metadata, которые можно заполнять кастомными данными и так же выводить в дашборды Grafana.

Вот тут:

А вот так это можно отобразить в Grafana:

SELECT  name "Наименование",  value "Значение"FROM  metadataWHERE  reference_id='${apis}'

Пример комплексного экрана

APIs (статика) - общее количество поставщиков и количество активных.

SELECT COUNT(*) AS "Всего" FROM apis;SELECT COUNT(*) AS "Активных" FROM apis WHERE lifecycle_state='STARTED';

Для Applications, запросы составляются по аналогично, только из таблицы applications

API Hits - количество вызовов по каждому поставщику. Тут уже немного по сложнее

SELECT  date_trunc('minute',"@timestamp") AS time,  apis.name,ee с Grafana  COUNT(*)FROM  logging.requests alJOIN  apis ON al.api = apis.idWHERE  query='@timestamp:[$__from TO $__to]'GROUP BY 1,2

Average response time by API - среднее время ответа, по каждому поставщику считается аналогичным способом.

SELECT  date_trunc('minute',"@timestamp") AS time,  apis.name,  AVG(al."api-response-time")FROM  logging.requests alJOIN  apis ON al.api = apis.idWHERE  query='@timestamp:[$__from TO $__to]'GROUP BY 1,2

Еще один интересный показатель Hits, by gateways, это равномерность распределения запросов по шлюзам. Считается так:

SELECT  date_trunc('minute',"@timestamp") as time,  al."local-address",  COUNT(*)FROM  logging.requests alWHERE  query='@timestamp:[$__from TO $__to]'GROUP BY 1,2

Заключение

Приведённое выше решение, по моему субъективному мнению, нисколько не уступает стандартным средствам визуализации APIM Gravitee, а ограничено лишь фантазией и потребностями.
Учитывая то, что Grafana, обычно является центральным объектом инфраструктуры мониторинга, то преимущества такого решения очевидны: более широкий охват, более высокая плотность информации и простая кастомизация визуальных представлений.

P.S.

В ближайшее время, планируется ещё статья по интеграции Gravitee с ActiveDirectory. Процесс достаточно прост, но как всегда, есть нюансы.

Конструктивная критика, пожелания и предложения приветствуются!

Если ваша жизнь DBA, сопровождающего PostgreSQL, наполнена вопросами "а почему так медленно?" и "как сделать, чтобы запрос не тормозил?", наш сервис анализа и визуализации планов запросовexplain.tensor.ru сделает ее немного легче за счет привлечения коллег и обновленных подсказок.

"Ландон из зе кепитал оф Грейт Британ"

Да, наконец свершилось то, о чем нас так долго просили ради иностранных коллег - мы поддержали работу сервиса не только "на русском", но и на других языках. Пока - только на английском, зато полноценно, включая и перевод рекомендательных подсказок.

А чтобы коллегу можно было оперативно подключить к анализу проблем, добавлена возможность оперативно "расшарить" ссылку на план через популярные мессенджеры, соцсети или почту.

Подсказки к плану

Про базовый набор подсказок и способов ими воспользоваться я уже рассказывал в статье "Рецепты для хворающих SQL-запросов" - теперь мы сделали их еще больше и удобнее!

• цветовая и текстовая кодировка разных видов

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

• несколько подсказок у одного узла

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

• на самом видном месте

  Больше не нужно листать огромный план, чтобы увидеть, где там возникла проблема - все подсказки уже сдублированы в заголовке, и достаточно всего одного клика, чтобы перейти к нужному узлу.

Точно так же клик на заголовке столбца сразу перенесет фокус на узел с максимальным значением, так что выяснить "кто тут отфильтровал миллион записей" можно за полсекунды.

Масштабируемая диаграмма

Если прежде увидеть "горячие точки" на потоковой диаграмме выполнения большого плана было сложно за счет возникавшей прокрутки, то теперь сразу запускается просмотр в режиме масштабирования. Но вернуться к режиму прокрутки можно в один клик.

Пользуйтесь! Возникнут идеи или замечания - прошу в комментарии.

Привет, Хабр. В прошлой статье я рассказал о начальном анализе предметной области и базовом проектировании нашей новой ECM-системы. Теперь я расскажу о первой практической задаче, которую мы решили. А именно - о выборе способа организации структуры хранилища бизнес-атрибутов объектов.

Напомню, что в результате анализа мы пришли к следующей структуре объекта:

Системные и интеграционные поля содержат техническую информацию и редко подвержены модификациям. А вот бизнес-поля - искомые атрибуты объектов - могут и будут различаться для разных типов документов, которых у нас на входе предполагается около 1000. Проанализировав возможность повторного использования полей, мы пришли к выводу, что у разных типов документов может быть не более 15% общих свойств. И при этом всё равно придется скрепя сердце согласиться на неполное соответствие названия и содержания.

Таким образом, есть большое количество сущностей с разнообразным атрибутивным составом, который нужно хранить, тратя как можно меньше ресурсов, и расширять который хотелось бы также с минимальными усилиями.

Какие есть варианты?

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

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

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

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

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

Именно с этим я столкнулся на двух прошлых (не самых старых) проектах со схожей декомпозицией объектов, разработчики которых выбрали именно такой путь организации данных.

Также стоит упомянуть про еще один способ организации данных, с которым я сталкивался. Он используется не в пример реже и больше напоминает "велосипед" - назовем его вертикальный. В хранилище создается таблица, которая содержит, условно, название поля, данные в нем, тип данных и связь с объектом. Возможно создание нескольких таких таблиц для разных типов данных или несколько колонок в одной таблице. Далее, при извлечении такие данные трансформируются из списка в плоский набор полей. Понимаете, почему я упомянул про велосипед?

А что у нас?

Второй вариант - это использование специализированных документоориентированных (или документных, как больше нравится) баз данных, реализующих NoSQL-подход к хранению и обработкенеструктурированной или слабоструктурированной информации. Наиболее часто данные хранятся в виде JSON объектов, но с предоставлением производителями СУБД инструментария для доступа к данным внутри этих структур.

У такого подхода, применительно к проектируемой системе, можно выделить несколько плюсов:

• можем управлять структурой объектов без программирования, в том числе в реальном времени, путем администрирования. Администратор сможет зайти в некий интерфейс справочника полей и задать новые.

• ограничиваем ситуации случайного воздействия на данные - модифицировать json объект гораздо сложнее, чем данные в колонке.

• проще описывать объекты в коде - иногда можно вообще не описывать структуру документа в коде, а работать прямо с полями в JSON.

Но есть и минусы:

• невозможно нативно реализовать проверки данных при размещении в хранилище.

• валидацию данных придется проводить в коде.

• невозможно организовать внутреннюю связность подчиненных данных, например, справочников.

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

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

Делаем прототип

Возьмем гипотезу, что NoSQL-подход для нашей системы применим как минимум не хуже, чем классический. Для ее проверки создадим прототип, в рамках которого реализуем оба подхода. В качестве СУБД возьмем Postgre, который уже давно умеет хорошо работать с JSON полями.

Создадим следующие таблицы:

Для описания объектов в табличном виде:

• r_objects, базовые данные по объектам: тип, дата создания и ссылка на хранилище атрибутов.

• r_attributes. атрибуты, во всех возможные колонки для всех объектов. В идеале конечно же хранилища атрибутов для разных объектов лучше разбить по разным таблицам, но для прототипа это не критично.

Для описания объектов в виде JSON:

• objects. Данные по объектам, где в поле data формата jsonb хранятся искомые атрибуты.

Остальные таблицы - это различные вспомогательные хранилища.

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

Методика тестирования

Для тестирования обоих подходов хранения данных используем следующие методы:

• добавление данных по объекту. Критерий успешности: объект с данными появился в хранилище, метод вернул в ответе его идентификатор.

• обновление данных по объекту. Критерий успешности: данные в хранилище были изменены, метод вернул отметку об успехе. Удаление данных в системе не предусматривается, поэтому удаление объекта является операцией, аналогичной обновлению.

• извлечение данных по объекту. Критерий успешности: объект с данными возвращен в ответе на запрос. Извлечение объекта происходит по конкретному идентификатору, по критериям поиска и постранично (пагинация).

Генерация запросов будет происходить в 20 параллельных потоков по 50 запросов в каждом потоке. Для того, чтобы тестирование было действительно показательным с точки зрения производительности, предварительно наполним базу 200 млн. объектов.

Тестирование показало следующие результаты:

Первая (высокая) часть графика - это получение объектов по случайной странице - пагинация. Здесь в обоих случаях пришлось применить небольшой трюк - так как Postgres не агрегирует точное число строк в таблице, то узнать, сколько всего записей на объеме данных теста простым count - это долго, и для получения количества записей пришлось брать статистику данных по таблице. Также время получения данных на страницах свыше 10000-й неприлично велико, поэтому верхняя планка для получения случайного номера страницы была установлена в 10000. Учитывая специфику нашей системы, получение пагинированных данных не будет частой операцией, и поэтому такое извлечение данных применяется исключительно в целях тестирования.

Вторая (средняя) часть графика - вставка или обновление данных.

Третья (низкая) часть графика - получение данных по случайному идентификатору.

В случае табличной структуры данных - линий на графике больше, потому что с точки зрения программной структуры, получение данных для каждого типа объекта идет по разным методам, тогда как для NoSQL - по одинаковым.

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

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

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

Что получилось?

В итоге, можно с уверенностью сказать, что для нашей системы NoSQL-подход к хранению данных оказался вполне подходящим. Даже с учетом потенциальных минусов, которые можно решить на этапе разработки серверной части, можно сказать, что он дает лучшие показатели по сравнению с табличным хранением. А плюсы в виде возможности настройки структуры объекта на лету, позволяют высвободить ресурсы от рутинного расширения объектов в сторону разработки новой функциональности.

Привет, самое хардовое IT комьюнити Рунета! Я Саша, главный архитектор в компании Quadcode. Мы пришли на Хабр для того, чтобы показать кухню Fintech варимся мы во всем этом 8 лет, поэтому уже можем поделиться опытом. В своем блоге будем рассказывать об архитектурах, технологиях, инструментах и лайфхаках.

Этот пост первый в списке, его можно считать знакомством. Под катом я расскажу про структуру нашей команды, про продукты Quadcode это платформа для трейдинга, банкинг и внутренние разработки, а также про наши первые шаги на пути к IPO.

Наша команда

Команда Quadcode уже 8 лет работает в финтехе. Цель компании создавать удобные финтех-инструменты для B2B клиентов со всего мира.

В разработке мы руководствуемся Agile принципами, да и в принципе склонны к гибким методологиям. Именно они позволяют достигать баланса в скорости и качестве разработки продуктов, поэтому разработка представляет из себя набор Scrum команд.

Во главе каждой команды стоит Team Lead. Сами команды сгруппированы в отделы, работающие над определенными предметными областями. Например, есть отдел Finance Development, в котором команды разрабатывают финансовые сервисы для платформы. Есть ветка, где располагаются владельцы продукта (product owners), задача которых развивать и улучшать наши продукты. Сейчас у нас в разработке 230+ опытных (реально опытных, у каждого много лет практики) специалистов. Это порядка 24 команд и 6 Product Owners. Джуниоров мы берем редко. Но с каждым годом искать опытных специалистов становится все сложнее, так что все больше в эту сторону смотрим.

Задачи по разработке выстраиваются на основе продуктовых Roadmap. Это план развития продукта с целью получения определенных бизнес-показателей. Роадмап выстраивается для каждого продукта и может быть составлен на разные временные промежутки: полгода, год, три года и т.д. Из готовых продуктовых роадмапов выстраивается общий план: когда, какие фичи и для каких продуктов должны быть сделаны.

Роадмап в нашем понимании это связующее звено между бизнесом, продуктом и разработкой.

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

Работаем удаленно или в офисе в Санкт-Петербурге каждый выбирает самостоятельно. Я для себя выбрал работу из дома, но по офису тоже скучаю, он у нас классный с тренажерным залом, массажным кабинетом и прочими плюшками.

Технологический стек

Наши основные языки для разработки Golang и C++. Из дополнительных технологий на бэкенде PHP, Python, NodeJS, на фронте JavaScript (ReactJS), в аналитике Python, Scala, а в автотестах Java.

Инфраструктура в компании гибридная. Мы арендуем собственные сервера в датацентрах. Все stateless приложения стараемся эксплуатировать в Kubernetes, если для этого нет ограничений, хотя бывает и такое. Kubernetes-кластера также преимущественно работают на наших серверах. То, что требует гарантированных ресурсов, например нагруженные базы данных, мы эксплуатируем на железе. Конечно, используем и облака там, где это приносит пользу. Например в задачах, где требуется обработать большое количество данных, чтобы предоставить отчет заказчику. Для таких задач нужно временно получить ресурсы для анализа, но после получения результата они не нужны.

Для точечных целей применяем технологии, которые позволяют решить специфические задачи. Например, наше Desktop приложение под Windows, Mac и Web написано на С++ и имеет единую кодовую базу. В данном случае С++ дает нам кроссплатформенность и отличную производительность при рендере графики. Однако мы практически не используем С++ для Backend разработки, потому что это дорого. Основной язык разработки для Backend у нас Go. В то же время мы не используем его как инструмент для тестирования. Для этих целей применяем Java, так как это намного удобнее и является уже практически промышленным стандартом в индустрии.

Какие продукты создает команда Quadcode

Наш флагманский продукт платформа для трейдинга. За 7 лет развития количество пользователей платформы выросло с 950 тысяч до 88 миллионов в 170+ странах.

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

Кроме того планируем расширять географический охват, выходить на новые рынки. Для этого обновляем платформу с учетом всех технических и юридических требований конкретных регионов. Все это поможет нам выйти на IPO.

А теперь кратко о наших продуктах:

SaaS Trading Platform

Команда с нуля разработала платформу с аптаймом 99.5%, на базе которой более 7 лет успешно функционирует брокер.

Платформа предоставляет клиенты под Windows, MacOS, Anrdoid, iOS, а также WEB трейдрум.

На платформе можно торговать следующими инструментами:

• Digital опционы

• FX опционы

• CFD

• Forex

• Crypto и др.

Основной язык для разработки платформы Golang. Платформа начала свое существование с монолитной архитектуры классического для своего времени стека: PHP+PostgreSQL+Redis+JS.

Через 3 года эксплуатации было решено перейти на микросервисную архитектуру, так как монолит уже не давал гибкости и не мог обеспечить необходимые темпы разработки. С миграцией на микросервисную архитектуру мы также ушли с PHP в сторону Go, о чем не жалеем.

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

С прошлого года наша платформа развивается как SaaS решение. На базе решения любой желающий может без больших усилий организовать своего собственного брокера, все есть в коробке под ключ: трейдинговый сервис, процедуры KYC, биллинг, support, crm. Словом, все, чтобы быстро стартануть бизнес. Любого нового брокера можно поднять за месяц. Чтобы обеспечить вариативность в функционале, мы разрабатываем гибкую систему модулей для SaaS-решения.

Команда Quadcode обеспечивает внутренний риск менеджмент, бэк офис и антифрод поэтому брокеру не нужно думать обо всем этом. В итоге клиент получает полностью работающее кросс-платформенное решение. Мы также решили вопрос с регистрацией и авторизацией. Вся IT поддержка лежит на наших плечах. Мы помогаем с построением лигал структуры и во многом другом.

Сейчас добиваемся того, чтобы в экосистеме платформы был максимально широкий спектр инструментов: Forex, СFD и инвестиционные продукты в удобной для пользователя форме. Идеальный вариант сделать платформу подходящей как для банков, так и для их клиентов. Мы собираем паззл продукта из мельчайших деталей. Процесс этот не такой быстрый, но пока все получается. Быстро и не получится ни в правовом плане, ни в плане технологий.

Примеры задач, которые стоят перед командой в этом году:

1. Конфигуратор платформы. В качестве примера возьмем конфигуратор сборки машин BMW. Вы можете зайти на сайт и собрать свой автомобиль, то есть выбрать цвет, диски, дополнительные возможности. Мы хотим сделать точно такой же функционал, только с возможностью сборки своего брокера прямо на сайте.

2. Также один из крупных проектов это разработка собственного движка Margin Forex & MCFD.

3. Проработка Prediction Churn. Фича основана на анализе данных и предсказывает момент, когда пользователь решит уйти. Сейчас результат Prediction Churn достоверен с вероятностью 82%. Когда система предсказывает, что пользователь готов уйти с платформы,в работу включаются менеджеры, чтобы создать удобные для трейдера условия работы на платформе. Это позволяет продлить срок работы с трейдером. Чем дальше, тем точнее будет работать Prediction Churn, и тем лучше мы сможем держать контакт с пользователем.

Banking

Это второй наш продукт. В основе направления находится собственный лицензированный провайдер финансовых услуг, который зарегистрирован в Великобритании. Продукт предоставляет следующие функции B2B и B2C клиентам:

• Дистанционный онбординг для физических и юридических лиц.

• Доступ к счету через мобильное приложение и онлайн-банкинг.

• Мультивалютные счета в формате IBAN.

• SEPA, TARGET2 и SWIFT переводы.

• Выпуск пластиковых и виртуальных карт.

Технологический стек классический: ядро системы работает под управлением JAVA. А также применяется PHP+JS для реализации административных интерфейсов управления и web приложений.

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

Внутренние разработки

Помимо проектов, напрямую завязанных на прибыль и клиентов, вкладываемся в разработку собственных решений, чтобы обеспечить удобную и гибкую профессиональную среду для работы сотрудников.

Из наиболее интересных можно выделить следующие:

1. Шина данных IQ Bus. Мы исповедуем микросервсиную архитектуру. В самом начале, когда возник вопрос, а что выбрать для обеспечения коммуникации между микросервисами, мы решили создать свое решение IQ Bus. Это шина, которая абстрагирует сервисы от транспортного уровня и предоставляет им простой унифицированный протокол для общения.

2. Sandbox. В сложных многокомпонентных, а в нашем случае системах с большим количеством сервисов, всегда возникает проблема с тестированием. Важно иметь возможность получать воспроизводимое окружение для тестирования, так называемые тестовые стенды. Еще в самом начале пути мы создали Sandbox систему, с помощью которой можно собирать копии платформы с различными конфигурациями. Это своего рода конструктор, куда можно зайти, выбрать какая функциональность нужна - будет создана сборка, запущены необходимые микросервисы и можно тестировать. Все это работает на базе Docker + Kubernetes.

3. Central Information System. Всегда возникает необходимость в инструменте, который может объединить в себе все системы компании. Речь не только про разработку, но и про КДП, HR, Финансовый отдел. Такая система должна помогать находить ответы на различные вопросы. Например, что за команда такая A, какие у нее сотрудники, кто руководитель, какой у нее ФОТ, что она сделала за прошедший квартал. И плюс еще много всяких индивидуальных хотелок. Найти такой продукт, имеющий в себе все, достаточно проблематично, да и выглядят такие системы довольно монструозно. Хороший пример SAP. Мы же вкладываемся в собственную разработку такой системы, которая реализует все потребности различных отделов и интегрируется с другими системами: Gitlab, таск трекер, финансовые системы (1C).

Вместо заключения

За 2020 мы проделали большой путь по разработке SAAS решения и внедрения нового банкинг продукта, сейчас появилось еще несколько важных целей. Компания использует стратегическое планирование, мы движемся в сторону присутствия на рынках всех стран, удвоения показателя EBITDA и выхода на IPO.

В будущих статьях на Хабре мы расскажем более подробно о нашем подходе к разработке, планированию и работе с командами. Вместо рекламной паузы ссылка на наши вакансии. Если остались вопросы, то пишите в ТГ @wolverinoid.

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

Иногда при выполнении длительных или плохо написанных запросов в PostgreSQL происходят разные неприятные вещи типа внезапного сбоя процесса или краша всего сервера.

В таких случаях на носителе могут остаться "мертвые души" - файлы (иногда совсем немаленькие, а вполне сравнимые по объему со всей остальной базой), которые были созданы во время работы процесса в качестве временного хранилища промежуточных данных.

Эти данные уже никому не нужны, никем не могут быть использованы, но сервер все равно не торопится избавиться от них как Плюшкин.

Сегодня посмотрим, как их можно найти и безболезненно "зачистить".

Разыскиваем temp buffers

Первая категория возникающих проблем - временное использование дискового пространства при выполнении узла плана, если необходимый объем памяти не уместился в work_mem.

Получить такой эффект достаточно просто - забыть поставить или выбрать слишком большой предел рекурсии:

explain (analyze, buffers)WITH RECURSIVE T AS (  SELECT    0 i  , '' sUNION ALL  SELECT    i + 1  , repeat('a', i + 1)  FROM    T  WHERE    i < 1e4 -- 10k итераций)TABLE T ORDER BY s DESC LIMIT 1;

[просмотреть на explain.tensor.ru]

Корень беды заключается в том, что для сортировки рекурсивной выборки T необходимо вычислить и куда-то записать ее полностью, что и показывает атрибут temp written:

->  CTE Scan on t  (actual time=0.008..374.157 rows=10001 loops=1)      Buffers: temp written=6126

Давайте теперь сэмулируем неприятность, случившуюся во время выполнения запроса - увеличим для этого ограничение рекурсии на порядок:

SELECT pg_backend_pid();-- 15004 - это PID процесса, обслуживающего наше клиентское соединениеexplain (analyze, buffers)WITH RECURSIVE T AS (  SELECT    0 i  , '' sUNION ALL  SELECT    i + 1  , repeat('a', i + 1)  FROM    T  WHERE    i < 1e5 -- 100k итераций)TABLE T ORDER BY s DESC LIMIT 1;

kill -9 15004

Сервер быстро упал - быстро поднялся. Но место на диске у нас убыло почти на 4GB - где же они?

Найти их нам поможет функция получения списка временных файлов pg_ls_tmpdir:

SELECT * FROM pg_ls_tmpdir();

 name            |  size      |  modificationpgsql_tmp15004.0 | 1073741824 | 2021-05-12 10:46:10+03pgsql_tmp15004.1 | 1073741824 | 2021-05-12 10:46:38+03pgsql_tmp15004.2 | 1073741824 | 2021-05-12 10:47:06+03pgsql_tmp15004.3 |  958078976 | 2021-05-12 10:47:31+03

Данная функция появилась только в PostgreSQL 12, поэтому если версия вашего сервера младше, придется воспользоваться pg_ls_dir по <data>/base/pgsql_tmp - это как раз то место, где сохраняются временные файлы, которые мы ищем.

Эти файлы в имени содержат PID процесса, который их породил. Поэтому, чтобы не зачистить лишнего, сразу проверим, что среди активных запросов такого идентификатора нет:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/pgsql_tmp' dir), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir), tmp AS (  SELECT    *  , regexp_replace(fn, '^pgsql_tmp(\d+).*$', '\1')::integer pid  , (pg_stat_file(dir || '/' || fn)).*  FROM    ls)SELECT  dir || '/' || fnFROM  tmpLEFT JOIN  pg_stat_activity sa    USING(pid)WHERE  sa IS NOT DISTINCT FROM NULL;

Теперь осталось пройти по полученному списку и поудалять. Замечу, что если "прибивать" запрос через pg_terminate_backend(pid), то и сервер не "падает", и подобного "мусора" в каталоге не остается.

Ничейные TEMPORARY TABLE

CREATE TEMPORARY TABLE x ASSELECT  i, repeat('a', i::integer) sFROM  generate_series(1, 1e5) i;

Теперь в списке схем нашего соединения появилась pg_temp_5:

SELECT current_schemas(true);-- {pg_temp_5,pg_catalog,public}

Именно на эту схему проецируется обращение к псевдосхеме pg_temp - то есть в этом соединении запросы TABLE x, TABLE pg_temp.x и TABLE pg_temp_5.x будут эквивалентны, пока эта временная таблица существует.

Но раз эта таблица полноценная, а не "полуфабрикат", как в случае temp buffers, то мы должны бы увидеть ее и в pg_class:

SELECT  oid, relnamespace::regnamespace, relname, relfilenodeFROM  pg_classWHERE  relnamespace::regnamespace::text ~ '^pg(_toast)?_temp';

 oid  |  relnamespace   |  relname             |  relfilenode66112 | pg_toast_temp_5 | pg_toast_66109       | 6611266114 | pg_toast_temp_5 | pg_toast_66109_index | 6611466109 | pg_temp_5       | x                    | 66109

Выяснение такой странной нумерации схем приводит к письму Tom Lane аж от февраля 2003:

> What is the origin of these schemas? local temporary tables? sorts?

Right, they're made to hold temporary tables. The first time a givenbackend does CREATE TEMP TABLE, it looks for a pg_temp_n schema, and makes it if it's not there. On shutdown, it removes the temp tables, but it seemed like a waste of cycles to remove the pg_temp_n schema itself.

(ObTrivialFact: the 'n' is the backend's pgproc slot number, so it's known not to be in use by any concurrently running backend. But it will certainly be used again in future.)

Итак, при штатном гашении сервера сами файлы временных таблиц должны быть вычищены. Собственно, а где они?

В отличие от temp buffers, относящихся ко всему серверу, файлы временных таблиц и индексов относятся к конкретной базе, но имеют несколько другой формат имени:

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/' || oid dir  FROM    pg_database  WHERE    datname = current_database()), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir)SELECT  *FROM  lsWHERE  fn ~ '^t';

 dir                |  fn.../data/base/16393 | t5_66109.../data/base/16393 | t5_66112.../data/base/16393 | t5_66114

То есть имя файла временного объекта выглядит как t<temp schema N>_<temp object OID>. Если сейчас мы "уроним" сервер снова, эти файлы останутся, как и записи в pg_class.

Чтобы избавиться от них, можно прогнать VACUUM FULL по всей базе, но это практически невозможно, если она достаточно велика. Или просто подождать когда то же самое доберется сделать autovacuum:

LOG: autovacuum: found orphan temp table "tst.pg_temp_5.x"

Но если таблиц в базе немало, это может наступить ой как нескоро, а дисковое пространство по-прежнему будет занято.

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

WITH dir AS (  SELECT    current_setting('data_directory') || '/base/' || oid dir  FROM    pg_database  WHERE    datname = current_database()), ls AS (  SELECT    *  , pg_ls_dir(dir) fn  FROM    dir), lsid AS (  SELECT    *  , (pg_stat_file(dir || '/' || fn)).*  FROM    ls  WHERE    fn ~ '^t'), sch AS (  SELECT DISTINCT    regexp_replace(fn, '^t(\d+)_.*$', '\1') sch  FROM    lsid  WHERE    modification < (      SELECT        stats_reset      FROM        pg_stat_database      WHERE        datid = 0    ))SELECT  string_agg('DROP SCHEMA ' || nspname || ' CASCADE;', E'\n') sFROM  schJOIN  pg_namespace nsp    ON nsp.nspname IN ('pg_temp_' || sch, 'pg_toast_temp_' || sch);

Получаем готовый текст запроса, который останется только выполнить:

DROP SCHEMA pg_temp_5 CASCADE;DROP SCHEMA pg_toast_temp_5 CASCADE;

Недавно мы публиковали статью про производительность Vault с разными бэкендами, а сегодня расскажем, как делать бэкапы и снова на разных бэкендах: Consul, GCS (Google Cloud Storage), PostgreSQL и Raft.

Как известно, HashiCorp предоставляет нативный метод бэкапа только для одного бэкенда Integrated Storage (Raft Cluster), представленного как GA в апреле прошлого года. В нем можно снять снапшот всего одним curlом и не беспокоиться о каких-либо нюансах. (Подробности смотрите в tutorial и документации по API.)

В остальных же случаях придется выкручиваться самим, придумывая, как правильно реализовывать бэкап. Очевидно, что во время резервного копирования Vault часть данных может меняться, из-за чего мы рискуем получить некорректные данные. Поэтому важно искать пути для консистентного бэкапа.

1. Consul

В Consul существует встроенный механизм для создания и восстановления снапшотов. Он поддерживает point-in-time backup, поэтому за консистентность данных можно не переживать. Однако есть существенный недостаток: на время снапшота происходит блокировка, из-за чего могут возникнуть проблемы с операциями на запись.

Для создания такого снимка необходимо:

1. Подключиться к инстансу с Consul и выполнить команду:

# consul snapshot save backup.snapSaved and verified snapshot to index 199605#

2. Забрать файл backup.snap (заархивировать и перенести в место для хранения бэкапов).

Для восстановления будет схожий алгоритм действий с выполнением другой команды на сервере с Consul:

# consul snapshot restore backup.snap

В HA Vault-кластере активный узел будет являться лидером во время экспорта данных или моментального снимка. После восстановления данных Vault в Consul необходимо вручную снять эту блокировку, чтобы кластер Vault мог выбрать нового лидера.

Выполните команду после восстановления данных для снятия блокировки:

# consul kv delete vault/core/lock

Работа со снапшотами обычно происходит быстро (см. примеры в конце статьи) и обычно не вызывает трудностей.

2. Google Cloud Storage

С GCS всё оказалось сложнее: подходящих вариантов для создания снапшотов/бэкапов бакета найти не удалось. Предусмотрена поддержка версионирования, но с ним восстановить сразу весь бакет на определенный момент времени нельзя можно только по одному файлу. В теории это решается скриптом для взаимодействия со всеми файлами, но если учесть размер Vaultа и количества файлов в нем, скорее всего такой скрипт будет работать слишком долго. Если мы хотим получить консистентные данные, то снятия дампа придется на время останавливать Vault.

А для копирования данных в GCS предусмотрен удобный способ Data Transfer. С его помощью можно создать полную копию бакета, а дальше уже работать с ним по своему усмотрению.

Последовательность действий:

1. Выключаем Vault.

2. Заходим в Data Transfer (https://console.cloud.google.com/transfer/cloud).

3. Выбираем исходный бакет, вводим название бакета, куда переносить данные, и выбираем разовую операцию. Здесь возможно установить и операцию по расписанию, но она не учитывает специфики Vault (тот факт, что сервис необходимо остановить Vault на время её выполнения), поэтому оставляем разовый запуск.

4. После успешного копирования запускаем Vault.

5. С созданным бакетом можно работать: например, скачать его содержимое при помощи gsutil, заархивировать все данные и отправить на долгосрочное хранение.

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

Скриншоты страницы при создании трансфера и после завершения:

3. PostgreSQL

Базу в PostgreSQL достаточно забэкапить любыми доступными для этой СУБД способами не сомневаюсь, что они хорошо известны инженерам, уже работающим с PgSQL. Инструкции по выполнению операций для настройки бэкапов и восстановления данных на нужный момент времени (PITR, Point-in-Time Recovery) описаны в официальной документации проекта. Также хорошую инструкцию можно найти в этой статье от Percona.

Не останавливаясь на технических деталях по этим операциям (они уже раскрыты в многочисленных статьях), отмечу, что у PostgreSQL получился заметно более быстрый бэкап, чем у вариантов выше. Для простоты действий, мы замеряли бэкап и восстановление обычными pg_dump и pg_restore (да, это упрощенное измерение, однако использование схемы с PITR не повлияет значительно на скорость).

4. Integrated Storage (Raft)

Однако обзор не был бы полным без самого удобного и простого на сегодняшний день бэкенда Raft. Благодаря тому, что Raft полностью дублирует все данные на каждый из узлов, выполнять снапшот можно на любом сервере, где запущен Vault. А сами действия для этого предельно просты.

Для создания снимка необходимо:

. Зайти на сервер/pod, где запущен Vault, и выполнить команду:

# vault operator raft snapshot save backup.snapshot

2. Забрать файл backup.snapshot (заархивировать и перенести в место для хранения бэкапов).

Для восстановления команду на сервере с Vault надо заменить на:

# vault operator raft snapshot restore backup.snapshot

Как работать с Raft и со снапшотами, хорошо описано в официальной документации.

Простой бенчмарк

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

Загрузка данных в Vault

Перед началом тестирования загрузим данные в Vault. Мы для этого использовали официальный репозиторий от HashiCorp: в нем есть скрипты для вставки ключей в Vault.

Протестируем на двух коллекциях: 100 тысяч и 1 млн ключей. Команды для первого теста:

export VAULT_ADDR=https://vault.service:8200export VAULT_TOKEN=YOUR_ROOT_TOKENvault secrets enable -path secret -version 1 kvnohup wrk -t1 -c16 -d50m -H "X-Vault-Token: $VAULT_TOKEN" -s write-random-secrets.lua $VAULT_ADDR -- 100000

Эту операцию проделаем для всех инсталляций Vault, а затем повторим её для другого количества ключей.

Результаты

После загрузки данных мы сделали бэкап для всех 4 бэкендов. Бэкап для каждого hosted-бэкенда снимался на однотипной машине (4 CPU, 8 GB RAM).

Результаты сравнения по бэкапу и восстановлению:

* Восстановление бэкапа в GCS может происходить в нескольких вариантах:

1. Мы просто переключаем Vault на бэкапный бакет. В таком случае достаточно перезапустить Vault с новым конфигом и все сразу же заработает.

2. Загрузить файлы бэкапа в бакет через утилиту gsutil и уже после этого переключать. Результаты для такого варианта будут зависеть от количества файлов и их размера в таблице приведен результат именно для такого варианта.

NB. В этом мини-тестировании сравниваются решения разного рода: single-экземпляр PostgreSQL против кластеров Consul и Raft против сетевого распределённого хранилища (GCS). Это может показаться не совсем корректным, потому что у таких бэкендов и разные возможности/свойства (отказоустойчивость и т.п.). Однако данное сравнение приведено исключительно как примерный ориентир для того, чтобы дать понимание порядковой разницы в производительности. Ведь это зачастую является одним из факторов при выборе того или иного способа.

Выводы

PostgreSQL занимает уверенное первое место по скорости создания бэкапа. Raft не так сильно отстает от лидера на небольшом объеме секретов, но разница заметно возрастает при увеличении количества данных. Однако в то же время Raft явно лидирует в скорости восстановления.

Если сравнивать удобство, то Raft и Consul максимально простые: для выполнения бэкапа и восстановления достаточно выполнить буквально одну команду. GCS же предоставляет встроенный функционал в UI для копирования бакетов: на мой вкус, это немного сложнее, однако для других пользователей может быть плюсом, что все действия выполняются мышкой в браузере. Но в GCS есть существенная проблема с отсутствием гарантий по времени создания снапшотов: одинаковый набор данных может бэкапиться как за 1 час, так и за 3-4 часа.

Помимо производительности и удобства есть и другой критерий надежность. Вывод, казалось бы, довольно банален: чем менее надежен бэкенд, тем легче его бэкапить. Хотя Vault сам по себе позиционируется как приложение категории cloud native, его бэкап полностью зависит от выбранного бэкенда, и во многих случаях мы получим простой, что недопустимо для такого важного сервиса:

• В Consul можно ожидать проблем с чтением и записью при бэкапе/восстановлении данных.

• А у PostgreSQL при восстановлении.

• У GCS проблема другого характера: нет гарантий на скорость копирования.

Получается, что все эти решения имеют серьёзные недостатки, которые могут быть недопустимы в production. Понимая это, в HashiCorp и создали своё оптимальное решение Integrated Storage (Raft). В нём получается делать бэкапы полностью беспростойно и при этом быстро.

В итоге: если вам важна максимальная скорость для снятия и восстановления бэкапов, то подойдут PostgreSQL и Raft. В первом случае, однако, надо повозиться с удобством: чтобы все правильно настроить и автоматизировать, придется потратить время (и иметь экспертизу). Наш текущий выбор пал на Integrated Storage (Raft) как самый простой в использовании и надежный бэкенд.

P.S.

Читайте также в нашем блоге:

• Сравниваем производительность HashiCorp Vault с разными бэкендами;

• Как мы Elasticsearch в порядок приводили: разделение данных, очистка, бэкапы.

Однажды мне потребовалось забирать регулярно относительно большие объемы данных в MS SQL из PostgreSQL. Неожиданно выяснилось, что самый очевидный способ, через Linked Server на родные ODBC к PostgreSQL, очень медленный.

История вопроса

На этапе прототипирования все было хорошо. Просто потому, что протипировалось всего несколько тысяч записей. Как только перешли к разработке, сразу возникло подозрение, что с производительностью что-то не то:

SET STATISTICS TIME ONDECLARE  @sql_str nvarchar(max)DROP TABLE IF EXISTS #tCREATE TABLE #t (  N int,  T datetime)SELECT @sql_str='  SELECT N, T  FROM generate_series(1,1000,1) N  CROSS JOIN generate_series($$2020-01-01$$::timestamp,    $$2020-12-31$$::timestamp, $$1 day$$::interval) T'INSERT #t (N, T)EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Такой простейший пример выборки всего 366 тысяч записей оказался жутко медленным:

SQL Server Execution Times:   CPU time = 8187 ms,  elapsed time = 14793 ms.

Решение

В первую очередь, захотелось исключить самый подозрительный элемент - ODBC. К тому времени MS уже предоставлял утилиту bcp для Linux. Поэтому bcp был установлен на сервер, где работал PostgreSQL и проведен следующий тест:

SET STATISTICS TIME ONDECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'DROP TABLE IF EXISTS ##tCREATE TABLE ##t (  N int,  T datetime)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##t'' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'','\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Результат сразу порадовал, причем сильно:

SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 881 ms.

Реализация

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

Вторая проблема в том, что в обычную временную таблицу bcp записать не может. Он ее просто не увидит. Значит нужно использовать постоянную таблицу или глобальную временную.

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

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

Итоговое решение следующее:

DECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'SELECT @sql_str='  DROP TABLE IF EXISTS ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+'  CREATE TABLE ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' (    N int,    T datetime  )'EXEC (@sql_str)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##proxy_table_'''    +CONVERT(nvarchar(max),@@SPID)+' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Пояснения

В PostgreSQL команда COPY может писать в файл или на стандартный ввод вызываемой ей программы. В данном случае вместо программы использован скрипт на sh. Вывод COPY, поступающий на стандартный ввод, записывается во временный файл с уникальным именем, форимруемым mktemp. К сожалению, bcp не умеет читать данные со стандартного ввода, поэтому приходится ему создавать файл.

Для совместимости формата, формируемого командой COPY и формата, ожидаемого bcp, обязательно следует указывать в COPY параметр NULL $nil$$nil$

Остальные параметры bcp:

• -c - символьный формат, так как бинарный формат PostgreSQL не совместим с бинарным форматом MS SQL и мы вынуждены использовать только символьный;

• -b - количество записей, вставляемых одной транзакцией. В моей конфигурации десять миллионов оказалось оптимальным значением. В иной конфигурации это число, скорее, может потребоваться уменьшить, чем увеличить;

• -a - размер пакета. В нашем случае лучше указывать сразу максимальный. Если сервер не поддерживает указанную длину пакета, то просто будет использована максимальная длина пакета, поддерживаемая сервером.

Если кто-то знает более быстрый способ получения данных на MS SQL из PostgreSQL - буду очень рад увидеть описание этого способа в комментариях.

Что такое iDempiere?

iDempiere ERP/CRM - это бесплатное ПО с открытым исходным кодом. Имеет функционал Tier II ERP, CRM, SCM, POS, Promotion, Campaign management и, в принципе, много чего ещё.

Важно понимать, это не просто бизнес-приложение - это Java платформа/конструктор для low-code разработки разных, ориентированных на работу с базой данных, бизнес-приложений c web интерфейсом. Во времена, когда многие пытаются построить бизнес-приложение с нуля, хорошо помнить о том, что существуют подобные фреймворки, основанные на принципах ООП, с миллионами строк проверенного кода, "активным словарём данных", который позволяет управлять сущностями, правилами проверки, окнами, таблицами, форматами и другими настройками приложения, не прибегая к кодированию вообще или же применяя небольшие, в несколько строк, инъекции кода.

Входит в широко известное за рубежом семейство "...piere" - Compiere, Adempiere, Open Bravo, Metafresh. Отличается от одних из них полным отсутствием pay wall, от других - модульной (плагинной) архитектурой (менять функциональность системы можно на лету, обновляя тот или иной плагин OSGi).

Каждый год на Хэллоуин сообщество проекта выпускает обновлённую версию. Осенью 2020 года вышла версия 8.2.

"Кривая обучения команды по iDempiere ERP/CRM была пройдена ранее, на предыдущих проектах. За нашими плечами есть проект на промышленном предприятии: 500 активных пользователей в день, база около 500 Гб, 100-400 млн. запросов к базе в сутки."

Если верить официальным сайтам этих близкородственных систем, а также зарубежных фирм-внедренцев, данное семейство ПО используется в Ив Роше (Франция), Декатлоне (Индия), Цирке дю Солей и в тысячах безвестных фирм, от Канады до Индонезии, в разных секторах, от производства молока до банковского сектора.

iDempiere работает с PostgresSQL >=9.6 или Oracle 11G/12C. Даёт кластеризацию серверов приложений и балансировку нагрузки с помощью HAProxy и Hazelcast. Имеет write to Master & report from hot replicas, два движка автоматизации бизнес-процессов ("строгий" бизнес-процесс и бизнес-процесс для кейс-менеджмента) и мн. др. фичи, делающие её вполне пригодной для большой индустрии.

Данный "панегирик" не что иное, как благодарность данной системе в режиме word of mouth за её возможности, которыми уже не раз приходилось пользоваться, в полном соответствии с философией open source и sharing.

"300 магазинов разбросаны на площади 450 тысяч км. кв., что примерно равно площади Испании. Объезжать их все, даже на моноколесе, - дороговато."

В чем состояла задача в данном проекте?

В 300 магазинах, которые работают по франшизе бренда и в которых сложился разношёрстный ИТ-ландшафт различных POS-систем и зачастую нет постоянного айтишника, развернуть программу лояльности, которая максимально соответствует пользовательской истории Заказчика (бренда), работает мгновенно и готова к omnichannel. Часть этой пользовательской истории мы написали и переписали вместе с Заказчиком.

Вот чем в процессе этой дискуссии мы озаботились.

Почему программы лояльности вызывают скепсис у покупателя?

Еcли верить большим дядям типа Forrester, этот скепсис есть во всем развитом мире, а не только у нас. 60-70% всех покупателей являются членами хотя бы одной программы лояльности. Но быть членом это одно, а реально пользоваться - это другое. Как минимум 50% "участников" среднестатистической программы лояльности ею не пользуются. Если коротко и только о главном, то вот почему:

• Проблемы бренда:

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

  • Если customer experience в физическом магазине бренда сильно хромает: нужного товара нет, продавец о товаре ничего рассказать не может, в магазине плохо пахнет, за разменом посылают в соседнюю лавку и т.д. Другими словами, если базовые запросы покупателя при общении с брендом не удовлетворены, то программа лояльности будет уместна, как на корове седло. Лучше уж сфокусироваться на улучшениях в торговой точке.

• Проблемы самой программы лояльности:

  • Правила участия и получения бенефитов замороченные

  • А награда находит покупателя не сразу, а только потом - отсутствует instant gratification.

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

"Тут и объяснять ничего не надо - каждой программе лояльности нужен BI и всё тут. Для нашего проекта мы опять решили использовать open source - Metabase. Заказчику понравилось"

Какие принципы программы лояльности мы с заказчиком в итоге сформулировали?

Возможно, вам это всё покажется прописными истинами, но всё же.

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

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

В центре программы лояльности - покупатель

Строить отношения в итоге нужно не картой и даже не сегментом, а с индивидом. Карты это всего лишь идентификатор, они со временем теряются, заменяются и т.д. Удивительно, но общение с знакомыми маркетологами показывает, что не всегда и не всем это очевидно. В итоге твой offer engine должен уметь работать с точностью до индивидуального покупателя.

Вознаграждение покупателя должно быть практически мгновенным

Баллы покупателя, заработанные им при покупке, должны быть доступны для списания не на следующий день, а сразу, при следующей покупке, не важно, в этом же твоём магазине или уже в онлайне (если у тебя есть онлайн). То есть все твои сервисы, и оффлайновые и онлайновые, должны работать с одними данными покупателя и при этом работать почти мгновенно.

Регистрация в программе лояльности за секунды. И никаких ФИО, пожалуйста!

Понятно, что бумажные анкеты, которые "завтра обработают в офисе" это безнадёжный олдскул. Зарегистрироваться твоему покупателю в твоей программе лояльности должно быть не сложнее, чем послать 1 СМС. Ещё лучше, если это в два клика сделает продавец прямо в магазине. И, боже мой, конечно, не надо спрашивать Имя-Фамилию-Отчество! Спроси "Как к Вам можно обращаться?". Если хочет покупатель быть Василисой Прекрасной, Darth Vader или Zaya, пусть будет, мы не на пограничном контроле. Твоя задача - максимально снизить боль покупателя при регистрации, и дать возможность покупателю быть тем, кем он хочет, это лояльно по отношению к нему. Удивительно, но сколько же людей из сферы маркетинга порываются мыслить терминами "ФИО"!

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

Легче сказать, чем сделать. В идеале должны быть говорящие ценники, а также информативные чеки: на чеках, помимо начислений и списаний баллов, нужно печатать покупателю его/ее статус в программе плюс оставшийся путь до следующего уровня бенефитов. Ещё лучше, если монитор покупателя может отражать всё это прямо при покупке (с уважением к privacy, конечно).

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

Даже если онлайн-магазина у тебя ещё нет, всё равно архитектуру и логику своего offer engine закладывай под

• многоканальность (физический магазин, онлайн-магазин, приложение, бот в Телеграме и т.д.)

• каждая торговая точка - потенциально отдельный канал (позитивно аукнется, например, при открытиях новых магазинов или при региональных промо)

• сосуществование множества акций одновременно (в т.ч. "противоречащих" друг другу)

Акции должны быть настраиваемы для одного отдельного канала (отдельная торговая точка, интернет магазин, приложение, телеграм-бот и т.д.), либо для набора из нескольких каналов, либо для всех каналов. Что там маркетологу будет угодно.

Скорость и надёжность, само собой

Прикинь сразу максимально возможное количество транзакций в твоей программе лояльности, в пиковый день, в пиковый час. Новые торговые точки, сезонность, праздники, все дела. В нашем случае мы с заказчиком заложились на 900'000 транзакций в день.

Подарочные карты

Надо, Федя, надо. Это профессионально, поэтому, рано или поздно неизбежно. По крайней мере, заложи в архитектуру заранее.

FIFO для баллов программы лояльности

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

Анализируй это!

Тут и объяснять ничего не надо - каждой программе лояльности нужен BI и всё тут. Для нашего проекта мы опять решили использовать open source - Metabase. Заказчик остался доволен.

Итак, проблемы проекта

1. 300 магазинов разбросаны на площади 450 тысяч км. кв., что примерно равно площади Испании. Объезжать их все, даже на моноколесе, - дороговато.

2. Да, магазины работают по франшизе, но исторически сложилось, что каждый предприниматель использует POS систему, выбранную по своему усмотрению (уже была; брат/сват уже использует и хвалит и т.д.) 99.9% всех рассматриваемых предпринимателей используют 1С разных версий и конфигураций. Затевать проект по переводу всех на один единственно правильный вариант POS, перестройке работы и переобучению всех сотрудников этой многоликой Испании, да еще в условиях пандемии, заказчик посчитал слишком дорогим по времени, а значит, по деньгам.

3. ИТ-экспертиза в этих магазинах имеет преходящий характер. Или приходящий.

4. НСИ только частично синхронизирована.

5. Период полного развёртывания программы лояльности во всех магазинах был определён как 6-9 месяцев от начала до конца.

"Штатные возможности iDempiere ERP/CRM для предметной области retail & promotion были полностью в нашем распоряжении..."

Сильные стороны, или почему всё получилось

1. Практически все магазины имели на начало проекта стабильный интернет, достаточный для работы веб-сервисов. А те, что не имели, могли легко и дёшево его получить.

2. Предприниматели, работающие по франшизе бренда, смогли мобилизовать свои скромные ИТ ресурсы на относительно локальную ИТ-пертурбацию в виде нормализации НСИ и подключения к разработанному для них API.

3. Кривая обучения команды по iDempiere ERP/CRM была пройдена ранее, на предыдущих проектах. За нашими плечами есть проект на промышленном предприятии: 500 активных пользователей в день, база около 500 Гб, 100-400 млн. запросов к базе в сутки.

4. Штатные возможности iDempiere ERP/CRM для предметной области retail & promotion были полностью в нашем распоряжении:

   1. Вкладка Контрагент и её разные подчиненные вкладки (Контакты, Адреса, Сферы интересов, и ещё пара десятков других) и их готовая логика;

   2. Вкладка Промоакция и её подчиненные вкладки (Условия срабатывания, Распределение количества, Вознаграждение для покупателя, Строка промоакции и др.) и их готовая логика;

   3. Вкладка Группы Промоакций;

   4. Вкладка Рекламные кампании;

5. Плюс штатные фичи iDempiere из смежных областей, которые тоже пригодились:

   1. Сильный буржуйско-бухгалтерский функционал по GL (General ledger - Главная книга) и учёту дебиторки/кредиторки;

   2. Работа со множеством валют в учёте (баллы - это просто ещё одна валюта, пока что с курсом 1:1 к рублю);

   3. Встроенный Report Cubeи таблица Fact_Acct_Summary, хранящая балансы финансовых транзакций (отличная от таблиц, содержащих движения);

   4. Кластеризация серверов приложений и балансировка нагрузки с помощью HAProxy;

   5. Поддержка REST web servicesдля обмена через JSON формат;

   6. Schedulers - Планировщики. Штатный функционал iDempiere, RPA процессы, которые запускаются по расписанию или, с небольшой модификацией, по событию, на отдельном служебном сервере приложений и делают своё дело. У вас могут быть сотни планировщиков, если это надо. В нашем случае планировщики проводят начисления бонусов по всем необходимым таблицам движения, еженощно сжигают устаревшие бонусы по методу FIFO, отслеживают и исправляют экзотические, проблемные транзакции (возврат приходит раньше продажи, например, из-за того, что моргнул интернет в магазине).

   7. Workflowengine - один из 2х встроенных движков автоматизации бизнес-процессов, может настраиваться на работу по событию, может пронизывать все ситуации, департаменты, сущности (читай - таблицы).

   8. Write to master database, report from hot replicasстандартная настройка, позволяющая отправлять все отчёты, включая автоматические, на одну или множество горячих реплик вашей базы данных;

   9. Возможности кеширования в памяти позволяют быстро извлекать данные, когда хочется получить максимальную скорость.

   10. Возможность расширять функционал, добавляя окна, вкладки разного уровня, поля, процессы, коллауты и т.д. Ну и понятно, полностью открытый исходный Java код, который можно инспектировать и улучшать (самостоятельно или взаимодействуя с сообществом), если хочется или надо.

Как в итоге всё работает?

1. В программе лояльности активировано уже 800'000 покупателей.

2. 99.9% из 300 магазинов имеют ту POS, которую имели до проекта.

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

4. Покупатели, их контакты и адреса, их транзакции, их балансы, их промо, другими словами собственно все данные, которые и обеспечивают функционал программы лояльности - все хранятся в централизованной бэк-офис ERP/CRM системе.

5. POS-система в онлайне общается с этой централизованной бэк-офис системой всякий раз, когда вызывается программа лояльности. Даже слова и цифры из программы лояльности, которые нужно напечатать многоуважаемому покупателю/покупательнице на его/её кассовом чеке - передаются из центра (расшифровка акций в чеке, текущий статус в программе лояльности и т.д.).

6. В зависимости от сценария поведения покупателя (только начислять баллы или начислять-списывать) на одну продажу приходится до 3х транзакций обмена данными. Они занимают в среднем 50, 200 и 200 миллисекунд соответственно.

7. Бонусы, начисленные на только что состоявшуюся покупку, доступны для списания во время следующей покупки не позднее 1 минуты.

В конце прошлого года Иван Панченко предложил мне рассказать на внутреннем семинаре Postgres Pro, чего, по нашему опыту использования PostgreSQL в "кровавом энтерпрайзе" "Тензора", не хватает в этой СУБД.

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

Обслуживание сервера

Легковесный менеджер соединений

он же Built-in connection pooler

Сейчас каждое соединение с PostgreSQL инициирует создание на сервере полноценного дочернего процесса, который занимается его обслуживанием и выполнением запросов.

Процесс старта ресурсоемкий и небыстрый сам по себе, а постоянно существующий процесс еще и резервирует на себя некоторую долю RAM, которая со временем имеет свойство расти за счет "накачки" метаинформацией.

Просто представьте, что у вас в базе развернуто миллион таблиц, к которым вы достаточно случайно обращаетесь. Только на системных таблицах pg_class, pg_depend, pg_statistics это даст объем порядка 1GB, который рано или поздно окажется в памяти процесса.

Поэтому в реальных условиях соединения непосредственно с БД стараются беречь, без нужды новые не создавать, а неактивные закрывать поскорее. Для этого "в разрыв" БЛ-БД ставят отдельный сервис connection pooler, который принимает от БЛ все "хотелки" по открытию/закрытию соединений и выполнению запросов, а в БД транслирует их в ограниченное количество длительно живущих коннектов.

Классические представители:

• pgbouncer

• Odyssey

• Pgpool-II

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

Поэтому еще в начале 2018 года Константин Книжник начал эксперименты с реализацией менеджера соединений "из коробки" на основе интеграции потоков (thread model) в ядро PostgreSQL. В настоящий момент его патч включен на ревью в июльский Commitfest, так что ждем и надеемся на появление хотя бы в v15.

64-bit XID

Если вы пишете в базу много данных, как мы, то достаточно скоро у вас запустится он - autovacuum (to prevent wraparound), чья единственная задача - пробежать по данным и "поправить" их так, чтобы уберечь счетчик транзакций от переполнения.

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

А чтобы никаких подобных процессов не запускалось, достаточно расширить идентификатор транзакции xid с 32 до 64 бит - соответствующий патч Александр Коротков запустил еще в 2017 году. В ядро он тогда так и не был включен, зато попал в Postgres ProEnterprise, откуда рано или поздно доберется и до "ванильного" ядра.

Система хранения данных

Микротаблицы

Каждая таблица и индекс в PostgreSQL с точки зрения хранения представляет из себя не меньше 3 файлов:

• heap и его сегменты

• _fsm - Free Space Map

• _vm - Visibility Map

То есть если вам необходимо иметь небольшую статичную "словарную" табличку на пару десятков записей, то вы автоматически получаете 3 файла по 8KB, хотя можно было бы обойтись и единственной страницей heap.

Поэтому такая табличка, созданная в каждой из клиентских схем, где изолированы пользовательские данные, сразу дает резкое увеличение количества файлов на носителе (миллионы их!), что негативно влияет как на время бэкапа, так и на производительность всей дисковой подсистемы.

zheap

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

Почитать по теме:

• DBA: когда пасует VACUUM чистим таблицу вручную

• DO or UNDO - there is no VACUUM

• Zheap: A Storage Engine to Provide Better Control Over Bloat

• zheap: Reinvented PostgreSQL storage

• zheap: Development status and progress reporting

Append-only Storage

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

Знание этого факта позволяет существенно сэкономить записываемый объем. Например, переведя базу нашего сервиса мониторинга с v12 на v13, мы сразу получили примерно 10% выигрыша в объеме индексов за счет дедупликации в btree-индексах на реальных данных.

В эту же категорию можно отнести уже принятый патч "BRIN multi-minmax and bloom indexes".

В идеале, хотелось бы иметь возможность назначать часть полей индексов или целых таблиц как "no-MVCC" (то есть не поддерживается никакое версионирование, а потому - и изменение данных, плюс мгновенная видимость со стороны других транзакций), чтобы иметь возможность сэкономить на полях поддержки MVCC.

Отложенная индексация

Одной из причин Uber-скандала, всколыхнувшего PostgreSQL-сообщество несколько лет назад была Index Write Amplification, когда записываемый в таблицу кортеж сразу же одновременно записывается и во все индексы, подходящие по условию для него. Получается, чем больше индексов есть на таблице, тем дольше будет производиться вставка в нее.

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

CREATE INDEX ... WITH (max_lag = '1min');

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

Почитать по теме:

• On Ubers Choice of Databases

• Uber's move away from PostgreSQL

• Uber - причины перехода с Postgres на MySQL

• DevConf: переход Uber с PostgreSQL на MySQL

• РИТ++ 2017: Наш ответ Uber'у

Columnar Storage

В идеале - в ядре или в contrib иметь возможность подключения колоночного хранилища для различных аналитических нужд.

Да, есть решения от Citus, но "в энтерпрайзе" не всегда есть возможность установки дополнительных внешних модулей.

In-memory Storage

Появление очень быстрого нетранзакционного хранилища без сброса на диск сильно помогло бы использовать разноуровневые кэши прямо в PostgreSQL, а не выносить их куда-то в Redis, например - получился бы некий аналог Oracle TimesTen In-Memory Database и Tarantool.

Масштабирование

TEMPORARY TABLE и реплики

Если для генерации отчетов в своей системе вы предпочитаете не писать мегасложные запросы, а более простые итерации по временным таблицам, то неприятностей будет ровно две:

• при частом пересоздании TEMP TABLE начинает "пухнуть" pg_class

• эту нагрузку невозможно вынести на реплику, поскольку там TEMP TABLE не работает

И если с первой проблемой еще как-то можно смириться и исправлять регулярным VACUUM pg_class, то второй недостаток достаточно сильно мешает.

Multimaster

Давно хочется иметь "из коробки".

SQL

SQL-defined Index

Было бы фантастично уметь описывать новые виды индексов прямо на SQL/PLPGSQL, без необходимости C-кодинга - фактически, тут нет ограничений, кроме производительности из-за необходимости сделать все "здесь и сейчас". Но если вспомнить про описанную выше возможность отложенной индексации, то задача уже не кажется такой уж нереальной.

Ведь если можно описывать на различных языках логику формирования и преобразования записи таблицы, то почему бы не позволить то же самое для записи индекса?..

Почитать по теме:

• SQL HowTo: префиксный FTS-поиск с релевантностью по дате

Мониторинг

В инфраструктуре PostgreSQL сильно не хватает удобных средств быстрого визуального контроля и анализа происходящего с производительностью базы. Тут мы в меру собственных сил стараемся создавать подобные инструменты и формировать некоторую базу возможных оптимизаций.

Производительность запросов

Сам анализ, куда ушло время в запросе по тексту плана является нетривиальным, но если его прогнать через визуализацию на explain.tensor.ru, становится попроще:

Почитать по теме:

• О чем молчит EXPLAIN, и как его разговорить

• Рецепты для хворающих SQL-запросов

• PostgreSQL Antipatterns: убираем медленные и ненужные сортировки

Снапшоты статистики таблиц

Чтобы определить, в каком именно таблице/индексе "сильно болит", у Oracle есть AWR а наиболее близким аналогом для PostgreSQL является pg_profile.

Но идея хранить дополнительные данных ровно в той же базе, на которой и так идут проблемы (иначе зачем вам нужен столь подробный мониторинг?), нам показалась не слишком эффективной. Поэтому мы вынесли съем, хранение и визуализацию этой информации на отдельный сервис, который собирает у нас внутри компании всю статистику о работе PostgreSQL-серверов.

Почитать по теме:

• Простой инструмент исторического анализа производительности - pg_profile

• Массовая оптимизация запросов PostgreSQL

• Мониторим базу PostgreSQL кто виноват, и что делать

Это - что напрягает нас в этой, безусловно, отличной СУБД. В комментариях оставляйте рассказы о своих "болях" и "хотелках" при использовании PostgreSQL.

В эфире снова Радио SQL, здравствуйте, согалактчики!

Сегодня у нас обещанный разбор задачи на поиск последней цены. Прошёл как раз земляной месяц. У вас же 60 солов в месяце, да? Я немного путаюсь во всех этих ваших неметрических то 12-ти, то 60-тиричных системах времени. Впрочем, перейдём к делу.

Напомню условие, а чтобы оно не занимало много места в и без того длинной статье, спрячу его под спойлер:

with price(stock_id, prod_id, start_date, kind, price1, cost1, bonus1) as (values (1,1,to_date('2000-01-01','YYYY-MM-DD'),'R',100.0,32.12,6.49),       (1,1,'2000-01-02','P', 80.0, 0,   0),       (1,1,'2000-01-03','P', 70.0, 0,   0),       (1,1,'2000-01-04','R',110.0,33.48,6.19),       (1,1,'2000-01-05','P', 90.0, 0,   0),       (1,1,'2000-01-06','R',120.0,41.22,6.19),       (1,1,'2000-01-07','P', 80.0, 0,   0),       (1,1,'2000-01-08','P', 90.0, 0,   0),       (1,1,'2000-01-09','R', 93.0,36.87,6.49),       (1,1,'2000-01-10','R', 94.0,36.85,6.99),       (1,2,'2000-01-01','R',101.0,52.06,9.00),       (1,2,'2000-01-02','P', 81.0, 0,   0),       (1,2,'2000-01-03','P', 71.0, 0,   0),       (1,3,'2000-01-04','R',111.0,64.96,4.50),       (1,3,'2000-01-05','P', 92.0, 0,   0),       (1,3,'2000-01-06','R',122.0,66.83,4.60),       (1,3,'2000-01-07','P', 82.0, 0,   0),       (1,3,'2000-01-08','P', 92.0, 0,   0))select ...

 stock_id | prod_id | start_date | kind | price1 | cost1 | bonus1 | price1x ----------+---------+------------+------+--------+-------+--------+---------        1 |       1 | 2000-01-01 | R    |  100.0 | 32.12 |   6.49 |   100.0        1 |       1 | 2000-01-02 | P    |   80.0 |     0 |      0 |   100.0        1 |       1 | 2000-01-03 | P    |   70.0 |     0 |      0 |   100.0        1 |       1 | 2000-01-04 | R    |  110.0 | 33.48 |   6.19 |   110.0        1 |       1 | 2000-01-05 | P    |   90.0 |     0 |      0 |   110.0        1 |       1 | 2000-01-06 | R    |  120.0 | 41.22 |   6.19 |   120.0        1 |       1 | 2000-01-07 | P    |   80.0 |     0 |      0 |   120.0        1 |       1 | 2000-01-08 | P    |   90.0 |     0 |      0 |   120.0        ...

Задачу эту можно решать разными способами. Начнём с самого простого:

Первый подход

Это решение в лоб. Так как у нас к каждой записи исходного набора данных нужно выбрать какие-то значения, то сделаем это подзапросом:

select * -- выбрать все поля из источника данных       -- а тут подзапрос, выбирающий нужную цену     , (select price1          from price sub         where sub.stock_id = p.stock_id -- те же склад           and sub.prod_id  = p.prod_id  -- и товар,           and sub.kind = 'R'            -- оставим только настоящие цены           and sub.start_date <= p.start_date  -- с датой более ранней или такой же,         order by start_date desc        -- отсортируем в порядке последние цены раньше         limit 1                         -- и возьмём только первую строку       ) as price1x  from price p;

Всё очень прямолинейно и просто, комментарии объясняют, что и как выбирается. Самое главное обеспечить, чтобы такой подзапрос возвращал не более одной записи, а то будет ошибка. Попутно отметим, что если записей не нашлось, что запрос вернёт NULL. В принципе это логичное поведение, но если дальше эти цены будут участвовать в каких-то расчётах, то это надо будет принять во внимание.

Отлично, мы сделали это! Запускаем! Можно расслабиться, прыснуть себе чего-нибудь зободробительного в жвалы, сделать первый (и самый вкусный) гулп... И пока запрос работает, давайте немного отвлечёмся и поразмышляем об его эффективности.

Итак, по условию у нас исходным набором данных была сборная вьюха. Что она там и как выбирает и не перелопачивает ли для этого полбазы неизвестно. Но сборная вьюха и эффективность это обычно понятия плохо совместимые. То есть ожидания от вьюхи что она тормознутая. И второй неутешительный вывод, который просто напрашивается: сборная вьюха практически не оставляет надежд на наличие индексов, так что в подзапросе, чтобы найти нужный склад и нужный товар на нём, скорее всего придётся прочитать всю вьюху целиком. На каждый подзапрос. А если она в самом деле полбазы вычитывает? Миллион строк, да для каждой полбазы перечитать... Вот так на ровном месте и без использования каких бы то ни было спецсредств можно поставить на колени практически любую базу. И мы получим highload на ровном месте. Вернее в highload это превратится, если оптимизатор найдёт способ распараллелить выполнение запроса, так что может быть всё ещё не так уж плохо. У меня на тестовых данных, кстати говоря, сумел.

А ну-ка запустим в соседнем окошке select count(*) from price и внимательно посмотрим как на количество записей, так и на время исполнения. Потому что именно эти два числа нужно будет умножить друг на друга, чтобы прикинуть общее время выполнения запроса. Наш запрос будет работать хоть сколько-нибудь обозримое время, только если количество записей не будет превышать десятков или сотен, ну максимум тысяч записей. Так что полюбовались на количество записей, и давай, тяни свои ложноножки (или ложноручки, что там у тебя) к консоли и прерывай запрос, пока не прилетели админы, чтобы убедительно объяснить на пальцах, что надо и что не надо делать на боевом сервере.

Подход второй

Второе, что приходит в голову, глядя на эту задачу это отфильтровать все реальные цены в отдельный набор данных и соединить (с-JOIN-ить) с самим исходным набором по условию, весьма смахивающему на то, которое мы использовали в подзапросе в подходе номер один.

Это в целом годный и рабочий способ, исходная вьюха скорее всего будет прочитана только два раза, если вдруг оптимизатор не найдёт более эффективный план выполнения (что вряд ли) или банально не ошибётся. Это даст хорошую производительность, но... Что же но? Почему не хочется так делать? А всё очень просто. Такая конструкция в виде соединения двух наборов данных с витиеватыми условиями, включая выборку только нужных данных, довольно капризна. В этих декартовых соединениях легко ошибиться и потерять часть исходных данных или наоборот, выбрать чего-то лишнего.

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

Так что оставим этот способ напоследок, а сперва попробуем способ получше, и это...

Подход третий

Что там у нас сделать-то надо было? Сгруппировать и для каждой группы выбрать значение?.. Ба, да сюда же прямо просятся аналитические функции! Они как раз предназначены для подобных задач, когда нужно что-то сгруппировать и выбрать для каждой группы. Если при этом нужно оставить только значения для каждой группы, то используются аналитические функции, а если нужно сохранить весь исходный набор данных то оконные.

Ну-ка, попробуем. Для каждой строки нам нужно выбрать ближайшую реальную цену, то есть надо использовать именно оконные функции. Определить окно, в которое попадут только нужные записи, отсортировать и выбрать значение из нужной строчки окна:

select *     , agg_func(...) over (...) -- тут нужно будет определить окно и найти подходящую функцию  from price

Казалось бы всё просто. Функция будет из тех, которые возвращают одно из значений в группе, это first_value() или lag() или что-то подобное. А вот с определением окна нужно немного помудрить. Оконные функции позволяют определять окно, указав группировку и сортировку. Понятно, что в определении окна будет partition by stock_id, prod_id и что-то надо добавить, чтобы ближайшая предыдущая строчка с реальной ценой встала на фиксированное место в этой группе. Если это не получается сделать в лоб (как, например, если бы нам надо было выбрать просто самую первую или минимальную цену), то обычно помогает такой приём, когда определяют специальное вычислимое поле и по нему делают или группировку, или сортировку. Навскидку пишем case when kind='R' then, потому что от поля kind у нас точно есть зависимость, и задумываемся... Мнэ...

Как выразился один из комментаторов предыдущей статьи, неожиданно стало интересно. Сходу найти подходящее под условие выражение, чтобы отделить группу записей, начинающихся со строки 'R', от других, что-то не получилось. И не сходу тоже как-то не получилось...

Решение постепенно нашлось, и вот какой приём при этом был применён. Получилась двухходовка. На первом этапе формируем специальное вычислимое поле, по которому на втором этапе уже выбираем нужные значения. Поле (назовём его уровнем цены) формируем так: суммируется нарастающим итогом для каждого склада и товара в порядке даты начала действия следующее: единица для строк с kind='R' и ноль для всего остального. Получается как раз, что уровень цены перещёлкивается на следующий, как только мы встречаем реальную цену:

select *     , sum(case when kind='R' then 1 else 0 end) -- сумма нарастающим итогом       over(partition by stock_id, prod_id       -- в разрезе складов и товаров            order by start_date                  -- порядок важен!            rows between unbounded preceding and current row) as lvl -- нам нужно именно до CURRENT ROW  from price

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

select *     , first_value(price1) over (partition by prod_id, stock_id, lvl                                     order by start_date) as price1x  from (select ...)

Собрать вместе можно хоть через подзапрос, хоть с CTE. Смотрим на результаты выполнения вычисляется за один проход, то есть в высшей степени эффективно (достат кол!).

Итого, вот оно решение:

select p.*     , first_value(price1) over (partition by prod_id, stock_id, lvl                                     order by start_date) as price1x  from (select *             , sum(case when kind='R' then 1 else 0 end)               over(partition by stock_id, prod_id order by start_date                    rows between unbounded preceding and current row) as lvl  from price) p

Дальше, если надо будет вытащить остальные значения из нужной строчки, то можно повторить first_value() нужное количество раз. Для компактности можно вынести определение окна отдельно, чтобы не загромождать запрос:

select *     , first_value(price1) over w as price1x     , first_value(cost1)  over w as cost1x  from (select *             , sum(case when kind='R' then 1 else 0 end)               over(partition by stock_id, prod_id order by start_date                 rows between unbounded preceding and current row) as lvl          from price) pwindow w as (partition by prod_id, stock_id, lvl order by start_date)

Вот собственно и всё, на этом можно закончить, реализация второго подхода не понадобилась.

Выводы

1. Во время составления SQL-запроса имеет смысл думать про эффективность его работы.

2. Использовать оконные функции отличная идея.

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

4. Если оконную функцию применить так и не удалось, то подход 2 даст нужные результаты, а если время работы запроса не является проблемой, то подход 1 даже лучше, потому что он прост и понятен.

Разбор решений, приведённых в комментариях

В статье с условием задачи я пообещал сделать разбор решений из комментариев с раздачей каких-нибудь слонов. Выполняю обещание.

Так или иначе я насчитал 11 разных вариантов решения от 10 представителей. Вообще запросов было приведено больше, но часто следующий SQL-запрос в цепочке комментариев был развитием или исправлением предыдущего. Так что количество сильно зависит от того, как считать. Пройдёмся по хронологии.

Статья с условием задачи была опубликована 16 марта 2021 в 17:12. Первое решение от @nice17 16 марта 2021 в 17:34 было предложено уже через 22 минуты после публикации статьи (22 минуты, Карл! и ещё же надо было хоть по диагонали прочитать условие). Решение было неверным, но подход к решению явно был уже в нужном направлении. Чуть позже автор довёл его до правильного. А первое более-менее работающее решение (от @ZMB138 16 марта 2021 в 19:07) появилось меньше чем через два часа(!) после публикации, это была реализация подхода 2.

Первое решение в третьем подходе от @Kilor появилось ещё спустя полчаса 16 марта 2021 в 19:39. Автор применил хитрый ход, использовав функцию array_agg(), которая допускает использование FILTER в определении окна, что позволило отфильтровать только записи с реальными ценами и избежать рассмотренной мной двухходовости. Также продемонстрирован интересный ход, что вся найденная для сопоставления строка таблицы упаковывалась в JSON, из которого дальше выбиралось нужное поле. Или все нужные поля. Не очень универсально в плане разных диалектов SQL, но изящно. Последняя версия запроса от 17 марта 2021 в 10:18 уже без упаковки/распаковки в JSON получилась компактной и эффективной.

(замечание в сторону) Воистину, вырисовывается универсальный способ, как можно решать сложные задачи на SQL публиковать их на хабре, и дальше остаётся только подождать пару часов!..

Аналогичный подход использовал @AngelloreA в запросе от 16 марта 2021 в 22:30. Сопоставляемая строка с реальными стоимостями упаковывалась правда не в JSON, а просто в текст, что потенциально чревато проблемами при распаковке (надо бы явно указать форматы), но это работает и тоже в один проход, то есть получилась реализация подхода 3.

@viras777 16 марта 2021 в 23:07 привёл необычное решение, где весьма остроумно для генерации уровней цены используется последовательность. В целом, как мне кажется, использовать последовательность для такого случая избыточно (да и Оккам не велел), плюс побочных эффектов в программировании обычно стараются избегать, но тем не менее оно работает.

@AlexKadetov уже 16 марта 2021 к 23:10 получил по сути такое же решение в подходе 3, к которому пришёл и я, пусть даже и с некоторыми огрехами в реализации. Вообще огрехи в реализации были много у кого, я старался не придираться, потому что в реальной жизни конечно всякий запрос ещё какое-то время доводится до кондиции, уточняется его работа и всё подобное вылавливается и исправляется.

Ночью @qvan 17 марта 2021 в 01:39 привёл несколько тяжеловесное, но рабочее решение в подходе 2. Утром @xxxcoltxxx 17 марта 2021 в 09:13 привёл более явное и ясное решение в реализации подхода 2. Чуть позже @jayrumi 17 марта 2021 в 13:11 тоже опубликовал своё пусть и несколько тяжеловесное, но работающее решение тоже в подходе 2.

Дальше опять вернулся @Kilor и 17 марта 2021 в 14:30 привёл пару красивых решений в подходе 2, продемонстрировав виртуозную технику JOIN-ов наборов данных. Ближе к вечеру @Miha_S7 17 марта 2021 в 18:19 привёл своё решение в подходе 3, повторяющее полученное мной в первой части статьи.

На этом активность в комментариях заглохла, статья скрылась из леныт сайта, и больше запросов никто не публиковал.

Итого, из интересного отмечу скорость, с которой тема была раскрыта со всех сторон, включая диалекты дружественных СУБД. Плюшкополучателем был избран @Kilor, как автор самого первого однопроходного решения (в подходе 3), а также за проявленную широту охвата техники владения SQL (использование оконных функций, JSON, FILTER, JOIN-ы) и ясную по форме реализацию с использованием всего перечисленного. С ним я уже связался. Специально отмечу самое первое работающее решение от @ZMB138, оригинальность и неожиданность использования последовательностей от @viras777, а также эталонное на мой взгляд решение от @AlexKadetov.

И отдельное большое спасибо всем, кто принял участие в обсуждении и поделился своими решениями.

Всем привет. В этом посте я расскажу про pgSCV - новый экспортер метрик для PostgreSQL (и не только), чем он так хорош и какие проблемы решает.

Наверняка все кто используют Prometheus и PostgreSQL сталкивались и с postgres_exporter. Этот экспортер довольно легко запуститьи начать им пользоваться. Также у него есть возможности для расширения, на основе своего запроса можно описать метрики иснимать их. Если есть хорошие знания о том как устроена постгресовая статистика можно собрать довольнобольшое количество метрик. Но как известно кроме метрик самого Postgres, еще желательно собирать метрики системы, а если винфраструктуре есть вспомогательные сервисы, например пулеры соединений (pgbouncer, odyssey и т.п.), то и с них также нужно сниматьметрики. Выходит что нужно поставить еще экспортеров.

В pgSCV я постарался решить обе этих проблемы.

Решение первое. pgSCV сразу умеет снимать очень большое количество метрик с PostgreSQL без необходимости дополнительной настройки. На мой взгляд это сильно облегчает задачу первоначального запуска. Вместо траты времени на написание кастомной конфигурации для съема дополнительных метрик их сразу можно получить из коробки. Возможности конфигурации кастомных метрик также присутствует. Возникает резонный вопрос - а что если метрик слишком много и не все они нужны? В таком случае, можно отключить сбор метрик или ограничить список БД с которых требуется снимать метрики.

Второе решение. На данный момент pgSCV умеет снимать метрики с системы, PostgreSQL и Pgbouncer. Для этого он автоматически ищет эти сервисы и начинает собирать с них метрики. Очевидно что для сбора метрик с сетевых служб нужны реквизиты для подключения. Съем метрик не ограничивается только локальными службами. При желании можно указать сбор метрик и с сервисов размещенных на других узлах.

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

curl -O -L https://github.com/weaponry/pgscv/releases/download/v0.5.0/pgscv_0.5.0_linux_amd64.tar.gztar xvzf pgscv_0.5.0_linux_amd64.tar.gzcat << EOF > pgscv.yamldefaults:    postgres_username: "monitoring"    postgres_password: "supersecretpassword"EOF./pgscv --config-file pgscv.yaml

После запуска можно открыть вторую консоль и с помощью curl -s 127.0.0.1:9890/metrics получить список метрик.

Отмечу, что pgSCV создавался для нужд Weaponry (проект по мониторингу PostgreSQL и всего вокруг него), теперь pgSCV на мой взгляд стабилизировался и мне не стыдно его показать.

На этом всё, спасибо за внимание! Если есть идеи, пожелания или нашлись баги, то пишите в discussions или issues. Напоследок немного ссылок:

• pgSCV on Github

• pgSCV wiki

• Getting started

• Available collectors

• Available metrics

Это вольный перевод поста одного из сильных разработчиков Postgres - Andres Freund. Кроме того что разработчик сильный, так еще и статья довольно интересная и раскрывает детали того как работает ОС Linux.

Довольно часто можно слышать заявления что постгресовые соединения используют слишком много памяти. Об этом часто упоминают сравнивая процессную модель обработки клиентских соединений с другой моделью, где каждое соединение обслуживается в отдельном потоке (thread).

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

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

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

В этом посте я буду говорить о Postgres работающем на Linux, т.к. именно в этом направлении у меня больше всего опыта.

И перед тем как продолжить я хочу акцентировать внимание, что при точном и аккуратном измерении, одно соединение имеет накладные расходы на уровне меньше 2MiB (см. выводы в конце поста).

первый взгляд

Если использовать стандартные утилиты операционной системы, то можно сделать вывод, что накладные расходы существенно больше (чем есть на самом деле). Особенно если не использовать большие страницы (huge pages), то использование памяти каждый процессом действительно выглядит слишком большим. Отмечу что настоятельно рекомендуется использовать большие страницы. Давайте взглянем на только что установленное соединение, в только что запущенном Postgres:

andres@awork3:~$ psqlpostgres[2003213][1]=# SELECT pg_backend_pid(); pg_backend_pid         2003213 (1 row)andres@awork3:~/src/postgresql$ ps -q 2003213 -eo pid,rss    PID   RSS2003213 16944

Около 16MiB.

Утечки памяти!?! К счастью, нет.

При этом со временем памяти используется все больше и больше. Чтобы продемонстрировать это, я воспользуюсь расширением pgprewarm, чтобы загрузить таблицу в буфер (shared buffers):

postgres[2003213][1]=# SHOW shared_buffers ; shared_buffers  16GB           (1 row)postgres[2003213][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;  sum    383341 andres@awork3:~$ ps -q 2003213 -eo pid,rss    PID   RSS2003213 3169144

Теперь использование памяти достигло уровня 3GB. При том что, на самом деле, в этой сессии не потребовалось выделять дополнительную память. Объем используемой памяти, увеличился пропорционально объему используемого буфера:

postgres[2003213][1]=# SELECT pg_size_pretty(SUM(pg_relation_size(oid))) FROM pg_class WHERE relfilenode <> 0; pg_size_pretty  2995 MB        (1 row)

Что еще хуже, даже если эти страницы будут использовать в других сессиях, это также будет отображаться как использование большого объема памяти:

postgres[3244960][1]=# SELECT sum(abalance) FROM pgbench_accounts ; sum    0 (1 row)andres@awork3:~/src/postgresql$ ps -q 3244960 -eo pid,rss    PID   RSS3244960 2700372

Конечно, Postgres на самом деле не использует 3GB и 2.7GB памяти в данном случае. На самом деле, в случае huge_pages=off, утилита ps отображает объем разделяемой (shаred - память совместно используемая с другими процессами) памяти, включая и страницы в буфере которые используются в каждой сессии. Очевидно это приводит к значительной переоценке величины используемой памяти.

На помощь внезапно приходят большие страницы

Множество процессорных микро-архитектур обычно используют страницы размером 4KiB, но также могут использовать и страницы большего размера, например широко распространенный вариант это 2MiB.

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

Если повторить вышеописанный эксперимент с huge_pages=on, можно увидеть гораздо более приятные глазу результаты. Для начала, взглянем на "новый процесс":

andres@awork3:~$ ps -q 3245907 -eo pid,rss    PID   RSS3245907  7612

Теперь, новый процесс использует всего около 7MiB. Такое уменьшение вызвано тем что таблицы управления страницами (page table) теперь требуют меньше места, из-за того что используются большие страницы, для управления тем же объемом памяти нужно в 512 раз меньше элементов чем раньше (4KiB * 512 = 2MiB).

Теперь давайте посмотрим что произойдет при доступе к большим объемам данных в памяти:

postgres[3245843][1]=# ;SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;postgres[3245851][1]=# SELECT sum(abalance) FROM pgbench_accounts ;andres@awork3:~$ ps -q 3245907,3245974 -eo pid,rss    PID   RSS3245907 122603245974  8936

В отличие от самого первого эксперимента, эти процессы используют всего 12MiB и 9MiB соответственно, в то время как в прошлый раз использовалось 3GiB и 2.7GiB.

Разница довольно очевидна ;)

Это следствие того, как в Linux реализован учёт использования больших страниц, а не потому, что мы использовали на порядки меньше памяти: используемые большие страницы не отображаются как часть значения RSS в выводе ps и top.

Чудес не бывает

Начиная с версии ядра 4.5, появился файл /proc/$pid/status в котором отображается более подробная статистики об использование памяти процессом:

• VmRSS общий размер используемой памяти. Значение является суммой трех других значений (VmRSS = RssAnon + RssFile + RssShmem)

• RssAnon размер используемой анонимной памяти.

• RssFile размер используемой памяти ассоциированной с файлами.

• RssShmem размер используемой разделяемой памяти (включая SysV shm, сегменты в tmpfs и анонимные разделяемые сегменты)

andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3247901/statusRssAnon:    2476 kBRssFile:    5072 kBRssShmem:    8520 kBHugetlbPages:       0 kBpostgres[3247901][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;andres@awork3:~$ ps -q 3247901 -eo pid,rss    PID   RSS3247901 3167164andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3247901/statusRssAnon:    3148 kBRssFile:    9212 kBRssShmem: 3154804 kBHugetlbPages:       0 kB

RssAnon отображает объем "анонимной" памяти, т.е. участки рабочей памяти которые не являются отображанием файлов на диске. RssFile это как раз отображение в памяти конкретных файлов на диске, включая даже исполняемый файл postgres. И последнее RssShmem отображает доступную разделяемую память без учета больших страниц.

Это хорошо показывает причину того почему ps и подобные утилиты показывают большие значения используемой памяти - из-за того что учитывается разделяемая память.

И теперь взглянем на эту же статистику, но с huge_pages=on:

andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3248101/statusRssAnon:    2476 kBRssFile:    4664 kBRssShmem:       0 kBHugetlbPages:  778240 kBpostgres[3248101][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;andres@awork3:~$ grep -E '^(Rss|HugetlbPages)' /proc/3248101/statusRssAnon:    3136 kBRssFile:    8756 kBRssShmem:       0 kBHugetlbPages:    3846144 kB

Увеличиваем точность

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

Первое, на самом деле учет RssFile в использовании памяти не играет роли - по факту это всего лишь исполняемый файл и используемые им разделяемые библиотеки (Postgres не использует mmap() для каких-либо файлов). Все эти файлы являются общими для всех процессов в системе и практически не дают накладных расходов для постгресовых процессов.

Второе, RssAnon также переоценивает использование памяти. Смысл тут в том что ps показывает всю память процесса целиком, при том что большая часть этой памяти в случае создания нового процесса делится между пользовательским соединением и памятью родительского процесса postgres (так же известен как postmaster). Это следует из того что Linux не копирует всю память целиком когда создает новый процесс (при выполнении операции fork()), вместо этого используется механизм Copy-on-Write для копирования в новый процесс, набора только измененных страниц.

Таким образом, пока нет хорошего способа аккуратно и точно измерить использование памяти отдельно взятого нового процесса. Но все не так плохо, начиная с версии 4.14 ядро предоставляет еще одну статистику (коммит с описанием) процесса в /proc/$pid/smaps_rollup файле. Pss показывает "принадлежащую процессу пропорциональную долю отображения" среди всех отображений этого процесса (детали можно найти в документации поиском по smaps_rollups и Pss которые сами по себе не имеют прямых ссылок). Для сегмента памяти используемого совместно между несколькими процессами, доля будет представлять собой отношение размера этого сегмента на количество процессов которые используют этот сегмент.

postgres[2004042][1]=# SELECT SUM(pg_prewarm(oid, 'buffer')) FROM pg_class WHERE relfilenode <> 0;  sum    383341 (1 row)postgres[2004042][1]=# SHOW huge_pages ; huge_pages  off        (1 row)andres@awork3:~$ grep ^Pss /proc/2004042/smaps_rollupPss:             3113967 kBPss_Anon:           2153 kBPss_File:           3128 kBPss_Shmem:       3108684 kB

Pss_Anon включает в себя анонимную память используемую процессом, Pss_File включает память используемую под разделяемые библиотеки задействование процессом, и Pss_Shmem (если не используются большие страницы) показывает использование общей памяти разделенное на все процессы которые обращались к соответствующим страницам.

Но у пропорциональных значений есть небольшой недостаток, это использование делителя который зависит от числа подключений к серверу. Здесь я использовал pgbench (scale 1000, -S -M prepared -c 1024) чтобы создать большое число подключений:

postgres[2004042][1]=# SELECT count(*) FROM pg_stat_activity ; count   1030 (1 row)postgres[2004042][1]=# SELECT pid FROM pg_stat_activity WHERE application_name = 'pgbench' ORDER BY random() LIMIT 1;   pid    3249913 (1 row)andres@awork3:~$ grep ^Pss /proc/3249913/smaps_rollupPss:                4055 kBPss_Anon:           1185 kBPss_File:              6 kBPss_Shmem:          2863 kB

И с использованием huge_pages=on:

andres@awork3:~$ grep ^Pss /proc/2007379/smaps_rollupPss:                1179 kBPss_Anon:           1173 kBPss_File:              6 kBPss_Shmem:             0 kB

Как я понимаю Андрес намекает здесь на то что из-за большого количества процессов и соответственно большого делителя, значение Pss_Shmem становится маленьким, отчего может складываться впечатление что процесс использует мало разделяемой памяти хотя по факту мог использовать ее гораздо больше (Прим. переводчика).

К сожалению Pss значения учитывают только те ресурсы, что видны приложению. Например, размер таблицы страниц не учитывается. Размер таблицы страниц можно увидеть в уже упоминавшемся `/proc/$pid/status`.

Я не уверен, но насколько я знаю, VmPTE (размер таблицы страниц) полностью приватный для каждого процесса, но остальное большинство Vm* значений, включая стек VmStk являются общими через copy-on-write.

Учитывая всё это, накладные расходы с учетом таблицы страниц и с huge_pages=off:

andres@awork3:~$ grep ^VmPTE /proc/2004042/statusVmPTE:      6480 kB

и с huge_pages=on:

VmPTE:     132 kB

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

Вывод

На основе проделанных измерений, мы можем представить что процесс выполняющий достаточно простую read-only OLTP нагрузку имеет накладные расходы около 7.6MiB с huge_pages=off и около 1.3MiB с huge_pages=on включая Pss_Anon в VmPTE.

Даже если представить что есть некий "невидимый" оверхэд, и большой объем данных в буфере и т.д., я думаю мы вернемся к моему раннему утверждению что накладные расходы на соединение меньше чем 2MiB.

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