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


Как правило, они отчаянно маскируются, и даже у нас в СБИС их найти можно в абсолютно разных сферах управления предприятием: контроле рабочего времени, оценке загрузки линий АТС или даже в бухгалтерском учете.

«Отличие enterprise [решения] от всего остального — он всегда идёт от запросов бизнеса и решает какую-то бизнес-задачу.» [src]

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

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

Если вы регулярно отлаживаете производительность запросов к PostgreSQL, а EXPLAIN (ANALYZE, BUFFERS) ... — любимый инструмент познания особенностей работы этой СУБД, то новые полезные «фишки» нашего сервиса визуализации и анализа планов explain.tensor.ru наверняка пригодятся вам в этом нелегком деле.

Но сразу напомню, что без полноценного всестороннего мониторинга базы PostgreSQL использовать только анализ плана — это выступать с позиции мудреца #5!


[источник КДПВ, «Слепые и слон»]

Программисты привыкли, что у айтишников крутые офисы, хорошие зарплаты и полный соцпакет. Это стало стандартом. Мы в Тензоре не останавливаемся на этом, и чтобы разрабы не заскучали, а новые к нам хотели, применяем и необычные методы мотивации. Какие — расскажут сами программисты.

Видимо, это осень так влияет, что за последний месяц на PostgreSQL уже и в «Морской бой» играли, и «Жизнь» Конвея эмулировали… Что уж оставаться в стороне! Давайте и мы потренируем мозг в реализации нетривиальных алгоритмов на SQL.

Тем более, сегодняшняя тема родилась из обсуждения моей же статьи «PostgreSQL Antipatterns: «Бесконечность — не предел!», или Немного о рекурсии», где я показал, как можно заменить рекурсивное чтение иерархичных данных из таблицы на линейное.

Прочитать-то мы прочитали, но ведь чтобы для вывода упорядочить элементы дерева в соответствии с иерархией, уж точно придется воспользоваться рекурсией! Или нет? Давайте разберемся, а заодно решим на SQL пару комбинаторных задач.

«Просто так» результат SQL-запроса возвращает записи в том порядке, который наиболее удобен серверу СУБД. Но человек гораздо лучше воспринимает хоть как-то упорядоченные данные — это помогает быстро сравнивать соответствие различных датасетов.

Поэтому со временем у разработчика может выработаться рефлекс «Дай-ка я на всякий случай это вот отсортирую!» Конечно, иногда подобная сортировка бывает оправдана прикладными задачами, но обычно такой случай выглядит как в старом анекдоте:

Программист ставит себе на тумбочку перед сном два стакана. Один с водой — на случай, если захочет ночью пить. А второй пустой — на случай, если не захочет.

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

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


СУБД в этом отношении работают по тем же принципам — "сказали копать, я и копаю". Ваш запрос может не только затормозить соседние процессы, постоянно занимая ресурсы процессора, но и «уронить» всю базу целиком, «съев» всю доступную память. Поэтому защита от бесконечной рекурсии — обязанность самого разработчика.

В PostgreSQL возможность использовать рекурсивные запросы через WITH RECURSIVE появилась еще в незапамятные времена версии 8.4, но до сих пор можно регулярно встретить потенциально-уязвимые «беззащитные» запросы. Как избавить себя от проблем подобного рода?

На прошедшей неделе вышло сразу две статьи (от Hubert 'depesz' Lubaczewski и автора самого патча Alvaro Herrera), посвященные реализованной в грядущей версии PostgreSQL 13 поддержке опции WITH TIES из стандарта SQL:2008:

OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

Что это, и как оно избавляет от проблем с реализацией пейджинга, о которых я рассказывал в статье «PostgreSQL Antipatterns: навигация по реестру»?

Про префиксное дерево (Trie) написано немало, в том числе и на Хабре. Вот пример, как оно может выглядеть:


И даже реализаций в коде, в том числе на JavaScript, для него существует немало — от «каноничной» by John Resig и разных оптимизированных версий до серии модулей в NPM.

Зачем же нам понадобилось использовать его для сервиса по сбору и анализу планов PostgreSQL, да еще и «велосипедить» какую-то новую реализацию?..

Этот пост родился как расширенный ответ на умозрительную задачу, обозначенную в статье «Хроники пэйджинга».

Пусть у нас есть реестр документов, с которым работают операторы или бухгалтеры в СБИС, вроде такого:



Традиционно, при подобном отображении используется или прямая (новые снизу) или обратная (новые сверху) сортировка по дате и порядковому идентификатору, назначаемому при создании документа — ORDER BY dt, id или ORDER BY dt DESC, id DESC.

Типичные возникающие при этом проблемы я уже рассматривал в статье «PostgreSQL Antipatterns: навигация по реестру». Но что если пользователю зачем-то захотелось «нетипичного» — например, отсортировать одно поле «так», а другое «этак» — ORDER BY dt, id DESC? Но второй индекс мы создавать не хотим — ведь это замедление вставки и лишний объем в базе.

Можно ли решить эту задачу, эффективно используя только индекс (dt, id)?

Многие, кто уже пользуется explain.tensor.ru — нашим сервисом визуализации планов PostgreSQL, возможно, не в курсе одной из его суперсособностей — превращать сложно читаемый кусок лога сервера…


… в красиво оформленный запрос с контекстными подсказками по соответствующим узлам плана:


В этой расшифровке второй части своего доклада на PGConf.Russia 2020 я расскажу, как нам удалось это сделать.

С транскриптом первой части, посвященной типовым проблемам производительности запросов и их решениям, можно ознакомиться в статье «Рецепты для хворающих SQL-запросов».

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


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

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

