Русский
Русский
English
Статистика
Реклама

Postgresql

Мониторинг 95 метрик PostgreSQL с помощью плагина Zabbix Agent 2

24.05.2021 20:06:11 | Автор: admin

В прошлом году популярный сервис мониторинга 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"

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

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

Подробнее..

Как реляционная СУБД делает JOIN?

03.06.2021 14:23:54 | Автор: admin

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

С 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# и получить следующую таблицу:

Method

Nested Loop

Merge Join

Hash Join

(10, 10)

62.89 ns

293.22 ns

1092.98 ns

(50, 100)

2.168 us

4.818 us

7.342 us

(100, 200)

8.767 us

10.909 us

16.911 us

(200, 500)

38.77 us

32.75 us

40.75 us

(300, 700)

81.36 us

52.54 us

54.29 us

(500, 1000)

189.58 us

87.10 us

82.85 us

(800, 2000)

606.8 us

173.4 us

172.7 us

(750, 5000)

1410.6 us

428.2 us

397.9 us

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

Method

Nested Loop

Nested Loop with Linked List

(10, 10)

62.89 ns

262.97 ns

(50, 100)

2.188 us

8.160 us

(100, 200)

8.196 us

32.738 us

(200, 500)

39.24 us

150.92 us

(300, 700)

80.99 us

312.71 us

(500, 1000)

196.3 us

805.20 us

(800, 2000)

599.3 us

2359.1 us

(750, 5000)

1485.0 us

5750.0 us

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

Таким образом мы приходим к понимаю, что время доступа к каждому конкретному элементу коллекции крайне важно. Одно из главных преимуществ реляционных СУБД в том, что они всегда готовы к добавлению новых данных в любой диапазон. При этом это добавление произойдет максимально эффективным образом - не будет релокации всего диапазона данных или т.п. Кроме того данные СУБД часто хранится в одном файле - таблицы и их данные. Если утрировать, то здесь также используется связанный список. В случае с 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-адресов в PostgreSQL

16.06.2021 14:10:53 | Автор: admin

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

Не раз наша команда в Каруне сталкивались с задачей, связанной с хранением и использованием 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;$$;

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

inet

cidr

749 мкс

891 мкс

Волшебный 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 раз.

Подробнее..

Использование данных OSM для анализа

25.04.2021 10:05:42 | Автор: admin

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

