Тестирую и ускоряю ДБО для юридических лиц в банке ВТБ

Развиваю @qa_load

О чем доклад

О мониторинге и оптимизации SQL-запросов и ...

  1. Как не раздуть из мухи слона в анализе производительности

  2. Как написать дефект, от которого можно посчитать эффект

  3. Отображение за секунду месячной статистики по SQL

  4. Зачем и как выполнять трассировку SQL-запросов

Как не раздуть из мухи слона в анализе производительности

  • О моих ошибках, как инженера по тестированию производительности

Как написать дефект, от которого можно посчитать эффект

  • Ускорение долгих запросов и Active state
  • Долгие транзакции и Idle in transaction
  • Нехватка подключений и Idle state
  • Блокировки и Blocked state

Отображение за секунду месячной статистики по SQL

Зачем и как выполнять трассировку SQL-запросов

  • Пошаговое ускорения одного SQL-запроса и роль трассировки

«Ценность любой практики зависит от ее контекста»

Джем Канер, Джеймс Бах, Брайан Марик и Брет Петтичорд

«The value of any practice depends on its context»

Cem Kaner, James Bach, Brian Marick, Bret Pettich

Контекст

100 JVM работающих друг с другом и базой

На тестовом стенде

Узким местом была база данных

  • База данных выросла и стала узким местом, замедлилась

  • Утилизация CPU на сервере PostgreSQL близка к 100%

  • Микросервисам не хватает пула подключений

  • Подключений к PostgreSQL уже 2 000

  • Много ошибок в логах сервисов

  • Медленные ответы сервисов

Стал нужен источник метрик по SQL-запросам

Но не просто срез статистики для ручного сравнения и анализа, а ...

  • с удобным сравнением: запрос ускорился на X процентов

  • с хранением статистики за длительный интервал

  • с гибким выбором интервалов для сравнения

  • с высокой точностью сбора метрик

  • с быстрым отображением данных

  • с отображением интенсивности

Что получилось

Статистика по запросам в виде таблиц

С фильтрами по всем полям

Детальная статистика по выбранному запросу

И не только это

Demo-стенд

  • PostgreSQL

  • InfluxDB

  • Grafana

  • Telegraf

  • JMeter

github.com/polarnik/pg-sql-query-performance

Как не раздуть из мухи слона в анализе производительности

Иногда приходится тестировать на заглушках

Если тестировать на заглушках

Мы найдем дефекты производительности

  • В одном компоненте, при быстрых ответах от окружения
  • При большом масштабировании компонента

Мы упустим

  • Многие сценарии использования
  • Дефекты взаимодействия
  • Другие компоненты

Иногда приходится тестировать на пустых БД

Если тестировать на пустых базах данных

Мы найдем дефекты производительности

  • Дефекты взаимодействия при быстрых ответах от базы данных
  • Очень частые SQL-запросы, что можно кешировать
  • Утечки подключений, файлов, ...

Мы упустим

  • Многие сценарии использования
  • Большую часть медленных SQL-запросов и блокировок
  • Расходы памяти на большие данные
  • Обработку больших коллекций

Есть много способов найти дефекты производительности

Которых не будет в продуктиве

Изображение с сайта imgur.com

Сделать лишнюю работу или получить отказ

Что может услышать инженер

Мы что-то поправили, проверь

Дефект когда-нибудь проанализируем

Дефект больше не воспроизводится — закрываем

Тут легко сделать из мухи слона

Повышая приоритет непроверенных дефектов

Инженер:

«Прошу проанализировать и исправить дефект, эффект уже проверен»
«Дефект важен, ускорение будет, для ключевой операции на 70%»

Команда:

«Поправили, но на DEV-стенде нет эффекта»
«Проверь на нагрузочном стенде»

Но есть проверенный способ изменить ситуацию

Что может сделать и сказать инженер

Поправить предлагаю так, уже проверено

Ускорение ключевой операции на 70%

Влияния на функциональность нет

