Палантир. Часть 17. Оптимизация базы данных.
#палантир@eshu_coding

В ближайшее время я планирую запустить поисковик @palantir_search_bot в общее пользование. Пришло время нагрузочного тестирования. Для этого я изобразил отдельный проектик, который имитирует поискового бота.

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

Из этих слов случайным образом формируется пул запросов.

Запрос отправляется на сервер, ожидается ответ. Сразу по получении ответа - посылается следущий запрос. И так в N потоков.

Запустил в 1 поток: всё отлично.
Запустил в 10 потоков: всё отлично, постгрес не замечает нагрузки.
Запустил в 30 потоков. Постгрес не замечает нагрузки. Проходит пара минут и всё виснет.

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

Оставил на ночь - постгрес не отвис: память утекла.

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

#postgresql

Палантир. Часть 19. Результаты нагрузочного тестирования.
#палантир@eshu_coding

В настоящий момент Master сервер,на котором осуществляется поиск, представляет собой следующее:
8 Гб оперативки, 4 ядра CPU, 1.5 Тб SSD диск. ОС - 18я серверная убунта, база данных - PostgreSQL 13.3. Объем базы - чуть больше 1 Тб, около 800 млн строк в основной таблице, по которой и осуществляется полнотекстовый поиск. Принцип формирования запросов я рассказывал выше.

После устранения утечки памяти (рассказывал выше) и оптимизации конфигурации Postgres по части памяти, сервис свободно держит 100 RPS (запросов в секунду).

В рамках экспериментов я выкручивал мощность машины до 16 ядер и 64 Гб оперативки. В таком сетапе удерживается нагрузка в 500-750 RPS.

Постгрес могёт однако.

#postgresql

Палантир. Часть 20. Ускорение поиска.
#палантир@eshu_coding

Оценить результат вы можете в боте: @palantir_search_bot

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

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

А потом в какой-то момент мне стукнуло в голову решение: основная таблица - messages - у меня секционирована по месяцам.

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

Соответственно, таблица messages представляет собой около 200 таблиц вида messages_01_2021, messages_02_2021 и так далее, с 2014 по 2030 год.

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

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

Также был доработан поисковый запрос на уровне c# - теперь он проводится в 3 этапа:
1. Запрос в последний месяц
2. Запрос в предпоследний месяц
3. Запрос во всю остальную базу, если нужно.

Результаты замеров скорости отклика на поиске глубиной в месяц:
1. Без кеширования - среднее время 200 мс, максимальное - 15 секунд
2. С закешированными индексами последних двух месяцев - среднее время 120 мс, максимальное - 6 секунд
3. С закешированными таблицами messages_10_2021 и messages_11_2021 - среднее время 80 мс, максимальное - 1.5 секунды

Под вариант "удобно использовать" подходит исключительно 3й, потому у сервера теперь 32 Гб оперативки (+3 тысячи к месячной плате)

#postgresql

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

Делал я полнотекстовый поиск в базе проекта #палантир@eshu_coding, приправленный дополнительным параметром: id юзера или канала, по которому осуществляется поиск. Эти поля у меня проиндексированы индексом hash, потому выборка записей по конкретным значениям этих полей мгновенная. Полнотекстовый поиск же штука относительно медленная.

Запрос вида

select * from messages where full_text_req @@ post_text and chat_id = id;

где @@ , грубо говоря, оператор соответствия текста запросу, на всей базе выполняется секунд 20.

Судя по всему, Postgres проверяет все записи на соответствие запросу, а затем уже применяет фильтр по id.

Для ускорения запроса мне на помощь пришли Common Table Expressions (CTE). Суть их - в поэтапном выполнении сложного запроса. Если переписать запрос с использованием CTE, скорость выполнения становится стабильно около 300 мс.

Переписанный запрос:

with sel as (select * from messages where chat_id = id) select * from sel where text @@ full_text_request;

#кодинг
#postgresql

Оказалось довольно удобно совмещать ORM с хранением jsonb в постгресе. Делаешь модельку вида:

class Model
{
Int Field1,
String Field2,
String Field3,
InnerModel ClassField
}

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

А остальные поля, которые надо будет только читать, в т.ч. содержащие подклассы и массивы, уезжают в поле, содержащее класс (ClassField в примере). Помечяешь поле атрибутом "хранить как jsonb" и ORM сама сериализует и десериализует его. В итоге, вместо веера из десятка таблиц, разложенных по нормальным формам, получаем одну, время чтения и модификации данных соответственно уменьшается.

Итого, лёгким движением руки Postgres превращается, Postgres превращается...
в мутанта, содержащего признаки реляционки и монги.

/Напевает себе под нос/ Денормализация! Денормализация!

#postgresql

