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

Поэтому, когда на каком-то из тысяч подконтрольных серверов случается аномальное потребление ресурсов (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'и исключим из анализа — это просто ошибки, и попробуем выяснить, сколько всего времени мы потеряли из-за блокировок за конкретный день на определенном хосте.

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

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

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

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

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

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

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


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

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

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



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

И теперь готовы рассказать о новых возможностях, которыми вы можете пользоваться.

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

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

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

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

В прошлой статье, где я рассказывал о мониторинге БД PostgreSQL, была такая фраза:

Растут wait — приложение в кого-то «уперлось» на блокировках. Если это уже прошедшая разовая аномалия — повод разобраться в исходной причине.

Такая ситуация — одна из самых неприятных для DBA:

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

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

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

Разве что короткую запись в логе:

process ... still waiting for ...

А давайте попробуем зацепиться именно за нее!

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

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


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

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

В сложных ERP-системах многие сущности имеют иерархическую природу, когда однородные объекты выстраиваются в дерево отношений «предок — потомок» — это и организационная структура предприятия (все эти филиалы, отделы и рабочие группы), и каталог товаров, и участки работ, и география точек продаж,…



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

Существует много способов хранения такого дерева в СУБД, но мы сегодня остановимся только на одном варианте:

CREATE TABLE hier(
  id
    integer
      PRIMARY KEY
, pid
    integer
      REFERENCES hier
, data
    json
);

CREATE INDEX ON hier(pid); -- не забываем, что FK не подразумевает автосоздание индекса, в отличие от PK

И пока вы всматриваетесь в глубину иерархии, она терпеливо ждет, насколько же [не]эффективными окажутся ваши «наивные» способы работы с такой структурой.


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

Мы, разработчики, очень любим юнит-тесты, полезность которых очевидна. И чтобы эти тесты действительно были полезными, а не приносили боль, необходимо обеспечивать их стабильность.

Наша компания разрабатывает интерфейсный фреймворк "Wasaby" и продает построенные на его базе продукты, представляющие собой облачные и десктопные приложения. Релизный цикл у нас жестко привязан к календарю, а для контроля качества продукта настроены процессы непрерывной инеграции. Мы используем Jenkins для сборок и Mocha в связке с Chai assert для юнит тестирования JavaScript кода. И недавно мы столкнулись с ситуацией, когда мониторинг сборок стал показывать, что примерно половина всех случаев их падения приходится на нестабильные юнит-тесты JavaScript. Симптоматика при этом одинаковая: отдельный тест из набора либо не успевает выполниться, либо возвращает не тот результат, что ожидается. И анализ кейсов практически всегда выявляет факт, что падает тест, содержащий вызовы функций setTimeout или setInterval в собственном, либо в тестируемом коде. О том, как правильно поступить в этой ситуации, мы и будем говорить дальше.

Что делать, если в базу хочется записать массу «фактов» много большего объема, чем она способна выдержать? Сначала, конечно, приводим данные к более экономичной нормальной форме и получаем «словари», в которые будем писать однократно. Но как это делать наиболее эффективно?

Именно с таким вопросом мы столкнулись при разработке мониторинга и анализа логов серверов PostgreSQL, когда остальные способы оптимизации записи в БД оказались исчерпаны.

Сразу оговоримся, что наши коллекторы работают под управлением Node.js, поэтому с процессорными регистрами и кэшами мы никак не взаимодействуем. А вариант использования «стораджей» или внешних кэширующих сервисов/БД дает слишком большие задержки при входящих потоках в несколько сотен Mbps.

Поэтому мы стараемся кэшировать все в RAM, конкретно — в памяти JavaScript-процесса. Про то, как эффективнее это организовать, и пойдет речь дальше.

Сегодня не будет никаких сложных кейсов и мудреных алгоритмов на SQL. Все будет очень просто, на уровне Капитана Очевидность — делаем просмотр реестра событий с сортировкой по времени.

То есть вот лежит в базе табличка events, а у нее поле ts — ровно то самое время, по которому мы хотим эти записи упорядоченно показывать:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

Понятно, что записей у нас там будет не десяток, поэтому нам потребуется в каком-то виде постраничная навигация.

#0. «Я у мамы погроммист»

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

Даже почти не шутка — редко, но встречается в дикой природе. Иногда после работы с ORM бывает тяжело перестроиться на «прямую» работу с SQL.

Но давайте перейдем к более распространенным и менее очевидным проблемам.

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

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


Однако, наш опыт оказался весьма продуктивным в этом плане, поскольку хранилище почти любого мониторинга по своей природе является большей частью append-only с точки зрения записываемых данных. И если вам интересно, как можно научить базу писать на диск вместо 200MB/s вдвое меньше — прошу под кат.

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

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

Какие проблемы могут быть у frontend-программиста, если тестировщик запустит его приложение на iPad с новой трекпад-клавиатурой, Windows-планшете, с неопределенным состоянием “режима планшета” или ноутбуке с подключенным к нему телевизором c поддержкой Multi-touch?

Это далеко не полный список допустимых конфигураций оборудования, которые мы поддерживаем при разработке системы СБИС. Сегодня СБИС — это не только знакомое многим решение для сдачи отчетности, ведения электронного документооборота и бухгалтерии, но и набор инструментов для автоматизации розницы, общепита, доставки и логистики. В этих сферах нужно уметь хорошо работать на самых разных планшетах и гаджетах с различными экранами и типами устройств ввода. И далеко не всегда проблемы могут быть связаны с экзотическим сочетанием настроек операционных систем и драйверов: если взять обычный iPad с браузером Safari, Android планшет или ноутбук-трансформер на Windows10 с последней версией Google Chrome — везде будет свой набор ошибок и особенностей обработки пользовательского ввода.

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

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

#1. Секционирование

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