В рамках проекта Фото-Географического Атласа России (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

до 1

до 0,5 часа пешком

1

от 1 до 5

до 2х часа

2

от 5 до 10

до 3х часов

4

от 10 до 25

до дня ходьбы

6

более 25

более 1 дня

10

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

до 1 км

до 0,5 часа пешком

2

от 1до 5

до 2х часа

4

от 5 до 10

до 3х часов

6

более 10

до дня ходьбы

10

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

с побережья северных морей

5

арктические острова

10

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

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

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

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

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

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

Подробнее..

Визуализация аналитики APIM Gravitee в Grafana

30.05.2021 10:22:52 | Автор: admin

Бесспорно, интерфейс 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 и обогащает их информацией и делает читаемой для администратора или любого другого бенефициара.
Схематически это будет выглядеть следующим образом (рисунок ниже).

Схема взаимодействия модулей GraviteeСхема взаимодействия модулей Gravitee

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

Все ниже описанные действия актуальны для следующей конфигурации: 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.

Вот тут:

Раздел Metadata в GraviteeРаздел Metadata в Gravitee

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

Вариант отображения Metadata в GrafanaВариант отображения Metadata в 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. Процесс достаточно прост, но как всегда, есть нюансы.

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

Подробнее..

Анализируем слона вместе с коллегами

15.06.2021 14:10:21 | Автор: admin

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

м/ф "Следствие ведут Колобки"м/ф "Следствие ведут Колобки"

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

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

Обсуждайте проблемный план там, где вам удобноОбсуждайте проблемный план там, где вам удобно

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

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

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

Рекомендательные подсказки узлов планаРекомендательные подсказки узлов плана
  • цветовая и текстовая кодировка разных видов

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

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

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

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

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

Все подсказки - вместе, клик - и вы на местеВсе подсказки - вместе, клик - и вы на месте

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

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

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

Карта будущего сражения за производительностьКарта будущего сражения за производительность

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

Подробнее..

Что нам стоит дом построить? (часть 2)

21.06.2021 12:17:59 | Автор: admin

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

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

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

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

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

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

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

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

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

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

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

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

А что у нас?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

График по тестированию табличного хранилищаГрафик по тестированию табличного хранилищаГрафик по тестированию NoSQL-хранилищаГрафик по тестированию NoSQL-хранилища

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

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

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

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

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

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

Табличная

NoSQL

Объем хранилища

74

66

Среднее количество операций в секунду

970

1080

Время тестирования, секунды

42

37

Количество запросов

40000

40000

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

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

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

Подробнее..

Fintech на практике как Quadcode технологии для трейдинга и банкинга разрабатывает

01.06.2021 12:20:22 | Автор: admin

Привет, самое хардовое 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-решения.

* Для того, чтобы наглядно объяснить, что такое SaaS, и показать, куда мы в итоге хотим прийти, приведем пример с пиццей. Это так называемая модель Pizza-as-a-service, вкусно и полезно.* Для того, чтобы наглядно объяснить, что такое SaaS, и показать, куда мы в итоге хотим прийти, приведем пример с пиццей. Это так называемая модель Pizza-as-a-service, вкусно и полезно.

Команда 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.

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

Подробнее..

DBA прибираем мертвые души

12.05.2021 18:04:50 | Автор: admin

Иногда при выполнении длительных или плохо написанных запросов в 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
Плохо "убитый" клиентский процесс тянет за собой postmaster и весь PostgreSQL-серверПлохо "убитый" клиентский процесс тянет за собой postmaster и весь PostgreSQL-сервер

Сервер быстро упал - быстро поднялся. Но место на диске у нас убыло почти на 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;
Подробнее..

Что нового в плане мониторинга в PostgreSQL 14

22.05.2021 22:20:06 | Автор: admin
Всем привет, на этой неделе вышел бета-релиз PostgreSQL 14. В этом небольшом посте я сделаю краткий обзор того что есть нового и полезного в плане мониторинга и наблюдения.

По идее пост должен быть интересен тем кому небезразлична тема мониторинга и поиска проблем в PostgreSQL системные администраторы, DBA, SRE, DBRE.



  1. В pg_stat_activity добавлено новое поле query_id. Поле содержит идентификатор запроса аналогичный тому что есть в pg_stat_statements. Таким образом с помощью трио полей datid/userid/query_id можно присоединить pg_stat_statements и посмотреть накопленную статистику по конкретному типу запросов. Забавный нюанс имена полей для объединения между pg_stat_activity и pg_stat_statements отличаются.
    Осторожно, текст
    select a.*, s.* from pg_stat_activity a inner join pg_stat_statements s on (a.datid = s.dbid AND a.usesysid = s.userid AND a.query_id = s.queryid) where a.pid = 1001291;-[ RECORD 1 ]-------+-----------------------------------------------------------datid               | 16413datname             | pgbenchpid                 | 1001291leader_pid          | usesysid            | 10usename             | postgresapplication_name    | pgbenchclient_addr         | client_hostname     | client_port         | -1backend_start       | 2021-05-22 10:15:57.299468+05xact_start          | 2021-05-22 10:16:25.566151+05query_start         | 2021-05-22 10:16:25.566623+05state_change        | 2021-05-22 10:16:25.566763+05wait_event_type     | wait_event          | state               | idle in transactionbackend_xid         | 237577backend_xmin        | query_id            | 2517686606037258902query               | SELECT abalance FROM pgbench_accounts WHERE aid = 1715456;backend_type        | client backenduserid              | 10dbid                | 16413toplevel            | tqueryid             | 2517686606037258902query               | SELECT abalance FROM pgbench_accounts WHERE aid = $1plans               | 0total_plan_time     | 0min_plan_time       | 0max_plan_time       | 0mean_plan_time      | 0stddev_plan_time    | 0calls               | 209439total_exec_time     | 4251.98569499987min_exec_time       | 0.005414max_exec_time       | 0.435581mean_exec_time      | 0.020301785698938563stddev_exec_time    | 0.005889254053319066rows                | 209439shared_blks_hit     | 884097shared_blks_read    | 0shared_blks_dirtied | 0shared_blks_written | 0local_blks_hit      | 0local_blks_read     | 0local_blks_dirtied  | 0local_blks_written  | 0temp_blks_read      | 0temp_blks_written   | 0blk_read_time       | 0blk_write_time      | 0wal_records         | 149wal_fpi             | 2wal_bytes           | 9870
    


  2. В pg_stat_activity в списке процессов теперь также отображается WAL archiver. Пока информации не так много, так что не особо информативно и есть куда развиваться далее.
  3. В pg_stat_activity для wal sender процессов (участвуют в репликации), в поле query отображается команда протокола репликации. Это небольшое улучшение позволяет отслеживать команды репликации между мастером и репликами, раньше это возможно было только через логи с включенным параметром log_replication_commands.
  4. В pg_locks добавлено поле waitstart время с которого началось ожидание. Поле позволяет получить время ожидания и при этом не присоединять pg_stat_activity. С одной стороны вроде и удобно, но чтобы взять текст запроса, все равно понадобится присоединить pg_stat_activity. А вот для использования в качестве метрики, вполне подходит т.к. в таком случае текст запроса может быть неинтересен.
    Выглядит так
    # select pid,mode,now()-waitstart as wait_time from pg_locks where not granted;-[ RECORD 1 ]--------------pid       | 1068094mode      | ShareLockwait_time | 00:00:12.669753-[ RECORD 2 ]--------------pid       | 1068093mode      | ShareLockwait_time | 00:00:14.789208
    


  5. Пара групп полей в pg_stat_database. Первая группа это session_time, active_time, idle_in_transaction_time коротко, в этих полях посчитано сколько времени проведено сессиями. Есть нюанс счетчики обновляются в момент смены состояния (поле state), поэтому если сессия долгое время находится в одном состоянии, то это время засчитается только тогда когда оно (состояние) сменится на другое или сессия завершится. Вторая группа полей sessions, sessions_abandoned, sessions_fatal, sessions_killed определяет статистику по сессиям и причинам их завершения. Обе группы счетчиков так и просятся в графики на мониторинг.
  6. Новое представление pg_stat_progress_copy. По названию очевидно что представление показывает ход выполнения команд COPY. Полезно за наблюдением того как 1) выполняется выгрузка и загрузка дампов (pg_dump), 2) непосредственное выполнение команд COPY, 3) выполнение начальной загрузки таблиц при использовании логической репликации через публикации/подписки.
    Пример копирования из файла в таблицу
    -[ RECORD 1 ]----+----------pid              | 1068106datid            | 16413datname          | pgbenchrelid            | 17612command          | COPY FROMtype             | FILEbytes_processed  | 30998528bytes_total      | 195764221tuples_processed | 313263tuples_excluded  | 0
    


  7. Новое представление pg_stat_wal с помощью него можно получить подробное представление об объемах генерируемого WAL. До версии 13 ничего подобного не было. Затем в 13-й версии подобная статистика была добавлена в pg_stat_statements и если ее просуммировать можно получить нечто похожее. Теперь эта статистика расширена и есть в более обобщенном виде (не привязана к запросам).
    Пример
    -[ RECORD 1 ]----+------------------------------wal_records      | 40811237wal_fpi          | 1551923wal_bytes        | 13744020096wal_buffers_full | 509935wal_write        | 1177449wal_sync         | 666045wal_write_time   | 26449.751wal_sync_time    | 10956905.427stats_reset      | 2021-05-21 10:33:39.009804+05
    


  8. Новое представление для наблюдения за логическими слотами репликации pg_stat_replication_slots. Если используется логическая репликация (PUBLICATIONS/SUBSCRIPTIONS, или например Debezium), то это будет полезно в добавок к уже имеющейся pg_replication_slots.
  9. Новое поле toplevel в pg_stat_statements. Известно что pg_stat_statements можно настроить так, чтобы хранить статистику о запросах вложенных в процедуры и функции. Однако если считать агрегаты, то можно получить неверные результаты статистика по вложенным запросам посчитается дважды. Все из-за того, что непонятно как отличить вложенный запрос от нормального. Новое поле позволяет отличать запросы и исключать их.
  10. Новое представление pg_stat_statements_info. Пока там всего два значения время сброса статистики и количество выброшенных значений из pg_stat_statements. Второе значение представляет пользу, поскольку pg_stat_statements имеет лимит на количество записей и если лимит достигнут, то старые записи будут удалены и этого никто не узнает. Теперь же это можно отследить и при необходимости пересмотреть значение pg_stat_statements.max.
  11. Новое представление pg_backend_memory_contexts одно из нововедений которое вызвало у меня противоречивые эмоции. Показывает распределение используемой памяти текущим процессом. Представление как мне кажется предназначено для разработчиков и для отладки на предмет утечек памяти при долгой работе сессий.
  12. Здесь как бы продолжение предыдущего пункта, в котором будет понятен тезис про эмоции.
    Новая функция pg_log_backend_memory_contexts() это развитие предыдущего улучшения, функция принимает в качестве аргумента идентификатор процесса и сохраняет в журнал, статистику утилизации подобно той что в представлении pg_backend_memory_contexts.

    И вот тут как мне кажется, разработчики перемудрили достаточно было бы сделать только одну эту функцию (без вьюхи) которая бы принимала идентификатор процесса, но при этом вместо логирования, выводила бы статистику в виде строк. В таком случае можно взять статистику и по текущей сессии и по любой соседней. При этом возможность залогировать вывод тоже никуда не девается (хоть и не в журнал, но тем не менее возможность есть). А в текущем виде есть представление и функция с логированием и еще в журнал надо сходить, чтобы достать сброшенную статистику. Короче, мне реализация показалось слегка странной.
  13. Новые поля в pg_prepared_statements generic_plans, custom_plans для подсчета количества планов для каждого запроса. Честно, я практически этим представлением никогда не пользовался и у меня нет практических примеров его применения.
  14. Новая функция pg_get_wal_replay_pause_state(). Чуть более улучшенная версия функции pg_is_wal_replay_paused(). Показывает состояние постановки репликации на паузу. Доступно три варианта not paused, pause requested, paused.
  15. Новый параметр log_recovery_conflict_waits включает журналирование случаев когда воспроизведение WAL журнала на реплике вступает в конфликт и ждет разрешения конфликта. Штука полезная, я бы рекомендовал включать по-умолчанию.
  16. Новые операторы для pg_lsn типа. Тип pg_lsn используется для работы с позицией в WAL журнале например с помощью этого типа удобно считать лаг репликации в байтах. С помощью двух новых операторов арифметика стала проще и значения pg_lsn можно складывать или вычитать с целыми числами.
    Пока не придумал где использовать, но выглядит клево
    pgbench=# select '1/8000000'::pg_lsn + 16777216;-[ RECORD 1 ]-------?column? | 1/9000000pgbench=# select '1/8000000'::pg_lsn - 16777216;-[ RECORD 1 ]-------?column? | 1/7000000
    


  17. Добавлена информация о таймингах ввода-вывода при журналировании операций autovacuum и autoanalyze. Должен быть включен log_autovacuum_min_duration.
    А ну-ка попробуйте отыскать
    2021-05-22 10:50:48.000 +05 1005664 @ from  [vxid:4/309623 txid:0] [] LOG:  automatic vacuum of table "pgbench.public.pgbench_accounts": index scans: 1        pages: 0 removed, 65600 remain, 0 skipped due to pins, 0 skipped frozen        tuples: 1936414 removed, 2000605 remain, 566 are dead but not yet removable, oldest xmin: 253998        buffer usage: 92201 hits, 108672 misses, 129131 dirtied        index scan needed: 58623 pages from table (89.36% of total) had 1961508 dead item identifiers removed        index "pgbench_accounts_pkey": pages: 10970 in total, 0 newly deleted, 0 currently deleted, 0 reusable        avg read rate: 3.522 MB/s, avg write rate: 4.185 MB/s        I/O Timings: read=392.361 write=1964.360        system usage: CPU: user: 2.92 s, system: 1.79 s, elapsed: 241.07 s        WAL usage: 195815 records, 72916 full page images, 308792606 bytes
    