Можно сделать из
слона (PostgreSQL)
муху (дефекты)

Проверенные дефекты

Изображение с сайта imgur.com

Используя непустые базы данных

Есть доклад по генерации данных: https://o.codefest.ru/lecture/1674

width:1220px

И оптимизируя SQL-запросы на тестовом стенде

Стенд нагрузки (большая БД с данными) позволяет оптимизировать

  • долгие запросы (active) с предсказуемыми планами
  • долгие транзакции (idle in transaction)
  • нехватка подключений (idle)
  • блокировки (blocked)

А стенд разработки (малая БД на контейнерах) не позволяет, ведь

  • планы запросов парадоксальные: sequnce scan вместо index scan
  • таблицы маленькие
  • запросы быстрые

Эффект будет и для дела и для репутации

Инженер:

«Дефект поправить предлагаю так, эффект уже проверен»
«Дефект важен, ускорение будет, для ключевой операции на 70%»

Команда:

«Поправили, есть эффект даже на DEV-стенде»
«Спасибо!»

Как написать дефект, от которого можно посчитать эффект

Очень важен выбор метрик, которые будем измерять

По ним оценим эффект

Долгие запросы и Active state

Длительность за выбранный в Grafana период

Запросы дольше
10-100 мсек можно попробовать ускорить

Колонка QueryID — ссылка на детали по запросу

Детали по запросу, не только в табличном виде, но с графиками

Использую DBeaver для работы с PostgreSQL

explain (analyse, buffers) ...

План запроса — один из возможных планов выполнения SQL

Собираю планы запроса с разными параметрами

Сохраняю их в комментариях

https://explain.tensor.ru/ помогает понять и визуализировать план

Полезный сайт

После оптимизации перепроверяю под нагрузкой

⬇️ Средняя длительность (Mean time) снизится

Сравнить показатели до и после правки, при сходной нагрузке

⬇️ Shared Blk Hit, Shared Blk Read снизятся

Параметры важны для SELECT-запросов

А что с Total Time и Calls по запросу?

На Total Time и Calls по запросу не смотрим

🔀 Calls при ускорении может расти, Total Time тоже, а должен падать

Долгие транзакции и Idle in transaction state

Такой код приведет к долгой транзакции

Будет подключение к БД в статусе Idle in transaction

Доска PostgreSQL Activity в Grafana покажет TOP

После какого SQL-запроса транзакция не закрывалась долго

Колонка Sum только для сортировки

Выбирем признаки для поиска в исходниках

По базе данных и логину выбираем сервис, а по SQL - место в коде

Иногда текста запроса нет или он не помогает

Тогда придется перечитать все исходники с учетом pg_stat_statements

Пусть у нас есть уникальный запрос и логин

qpt_transaction_user: SELECT * FROM aircrafts_data WHERE range > 3000

По тексту запроса и логину находим исходники

По исходникам определяем, что не так

Переписываем код: просим убрать из транзакции ожидание

Или сокращаем ожидание

Сокращаем ожидание, если транзакция нужна

sqlClient.transactionBegin();
try {
    sqlClient.exec("INSERT INTO table_name ...");
    // Внутри транзакции: HTTP, JMS, MQTT (Kafka), ...
    httpClient.Send("http://load.qa/ADD"); // Ускоряем
} finally {
    if ( ... )
        sqlClient.transactionCommit(); 
    else
        sqlClient.transactionRollback();
}

Переписываем код, если есть возможность

До переписывания: длительный вызов внутри транзакции

sqlClient.transactionBegin();
try {
    // Внутри транзакции: HTTP, JMS, MQTT (Kafka), ...
    httpClient.Send("http://load.qa/ADD"); // Переписываем
    sqlClient.exec("INSERT INTO table_name ...");
} finally {
    if ( ... )
        sqlClient.transactionCommit(); 
    else
        sqlClient.transactionRollback();
}

Переписываем код, если есть возможность

После переписывания: длительный вызов вне транзакции