Intro

Напомню некоторые вводные:

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

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

Более полно о принципах работы сервиса можно посмотреть в видео доклада и прочитать в статье «Массовая оптимизация запросов PostgreSQL».

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

Таблица счетчиков

Казалось бы — чего проще? Заводим отдельную табличку, в ней — запись со счетчиком. Надо получить новый идентификатор — читаем оттуда, чтобы записать новое значение — делаем UPDATE…

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

• постоянных пересекающихся блокировок при UPDATE
  см. PostgreSQL Antipatterns: сражаемся с ордами «мертвецов»
• постепенной деградации скорости доступа к данным таблицы счетчиков
  см. PostgreSQL Antipatterns: обновляем большую таблицу под нагрузкой
• … и необходимости ее зачистки при активных транзакциях, которые будут вам мешать
  см. DBA: когда пасует VACUUM — чистим таблицу вручную

Автоматическое определение клиента и его региона по входящему телефонному звонку стало неотъемлемой частью любой развитой HelpDesk или CRM-системы. Только надо уметь делать это быстро — тогда появляется масса возможностей.

Например, можно менеджеру сразу показать из какого города идет звонок, подтянуть актуальный прайс и условия доставки, вывести карточку звонящего клиента, последние сделки с ним, конкретное контактное лицо,… — да много чего полезного, как это умеет наш СБИС CRM!

А как этот функционал реализовать самостоятельно? Оказывается, не так уж сложно. Собрать и опробовать работающую модель можно, буквально, «на коленке» — нужна только связка из Node.js и PostgreSQL.

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

Поэтому внутри каждого отдельного процесса нет никаких традиционных «странных» проблем с параллельным выполнением кода, блокировками, race condition,… А разработка самой СУБД приятна и проста.

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

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

Со схемами работы некоторых параллельных узлов можно ознакомиться в статье «Parallelism in PostgreSQL» by Ibrar Ahmed, откуда взято и это изображение.

Правда, читать планы в этом случае становится… нетривиально.

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

Сегодня на предельно простых примерах посмотрим, к чему это может приводить в контексте использования GROUP/DISTINCT и LIMIT вместе с ними.

Вот если вы написали в запросе «сначала соедини эти таблички, а потом выкинь все дубли, должен остаться только один экземпляр по каждому ключу» — именно так и будет работать, даже если соединение вовсе не было нужно.

И иногда везет и это «просто работает», иногда — неприятно сказывается на производительности, а иногда дает абсолютно неожидаемые с точки зрения разработчика эффекты.


Ну, может, не настолько зрелищные, но…

«Сладкая парочка»: JOIN + DISTINCT

SELECT DISTINCT
  X.*
FROM
  X
JOIN
  Y
    ON Y.fk = X.pk
WHERE
  Y.bool_condition;

Как бы понятно, что хотели отобрать такие записи X, для которых в Y есть связанные с выполняющимся условием. Написали запрос через JOIN — получили какие-то значения pk по несколько раз (ровно сколько подходящих записей в Y оказалось). Как убрать? Конечно DISTINCT!

Продолжаем открывать для публичного доступа новый функционал нашего сервиса анализа планов выполнения запросов в PostgreSQL explain.tensor.ru. Сегодня мы научимся определять больные места навскидку в больших и сложных планах, лишь мельком взглянув на них вооруженным глазом…


В этом нам помогут различные варианты визуализации:

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

Поэтому, когда на каком-то из тысяч подконтрольных серверов случается аномальное потребление ресурсов (CPU, памяти, диска, сети, ...), возникает потребность разобраться «кто виноват, и что делать».


Для оперативного мониторинга использования ресурсов Linux-сервера «в моменте» существует утилита pidstat. То есть если пики нагрузки периодичны — их можно «высидеть» прямо в консоли. Но мы-то хотим эти данные анализировать постфактум, пытаясь найти процесс, создавший максимальную нагрузку на ресурсы.

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



В этой статье рассмотрим, как все это можно экономично расположить в БД, и как максимально эффективно собрать по этим данным отчет с помощью оконных функций и GROUPING SETS.

После публикации статьи об особенностях типизации в PostgreSQL, первый же комментарий был про сложности работы с вещественными числами. Я решил бегло пробежаться по коду доступных мне SQL-запросов, чтобы посмотреть, насколько часто в них используется тип REAL. Достаточно часто используется, как оказалось, и не всегда разработчики понимают опасности, стоящие за ним. И это несмотря на то, что в Интернете и на Хабре достаточно много хороших статей про особенности хранения вещественных чисел в машинной памяти и о работе с ними. Поэтому в этой статье я постараюсь применить такие особенности к PostgreSQL, и попробую «на пальцах» рассмотреть связанные с ними неприятности, чтобы разработчикам SQL-запросов было легче избежать их.

Документация PostgreSQL содержит лаконичную фразу: «Управление подобными ошибками и их распространение в процессе вычислений является предметом изучения целого раздела математики и компьютерной науки, и здесь не рассматривается» (при этом благоразумно отсылая читателя к стандарту IEEE 754). Что за ошибки здесь имеются в виду? Давайте обсудим их по-порядку, и скоро станет понятно, почему я снова взялся за перо.

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

В логах у нас отражается всего 3 вида событий, которые могут происходить с блокировкой:

• ожидание блокировки
  LOG: process 38162 still waiting for ExclusiveLock on advisory lock [225382138,225386226,141586103,2] after 100.047 ms
• получение блокировки
  LOG: process 38162 acquired ExclusiveLock on advisory lock [225382138,225386226,141586103,2] after 150.741 ms
• взаимоблокировка
  ERROR: deadlock detected

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