Вот и всё.

В завершение хочу сделать анонс мероприятия 8 и 9 июля в онлайне состоится PG Day21 Russia. Это будет двух-дневная тусовка фанатов PostgreSQL, 12 выступлений от отечественных и зарубежных докладчиков. Участие бесплатное. Прием докладов также открыт до 7 июня

На этом все, спасибо за внимание!
Подробнее..

Бэкапы для HashiCorp Vault с разными бэкендами

26.05.2021 10:15:20 | Автор: admin

Недавно мы публиковали статью про производительность 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
  1. Забрать файл 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).

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

100k backup

100k restore

1kk backup

1kk restore

Consul

3.31s

2.50s

36.02s

27.58s

PosgreSQL

0.739s

1.820s

4.911s

24.837s

GCS*

1h

1h24m

12h

16h

Raft

1.96s

0.36s

22.66s

4.94s

* Восстановление бэкапа в 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.

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

Подробнее..

Как быстрее всего передавать данные с PostgreSQL на MS SQL

21.04.2021 00:08:36 | Автор: admin

Однажды мне потребовалось забирать регулярно относительно большие объемы данных в 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 - буду очень рад увидеть описание этого способа в комментариях.

Подробнее..
Категории: Postgresql , Sql , Postgres , Microsoft sql server , Mssql , Bcp

Как мы сделали программу лояльности для 300 магазинов У Палыча на open source iDempiere ERPCRM

25.04.2021 16:15:44 | Автор: admin

Что такое 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 минуты.

Подробнее..

Чего энтерпрайзу в PostgreSQL не хватает

26.04.2021 10:24:01 | Автор: admin

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

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

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

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

он же Built-in connection pooler

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

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

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

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

Пример работы connection poolПример работы connection pool

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

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

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

64-bit XID

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

Одно из наглядных объяснений 32-bit transaction ID wraparoundОдно из наглядных объяснений 32-bit transaction ID wraparound

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

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

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

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

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

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

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

zheap

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

Информация о транзакциях хранится отдельно от контейнера записиИнформация о транзакциях хранится отдельно от контейнера записи

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

Append-only Storage

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

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

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

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

Timescale базируется на ядре PostgreSQL, но "заточена" на timeseries-dataTimescale базируется на ядре PostgreSQL, но "заточена" на timeseries-data

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

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

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

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

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

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

Columnar Storage

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

Хранение данных "по столбцам"Хранение данных "по столбцам"

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

In-memory Storage

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

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

TEMPORARY TABLE и реплики

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

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

Multimaster

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

SQL

SQL-defined Index

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

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

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

Мониторинг

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

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

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

Визуальный анализ плана запросаВизуальный анализ плана запросаДиаграмма выполнения планаДиаграмма выполнения плана

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

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

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

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

Тепловая карта операций по таблицамТепловая карта операций по таблицам

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


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

Подробнее..

Postgresso 31

11.05.2021 16:15:42 | Автор: admin
Надеемся, что вы хорошо отдохнули и попраздновали. А мы предлагаем вам очередную сводку Postgres-новостей.

PostgreSQL 14 Beta 1


Релизная группа в составе Пит Гейган (Pete Geoghegan, Crunchy Data), Мишель Пакье (Michael Paquier, VMWare) и Эндрю Данстан (Andrew Dunstan, EDB) предлагают опубликовать бету 20-го мая, как это и происходило с предыдущими бетами.



Commitfest afterparty


PostgreSQL 14: Часть 5 или весенние заморозки (Коммитфест 2021-03)

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

Вот авторский тизер:
  • Может ли один запрос параллельно выполняться на разных серверах?
  • Как найти запрос из pg_stat_activity в pg_stat_statements?
  • Можно ли добавлять и удалять секции секционированной таблицы не останавливая приложение?
  • Как пустить разработчиков на прод чтобы они могли всё видеть, но ничего не могли изменить?
  • Почему VACUUM после COPY FREEZE заново переписывает всю таблицу и что с этим делать?
  • Можно ли сжимать TOAST чем-то кроме медленного zlib?
  • Как понять сколько времени длится блокировка найденная в pg_locks?
  • Для чего нужны CYCLE и SEARCH рекурсивному запросу?
  • Текст функций на каких языках (кроме C) не интерпретируется при вызове?


Миграция


CHAR(1) to Boolean transformation while migrating to PostgreSQL

В Oracle нет типа boolean, а в PostgreSQL есть. Но почему бы не использовать этот тип, если в исходной оракловой базе есть столбец boolean, который хранится там в виде CHAR(1) с ограничением CHECK? Можно. Но хотелось бы ещё получить гарантию, что значения, отличные от резрешенных для Postgres не остановят работу приложения, а будут должным образом обработаны. Для этого можно создать CAST:
CREATE CAST (char as bool) WITH FUNCTION char_to_bool(char);
Далее автор Дилип Кумар (Dileep Kumar, MigOps) показывает изменение поведения при определении CAST как IMPLICIT, а потом прогоняет запрос (обычный SELECT) на тестах, чтобы увидеть разницу CHAR(1) vs Explicit Casting vs Implicit Casting vs Boolean. Побеждает, как и ожидалось, Boolean.

Choice of Table Column Types and Order When Migrating to PostgreSQL

В статье Стивена Фроста (Stephen Frost) с участием его коллеги по Crunchy Data Дэвида Юатта (David Youatt) тоже говорится о том, какой тип выбрать в PostgreSQL при миграции, но ещё и о том, в каком порядке располагать столбцы, чтобы данные выбранных типов хранились максимально эффективно. Сначала самые широкие поля с фиксированной шириной, затем менее широкие с фиксированной и только потом поля переменной ширины иначе появятся дыры в данных. Стивен рассказывает и про неприятные сюрпризы с выравниванием, которые можно получить, излишне рьяно экспериментируя с типами PostgreSQL. Ещё совет: выбирайте NUMERIC или DECIMAL только тогда, когда необходимо (считая деньги, например), а если нет, то обходитесь NTEGER, BIGINT, REAL, DOUBLE PRECISION это проще и эффективней.


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


Lessons Learned From 5 Years of Scaling PostgreSQL