// Вне транзакции
httpClient.Send("http://load.qa/ADD"); // Переписали

if (...) {
    sqlClient.transactionBegin();
    sqlClient.exec("INSERT INTO table_name ...");
    sqlClient.transactionCommit();
}

Настраиваем более короткие подключения к БД

maxLifetime по умолчанию 1800000 (30 минут)

Например, 3 минуты вместо 30-ти минут:

ConfigMap:
    spring.datasource.hikari:
        maxLifetime: 180000

⬇️ Эффект оценим по статусам подключений

⬇️ По суммарной длительности состояния

Нехватка подключений и Idle state

spring.datasource.hikari.maximumPoolSize=10...50

для server.tomcat.threads.max = 200

Проблема:

  • В Tomcat по умолчанию maxThreads = 200 (потоков обработки)
  • А HikariPool по умолчанию 10 (потоков подключения к БД)
  • Подключений не хватает под нагрузкой, в логах ошибки
  • Разработчик старается сделать HikariPool побольше, 50
  • Если 250 сервисов со Scale 2 установят по 50 сразу:
    • 250 x 2 x 50 = 25 000 (подключений к PostgreSQL)
  • А подключений всего пусть 4000

Задача — сократить пулы потоков

и spring.datasource.hikari.maximumPoolSize и server.tomcat.threads.max

Чтобы соединения быстрее возвращались в пул потоков HikariPool:

  • Ускоряем медленные запросы (Active)
  • Исправляем долгие транзакции (Idle in transaction)

А далее смотрим, кто не использует свои подключения и уменьшаем

spring.datasource.hikari:
    maximumPoolSize: 50     # было увеличено
    minimumIdle: 10         # по умолчанию
    idleTimeout: 600000     # (10 minutes)

Выделяем фаворитов и уменьшаем им пул

В индивидуальном порядке

Перераспределяем пул пропорционально Total Time

Каждому по потребностям

Блокировки и Blocked state

Как правило, причина в долгих запросах

Ускоряем медленные запросы — решаются блокировки

Детализацию хорошо показывает PASH Viewer

Аналог ASH Viewer, но только для PostgreSQL

Отображение за секунду месячной статистики по SQL

В InfluxDB все теги индексируются сразу

И основная задача в уменьшении этих индексов

CREATE CONTINUOUS QUERY query_md5_10min ON telegraf_pg_demo
BEGIN
    SELECT
        sum("duration") AS duration_sum,
        sum(calls) AS calls_sum
    INTO
        telegraf_pg_demo.autogen.pg_stat_statements_query_md5_10min
    FROM
        telegraf_pg_demo."7d".pg_stat_statements_diff
    WHERE
        time >= now() - 20m AND calls > 0
    GROUP BY host, usename, datname, queryid, query_md5, time(10m)
END;

За счет Continious Query Influxdb cтатистика отдельно

А Query Text тоже отдельно

https://github.com/ polarnik/pg-sql-query-performance

Проект с демонстрацией

Как запустить проект

# Отдельно поднять сервер баз данных
docker-compose up db

# Остановить поднятый сервер БД
# <Ctrl+C> или командой
docker stop sql_monitor_postgres

# А теперь поднять все компоненты
docker-compose up

# Открыть Grafana
open 'http://localhost:3000'

Для чего и как выполнять трассировку SQL-запросов

Partial-индексы и вот это вот все

select 
    concat('{"m":"', message, '"}') body 
from web_message
where
    user_id = '0eaab87d-5977-4a3a-af9f-6f5f424140f5'
    and status = 'NEW'
    and category in ('NOTIFICATION')
    and channel = 'WEB_POPUP'
    and (   expiration_time is null 
            or expiration_time >= now())
order by created_at desc
limit 10;

Запрос в статистике

select 
    concat($1, message, $2) body 
from ntn_web.web_message msg
where
    user_id = $3
    and status = $4
    and category in ($5)
    and channel = $6
    and (   expiration_time is null 
            or expiration_time >= $7)
