Примитивный запрос - простой джойн и группировка. Традиционные методы оптимизации - казалось бы, что могло пойти не так?..
Небольшой эксперимент, на тему необходимости проверки любых гипотез в конкретных условиях.
Архитектура ИС: PostgreSQL, Node.js и highload
Примитивный запрос - простой джойн и группировка. Традиционные методы оптимизации - казалось бы, что могло пойти не так?..
Небольшой эксперимент, на тему необходимости проверки любых гипотез в конкретных условиях.
Для систем управления бизнесом часто приходится решать очень похожий класс задач по вычислению количества уникальных объектов на произвольном временном интервале. В контексте CRM это могут быть "пользователи, обращавшиеся на горячую линию на прошлой неделе", "контрагенты, оплатившие за последние 30 дней" или "потенциальные клиенты, с кем был контакт в этом квартале".
Искать в большом количестве фактов «уники» — всегда сложно и долго, если их достаточно много. Если интервалы фиксированы (календарные месяц/квартал/год), можно материализовывать такие агрегаты заранее. А если интервал — произвольный, как тогда эффективно найти ответ?
Большая часть оптимизаций запросов к базам PostgreSQL может выполняться "механически", следуя разного рода маркерам в плане выполнения запроса, которые подскажут, что и как можно ускорить. Но "глубинные" переработки алгоритма, вроде описанных в статье про DBA-детектив, требуют от разработчика детального понимания используемой структуры логических связей.
И хорошо, когда эта структура уже где-то описана и детально задокументирована. Но плохо, когда такая документация ничтожно мала, избыточно велика, сложно доступна...
А ведь она уже и так находится "под ногами" в момент анализа плана запроса - надо только лишь удобно увидеть ее!
Добро пожаловать под кат, если вдруг вы пропустили какие-то из наших статей за прошедший год об интересных и полезных возможностях PostgreSQL, которые мы узнаем при разработке нашей системы полного цикла управления бизнесом СБИС — от кадрового учета, бухгалтерии, делопроизводства и налоговой отчетности, до таск-менеджмента, корпоративного портала и видеокоммуникаций.
Если не видели дайджест за первый год — время наверстать упущенное!
SQL является мощным инструментом для обработки множеств, а функционал PostgreSQL позволяет делать многие вещи еще проще, поэтому идеально подходит для реализации некоторых алгоритмов на графах.
Причем работа с графами - это не просто разминка для ума, а вполне себе прикладная задача. Например, в прошлой статье мы сделали "из мухи - слона" волновым алгоритмом Ли, аналогичным используемому у нас в СБИС при расчете себестоимости в многокомпонентных актах выпуска.
А сегодня мы научимся генерации случайных лабиринтов алгоритмом Прима с использованием геометрических типов данных.
Правила игры очень просты: надо построить цепочку слов от начального (МУХА) до конечного (СЛОН), на каждом шаге меняя только одну букву. При этом могут использоваться только русские 4-буквенные нарицательные существительные в начальной форме: например, слова БАЗА, НОЧЬ, САНИ допускаются, а слова ЛИТЬ, ХОТЯ, РУКУ, НОЧИ, САНЯ, ОСЛО, АБВГ, ФЦНМ — нет.
Эта игра под названием «Дублеты» приобрела известность благодаря Льюису Кэрроллу — не только автору книг про Алису, но ещё и замечательному математику. В марте 1879 года он начал раз в неделю публиковать в журнале «Ярмарка тщеславия» по три задания в форме броских фраз: «Turn POOR into RICH» — «Преврати бедного в богатого», «Evolve MAN from APE» — «Выведи человека из обезьяны», «Make TEA HOT» — «Сделай чай горячим». В том же году он выпустил брошюру «Дублеты», подробно описал в ней правила и предложил читателям попрактиковаться на нескольких десятках примеров.
Александр Пиперски, "Из мухи — слона", «Квантик» №2, 2019 и №3, 2019
Сегодня мы научимся реализовывать на SQL волновой алгоритм, решив заодно классический пример из этой игры для конкретного словаря.
Как найти самые "горячие" запросы на вашем PostgreSQL-сервере? Поискать их в логе и проанализировать план или воспользоваться расширением pg_stat_statements.
А если в лог попадает миллион запросов за сутки?.. Тогда любое значение лимита pg_stat_statements.max
окажется недостаточно велико, чтобы собрать правдивую статистику. Так давайте собирать эту статистику прямо с планов!
Но для некоторых сервисов СБИС нам в "Тензоре" производительность запросов к базе настолько важна, что auto_explain.log_min_duration
приходится выставлять в единицы миллисекунд - и вот они, миллионы планов... Как не потеряться в них?
При реализации некоторых прикладных задач в рамках экосистемы СБИС случается сталкиваться с неочевидными возможностями PostgreSQL, которые позволяют вместо сложной логики создать решение "в один ход".
Сегодня на примере вполне реальной задачи рассмотрим такие возможности оператора INSERT ... ON CONFLICT
.
В первой части серии статей про работу с адресами по КЛАДР мы научились импортировать данные этого справочника к себе в базу и превращать их во что-то более удобное для дальнейшей работы.
Сегодня же займемся реализацией конкретных прикладных алгоритмов на этой структуре и рассмотрим, как можно реализовать мгновенную помощь пользователю при вводе адреса, используя возможности префиксного поиска в PostgreSQL.
Иногда при анализе производительности запроса на предмет "куда ушло все время" возникает стойкое ощущение deja vu, что вот ровно этот же кусок плана ты уже где-то раньше видел...
Пролистываешь выше - и таки-да, вот он рядом - но почему он там оказался, и как выйти из Матрицы самому и помочь коллегам?
Достаточно часто при работе с вводимыми пользователем адресными данными возникает необходимость сначала подсказать ему, как правильно свой адрес указать, а потом - полученную строку адреса привести в некоторый машинно-читаемый вид.
Таким видом у нас в стране можно считать код по справочникам КЛАДР или ФИАС.
Первый из них уже несколько лет считается устаревающим, но отличается более простой структурой и исторически продолжает использоваться во множестве систем, поскольку вполне подходит для большинства задач.
Давайте научимся разбирать строку адреса "туда и обратно", а заодно познакомимся с некоторыми алгоритмическими подходами и их реализацией на SQL.
Иногда мы пишем SQL-запросы, мало задумываясь над тем фактом, что сначала они должны быть по сети как-то доставлены до сервера, а затем их результат - обратно в клиентское приложение. Если при этом на пути до сервера присутствует еще и пулер соединений типа pgbouncer
, дополнительно "перекладывающий" байты между входящими и исходящими коннектами, ситуация становится еще тяжелее...
Поэтому сегодня рассмотрим некоторые типичные ситуации, в которых разработчики иногда принимают не самые оптимальные решения, гоняя по сети мегабайты трафика при общении с сервером PostgreSQL - а заодно посмотрим, как можно увидеть такую ситуацию в плане с помощью explain.tensor.ru и подумаем над вариантами, как сделать подобное взаимодействие более эффективным.
Однажды при выполнении достаточно тривиального запроса:
UPDATE tbl SET val = val + 1 WHERE id IN (1, 2, 3)
... вы получаете ошибку ERROR: deadlock detected
Но почему? Ведь еще вчера все успешно работало!
И что с этим теперь делать? Давайте разбираться.
Если ваша жизнь DBA, сопровождающего PostgreSQL, наполнена вопросами "а почему так медленно?" и "как сделать, чтобы запрос не тормозил?", наш сервис анализа и визуализации планов запросов explain.tensor.ru сделает ее немного легче за счет привлечения коллег и обновленных подсказок.
Иногда в интерфейсе наших приложений СБИС возникает необходимость "сгруппировать" часть записей в некотором списке (например, служебные сообщения в чате, контакты и телефонные звонки).
Хорошо, если все эти записи приходят с одного источника, а вот если из разных сервисов, да с навигацией по курсору - алгоритм реализации становится весьма нетривиальным.
Иногда при выполнении длительных или плохо написанных запросов в PostgreSQL происходят разные неприятные вещи типа внезапного сбоя процесса или краша всего сервера.
В таких случаях на носителе могут остаться "мертвые души" - файлы (иногда совсем немаленькие, а вполне сравнимые по объему со всей остальной базой), которые были созданы во время работы процесса в качестве временного хранилища промежуточных данных.
Эти данные уже никому не нужны, никем не могут быть использованы, но сервер не торопится избавиться от них как Плюшкин.
В конце прошлого года Иван Панченко предложил мне рассказать на внутреннем семинаре Postgres Pro, чего, по нашему опыту использования PostgreSQL в "кровавом энтерпрайзе" "Тензора", не хватает в этой СУБД.
С докладом пока так и не сложилось, зато появилась эта статья, в которой я постарался собрать наиболее показательные вещи, которые вызывают "напряги" при активном использовании PostgreSQL в реальном бизнесе.
Многие знают правила этой головоломки (Skyscrapers):
"Перед вами вид сверху на городской квартал. В каждой клетке стоит "небоскреб" высотой, равной числу в этой клетке. Числа с боков сетки означают количество "небоскребов", видимых из соответствующей строки или столбца, если смотреть от этого числа.
Задача: заполнить сетку числами так, чтобы в каждой строке и в каждом столбце каждое число использовалось лишь единожды."
Понятно, что алгоритмом полного перебора можно решить что угодно, но - за экспоненциальное время. Поэтому мы попробуем написать такой SQL-запрос, который решит нам такую головоломку за приемлемое время.
Зачем же делать это на SQL? Потому что можем! А заодно потому что это позволит научиться конструировать "очень сложные запросы", что может пригодиться и в обычной работе.
Как нормальные DBA, мы подождали выпуск пары минорных версий к PostgreSQL 13, который должен порадовать нас многими полезными вещами, и теперь готовы перенести базу нашего сервиса мониторинга этой СУБД с 12-й версии на 13-ю.
Но как это сделать с минимальным простоем, а лучше вообще без него? На помощь придет функционал Foreign Data Wrappers, а точнее - postgres_fdw.
Практически любая enterprise-система (под которой мы будем подразумевать некоторое ПО, где пользователи работают постоянно в течение всего рабочего дня) в современном мире стремится вырасти вместе с управляемым ей бизнесом в высоконагруженное web-решение вроде нашего СБИС.
Оно и понятно: доступность с любого устройства, где есть браузер, минимальные вложения "на старте" - все, что бизнес так любит. Но с развитием системы растет не только ее размер, но и сложность архитектуры решения, а с ней - и цена любой ошибки, вызывающей сразу каскад возможных проблем и "эффект домино".
Когда, где и как их может вызвать затаившийся до поры диверсант?