Послушал открытый урок Otus по некоторым особенностям оптимизации работы кластеров PostgreSQL. В целом, получилось очень познавательно: некоторые из услышанных вещей, упомянутых лишь краем, вызвали реакцию "хм, а так было можно?"

Вот перечень запомнившихся моментов:
1. Оптимизация скорости работы базы данных за счёт разнесения разных таблиц по разным дискам с помощью указания табличных пространств (tablespace). Я про что-то подобное мельком читал во время работы над Палантиром #палантир@eshu_coding, но воспринимал эти махинации исключительно с т.з. размещения большой базы, не влезающей на диск.

2. Оптимизация путем указания стоимости операции одиночного чтения с произвольного места диска. С классических hdd намного быстрее читать последовательно записанные данные. Потому в случае соотношения размера таблицы к размеру индекса 4 к 1 читать с hdd всю таблицу может быть быстрее, чем сканировать индекс и брать нужное из таблицы. А вот на SSD такой разницы нет, и выставив параметр конфигурации random_page_cost=1 мы ощутимо ускорим чтение данных.

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

4. Узнал про возможность конкуррентного переиндексирования. Предположим, индекс раздулся и начал тупить и есть много места. Можно его удалить и перестроить с нуля. Но "на живую" это приведет к большим проблемам. А оказывается можно запустить фоновое переиндексирование.

5. Узнал, что в постгрес таки есть механизм подсказок планировщику запросов, что ему делать. Для этого нужно расширение pg_hint_plan.

6. Услышал оговорку "обычно балансировку запросов между мастером и репликами у нас делают сами приложения". А так было можно?! Полез читать - оказывается, у стандартного шарпового коннектора Npgsql и балансировщик и координатор выборов нового мастера в случае сбоя есть под капотом.

Курс я конечно покупать не буду, но за 1.5 часа времени информация получена очень полезная.

#postgresql

#dotnext, просмотр в записи.

B-Tree индексы в PostgreSQL.

Доклад получился крайне полезным: в связной легкодоступной форме пересказали то, что я в общем-то знал по устройству внутрянки BTree индексов.

Кроме того, в процессе доклада всплыла ещё масса полезностей, которые ускользнули от моего внимания ранее;

1. explain (analyze, costs off, buffers) - синтаксис для просмотра плана запроса с прогнозом числа чтений страниц. Heap fetches - тот самый прогноз походов в таблицу после сканирования индекса для проверки наличия записи в таблице (если она ещё не зачищена от мусора вакуумом).

2. Спикер заострил внимание на разнице index scan и index only scan в плане выполнения запросов (последний не полезет читать доп информацию из таблицы после выполнения, ему для выполнения хватит инфы их индекса).

3. Спикер рассмотрел разницу по работе с BTree индексом в случае индексирования по bigint и по uuid. bigint при добавлении растит индекс в одну сторону. uuid - растит хаотично, расходует больше оперативной памяти в процессе работы. Разница по быстродействию может доходить до 7 раз. Отчасти проблему решает использование uiid v7, в котором используется временная метка. Но это не про стандартную библиотеку c#:)

4. Удаленные из таблицы данные остаются какое-то время залипать в индексе и накручивают Heap fetches в плане выполнения запроса. Помогает не дожидаться долго и тяжёлого вакуума (процесс очистки от мусора) фича из PostgreSQL 14 - bottom-up deletion - зачистка индекса от мусора в процессе вставки новых данных.

5. Спикер упомянул интересный путь оптимизации запросов: учитывать сортировку в индексе и идти к результату с определенной стороны с помощью CTE, например вызовом рекурсивного подзапроса > treshold_value, с постепенным нарастанием treshold_value.

#postgresql
#conf

Попробовал небольшую махинацию на связке ORM Entity Framework и СУБД PostgreSQL.

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

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

Для отработки механики сделал что-то вроде DataBase first, чтобы "натянуть" ORM на секционированную таблицу и удостовериться, что всё нормально работает.

Экспортнул результат автогенерации EF при создании базы и доработал напильником: снес старую таблицу, добавил вместо неё партицированную. Чтобы жизнь медом не казалась - сделал у таблицы foreign key, указывающий на саму себя (теперь можно хранить древовидные структуры).

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

Результат вылил на гитхаб. SQL живет в файле.

#postgresql

Sphagnum. Часть 4. Техобзор. Репликация и отказоустойчивость в PostgreSQL.
#sphagnum@eshu_coding

PostgreSQL предоставляет два вида репликации: физическую и логическую. Физическая репликация создаёт полную копию master-ноды, из которой при желании можно читать, но писать нельзя. Между нодами передаются байтики, записываемые в WAL. В режиме одиночного инстанса WAL удаляется после применения транзакции к данным. При включенной физической репликации - WAL сохраняются дольше и по мере необходимости отдаются репликам.