Джо Уилм (Joe Wilm) обобщает опыт использования PostgreSQL в компании OneSignal. Система доросла за 5 лет до 75 ТБ на 40 серверах. Понятно, что не все технические решения были приняты сразу на вырост. Как решают проблемы масштабирования, и как их можно было избежать об этом и рассказывает автор. Для удобства он разбил статью по разделам (сознательно не перевожу, слишком много английских слов пришлось бы писать кириллицей):
Bloat таблиц и индексов. Коротко о (хорошо известных) причинах распухания. pg_repack справлялся так себе (см. причины), написали собственный демон, координирующий его работу. Перешли к pgcompacttable там, где pg_repack обваливает производительность (перешли не везде, pgcompacttable работает надёжней, но медленней). Есть и об уловках по ситуации: в системе были таблицы, в которых большие поля (около 1 КБ) в личных данных, и поле last_seen_time int, которое часто обновлялось. Их разнесли по разным таблицам: одним JOIN больше, зато не копятся килобайты при обновлении строки.
Database upgrade. Мажорные и минорные. С мажорными справлялись при помощи логической репликации pglogical. При минорых просто перестартовывали postgres.
Wraparound. Серьёзная проблема для таких нагрузок. Остановились на оповещениях при приближении к 250 млн оставшихся XID. Напомним, конечно, что в Postgres Pro Enterprise 64-битные XID.
Replica Promotion. Для этого обходятся средствами haproxy. Упоминается только Patroni, но и то в контексте мы не используем, но может и стоило. Для каждой логической базы данных есть два бэкенда: один read-write, другой read-only. Переключение занимает пару секунд.
Partitioning и Sharding. Важнейшая штука для такой базы, конечно. Сначала порезали на 16 секций, потом на 256, а в ближайших планах 4096. Резали на куски выбирая в качестве критерия разбиения id пользователей системы. Сейчас думают над созданием data proxy слое, который будет знать, как разрезаны данные и где лежат, и действовать соответственно. Чтобы приложениям этого не требовалось знать для нормальной работы. Сетуют, что не сделали так с самого начала.


Самокритика


Чего энтерпрайзу в PostgreSQL не хватает

Вот чего ему не хватает в порядке важности (по Кириллу Боровикову, автору статьи)
  • легковесного менеджера соединений (он же built-in connection pooler);
  • 64-bit XID;
  • микротаблиц (речь о том, что у каждой таблицы и индекса в PostgreSQL есть 3 форка файла, но почему бы не обойтись 1 файлом (heap) для мелких справочных табличек?);
  • zheap;
  • append-only storage (а в идеале, считает Кирилл хотелось иметь возможность назначать часть полей индексов или целых таблиц как no-MVCC чтобы иногда экономить на полях поддержки MVCC);
  • отложенная индексация (чтобы сервер мог размазать необходимые операции во времени для балансировки нагрузки эта тема особенно важна для конкуренции с поисковыми системами, где основная задача найти вообще, а не найти прямо сразу сейчас);
  • columnar storage (в идеале в ядре или в contrib);
  • in-memory storage (очень быстрого нетранзакционного хранилища без сброса на диск);
  • не пухнущих TEMPORARY TABLE, в том числе на репликах;
  • multimaster из коробки;
  • SQL-defined index (уметь описывать новые виды индексов прямо на SQL/PLpgSQL);
  • мониторинга производительности запросов (здесь Кирилл предлагает глянуть, как это визуализируется на родном explain.tensor.ru);
  • снапшотов статистики таблиц (как в pg_profile [а тем более в pgpro_pwr примечание редакции]).

К ЭТОМУ ДОБАВИЛИСЬ ХОТЕЛКИ ИЗ КОММЕНТАРИЕВ:

  • IS NOT DISTINCT FROM при индексации;
  • failover из коробки (аналогично Always on у MS SQL) без Patroni и сопутствующих;
  • Asynchronous IO и Direct IO;
  • бесшовного обновления мажорной версии;
  • flashback queries;
  • edition-based redefinition;
  • нормальной компрессии.

Некоторые из этих хотелок на пути к дальнейшим версиям, некоторые уже есть в Postgres Pro Enterprise (о чём не умалчивает и автор).


Видео-вторник s02e15: Десять проблем PostgreSQL. Мониторинг запросов, pg_profile

(это продолжение вторника ) с Андреем Зубковым)

Статья Рика Брэнсона: (Rick Branson) 10 things I Hate In Postgres внезапно попала в топ обсуждаемых. Вот её не миновали и устроители ruPostgres.Вторников Николай Самохвалов и Илья Космодемьянский.

О ней мы писали в Postgreso 20. На ruPostgres.вторнике s02e15 6-го апреля самые жаркие вопросы возникали, как всегда, вокруг MVCC и VACUUM, переполнения 32-битных счётчиков XID.

На 50-й минуте обсуждения 10 ненавистных вещей Андрей Зубков продолжил рассказал о pg_profile (до pgpro_pwr речь опять не дошла, говорили даже о том, чтобы наверстать в 3-й серии) и о своём патче pg_stat_statements: Track statement entry timestamp (ровно 1:00 записи).

Вторник 20-го апреля назывался Как поменять тип колонки в таблице PostgreSQL с 1 млрд строк без даунтайма?. Два разных варианта решения на уровне колонки и на уровне таблицы.

А совсем недавний 4-го мая о разном, например, о WAL-G vs. pgBackRest, об амазоновских инстансах на ARM, о которых чуть ниже. Список тем лежит в файле.


Облака и контейнеры


Dramatical Effect of LSE Instructions for PostgreSQL on Graviton2 Instances

Александр Коротков в своём блоге пишет об опыте работы с новейшими облаками инстансы Graviton2 работают на амазоновских ARM-процессорах. Но следующие за модой расплачиваются некоторыми сложностями у ARM есть специфика (по мнению Александра работа с ними скорее напоминает работу с IBM Power).

Команды LSE (Large System Extensions), доступные с версии 8.1, действительно ускоряют работу. Вот здесь это разъясняют с некоторыми подробностями, испытывая MySQL на включенных и отключенных LSE. Александр же получил колоссальный выигрыш на pgbench, скомпилировав PostgreSQL 14 с поддержкой LSE. Но это касается только амазоновских ARM AWR Graviton2. Apple M1 не удалось оптимизировать (возможно, в этих процессорах есть какая-то внутренняя оптимизация), а на китайских Kunpeng 920 результаты даже ухудшились.


Что делать


Managing Transaction ID Exhaustion (Wraparound) in PostgreSQL

Кит Фиске (Keith Fiske, Crunchy Data) регулярно пишет в своём собственном блоге Keith's Ramblings о вакууме, распухших индексах и других важнейших для вдумчивого постгресиста вещах.

В этой статье есть конкретные SQL-запросы, использующие autovacuum_freeze_max_age для получения внятной информации о происходящем с конкретными таблицами, так как vacuumdb --all --freeze --jobs=2 --echo --analyze всего кластера баз данных во многих случаях слишком радикальная мера. Если недовакуумированных таблиц очень много, то Кит советует вакуумировать в батчах не больше сотни в каждом. Сам он предпочитает держать max XID < to 50% autovacuum_freeze_max_age, лучше 30-40%.

Он написал статью и о настройке автовакуума: Per-Table Autovacuum Tuning. Но даже аккуратно настроив автовакуум, стоит с не меньшей аккуратностью мониторить ситуацию. Риск не велик, но ставка высока, как говорили наши деды.

Не удержусь от перечисления собственных проектов Кита (или с его существенным участием):
pg_partman расширение с автоматической поддержкой секционирования по времени и serial id;
pg_extractor продвинутый фильтр дампа;
pg_bloat_check скрипт для мониторинга таблиц и индексов;
mimeo расширение PostgreSQL для потабличной логической репликации;
pg_jobmon расширение для логирования и мониторинга автономных функций.

Postgres is Out of Disk and How to Recover: The Dos and Don'ts

Статья Элизабет Кристинсен (Elizabeth Christensen) с участием Дэвида Кристинсена (David Christensen), Джонатана Каца (Jonathan Katz) и Стивена Фроста (Stephen Frost) все из Crunchy Data. Почему забился диск, что НЕ делать, и что делать.
Возможные причины:
  • отказала archive_command и WAL начал заполнять диск;
  • остались слоты репликации у стендбая, а реплика стала недоступна: опять же WAL заполняет диск;
  • изменения в базе настолько большие, что генерящийся WAL съедает всё доступное дисковое пространство;
  • просто-напросто данных было слишком много, а средства мониторинга и предупреждения не сработали.