order by created_at desc
limit $8;

Первое предположение: добавить в индекс все

select 
    concat($1, message, $2) body 
from ntn_web.web_message msg
where
    user_id = $3                      -- в индекс
    and status = $4                   -- в индекс
    and category in ($5)              -- в индекс
    and channel = $6                  -- в индекс
    and (   expiration_time is null  
            or expiration_time >= $7) -- в индекс
order by created_at desc              -- в индекс
limit $8;

Большой индекс, который не будет использован

select concat($1, message, $2) body from web_message
where user_id = $3 and status = $4
  and category in ($5) and channel = $6
  and ( expiration_time is null or expiration_time >= $7 )  
order by created_at desc limit $8;

------ В индекс включены все поля
create index fix_web_message_idx_1 on web_message
using btree(user_id, status, category, channel,
   expiration_time, created_at DESC);

Меньший индекс, который будет использован

select concat($1, message, $2) body from web_message
where user_id = $3 and status = $4
  and category in ($5) and channel = $6
  and ( expiration_time is null or expiration_time >= $7 )  
order by created_at desc limit $8;

------ Из индекса исключены неподходящие строки
create index fix_web_message_idx_2 on web_message
using btree(user_id, expiration_time, 
  created_at DESC)
where status = 'NEW'
  and category in ('NOTIFICATION')
  and channel = 'WEB_POPUP';

Для заданных параметров фильтрации годится

select concat($1, message, $2) body from web_message
where user_id = $3 and status = 'NEW'
  and category in ('NOTIFICATION') and channel = 'WEB_POPUP'
  and ( expiration_time is null or expiration_time >= $7 )
order by created_at desc limit $8;

------ Из индекса исключены неподходящие строки
create index fix_web_message_idx_2 on web_message
using btree(user_id, expiration_time, 
  created_at DESC)
where status = 'NEW'
  and category in ('NOTIFICATION')
  and channel = 'WEB_POPUP';

Немного перепишем запрос

select concat($1, message, $2) body from web_message
where user_id = $3 and status = 'NEW'
  and category in ('NOTIFICATION') and channel = 'WEB_POPUP'
--and ( expiration_time is null or expiration_time >= $7 )
  and coalesce( expiration_time, '3000-01-01' ) >= now()
order by created_at desc limit $8;
-- TODO: успеть актуализировать запрос до 3000-го года

Немного перепишем запрос

select concat($1, message, $2) body from web_message
where user_id = $3 and status = 'NEW'
  and category in ('NOTIFICATION') and channel = 'WEB_POPUP'
  and coalesce( expiration_time, '3000-01-01' ) >= now()
order by created_at desc limit $8;
-- TODO: успеть актуализировать запрос до 3000-го года

Код будет работать до 3000-го года корректно

А потом мы что-нибудь придумаем

Функцию coalesce тоже можно индексировать

select concat($1, message, $2) body from web_message
where user_id = $3 and status = 'NEW'
  and category in ('NOTIFICATION') and channel = 'WEB_POPUP'
  and coalesce( expiration_time, '3000-01-01' ) >= now()
order by created_at desc limit $8;

------ В индекс добавлен результат выполнения coalesce
create index fix_web_message_idx_3 on web_message
using btree(user_id, coalesce(expiration_time,'3000-01-01'),
  created_at DESC)
where status = 'NEW'
  and category in ('NOTIFICATION')
  and channel = 'WEB_POPUP';

Сделаем из индекса аналог View

select concat($1, message, $2) body from web_message
where user_id = $3 and status = 'NEW'
  and category in ('NOTIFICATION') and channel = 'WEB_POPUP'
  and coalesce( expiration_time, '3000-01-01' ) >= now()
order by created_at desc limit $8;

------ В индекс добавлена колонка message
create index fix_web_message_idx_4 on web_message
using btree(user_id, coalesce(expiration_time,'3000-01-01'),
  message, created_at DESC)
where status = 'NEW'
  and category in ('NOTIFICATION')
  and channel = 'WEB_POPUP';