Логическая репликация передает между инстансами SQL команды. В контексте разработки брокера сообщений - не интересна.

Из коробки механизма поддержания отказоустойчивого кластера в постгресе нет, но есть сторонние решения, например - patroni. Каждому из инстансов replica-set-а постгресов закрепляется по надзирающему сервису. Эти сервисы общаются между собой. В случае падения мастера - выбирается новый мастер, простым голосованием. Прочёл про интересный механизм: если мастер остается изолирован от других реплик, он не начинает думать что он единственный такой в мире, а принудительно гасится patroni, дабы минимизировать шансы грядущей синхронизации двух мастеров.

Итого, из того, что мне пока хотелось бы взять для своего проекта:
1. Механизм распространения WAL для синхронизации.
2. Принудительное гашение мастера при изоляции его от остальной части кластера.

#sphagnum_theory@eshu_coding
#postgresql

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

Но новость очень приятная.

#postgresql
#csharp

Попал я на обучение постгресу. Достаточно продвинутый уровень, для миддл разработчиков и выше, ранее активно пользовавшихся MS SQL или Oracle. У меня основная СУБД в стеке - PostgreSQL, но интересного и нового довольно много. Далее будет что-то типа заметок, содержащих новые для меня вещи, которые я узнал.

1. Штатная среда для написания запросов для постгреса - PgAdmin, очень функциональна в плане администрирования базы. Я всю жизнь сидел на DataGrip. Она хороша для написания запросов, спору нет. Но для сложных вещей, связанных с администрированием - лучше использовать PgAdmin. Как минимум, многие запросы для администрирования (ограничения для пользователей, собственные типы данных) проще сгенерировать через UI чем по кусочкам собирать копипастя из документации. В дополнение там очень подробный визуализатор внутренних объектов базы: в ui можно посмотреть кастомные типы, расширения, инструменты для полнотекстового поиска. В DataGrip или Dbeaver все намного беднее в этом плане.

2. Очень понравилось что-то типа кастомного типа: Domain. Это унаследованный базовый тип с прикрученным к нему ограничением на диапазон значений. Например - терминал аэропорта. Предположим, есть терминалы A, B, C, D, E. Унаследуемся от текстового типа, поставим ограничение - только одна заглавная буква из этого диапазона.

3. Механизм TOAST. Когда размер поля в таблице превышает определенное (настраиваемое) значение, в таблицу пишется ссылка на элемент "теневой таблицы", куда собственно записываются данные из поля. Для типа text по дефолту это 2Кб. Максимальное значение - 1Гб, обусловлено особенностями самого постгреса. Для записываемого в теневую таблицу значения можно настроить механизм сжатия. Кроме простой экономии занимаемого места, достигается и буст производительности: при чтении данных из таблицы, например при полном сканировании, вместо тяжёлых значений читаются десятки байт, содержащих ссылку на нужное место теневой таблицы, куда постгрес полезет только в случае реальной нужды.

#postgresql

Продолжаю конспект интересных вещей, услышанных на обучении постгресу. Начало выше.

4. Механизм шардирования постгреса на партицированную таблицу, партиции которой - foreign table на других инстансах постгреса - нормальная практика, применяется, например, в Авито.

5. Постгресовский varchar от типа text под капотом отличается только наличием проверки на размер, потому если проверка на размер не нужна - просто используем text.

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

7. Есть крайне полезное расширение для диагностики таблиц и индексов: pgstattuple. Ставишь его, делаешь select * from pgstatindex('my_index') и тебе выдаётся статистика, насколько разреженный индекс. И по этой статистике принимаешь обоснованное решение, пора ли индекс перестраивать.

8. Вместо limit + offset для пагинации возвращаемых результатов лучше использовать fetch: он гибче.

#postgresql

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

9. Я узнал о существовании фильтрованных индексов. Например, при создании btree индекса по дате указываешь > 01.01.2024 - и твой индекс будет работать только на данные в указанном тобой условии. Собственно, можно оставить архивированое в архиве и работать только со свежими данными.

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

11. Узнал некоторые особенности индексов, о которых раньше не задумывался.
Хэш индексы немногим быстрее btree, но при этом лишены упорядочивания, ускорения запросов по интервалу, возможности сортировать таблицу по ним. Зато, если мы строим их по большим строкам, по которым надо будет искать только на условие равенства - они дают существенный выигрыш по размеру индекса. При этом, размер хэша меняется, подстраиваясь под данные так, чтобы был минимум коллизий.
Есть такой тип индекса - BRIN. Отлично работает для индексации неизменяемого вечнохранимого набора данных, ранжированных по какому-то параметру. Занимает мизерное пространство на диске, но в большинстве кейсов бесполезен.

#postgresql

Еще небольшая порция заметок про постгрес.