Что НЕЛЬЗЯ делать:
удалять WAL-файлы нельзя категорически;
  • не дайте переписать существующие данные, восстанавливаясь из бэкапа;
  • Никакого resize.


Что надо делать:
  • сделайте сразу бэкап на уровне файловой системы;
  • создайте новый инстанс (или хотя бы новый том) с достаточным местом, убедитесь, что Postgres остановлен и сделайте бэкап директории данных PostgreSQL (обязательно директории pg_wal и недефолтные табличные пространства), чтобы вам было куда вернуться, если понадобится;
  • когда база данных заработала, просмотрите логи, разберитесь, из-за чего возникли проблемы и почините поломки, если это возможно.

В статье рассказывается, как архивируется WAL, об попорченных архивах, кое-что о pgBackRest, а ещё предлагается почитать How to Recover When PostgreSQL is Missing a WAL File.

Кстати, о WAL. Если нужно порекомендовать хорошую статью англоязычным коллегам, то в блоге Postgre Pofessional опубликован перевод 3-й части серии Егора Рогова о WAL: WAL in PostgreSQL: 3. Checkpoint. Оригинал её здесь, en-начало-серии здесь, а ru-начало здесь.


Из блога БРЮСА МОМДЖАНА


(то есть отсюда)

Jsonb Multi-Column Type Casting

Брюс делится радостью, что есть jsonb_to_record() и можно без всяких двойных двоеточий сразу сказать:
SELECT a, b, pg_typeof(a) AS a_type, pg_typeof(b) AS b_typeFROM test, jsonb_to_record(test.x) AS x (a TEXT, b INTEGER);

(А ведь добавим от себя есть ещё и jsonb_to_recordset(jsonb)).

Брюс обращает внимание на устройство таких запросов. Если сказать
SELECT x.a, b, pg_typeof(a) AS a_type, pg_typeof(b) AS b_typeFROM test, jsonb_to_record(test.x) AS x (a TEXT, b INTEGER)WHERE b <= 4;

то это будет работать, ведь b уже integer потому, что запрос уже создал табличку x с областью видимости только внутри запроса, где типы уже преобразованы. Немногословный (как обычно в своём блоге) Брюс предлагает ознакомиться с деталями в тредах json_to_record Example и Abnormal JSON query performance.

Oracle vs. PostgreSQL

Брюс решил оценить функциональную полноту обеих СУБД в %, в ответ на чьё-то сравнение Postgres и Oracle это как резиновая уточка против танкера водоизмещением 300 тыс. тонн. Он считает:
Более реалистичной была бы оценка в 80-90%, в зависимости от того, какая функциональность для вас важней. Но можно бы поговорить и том, что в Postgres есть, а в Oracle нет. С точки зрения админа получится, может быть, и меньше 80%, а вот с точки зрения разработчика в Oracle нет многого, и оценка перевалит за 100%.

Challenging Assumptions

Следующие, некогда справедливые допущения теперь сомнительны:
  • платный софт всегда лучше бесплатного;
  • открытый код не столь безопасен, так как слабые места видны;
  • серьёзные люди софт с открытым кодом не разрабатывают;
  • Oracle лучшая СУБД;
  • со знанием Oracle без работы я не останусь;

Кто закрывает дыры и латает щели (в оригинале Database Software Bundles)

Проект Postgres дал миру великолепную, полнофункциональную СУБД. Но когда пользователь думает о бэкапе, мониторинге, высокой доступности, ему приходится смотреть на сторону, так как возможности Postgres могут не совпадать с его потребностями. Иногда бреши закрывают проекты с открытым кодом, но в других случаях решают проблемы коммерческие Postgres-компании: Cybertec, edb, HighGo, Ongres, Postgres Pro, sra oss и другие, которые поставляют сервисы последней мили для корпоративных решений.

Также можно заглянуть в

Shared Memory Sizing
или, скажем, в
Replica Scaling by the Numbers


ИИ


Regression Analysis in PostgreSQL with Tensorflow

Дейв Пейдж (Dave Page, вице-президент и главный архитектор EDB) продолжает серию, посвященную ИИ и статистическим методам анализа данных. Из последнего: вышли две статьи посвященные регрессионному анализу, который ускоряют с помощью Tensorflow. В приведенных примерах можно увидеть много ласкающих слух питониста слов: pandas, numpy, matplotlib и seaborn. Подчеркнём, что используется расширение PostgreSQL plpython3u, а не просто внешние по отношению к базе библиотеки.

Во второй части дело доходит до пред-обработки данных. Используется популярный у педагогов машинного обучения набор данных Boston Housing Dataset по ним тренируются угадывать цену дома в Бостоне в зависимости от некоторых факторов. Из набора выкидывают значения, сильно отличающиеся от общей массы, чтобы не запутать нейронную сеть при обучении. Ещё смотрят распределения и строят корреляции. Третья статья ещё не вышла. Обещано, что в ней уже воспользуются достижениями 2-й части, чтобы обучать нейронную сеть регрессионному анализу.


Релизы


Kubegres

