• SQL HowTo: курсорный пейджинг с неподходящей сортировкой

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

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



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

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

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

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


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


        В этой расшифровке второй части своего доклада на PGConf.Russia 2020 я расскажу, как нам удалось это сделать.
        С транскриптом первой части, посвященной типовым проблемам производительности запросов и их решениям, можно ознакомиться в статье «Рецепты для хворающих SQL-запросов».
        Читать дальше →
        • +13
        • 2,1k
        • 5
      • Динамическая балансировка нагрузки в pull-схеме

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


          Давайте посмотрим, какие решения есть у этой задачи.
          Читать дальше →
        • Телепортация тонн данных в PostgreSQL

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


            Intro


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

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

            Именно про последний пункт — как все это можно доставить в PostgreSQL-хранилище, и поговорим. В нашем случае таких данных кратно больше, чем исходных — статистика нагрузки в разрезе конкретного приложения и шаблона плана, потребление ресурсов и вычисление производных проблем с точностью до отдельного узла плана, мониторинг блокировок и многое другое.
            Более полно о принципах работы сервиса можно посмотреть в видео доклада и прочитать в статье «Массовая оптимизация запросов PostgreSQL».
            Читать дальше →
            • +11
            • 2,9k
            • 8
          • PostgreSQL Antipatterns: уникальные идентификаторы

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


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


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

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

              Читать дальше →
            • У меня зазвонил телефон. Кто говорит?.. Поможет «слон»

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

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


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

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

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

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

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


                  Со схемами работы некоторых параллельных узлов можно ознакомиться в статье «Parallelism in PostgreSQL» by Ibrar Ahmed, откуда взято и это изображение.
                  Правда, читать планы в этом случае становится… нетривиально.
                  Читать дальше →
                • PostgreSQL Antipatterns: «Должен остаться только один!»

                    На 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-запроса

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


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


                    Читать дальше →
                    • +32
                    • 5,9k
                    • 1
                  • SQL HowTo: красивые отчеты по «дырявым» данным — GROUPING SETS

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

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


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

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



                      В этой статье рассмотрим, как все это можно экономично расположить в БД, и как максимально эффективно собрать по этим данным отчет с помощью оконных функций и GROUPING SETS.
                      Читать дальше →
                    • Unreal Features of Real Types, или Будьте осторожны с REAL

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


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

                        Читать дальше →
                      • PostgreSQL Antipatterns: анализируем блокировки — SELF JOIN vs WINDOW

                          Ранее мы уже научились перехватывать блокировки из лога сервера 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'и исключим из анализа — это просто ошибки, и попробуем выяснить, сколько всего времени мы потеряли из-за блокировок за конкретный день на определенном хосте.
                          Читать дальше →
                          • +14
                          • 2,4k
                          • 2
                        • PostgreSQL Antipatterns: накручиваем себе проблемы

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

                            Сегодня разберем пару примеров, как неудачная организация БД и кода могут превратить наше приложение в клубок проблем:

                            • накрутка serial при ON CONFLICT
                            • накрутка счетчика транзакций

                            Читать дальше →
                          • Подозрительные типы

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


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

                            Читать дальше →
                          • SQL HowTo: 1000 и один способ агрегации

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

                              А чтобы эти итоги собрать, необходимо по исходным данным вычислить значение некоторой агрегатной функции: количество, сумма, среднее, минимум, максимум,… — и, как правило, не одной.


                              Сегодня мы рассмотрим некоторые способы, с помощью которых можно вычислить агрегаты в PostgreSQL или ускорить выполнение SQL-запроса.
                              Читать дальше →
                              • +12
                              • 4,6k
                              • 1
                            • DBA: кто скрывается за блокировкой

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


                                Читать дальше →
                              • Классифицируем ошибки из PostgreSQL-логов

                                  Посвящается всем любителям анализировать логи.

                                  В логах работающих систем рано или поздно появляются тексты каких-то ошибок. Чем таких систем больше в обозримом пространстве, тем больше вероятность ошибку увидеть. Серверы PostgreSQL, которые находятся под нашим мониторингом ежедневно генерируют от 300K до, в неудачный день, 12M записей об ошибках.

                                  И такие ошибки — это не какой-то там «о, ужас!», а вполне нормальное поведение сложных алгоритмов с высокой степенью конкурентности вроде тех, о которых я рассказывал в статье про расчет себестоимости в СБИС — все эти deadlock, could not obtain lock on row in relation …, canceling statement due to lock timeout как следствие выставленных разработчиком statement/lock timeout.

                                  Но есть ведь и другие виды ошибок — например, you don't own a lock of type ..., которая возникает при неправильном использовании рекомендательных блокировок и может очень быстро «закопать» ваш сервер, или, мало ли, кто-то периодически пытается «подобрать ключик» к нему, вызывая возникновение password authentication failed for user …

                                  [источник КДПВ]

                                  Собственно, это все нас подводит к мысли, что если мы не хотим потом хвататься за голову, то возникающие в логах PostgreSQL ошибки недостаточно просто «считать поштучно» — их надо аккуратно классифицировать. Но для этого нам придется решить нетривиальную задачу индексированного поиска регулярного выражения, наиболее подходящего для строки.
                                  Читать дальше →
                                • Понимаем планы PostgreSQL-запросов еще удобнее

                                    Полгода назад мы представили explain.tensor.ru — публичный сервис для разбора и визуализации планов запросов к PostgreSQL.



                                    За прошедшие месяцы мы сделали про него доклад на PGConf.Russia 2020, подготовили обобщающую статью по ускорению SQL-запросов на основе рекомендаций, которые он выдает… но самое главное — собирали ваши отзывы и смотрели за реальными use case.

                                    И теперь готовы рассказать о новых возможностях, которыми вы можете пользоваться.
                                    Читать дальше →
                                  • Как мы в СБИС автоматический расчет себестоимости делали

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

                                      Очередной семинар про работу с СУБД PostgreSQL. Сегодня расскажу, как суровую прагматику требований бизнеса перенести на разработку высоконагруженных сервисов, как бороться с конкурентным доступом к данным, как это все аккуратно обходить и при этом не «отстрелить себе ногу».

                                      Сегодня мы поговорим про расчет себестоимости в СБИС:

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


                                      Читать дальше →
                                    • DBA: в погоне за пролетающими блокировками

                                        В прошлой статье, где я рассказывал о мониторинге БД PostgreSQL, была такая фраза:
                                        Растут wait — приложение в кого-то «уперлось» на блокировках. Если это уже прошедшая разовая аномалия — повод разобраться в исходной причине.
                                        Такая ситуация — одна из самых неприятных для DBA:

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

                                        Шансов поймать блокировки «в моменте» крайне мало, да и длиться они могут всего по несколько секунд, но ухудшая при этом плановое время выполнения запроса в десятки раз. А хочется-то не сидеть и ловить происходящее в онлайн-режиме, а в спокойной обстановке разобраться постфактум, кого из разработчиков покарать в чем именно была проблема — кто, с кем и из-за какого ресурса базы вступил в конфликт.

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

                                        Разве что короткую запись в логе:
                                        process ... still waiting for ...
                                        А давайте попробуем зацепиться именно за нее!
                                        Читать дальше →
                                        • +18
                                        • 2,8k
                                        • 4

                                      Самое читаемое