Массовая оптимизация запросов PostgreSQL – explain.sbis.ru

100+ проектов
10 центров разработки
более 1000 сотрудников в них
«Тензор» – это СБИС
миллион клиентов

СБИС – data-centric application
Активно используем PostgreSQL
~400TB «рабочих» данных
«в продакшене» с 2008 года
уже более 250 серверов

СБИС – data-centric application
SQL – декларативный язык
вы описываете, что хотите получить
СУБД лучше «знает», как это сделать:
какие индексы использовать, в каком порядке соединять таблицы, как накладывать условия…

СБИС – data-centric application
SQL – декларативный язык
некоторые СУБД принимают «подсказки»
PostgreSQL – нет, но…
всегда готов рассказать, как конкретно он выполняет ваш запрос

СБИС – data-centric application
Классика: «А почему у нас тут выполнялось долго?»
алгоритмически неэффективный запрос/план
неактуальная статистика
«затык» по ресурсам (процессор, диск, память)
блокировки – для DML-запросов

СБИС – data-centric application
Классика: «А почему у нас тут выполнялось долго?»
алгоритмически неэффективный запрос/план
неактуальная статистика
«затык» по ресурсам (процессор, диск, память)
«Нам нужен план!»

Получение плана

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

Получение плана
EXPLAIN (ANALYZE, BUFFERS) SELECT …
https://postgrespro.ru/docs/postgrespro/9.6/using-explain
подходит только для локальной отладки

Получение плана
Модуль auto_explain
https://postgrespro.ru/docs/postgresql/9.6/auto-explain
анализирует все запросы подряд дольше XXXms
фиксирует для них планы выполнения
пишет все это в лог сервера

Получение плана
Модуль auto_explain

Получение плана
Модуль auto_explain

Получение плана
Логи и план текстом – ненаглядно:
узел содержит сумму по ресурсам поддерева
время необходимо умножать на loops
… так кто же «самое слабое звено»?

Получение плана
Логи и план текстом – ненаглядно:
узел содержит сумму по ресурсам поддерева
время необходимо умножать на loops
… так кто же «самое слабое звено»?
«Понимание плана – это искусство, и чтобы овладеть им, нужен определённый опыт…»

Получение плана
Логи и план текстом – ненаглядно:
узел содержит сумму по ресурсам поддерева
время необходимо умножать на loops
… так кто же «самое слабое звено»?
Нужна хорошая визуализация!

Визуализация плана

Визуализация плана
explain.depesz.com

Визуализация плана
explain.depesz.com – pro
«собственное» время каждого узла
отклонение от статистически-плановых rows
количество повторов каждого узла
архив планов (можно обмениваться ссылками)

Визуализация плана
explain.depesz.com – contra
требует copy&paste планов из лога
нет анализа ресурсов (buffers)
код на Perl, нет развития
ошибки анализа CTE/InitPlan :(

Визуализация плана
explain.depesz.com – ошибки анализа CTE Scan

Визуализация плана
explain.sbis.ru
ура! мы пишем свое!
Node.JS + Express + Twitter Bootstrap + D3.js
прототип за 2 недели

Визуализация плана
explain.sbis.ru
собственный парсер плана
корректный анализ CTE Scan
анализ распределения ресурсов (buffers)
наглядность, подсветка синтаксиса

Визуализация плана
explain.sbis.ru – полный план

Визуализация плана
explain.sbis.ru – сокращенный план (шаблон)

Визуализация плана
explain.sbis.ru – распределение затрат времени

Визуализация плана
explain.sbis.ru – распределение затрат времени

Визуализация плана
explain.sbis.ru – «грабли»
проблемы округления
0.001ms × (loops=1000) = 0.95ms .. 1.05ms
распределение ресурсов CTE/InitPlan/SubPlan
+4 недели отладки :(

Визуализация плана
explain.sbis.ru – «грабли»
WITH cl AS (
TABLE pg_class
)
(TABLE cl LIMIT 1)
UNION ALL
(TABLE cl LIMIT 1 OFFSET 100);

Визуализация плана
explain.sbis.ru – «грабли»

Визуализация плана
explain.sbis.ru – дерево выполнения

Визуализация плана
explain.sbis.ru – дерево выполнения

Визуализация плана
explain.sbis.ru – дерево выполнения

Визуализация плана
explain.sbis.ru
«Теперь, Нео, ты знаешь кунг-фу»

Консолидация логов

Консолидация логов
«Копипаста» – плохо
100+ серверов
1000+ разработчиков

коллектор
SSH port forward + psql
SSH connection (ключ)
tail -F
SELECT * FROM pg_stat_activity;
Консолидация логов
SELECT * FROM pg_locks;

tail -F
Консолидация логов
COPY … FROM STDIN

Консолидация логов
100+ серверов, 50Kqps, 100-150GB/день
секционирование по дням (ждем 10.0!)
очень-очень быстрый «потоковый» COPY
отказались от триггеров (почти)

Консолидация логов
Отказались от триггеров
нет ссылочной целостности (нет FK и их проверки)
агрегация и хэширование на стороне коллектора
каждая таблица наполняется «своим» потоком

коллектор
COPY plan FROM STDIN
COPY query FROM STDIN
COPY error FROM STDIN
COPY planagg FROM STDIN
Консолидация логов

Консолидация логов
«Потоковый» COPY
всегда открыт COPY-канал/пул на таблицу
«переоткрывается» раз в XXXms для закрытия TX
отправляем запись в канал сразу при получении
никакой дополнительной буферизации, да-да

Консолидация логов
«Потоковый» COPY
таблицы-словари
триггер BEFORE INSERT
9.5+: INSERT … ON CONFLICT DO NOTHING

Консолидация логов
«Потоковый» COPY
тогда: 4K write ops -> 1K write ops (в 4 раза!)
сейчас: 6K write ops ~100MB/s, 10TB/3мес

Понимаем проблемы

Понимаем проблемы
100+ серверов, 50Kqps, 100-150GB/день
миллионы планов за сутки

Понимаем проблемы
100+ серверов, 50Kqps, 100-150GB/день
миллионы планов за сутки

Понимаем проблемы
100+ серверов, 50Kqps, 100-150GB/день
миллионы планов за сутки

Понимаем проблемы
100+ серверов, 50Kqps, 100-150GB/день
кто? откуда этот запрос
где? что за сервер, база
как? в чем проблема в плане

Понимаем проблемы
«Хозяин» у каждого запроса
SET application_name = '
:
'
страдаем от ограничения в 63 байта (тип name)

Понимаем проблемы
«Хозяин» у каждого запроса
log_line_prefix = ' %m [%p:%v] [%d] %r %a'
https://postgrespro.ru/docs/postgrespro/9.6/runtime-config-logging

Понимаем проблемы
«Хозяин» у каждого запроса

Понимаем проблемы
Модель анализа
экземпляр PostgreSQL (хост:порт), день
шаблон, приложение/метод, узел плана

Понимаем проблемы
От планов – к шаблонам
уменьшение количества анализируемых объектов
вычленение общих паттернов поведения

Понимаем проблемы
Разрезы анализа планов
количество фактов по шаблону/методу
суммарное и среднее время
количество ресурсов (buffers hit/read)
таймлайны

Понимаем проблемы

Понимаем проблемы

Понимаем проблемы
Разрезы анализа узлов
Seq Scan, Index [Only] Scan, Bitmap (Index|Heap) Scan
количество фактов/шаблонов по узлу
loops, rows, RRbF (суммарно и в среднем)

Понимаем проблемы

… и устраняем причины