Обычно в разговоре о PostgreSQL в Kubernetes на третьей фразе появляются операторы от Crunchy Data и Zalando. Kubegres, возможно, вклинится в разговор. Разработчик Алекс Арика (Alex Arica, Reactive Tech Limited). Создавался Kubegres на базе фреймворка Kubebuilder version 3 (SDK для разработки Kubernetes APIs с использованием CRD. Можно забрать отсюда.

KuiBaDB

KuiBaDB это Postgres для OLAP, переписанный с Rust и многопоточностью. У этой СУБД есть только базовая функциональность. Она, например, поддерживает транзакции, но не вложенные транзакции. KuiBaDB создан для разработчиков, чтобы они могли быстренько проверить на ней свои идеи. В ней есть векторный движок и колоночное хранение, она опирается на каталоги (catalog-driven).

pgBackRest 2.33

Появилась поддержка нескольких репозиториев данные и WAL можно копировать сразу в несколько хранилищ.
pgBackRest поддерживает теперь GCS Google Cloud Storage.
Отныне можно задать путь вручную с ./configure --with-configdir. Стало удобней работать с не-Linux ОС, например с FreeBSD.
Появилось логирование в процессе бэкапа.

pg_probackup 2.4.15

В новой версии pg_probackup при бэкапе в инкрементальном режиме автоматически обнаруживается переключение таймлайнов, за счёт использования команды TIMELINE_HISTORY протокола репликации (предложил Алексей Игнатов).

При операциях merge и retention merge теперь тоже можно использовать флаги --no-validate и --no-sync.

pgmetrics 1.11.0

pgmetrics утилита с открытым кодом для сбора статистики работающего PostgreSQL, распространяемая в виде единого бинарного файла без внешних зависимостей. Разработчик RapidLoop, у которой есть ещё и pgDash, для которой pgmetrics собирает статистику.

Новое в версии:
  • собирает и парсит логи из AWS RDS и Aurora, используя CloudWatch;
  • поддержка пулера Odyssey v1.1;
  • улучшена поддержка Postgres 13;
  • улучшена поддержка метрик AWS RDS;
  • появились бинарники для ARMv8

Скачать можно отсюда.

HypoPG 1.2

HypoPG одно из произведений Жульена Руо (Julien Rouhaud). Это расширение для работы с гипотетическими индексами. Новое в версии: работая на стендбае, hypopg использует фальшивый (fake) генератор oid, который одалживает их внутри интервала FirstBootstrapObjectId / FirstNormalObjectId, а не генерит реальные. Если потребуется, можно работать по-старому, используя опцию hypopg.use_real_oids. Есть и ещё изменения, hypopg_list_indexes(), подробности в документации.

pgstats.dev

Это динамическая диаграмма Postgres Observability упрощенное представление устройства PostgreSQL и доступные системные представления и функции для получения статистики о работе подсистем Postgres. Этому необычному произведению Алексея Лесовского (Data Egret) всего 5 месяцев, но её знают многие DBA, спорят и интересуются: что новенького? Новое, например, вот:
  • стрелки, которые раньше показывали связи между блоками и метками статистики, теперь исчезли, а соответствующие цвета введены, чтобы показать их отношения;
  • на страницах описания статистик (см. pg_stat_progress_create_index в качестве примера) улучшена внутренняя навигация за счет добавления ссылок на связанные элементы;
  • добавлены ресурсы внешние ссылки с дополнительной информацией;
  • теперь есть управление версиями, чтобы вы могли видеть, как Postgres эволюционировал от одной версии к другой.


AGE 0.4.0

Расширение, добавляющее графовую функциональность. Новшества в 0.4.0 здесь.

pg_log_statements 0.0.2

pg_log_statements расширение PostgreSQL, которое позволяет логировать SQL-запросы так, что переменная log_statement может быть установлена для отдельного серверного процесса (по id или фильтру), а не на уровне базы или инстанса.

Можно зайти на PGXN или на гитхабе создателя Пьера Форстмана, специалиста по Oracle.


Конференции


PostgresLondon 2021

Состоится уже 12-го мая, виртуальная. Расписание.

Highload++

Состоится офлайн 17 -18 мая в Крокус-Экспо, Москва. Расписание.

Postgres Vision 2020

Postgres Vision виртуальная конференция EDB, но участие свободное. Состоится 22-23 июня. Регистрация.

Следующий номер Postgresso 32 выйдет в первых числах июня.
Подробнее..

Этюд копированию баз данных PostgreSQL без использования pg_dump

14.05.2021 16:12:54 | Автор: admin

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

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

Первое и самое очевидное решение pg_dump
Достоинства простота решения. Штатные методы. Все отработано, документации и материалов великое множество.
Но, достоинства есть продолжения недостатков.
Во первых-объемы дампов.
Во вторых-и это самое неприятное, были случаи несовпадения исходной и целевой БД при восстановлении из дампа.
В третьих-время, сначала на создание дампа, потом на восстановление БД из дампа.

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

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

Термины и исходные данные

Исходный кластер -кластер PostgreSQL содержащий БД которую нужно скопировать.
Исходная БД объект копирования, БД на исходном кластере
Клон БД копия исходной БД на исходном кластере
Кластер хранения копий БД отдельный кластер PostgreSQL.
Копия БД-целевая копия БД на кластере хранения копий БД

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

Шаг 1.

Создается клон БД, используя
CREATE DATABASE ... TEMPLATE = Исходная БД
фрагмент скрипта
CLONE_DB=$source_db_name'_'$timestamp_labelpsql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$source_db_name'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "CREATE DATABASE $CLONE_DB TEMPLATE=$source_db_name " >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1

Шаг 2.

Загружается схема клона БД, используя
pg_dump --shema_only --file=$DUMP_FILE ... 
фрагмент скрипта
pg_dump -h $source_host_name -U postgres --schema-only --verbose --file=$DUMP_FILE $source_db_name 2>$SCHEMA_DUMP_LOG_FILE

Шаг 3.

Создается пустая БД копия БД, используя
createdb ... 
фрагмент скрипта
createdb $CLONE_DB

Шаг 4.

Создается схема клона БД в копии БД, используя
psql ... < $DUMP_FILE
фрагмент скрипта
TARGET_SCHEMA_DUMP_LOG_FILE=$FILE_LABEL'target.log'psql -U postgres -d $CLONE_DB  < $DUMP_FILE > $TARGET_SCHEMA_DUMP_LOG_FILE 2>&1

Шаг 5.

Создание логической репликации.
Создание публикации в клоне БД, используя
CREATE PUBLICATION ... FOR ALL TABLES 
фрагмент скрипта
PUBLICATION_NAME=$CLONE_DB'_pub'psql -h $source_host_name -U postgres -d $CLONE_DB  -c "CREATE PUBLICATION $PUBLICATION_NAME FOR ALL TABLES " >>$LOG_FILE 2>&1

Создание подписки в копии БД, используя
CREATE SUBSCRIPTION ...
фрагмент скрипта
SUBSCRIPTION_NAME=$CLONE_DB'_sub'CONNECTION_STR="CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION 'host=$source_host_name port=5432 user=postgres dbname=$CLONE_DB' PUBLICATION $PUBLICATION_NAME"psql -U postgres -d $CLONE_DB  -c "$CONNECTION_STR" >>$LOG_FILE 2>&1

Шаг 6.Синхронизация клона БД и копии БД, используя
SELECT count(*) FROM pg_stat_subscription WHERE subname =... AND relid IS NOT NULL
БД считаются синхронизированными, если нет процесса синхронизации между таблицами. Клон БД для работы приложения не используется, что гарантирует идентичность баз.
фрагмент скрипта
flag=0while [[ $flag = '0' ]];do    COUNT_STR="SELECT count(*) FROM pg_stat_subscription WHERE subname ='$SUBSCRIPTION_NAME' AND relid IS NOT NULL "    subscription_process_count=`psql -At -U postgres -d $CLONE_DB -c "$COUNT_STR"`    if [[ $subscription_process_count = '0' ]];    then        break    fi    sleep 60done

Шаг 6.

Удалить логическую репликацию, используя
DROP SUBSCRIPTION...
фрагмент скрипта
psql -d $CLONE_DB -Aqt -c "DROP SUBSCRIPTION $SUBSCRIPTION_NAME" >> $LOG_FILE 2>&1
DROP PUBLICATION ...
фрагмент скрипта
psql -h $source_host_name  -d $CLONE_DB -Aqt -c "DROP PUBLICATION $PUBLICATION_NAME" >> $LOG_FILE 2>&1
в копии БД и клоне БД соответственно.

Шаг 7.

Удалить клон БД, используя
DROP DATABASE ...
фрагмент скрипта
psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$CLONE_DB'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "DROP DATABASE $CLONE_DB " >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1

Результат


Проведено копирование БД между серверами с минимальными издержками на передачу и хранение данных.
Подробнее..

SQL разбор задачи на поиск последней цены

17.05.2021 16:18:04 | Автор: admin

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

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

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

Условие задачи

Есть набор данных с ценами на товары (prod_id) на складах (stock_id). Причём цены бывают настоящие (R=Real), а бывают рекламные (P=Promo). Для каждой цены есть дата начала действия. Нужно к каждой строчке набора вытащить реальную цену, которая является последней по дате настоящей ценой (price1) с типом 'R' на этот товар на соответствующем складе.

Вот начало запроса с тестовыми данными в виде CTE, на которых можно потренироваться:

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        ...

Особенности же тут вот в чём. Я не зря радировал выше источник данных, потому что не таблица тут у нас, а вьюха, собранная из самых разных и зачастую совершенно неожиданных источников, откуда всякие промо-цены и берутся. То есть primary key для строчек не только нету, но и даже суррогатный-то на лету не так сразу получишь, так как никаких CTID (или там ROWID) в помине нету... Второй нюанс это тут я оставил только колонки price1, cost1 и bonus1, а в настоящем источнике данных много всяких характеристик нужно было вытащить из последней 'R'-строки, так как на рекламных строках эти данные отсутствуют. И не спрашивайте, почему так бизнесу виднее. Считайте расширенным условием задачи выбрать все эти поля из последней R-записи.

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

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

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

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

27.05.2021 12:14:32 | Автор: admin

Всем привет. В этом посте я расскажу про 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. Напоследок немного ссылок:

Подробнее..

Отказоустойчивый кластер PostgreSQL с помощью crm

08.06.2021 14:19:38 | Автор: admin
Автор Игорь Косенков, инженер postgres Professional

Привет всем! Сегодня речь пойдет о кластере. Да, снова об отказоустойчивом кластере на базе Corosync/Pacemaker. Только настраивать мы его будем не как обычно с помощью утилиты pcs, а с помощью мало используемой утилиты crm.

С точки зрения использования этих утилит (pcs и crm) весь мир Unix-like операционок делится на два вида:
  • содержит пакеты утилиты pcs (RHEL, CentOS, Debian, Ubuntu);
  • содержит пакеты утилиты crm (SLES, Opensuse, Elbrus, Leningrad и т.д.).

crm cluster resource manager специальная утилита, которая используется для создания и управления отказоустойчивым кластером. Она включена в пакет crmsh, который обычно не входит в состав самых распространенных дистрибутивов Linux.

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

В то же время, если спросить у поисковика про утилиту настройки кластера pcs, которая является по функционалу такой же утилитой, как и crm, то информации будет много. Есть даже несколько статей на Хабре (в том числе и моя статья Кластер pacemaker/corosync без валидола).

Утилита crm такая же мощная и гибкая, как и pcs, но незаслуженно обделена вниманием.

Решено было исправить этот пробел и написать статью.

Причины, по которым те или иные разработчики дистрибутивов предпочитают кто crm, а кто pcs, мне неизвестны. Могу предположить, что все дело в зависимостях. Например, если сравнить количество зависимостей у pcs и crm, то получается такая картина:
$ sudo rpm -qpR crmsh-3.0.1-1.el7.centos.noarch.rpm | wc -l19$ sudo rpm -qpR pcs-0.9.169-3.el7.centos.x86_64.rpm | wc -l50

Сторонники минимализма, скорей всего, предпочтут crmsh. А если еще учесть, что pcs тянет за собой ruby, openssl, pam и python, а crmsh только python, то выбор в некоторых случаях будет однозначно на стороне crm. В каких случаях? Ну, например, при сертификации ОС есть некоторые трудности с пакетом ruby. Также известны случаи, когда в банковских структурах служба безопасности не разрешает установку нерегламентированного ПО.

Сходства и различия


У утилиты crm есть как сходства, так и различия с известной всем утилитой pcs.
Сходства утилит приведены в таблице 1:



На этом сходства заканчиваются и дальше идут различия, которых много, поэтому привожу лишь некоторые из них (таблица 2):



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

Удалить кластер (разобрать) у pcs можно одной командой сразу, а у crm необходимо удалять по одному узлу до тех пор, пока их не останется в кластере.

Чтобы изменить параметры ресурса, который мы уже создали в кластере, у pcs есть опция update. У crm такой опции нет, но есть команда configure edit, которая позволяет менять любые настройки кластера налету и мгновенно. Даже больше мы можем за один прием отредактировать любое количество параметров и ресурсов, и в конце редактирования применить все изменения сразу. Удобно? Думаю, да.

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

У crm в стандартной поставке нет веб-инструмента, но зато он есть в коммерческой версии SUSE HAWK.

Подготовка к настройке отказоустойчивого кластера


Лучший способ узнать и познакомиться с crm это настроить отказоустойчивый кластер.

Чем мы сейчас и займемся. Для примера возьмем ОС CentOS 7.9.

Для создания отказоустойчивого кластера PostgreSQL нам понадобится стенд, состоящий из 3-х узлов node1, node2, node3. На каждом узле установлена ОС CentOS 7.9 и пакеты corosync, pacemaker, fence-agents* (агенты фенсинга).

В качестве СУБД будем использовать Postgres Pro Standard v.11, но вы можете с таким же успехом использовать ванильную версию PostgreSQL. В нашей системе установлены необходимые пакеты postgrespro-std-11-server, postgrespro-std-11-libs, postgrespro-std-11-contrib, postgrespro-std-11-client.

Настройки СУБД (postgresql.conf) и доступа к ней (pg_hba.conf) не рассматриваются в данной статье, информации об этом достаточно в интернете. На одном из узлов (например, node1) необходимо инициализировать базу данных с помощью initdb, а на двух других узлах с помощью pg_basebackup скопировать базу данных с node1.

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

ПРИМЕЧАНИЕ:
В этом разделе все команды необходимо выполнить на всех узлах кластера.
Поскольку пакет crmsh не входит в состав дистрибутива ОС, то необходимо подключить репозиторий
Extra OKay Packages for Enterprise Linux с этим пакетом.
node1,2,3$ sudo rpm -ivh http://repo.okay.com.mx/centos/7/x86_64/release/okay-release-1-5.el7.noarch.rpm

Нам также понадобится репозитарий EPEL:
node1,2,3$ sudo yum install epel-releasenode1,2,3$ sudo yum update

Устанавливаем пакет crmsh:
node1,2,3$ sudo yum install crmsh

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

Разработчики crmsh вставили код вызова этой кластерной утилиты для удобства настройки. Можно, конечно, обойтись и без csync2, но в таком случае будет больше ручной работы с конфигурационными файлами на каждом узле.

ОТСТУПЛЕНИЕ:
Сервис csync2 может использоваться не только для создания отказоустойчивого кластера Corosync/Pacemaker. Например, если есть несколько серверов, у которых меняются конфигурационные файлы и эти файлы периодически нужно синхронизировать по критерию самый свежий файл.


Итак, устанавливаем csync2 и простейшую базу данных для хранения мета-данных (sqlite).
$ sudo yum install csync2 libsqlite3x-devel

Тут нас поджидает подводный камень.

Поскольку csync2 и crmsh не являются родными для CentOS, то без дополнительных танцев сразу после установки они не заработают. Вызов crm влечет вызов утилиты csync2, которой в свою очередь не хватает парочки systemd-юнитов. Почему этих файлов нет в пакете csync2 для CentOS мне неизвестно. Замечу, что в коммерческом дистрибутиве SLES (crmsh там родной) все необходимые файлы есть, все работает из коробки сразу после установки пакетов.
Итак, создадим и добавим недостающие systemd-юниты.
Первый называется csync2.socket и содержит:
[Socket]ListenStream=30865Accept=yes[Install]WantedBy=sockets.target

Второй называется csync2@.service с таким содержимым:
[Unit]Description=csync2 connection handlerAfter=syslog.target[Service]ExecStart=-/usr/sbin/csync2 -i -vStandardInput=socketStandardOutput=socket

Оба файла нужно разместить в стандартной папке systemd /usr/lib/systemd/system.

Юнит, относящийся к сокету, нужно активировать и установить в автозапуск при загрузке ОС:
node1,2,3$ sudo systemctl enable --now csync2.socket

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

Теперь у нас все готово к началу работ по настройке кластера.

Настройка кластера с помощью crm


Настройка кластера производится в 2 этапа инициализация, затем создание и добавление ресурсов. Инициализация кластера с настроенным сервисом синхронизации конфигураций csync2 производится на одном узле.

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

На всякий случай сначала удалим кластер (на всех узлах) с помощью такого набора команд:
node1,2,3$ sudo systemctl stop corosync;sudo find /var/lib/pacemaker/cib/ -type f -delete; sudo find -f /etc/corosync/ -type f -delete

Далее надо выполнить команду инициализации кластера:
node1$ sudo crm cluster init --name demo-cluster --nodes "node1 node2 node3" --yes

где demo-cluster название нашего кластера.

По этой команде создаются необходимые файлы в папке /etc/corosync: corosync.conf, ключ авторизации authkey, а также прописываются ssh-ключи для беспарольной авторизации и выполнения команд в кластере с привилегиями суперпользователя root (на всех трех узлах кластера).

По умолчанию инициализация кластера выполняется в режиме multicast. Но есть также возможность проинициализировать кластер в режиме unicast:
node1$ sudo crm cluster init --unicast --name demo-cluster --nodes "node1 node2 node3" --yes

Кластер проинициализирован и запущен.
Проверить работоспособность можно с помощью консольного монитора состояния кластера crm_mon:
node1$ sudo crm_mon -Afr

Далее можно приступать к созданию ресурсов в кластере.

Создание ресурсов в кластере


Для начала поменяем некоторые значению по умолчанию. Например, порог миграции ресурсов migration-threshold по умолчанию равен 0. Меняем на 1, чтобы после первого сбоя ресурсы мигрировали на другой узел.

node1$ sudo crm configure rsc_defaults rsc-options: migration-threshold=1 resource-stickiness=INFINITY

По умолчанию, кластер запускается в симметричном режиме.

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

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

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

Если, вдруг, вам когда-то понадобится изменить режим кластера с симметричного на несимметричный, то достаточно ввести команду:
node1$ sudo crm configure property symmetric-cluster=false

Мы оставим этот параметр без изменения.

Включаем механизм stonith:
node1$ sudo crm configure property stonith-enabled=yes

Создадим и добавим ресурс виртуальный IP адрес:
node1$ sudo crm configure primitive master-vip IPaddr2 op start timeout=20s interval=0 op stop timeout=20s interval=0 op monitor timeout=20s interval=10s params ip=<virtual IP> nic=eth0

где <virtual IP> виртуальный IP-адрес в кластере.

С помощью монитора состояния кластера crm_mon можно убедиться в том, что ресурс успешно создан и запущен на первом попавшемся узле:
node1$ sudo crm_mon -Afr

Создадим ресурс postgresql и назовем его pg:
node1$ sudo crm configure primitive pg pgsql op start interval=0 timeout=120s op stop interval=0 timeout=120s op monitor interval=30s timeout=30s op monitor interval=29s role=Master timeout=30s params pgctl="/opt/pgpro/std-11/bin/pg_ctl" psql="/opt/pgpro/std-11/bin/psql" pgdata="/var/lib/pgpro/std-11/data" pgport="5432" repuser=postgres master_ip=<virtual IP> rep_mode=sync node_list="node1 node2 node3"

ПРИМЕЧАНИЕ:
В данном примере пути расположения бинарников и БД указаны по умолчанию для версии Postgres Pro Std 11. Также для упрощения указан пользователь для репликации postgres. Но ничто не мешает вам изменить умолчательные пути и пользователя репликации на свои.


Хочу обратить внимание на параметр rep_mode: он задан sync. Это означает, что в отказоустойчивом кластере хотя бы одна реплика будет синхронной. Синхронность реплики в кластере обеспечивает RPO=0 (кластер без потерь данных в случае сбоя).

Зададим тип ресурса Master-Standby (ms):
node1$ sudo crm configure ms mspg pg meta target-role=Master clone-max="3"

Нам нужно, чтобы ресурсы vip-master и mspg в режиме мастер запускались на одном узле:
node1$ sudo crm configure colocation pgsql-colocation inf: master-vip:Started mspg:Master

Указываем порядок запуска ресурсов сначала СУБД в режиме мастер, потом виртуальный IP:
node1$ sudo crm configure order order-promote-pgsql Mandatory: mspg:promote master-vip:start

Таким образом, мы создали 2 необходимых ресурса виртуальный IP адрес и ресурс postgresql.

Теперь можно переходить к настройке фенсинга в отказоустойчивом кластере.

Фенсинг узлов


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

Для начала можно ознакомиться со списком всех агентов фенсинга:
node1$ sudo crm ra list stonith

На моем стенде node1, node2, node3 это виртуальные машины, которые запущены и управляются с помощью гипервизора KVM. Соответственно, ресурс-агент фенсинга для KVM называется fence_virsh.

Вывести полную информацию о fence_virsh:
node1$ sudo crm ra info stonith:fence_virsh

Прежде чем создавать ресурс фенсинга, рекомендую проверить работоспособность всей цепочки от ОС виртуалки до гипервизора.

Проверка работоспособности фенсинга для узла node1 выглядит так:
node1$ fence_virsh -a <hypervisor IP> -l <username>-p <password> -n node1 -x --use-sudo -o status

где username & password учетная запись на хосте гипервизора.

Фенсинг для node1 настраивается так:
node1$ sudo crm configure primitive fence-node1 stonith:fence_virsh params ipaddr=<hypervisor IP> ip=<hypervisor IP> login=<username> username=<username> passwd=<password> pcmk_host_list=node1 sudo=1 op monitor interval=60s

ПРИМЕЧАНИЕ:
Ресурсы фенсинга не должны запускаться на своих узлах, иначе фенсинг может не сработать.

Следующее правило расположения запретит ресурсу фенсинга для узла node1 располагаться на этом узле:
node1$ sudo crm configure location l_fence_node1 fence-node1 -inf: node1

Для node2:
node1$ sudo crm configure primitive fence-node2 stonith:fence_virsh params ipaddr=<hypervisor IP> ip=<hypervisor IP> login=<username> username=<username> passwd=<password> pcmk_host_list=node2 sudo=1 op monitor interval=60snode1$ sudo crm configure location l_fence_node2 fence-node2 -inf: node2

Для node3:
node1$ sudo crm configure primitive fence-node3 stonith:fence_virsh params ipaddr=<hypervisor IP> ip=<hypervisor IP> login=<username> username=<username> passwd=<password> pcmk_host_list=node3 sudo=1 op monitor interval=60snode1$ sudo crm configure location l_fence_node3 fence-node3 -inf: node3

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

Инициализация кластера с помощью crm без csync2


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

Сначала вариант с использованием multicast.

Все команды выполняются на одном узле, например, на node1.
node1$ sudo crm cluster init --name demo-cluster --nodes "node1" --yes

По этой команде создаются необходимые файлы в папке /etc/corosync: corosync.conf, ключ авторизации authkey.

Далее нужно скопировать авторизационный файл authkey и corosync.conf на узлы node2 и node3:
node1$ sudo scp /etc/corosync/{authkey,corosync.conf} node2:/etc/corosync/node1$ sudo scp /etc/corosync/{authkey,corosync.conf} node3:/etc/corosync/

На остальных узлах (на node1 кластер уже запущен) запустить кластер:
node2,3$ sudo crm cluster start<source>С помощью монитора crm_mon можно убедиться, что кластер проинициализирован и запущен:<source lang="sh">node1$ sudo crm_mon -Afr


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

Все команды выполняются на одном узле, например, на node1.
node1$ sudo crm cluster init --unicast --name demo-cluster --nodes "node1" --yes

Открываем файл /etc/corosync/corosync.conf и добавляем строки в секцию nodelist:
node {ring0_addr: node2nodeid: 2}node {ring0_addr: node3nodeid: 3}

В секции quorum меняем число голосов:

expected_votes: 3

Далее необходим рестарт сервиса corosync на первом узле:
node1$ sudo systemctl restart corosync

Затем нужно скопировать файл authkey и отредактированный corosync.conf на узлы node2 и node3:
node1$ sudo scp /etc/corosync/{authkey,corosync.conf} node2:/etc/corosync/node1$ sudo scp /etc/corosync/{authkey,corosync.conf} node3:/etc/corosync/

На остальных узлах (на node1 кластер уже запущен) запустить кластер:
node2,3$ sudo crm cluster start

С помощью монитора crm_mon можно убедиться, что кластер проинициализирован и запущен:
node1$ sudo crm_mon -Afr

На этом инициализация кластера без csync2 закончена.

Вспомогательные команды crm



При работе с кластером могут пригодиться некоторые crm-команды.
Для удобства команды и пояснения сведены в таблицу 3:



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

Перевод Измеряем расходы на память у Postgres процессов

10.06.2021 18:12:02 | Автор: admin

Это вольный перевод поста одного из сильных разработчиков 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.

Подробнее..

Категории

Последние комментарии

© 2006-2021, personeltest.ru