Результат функции concat можно индексировать

select concat($1, message, $2) body from web_message
where user_id = $3 and status = 'NEW'
  and category in ('NOTIFICATION') and channel = 'WEB_POPUP'
  and coalesce( expiration_time, '3000-01-01' ) >= now()
order by created_at desc limit $8;

------ В индекс добавлен результат выполнения concat
create index fix_web_message_idx_4 on web_message
using btree(user_id, coalesce(expiration_time,'3000-01-01'),
  concat('{"m":"', message, '"}'), created_at DESC)
where status = 'NEW'
  and category in ('NOTIFICATION')
  and channel = 'WEB_POPUP';

Все это стало возможно при знании параметров

select concat('{"m":"', message, '"}') body from web_message
where user_id = $3 and status = 'NEW'
  and category in ('NOTIFICATION') and channel = 'WEB_POPUP'
  and coalesce( expiration_time, '3000-01-01' ) >= now()
order by created_at desc limit $8;

------ В индекс добавлен результат выполнения concat
create index fix_web_message_idx_4 on web_message
using btree(user_id, coalesce(expiration_time,'3000-01-01'),
  concat('{"m":"', message, '"}'), created_at DESC)
where status = 'NEW'
  and category in ('NOTIFICATION')
  and channel = 'WEB_POPUP';

Чтобы узнать параметры запроса

Нужна трассировка приложения, интуиция или знания

  • Смотрим исходники

  • Угадываем параметры (UI, БД)

  • Параметры из профилирования JVM (JProfiler, YourKit)

  • Параметры из логов трассировки JDBC

  • Параметры из логов трассировки Hibernate

  • Параметры из логов планов запросов PostgreSQL

  • Перехват параметров с JDBC Proxy

Приложение: инструменты

Трассировка (с параметрами)

Мониторинг

История ускорения системы. Про три месяца оформления и исправления дефектов большой системы, которую можно услышать за тридцать минут и прожить за десять. Как известно, инструмент подбирается под задачу. Но для задачи визуализации статистики по SQL-запросам с точностью до минуты (или секунды, при желании) инструмента не было, так чтобы инструмент работал в закрытой сети, работал под максимально высокой нагрузкой, не требовал модификаций PostgreSQL. Инструмент был собран из открытых компонент: Telegraf, InfluxDB, Grafana. И отлично показал себя на проекте. Позволив оформлять и исправлять дефекты в течение трех месяцев не отвлекаясь ни на что другое. Что позволило достаточно быстро ускорить систему. А если нужна детальная статистика с учетом значений параметров запроса, то это тоже возможно. За счет простого логирования и анализа лога. Расскажу об опыте применения двух подходов к сбору и визуализации статистики, о том какие есть альтернативные подходы, которые не дали результата, о том какие есть альтернативные решения, которыми вдохновлялся, о том какие новые и полезные решения есть и появляются сейчас. Аудитория и уровень Доклад будет интересен инженерам по производительности и мониторингу систем, имеющим дело с PostgreSQL. Тем, кто стремиться не только тестировать и мониторить, но и оптимизировать, как саму систему так и механизм мониторинга. Опыт оптимизации не требуется, но представление о том, что такое Java, SQL, PostgreSQL и Time Series Database нужно иметь.

Повышаю качество более десяти лет. Занимаюсь системой дистанционного банковского обслуживания юридических лиц. Основной профиль моей работы — тестирование производительности. Развиваю сообщество инженеров по тестированию производительности, помогая коллегам в telegram чате «QA — Load & Performance».

* Выбор наиболее медленных SQL-запросов * Анализ интенсивности SQL-запроса * Среднее и максимальное время * Другие важные метрики

* Выбор SQL-запросов в долгих транзакциях * Поиск кода, вызывающего SQL-запрос * Анализ причин зависания

* Выбор сервисов с неиспользуемыми подключениями * Справедливое распределение подключений * Настройки времени жизни подключений