12. Параметры конфигурации, например work mem, можно менять непосредственно для пользователя. Соответственно, можно делать разные настройки для юзеров - аналитиков и юзеров, под которыми в базу ходят сервисы.

13. Узнал о существовании разных джоинов - hash, merge и nested loop join. Первый использует хэш таблицу, чтобы объединить данные из разных таблиц, второй - использует сортировки, третий тупо перебирает данные. Первый хорош для джоина неиндексированных данных. Второй берет сортировку исходя из индексов, соответственно хорош при наличии сортированных (btree) индексов по обеим колонкам, по которым осуществляется джоин. Третий хорош, чтобы сджоинить мизерную таблицу с большой.

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

#postgresql

Окончание заметок про постгрес.

15. Если у нас возник дедлок строки или какой-то запрос барабанит вечность - идем в представления pg_locks и pg_stat_activity, ищем источник проблемы и делаем с ним pg_cancel_backend или pg_terminate_backend.

16. Триггеры плохи не только сложностью отлова багов, но и тем что за счёт вызова доп функции на plsql, что трудозатратно.

17. Чистка мусора - vacuum - срабатывает автоматически при обновлении 20% записей. Можно подкрутить персональные настройки вакуумирования для таблиц.

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

#postgresql

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

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

#postgresql

#ef_core
#db_design
#postgresql

Тонкости JSONB в PostgreSQL и EF Core: Как постичь дзен?

Сегодня мы рассмотрим работу с JSONB в базе данных PostgreSQL совместно с родной для .NET ORM от Microsoft — Entity Framework Core.

Мы разберём, как удобно хранить данные в денормализованном виде, как защититься от параллельных изменений и как поступать, когда необходимо выстраивать отношения между сущностями, чьи атрибуты упакованы в JSONB. Помимо этого, мы затронем такие темы, как optimistic concurrency, data encryption, миграции подобных сущностей и их версионирование.

Итак, начнём.

Тонкости JSONB в PostgreSQL и EF Core: Как постичь дзен?

Раньше я упоминал про использование jsonb совместно с ORM.

Вышла очень крутая статья, раскрывающая нюансы этого вопроса.

#postgresql

Был в начале декабря на конференции Highload++ (да, только сейчас нашлось время оформить интересное услышанное в посты). Дальше посты будут под тегом #highload

Услышал ещё некоторые занятные вещи про постгресс.

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

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

pg_stat_replication - посмотреть что происходит в репликации, какой из трёх занятых в ней процессов (отправка WAL по сети, прием WAL и складирование на диск, чтение и применение изменений к реплике) тормозит.

В постгресе 15+ prefetch при репликации по факту не используется, лучше отключить.

#postgresql
#conf

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

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

За прошедшие 5 лет я сменил 4 места работы:
1. Фирма, занимающая АСУ ТП в области учёта ресурсов.
2. Медтех стартап в Сколково, делали системы поддержки принятия врачебных решений.
3. Сеть общепита, делал бэкенд службы доставки.
4. Банк, текущее место работы. Работаю в домене клиентских карточек.

Мой технологический стек:
C#, PostgreSQL. Плотно работал с MongoDB, RabbitMQ, Tarantool, умею строить базовую инфраструктуру: логи (Loki), метрики (Prometheus), девопсятина (gitlab, gitea, github actions, docker).

Поверхностно знаком с Apache Kafka, MS SQL и фронтовыми фреймворками - React.js и AvaloniaUI.

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

Далее будет навигация по каналу.

Общие теги:
#csharp@eshu_coding - общий тег для постов про разные аспекты разработки на языке программирования c#
#postgresql@eshu_coding - разные интересные моменты про PostgreSQL.
#devops@eshu_coding - мои эксперименты в девопсятине и инфраструктуре.
#mongodb@eshu_coding - записки про MongoDB.
#tarantool@eshu_coding - заметки про Tarantool.

Pet - проекты:
#палантир@eshu_coding - завершенный проект, которым я занимался весь 2021 год - поисковик по телеграму.
#sphagnum@eshu_coding - попытка написать свой брокер сообщений, пока застопорилась на стадии изучения теории и прототипирования по причине нехватки времени.

Книги:
#рихтер@eshu_coding - заметки и конспекты по основополагающей книге про C# - CLR via C#. Программирование на платформе Microsoft .NET Framework 4.5 на языке C#, Джеффри Рихтер. Хоть .NET 4.5 вышел до моего появления в IT, внутренности платформы во основном остались прежними.

Конспекты прослушанных выступлений на конференциях:
#dotnext@eshu_coding - Dotnext 2023
#highload@eshu_coding - Highload++ 2024

Шпаргалки и мои заметки для подготовки к собеседованиям #собес@eshu_coding

Природа и путешествия #природа@eshu_coding #путешествие@eshu_coding