Русский
Русский
English
Авторизация
Ip-адрес
Восстановление пароля
Регистрация
Статистика
Реклама

Explain

SQL HowTo 1000 и один способ агрегации

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

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


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

Совместные агрегаты


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

->  $1 = '{2,3,5,7,11,13,17,19}'<-  count | sum      8 |  77

Это самый-самый простой случай просто сразу одновременно в запросе пишем count и sum:

SELECT  count(*), sum(prime)FROM  unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;

И хоть агрегатных функций мы использовали две, в плане у нас все хорошо узел Aggregate выполнялся всего лишь один раз:


Несовместимые агрегаты


Проблемы начинаются, если мы хотим вычислить сразу несколько агрегатов, но вот у каждого из них разные условия [не]включения одной и той же записи исходного набора. Например, попробуем вычислить для нашего набора количество чисел больше и меньше 10 отдельными полями:

->  $1 = '{2,3,5,7,11,13,17,19}'<-  countlt | countgt        4 |       4

Вложенные запросы


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

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime) SELECT  (SELECT count(*) FROM src WHERE prime < 10) countlt, (SELECT count(*) FROM src WHERE prime > 10) countgt;



Какие из этого плана можно сделать выводы? Много бегаем и много фильтруем дважды [CTE Scan + Rows Removed by Filter: 4].

А если выборка будет из 10K записей, а агрегатов захочется 3-4-5?.. Совсем нехорошо.

FILTER-агрегаты


Этот вариант, наверное, самый простой и понятный:

SELECT  count(*) FILTER(WHERE prime < 10) countlt, count(*) FILTER(WHERE prime > 10) countgtFROM  unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;



Он идеален во всех отношениях, быстро и эффективно решает все наши задачи, но имеет и ложку дегтя доступен только с версии 9.4 (мало ли у кого что на бою стоит).

Агрегаты от условия


Допустим, 9.4 еще не подвезли, а запрос все-таки хочется написать в один проход. В этом случае можно воспользоваться знанием, что count(*) FILTER(WHERE cond) эквивалентно sum(CASE cond):

SELECT  sum(CASE WHEN prime < 10 THEN 1 ELSE 0 END) countlt, sum(CASE WHEN prime > 10 THEN 1 ELSE 0 END) countgtFROM  unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;

Или можно чуть короче, если вспомнить о возможности скастовать boolean в integer вместо CASE с результатами 1/0:

SELECT  sum((prime < 10)::integer) countlt, sum((prime > 10)::integer) countgtFROM  unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime;

Вообще, подход вычисления агрегата от некоторого условия достаточно универсален, но имеет и свои подводные камни.

Агрегация в массив


Допустим, мы хотим теперь получить не просто количество чисел, подходящих под то или иное условие, но массивы из них состоящие:

->  $1 = '{2,3,5,7,11,13,17,19}'<-   primeslt |   primesgt  {2,3,5,7} | {11,13,17,19}

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

Вариант с использованием FILTER очевиден:

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime)SELECT  array_agg(prime) FILTER(WHERE prime < 10) primeslt, array_agg(prime) FILTER(WHERE prime > 10) primesgtFROM  src;



А вот если попытаться превратить его в агрегат от условия придется разбираться с попаданием в набор NULL'ов, что уже совсем невесело:

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime) , tmp AS (  SELECT    array_agg(CASE WHEN prime < 10 THEN prime END) primeslt -- {2,3,5,7,NULL,NULL,NULL,NULL}  , array_agg(CASE WHEN prime > 10 THEN prime END) primesgt -- {NULL,NULL,NULL,NULL,11,13,17,19}  FROM    src)SELECT  ARRAY(SELECT * FROM unnest(primeslt) prime WHERE prime IS NOT NULL) primeslt, ARRAY(SELECT * FROM unnest(primesgt) prime WHERE prime IS NOT NULL) primesgtFROM  tmp;

Но если вам хоть немного повезло, и стоит хотя бы версия 9.3, то можно воспользоваться функцией array_remove для достижения того же эффекта:

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime) SELECT  array_remove(array_agg(CASE WHEN prime < 10 THEN prime END), NULL) primeslt, array_remove(array_agg(CASE WHEN prime > 10 THEN prime END), NULL) primesgtFROM  src;

Несколько агрегатов: Function Scan vs CTE


Мы тут внезапно вынесли наш исходный набор в CTE а почему? Потому что так банально быстрее. Давайте проверим на простом примере:

SELECT  array_agg(i) FILTER(WHERE i % 2 = 0) even, array_agg(i) FILTER(WHERE i % 2 = 1) oddFROM  generate_series(1, 1000000) i;




WITH src AS (  SELECT generate_series(1, 1000000) i)SELECT  array_agg(i) FILTER(WHERE i % 2 = 0) even, array_agg(i) FILTER(WHERE i % 2 = 1) oddFROM  src;



Почти на 40% быстрее! Пример, конечно, вырожденный, но эффект имеет место быть.

DISTINCT + OVER


Еще один способ агрегации за единственный проход заключается в использовании уникализации выборки с помощью DISTINCT и применения окна к агрегатной функции:

WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime)SELECT DISTINCT  array_agg(prime) FILTER(WHERE prime < 10) OVER() primeslt, array_agg(prime) FILTER(WHERE prime > 10) OVER() primesgtFROM  src;

Единственная проблема такая группировка небесплатна:



Сложный агрегат


Но предположим, что мы хотим что-то этакое сложное, для чего нет подходящего агрегата:

->  $1 = '{2,3,5,7,11,13,17,19}'<-                 exp                  |   val  2 * 3 * 5 * 7 * 11 * 13 * 17 * 19 = | 9699690

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

Соберем запрос, который решит нашу задачу:

WITH RECURSIVE src AS (  SELECT    *  FROM    unnest('{2,3,5,7,11,13,17,19}'::integer[])      WITH ORDINALITY T(prime, rn)), T(rn, exp, val) AS (  SELECT    0::bigint    -- база агрегации  , '{}'::integer[]  , 1UNION ALL  SELECT    src.rn    -- итеративное вычисление сразу всех агрегатов  , exp || src.prime  , val * src.prime   FROM    T  JOIN    src      ON src.rn = T.rn + 1 -- переход к следующей записи)SELECT  array_to_string(exp, ' * ') || ' = ' exp, valFROM  TORDER BY -- отбор финального результата  rn DESCLIMIT 1;



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

WITH RECURSIVE src AS (  SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr), T(i, exp, val) AS (  SELECT    1::bigint    -- база агрегации  , '{}'::integer[]  , 1UNION ALL  SELECT    i + 1    -- итеративное вычисление сразу всех агрегатов  , exp || arr[i]  , val * arr[i]  FROM    T  , src  WHERE    i <= array_length(arr, 1))SELECT  array_to_string(exp, ' * ') || ' = ' exp, valFROM  TORDER BY -- отбор финального результата  i DESCLIMIT 1;



Намного лучше!

Math.bonus


Применим string_agg и немного математической магии:


WITH src AS (  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime)SELECT  string_agg(prime::text, ' * ') || ' = ' exp, exp(sum(ln(prime)))::integer val -- для любителей математикиFROM  src;

Подробнее..
Категории: Программирование , Ненормальное программирование , Postgresql , Sql , Высокая производительность , Блог компании тензор , Sql tips and tricks , Базы данных , Explain , Explain.tensor.ru , Array , Unnest , Рекурсия , Агрегаты

Вооруженным глазом наглядно о проблемах PostgreSQL-запроса

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


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



Сокращенный текстовый вид


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



Поэтому мы предпочитаем сокращенный вид, когда влево-вправо вынесена ключевая информация о времени выполнения и использованных buffers каждого узла, и очень просто заметить максимумы:



Круговая диаграмма


Но иногда даже просто понять где болит сильнее всего непросто, особенно, если он содержит несколько десятков узлов и даже сокращенная форма плана занимает 2-3 экрана.



В этом случае на помощь придет обычная круговая диаграмма:



Сразу, навскидку, видна примерная доля потребления ресурсов каждым из узлов. При наведении на него, слева в текстовом представлении мы увидим иконку у выбранного узла.

Плитка


Увы, piechart плохо показывает отношения между разными узлами и самые горячие точки. Для этого гораздо лучше подойдет вариант отображения плиткой:



Диаграмма выполнения


Но оба эти варианта не показывают полную цепочку вложений служебных узлов CTE/InitPlain/SubPlan его можно увидеть только на диаграмме реального выполнения:



Нужно больше метрик!


Если вы снимаете план реального выполнения запроса как EXPLAIN (ANALYZE), то увидите там только затраченное время. Но очень часто этого недостаточно для правильных выводов!

Например, выполняя запрос на непрогретом кэше вы получите (но не увидите!) время получения данных с носителя, а вовсе не работы самого запроса.

Поэтому пара рекомендаций:

  • Используйте EXPLAIN (ANALYZE, BUFFERS), чтобы увидеть объем вычитываемых страниц данных. Эта величина практически не подвержена колебаниям от нагрузки самого сервера и может быть использована в качестве метрики при оптимизации.
  • Используйте track_io_timing, чтобы понимать, сколько именно времени заняла работа с носителем.

И вот если ваш план содержит не только время, но и buffers или i/o timings, то на каждой из вариантов диаграмм вы сможете переключиться в режим анализа этих метрик. Иногда можно сразу увидеть, например, что больше половины всех чтений пришлось на единственный проблемный узел:



Предыдущие статьи по теме:

Подробнее..
Категории: Визуализация данных , Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Explain , Explain.tensor.ru

Правильно cчитаем параллельные планы PostgreSQL

10.08.2020 12:13:56 | Автор: admin
Исторически, модель работы сервера PostgreSQL выглядит как множество независимых процессов с частично разделяемой памятью. Каждый из них обслуживает только одно клиентское подключение и один запрос в любой момент времени и никакой многопоточности.

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

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

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


Со схемами работы некоторых параллельных узлов можно ознакомиться в статье Parallelism in PostgreSQL by Ibrar Ahmed, откуда взято и это изображение.
Правда, читать планы в этом случае становится нетривиально.

Вкратце хронология внедрения параллельного исполнения операций плана выглядит так:


Поэтому, если вы пользуетесь одной из последних версий PostgreSQL, шансы увидеть в плане Parallel ... весьма велики. А с ним приходят и

Странности со временем


Возьмем план из PostgreSQL 9.6:


[посмотреть на explain.tensor.ru]

Только один Parallel Seq Scan выполнялся 153.621ms внутри поддерева, а Gather вместе со всеми подузлами всего 104.867ms.

Как так? Суммарно времени наверху стало меньше?..

Взглянем чуть подробнее на Gather-узел:

Gather (actual time=0.969..104.867 rows=333333 loops=1)  Workers Planned: 2  Workers Launched: 2  Buffers: shared hit=4425

Workers Launched: 2 говорит нам о том, что дополнительно к основному процессу ниже по дереву были задействованы еще 2 дополнительных итого 3. Поэтому все, что происходило внутри Gather-поддерева является суммарным творчеством всех 3 процессов сразу.

Теперь посмотрим, что там в Parallel Seq Scan:

Parallel Seq Scan on tst (actual time=0.024..51.207 rows=111111 loops=3)  Filter: ((i % 3) = 0)  Rows Removed by Filter: 222222  Buffers: shared hit=4425

Ага! loops=3 это сводная информация по всем 3 процессам. И, в среднем, каждый такой цикл занял по 51.207ms. То есть суммарно для отработки этого узла серверу понадобилось 51.207 x 3 = 153.621 миллисекунды процессорного времени. То есть если мы хотим понять чем был занят сервер именно это число и поможет нам понять.
Замечу, что для понимания реального времени выполнения надо суммарное время разделить на количество worker'ов то есть [actual time] x [loops] / [Workers Launched].

В нашем примере каждый worker выполнил лишь один цикл по узлу, поэтому 153.621 / 3 = 51.207. И да, теперь уже нет ничего странного, что единственный Gather в головном процессе выполнился за как бы меньшее время.

Итого: смотрите на explain.tensor.ru суммарное (по всем процессам) время узла, чтобы понять, какой именно нагрузкой был занят ваш сервер, и оптимизации какой части запроса стоит уделять время.

В этом смысле поведение того же explain.depesz.com, показывающего сразу усредненное реальное время, выглядит менее полезным для целей отладки:



Не согласны? Добро пожаловать в комментарии!

Gather Merge теряет все


Теперь выполним тот же запрос на версии PostgreSQL 10:


[посмотреть на explain.tensor.ru]

Обратим внимание, что в плане у нас вместо узла Gather теперь оказался Gather Merge, а последовательное чтение таблицы превратилось в упорядоченное, по индексу, хоть и параллельное. Вот что говорит по этому поводу мануал:
Когда над параллельной частью плана оказывается узел Gather Merge, а не Gather, это означает, что все процессы, выполняющие части параллельного плана, выдают кортежи в отсортированном порядке, и что ведущий процесс выполняет слияние с сохранением порядка. Узел же Gather, напротив, получает кортежи от подчинённых процессов в произвольном удобном ему порядке, нарушая порядок сортировки, который мог существовать.

Но не все ладно в датском королевстве:

Limit (actual time=110.740..113.138 rows=10000 loops=1)  Buffers: shared hit=888 read=801, temp read=18 written=218  I/O Timings: read=9.709  ->  Gather Merge (actual time=110.739..117.654 rows=10000 loops=1)        Workers Planned: 2        Workers Launched: 2        Buffers: shared hit=2943 read=1578, temp read=24 written=571        I/O Timings: read=17.156

При передаче атрибутов Buffers и I/O Timings вверх по дереву часть данных была безвременно утрачена. Мы можем оценить размер этой потери как раз примерно в 2/3, которые формируются вспомогательными процессами.

Увы, в самом плане эту информацию взять неоткуда отсюда и минусы на вышележащем узле. И если посмотреть дальнейшую эволюцию этого плана в PostgreSQL 12, то он принципиально не меняется, разве что добавляется немного статистики по каждому worker на Sort-узле:

Limit (actual time=77.063..80.480 rows=10000 loops=1)  Buffers: shared hit=1764, temp read=223 written=355  ->  Gather Merge (actual time=77.060..81.892 rows=10000 loops=1)        Workers Planned: 2        Workers Launched: 2        Buffers: shared hit=4519, temp read=575 written=856        ->  Sort (actual time=72.630..73.252 rows=4278 loops=3)              Sort Key: i              Sort Method: external merge  Disk: 1832kB              Worker 0:  Sort Method: external merge  Disk: 1512kB              Worker 1:  Sort Method: external merge  Disk: 1248kB              Buffers: shared hit=4519, temp read=575 written=856              ->  Parallel Seq Scan on tst (actual time=0.014..44.970 rows=111111 loops=3)                    Filter: ((i % 3) = 0)                    Rows Removed by Filter: 222222                    Buffers: shared hit=4425Planning Time: 0.142 msExecution Time: 83.884 ms

Итого: не доверяйте данным узла над Gather Merge.
Подробнее..
Категории: Визуализация данных , Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Explain , Explain.tensor.ru , Gather , Gather merge , Parallel

Анализируем слона по частям

29.10.2020 14:07:14 | Автор: admin
Если вы регулярно отлаживаете производительность запросов к PostgreSQL, а EXPLAIN (ANALYZE, BUFFERS) ... любимый инструмент познания особенностей работы этой СУБД, то новые полезные фишки нашего сервиса визуализации и анализа планов explain.tensor.ru наверняка пригодятся вам в этом нелегком деле.

Но сразу напомню, что без полноценного всестороннего мониторинга базы PostgreSQL использовать только анализ плана это выступать с позиции мудреца #5!


[источник КДПВ, Слепые и слон]

Самуил Маршак, 1940
Ученый спор

Слепцы, числом их было пять,
В Бомбей явились изучать
Индийского слона.
Исследовав слоновий бок,
Один сказал, что слон высок
И прочен, как стена.

Другой по хоботу слона
Провел рукой своей
И заявил, что слон одна
Из безопасных змей.

Ощупал третий два клыка,
И утверждает он:
На два отточенных штыка
Похож индийский слон!

Слепец четвертый, почесав
Колено у слона,
Установил, что слон шершав,
Как старая сосна.

А пятый, подойдя к слону
Со стороны хвоста,
Определил, что слон в длину
Не больше чем глиста.

Возникли распри у слепцов
И длились целый год.
Потом слепцы в конце концов
Пустили руки в ход.

А так как пятый был силен,
Он всем зажал уста.
И состоит отныне слон
Из одного хвоста!

Итак, сегодня в программе:

  • меняем шевроны на погоны
  • сводим мега-планы
  • ведем персональный архив
  • изучаем генеалогию планов
  • всматриваемся в окна

Не цветом единым!


Исторически, при просмотре плана самые горячие узлы мы отмечали вертикальным шевроном слева от значения чем больше значение, тем насыщеннее цвет.



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



Полезная статистика для мега-планов


Многие не замечают у плана вкладку Статистика, вот она справа:


А кто и заметил вряд ли активно пользовался. Мы решили исправить это упущение, и сделать ее действительно полезной для анализа крупногабаритных планов (100+ узлов).

Группировка узлов


Все одинаковые узлы плана (то есть те, у которых совпадают тип узла, использованная таблица и индекс) группируются в одну строку таблицы. При этом все их показатели (время выполнения, количество прочитанных и отброшенных записей, общее число проходов и объем прочитанных данных) суммируются.

А для наглядности каждый тип узла несет цветовую метку:

  • красная чтение данных
    узлы Seq Scan, Index Scan, CTE Scan и разные другие ... Scan
  • желтая обработка данных
    узлы Sort, Unique, Aggregate, Group, Materialize, ...
  • зеленая соединения
    узлы Nested Loop, Merge Join, Hash Join, ...


Сортировка по любому показателю


Если вдруг вам требуется анализ не по суммарному времени, а типу узла, например просто кликните по заголовку столбца и все будет:



Контекстная подсказка узла


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



Персональный архив планов

Без регистрации и SMS!

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


Генеалогия планов


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

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



или на уже существующем плане вы можете задать его, отредактировать или добавить связанный план:



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



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


Всматриваемся в окна


Небольшое, но полезное усовершенствование Query Profiler, о котором я писал ранее мы научили его заглядывать в окна и правильно сопоставлять с узлами плана:

->  WindowAgg   ==>  WINDOW / OVER      ->  Sort  ==>    PARTITION BY / ORDER BY

как несколько независимых определений окна (WINDOW) в рамках одного запроса:



так и сортировки в оконных функциях без явного определения:



Удачной охоты на разные неэффективности!
Подробнее..
Категории: Визуализация данных , Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Explain , Explain.tensor.ru

One Tool to Analyze Them All

07.12.2020 12:09:16 | Автор: admin
Мы рады сообщить о реализации на explain.tensor.ru базовой поддержки анализа и визуализации планов, специфичных для PostgreSQL-совместимых решений: Timescale, Citus, Greenplum и Redshift.


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

EXPLAIN <-> SQL


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



Поддерживается определение узлов [Parallel] Custom Scan (ChunkAppend):




Собственное время Task-узлов, выполняющихся на отдельных нодах кластера, определяется на основании фактического Execution Time:



Время родителя Custom Scan (Citus Adaptive) вычисляется корректно, даже в случае одновременного выполнения Task сразу на нескольких узлах кластера:


Подробнее об анализе EXPLAIN в Citus.


Поддерживаются специфичные узлы вроде Broadcast Motion, Redistribute Motion, Gather Motion, Partition Selector, Sequence:


Подробнее об анализе EXPLAIN в Greenplum.


Поддерживается работа со всеми XN-узлами, включая специфичные XN Network, XN Merge, XN Window и data redistribution у Join-узлов:



Подробнее об анализе EXPLAIN в Redshift.


Если вдруг вы нашли что-то, что мы пока не учли напишите в комментариях.
Подробнее..
Категории: Визуализация данных , Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Explain , Explain.tensor.ru , Greenplum , Redshift , Citus , Timescaledb

Анализируем слона вместе с коллегами

15.06.2021 14:10:21 | Автор: admin

Если ваша жизнь DBA, сопровождающего PostgreSQL, наполнена вопросами "а почему так медленно?" и "как сделать, чтобы запрос не тормозил?", наш сервис анализа и визуализации планов запросовexplain.tensor.ru сделает ее немного легче за счет привлечения коллег и обновленных подсказок.

м/ф "Следствие ведут Колобки"м/ф "Следствие ведут Колобки"

"Ландон из зе кепитал оф Грейт Британ"

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

Обсуждайте проблемный план там, где вам удобноОбсуждайте проблемный план там, где вам удобно

А чтобы коллегу можно было оперативно подключить к анализу проблем, добавлена возможность оперативно "расшарить" ссылку на план через популярные мессенджеры, соцсети или почту.

Подсказки к плану

Про базовый набор подсказок и способов ими воспользоваться я уже рассказывал в статье "Рецепты для хворающих SQL-запросов" - теперь мы сделали их еще больше и удобнее!

Рекомендательные подсказки узлов планаРекомендательные подсказки узлов плана

  • цветовая и текстовая кодировка разных видов

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

  • несколько подсказок у одного узла

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

  • на самом видном месте

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

Все подсказки - вместе, клик - и вы на местеВсе подсказки - вместе, клик - и вы на месте

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

Масштабируемая диаграмма

Если прежде увидеть "горячие точки" на потоковой диаграмме выполнения большого плана было сложно за счет возникавшей прокрутки, то теперь сразу запускается просмотр в режиме масштабирования. Но вернуться к режиму прокрутки можно в один клик.

Карта будущего сражения за производительностьКарта будущего сражения за производительность

Пользуйтесь! Возникнут идеи или замечания - прошу в комментарии.

Подробнее..
Категории: Визуализация данных , Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Dba , Explain , Explain.tensor.ru

PostgreSQL Antipatterns работаем с отрезками в кровавом энтерпрайзе

10.11.2020 10:22:20 | Автор: admin
В различных бизнес-приложениях регулярно возникает необходимость решить какую-либо задачу с отрезками/интервалами. Самое сложное в них понять, что это именно одна из таких задач.


Как правило, они отчаянно маскируются, и даже у нас в СБИС их найти можно в абсолютно разных сферах управления предприятием: контроле рабочего времени, оценке загрузки линий АТС или даже в бухгалтерском учете.
Отличие enterprise [решения] от всего остального он всегда идёт от запросов бизнеса и решает какую-то бизнес-задачу. [src]
Вот и давайте посмотрим, какие именно прикладные задачи и как можно решить с помощью PostgreSQL и сократить время анализа данных с нескольких секунд на бизнес-логике до десятков миллисекунд, умея эффективно применять следующие алгоритмы непосредственно внутри SQL-запроса:

  • поиск отрезков, пересекающих точку/интервал
  • слияние отрезков по максимальному перекрытию
  • подсчет количества отрезков в каждой точке

Отрезки в точке


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



Типичная бизнес-задача: кто из сотрудников был на больничном в прошлый понедельник?, можно ли сейчас уйти на обед? или даже кто из контрагентов должен нам на начало этого года?.
Сгенерируем тестовую модель больничных для отладки наших запросов:

CREATE TABLE ranges(  owner    integer, dtb -- начало отрезка    date, dte -- окончание отрезка    date);INSERT INTO ranges(  owner, dtb, dte)SELECT  (random() * 1e3)::integer, dtb, dtb + (random() * 1e2)::integerFROM  (    SELECT      now()::date - (random() * 1e3)::integer dtb    FROM      generate_series(1, 1e5)  ) T;

Попробуем выяснить, кто из сотрудников числился на больничном в новогоднюю ночь.

BETWEEN + btree


Как правило, первый вариант запроса будет выглядеть как-то так:

SELECT  *FROM  rangesWHERE  '2020-01-01'::date BETWEEN dtb AND dte;

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

CREATE INDEX ON ranges(dtb, dte);

И это не работает примерно никак, потому что мы все равно перебрали всю таблицу (100K записей, из которых 95K отбросили):


Чтобы понять почему, достаточно представить визуально, как именно должен работать такой индекс, как мы делали это в статье DBA: находим бесполезные индексы: значение dtb ограничено только справа, dte только слева, в результате имеем два диапазонных ключа, которые совместно по btree работают плохо.

*range + GiST


Но, конечно же, эффективное решение есть это использование возможностей диапазонных типов и GiST-индексов:

CREATE INDEX ON ranges  USING gist( -- используем "пространственный" GiST-индекс    daterange(dtb, dte, '[]') -- формируем диапазон дат  );

Наш запрос теперь нужно модифицировать к такому виду:

SELECT  *FROM  rangesWHERE  daterange(dtb, dte, '[]') @> '2020-01-01'::date; -- значение входит в диапазон



С помощью подходящего индекса мы сократили время работы запроса в 3 раза.

Отрезки по группе


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



Поэтому усложним задачу и добавим в нашу модель принадлежность одному из отделов:

ALTER TABLE ranges ADD COLUMN segment integer;UPDATE ranges SET segment = (random() * 1e2)::integer;


SELECT  *FROM  rangesWHERE  segment = 1 AND  daterange(dtb, dte, '[]') @> '2020-01-01'::date;



Индекс, конечно, по-прежнему используется, но теперь получается, что 99% данных мы вычитываем зря 4945 строк отбрасывается при проверке сегмента.

Решение очевидно добавим нужное нам поле в индекс, да?..

CREATE INDEX ON ranges USING gist(segment, daterange(dtb, dte, '[]'));-- ОШИБКА:  для типа данных integer не определён класс операторов по умолчанию для метода доступа "gist"-- HINT:  Вы должны указать класс операторов для индекса или определить класс операторов по умолчанию для этого типа данных.

Увы, нет GiST-индекс просто так не поддерживает операции над скалярными типами. Зато если подключить расширение btree_gist научится:

CREATE EXTENSION btree_gist;CREATE INDEX ON ranges USING gist(segment, daterange(dtb, dte, '[]'));



Наш запрос избавился от всех неиндексных фильтраций и стал в 20 раз быстрее! До кучи, еще и читать стал в 10 раз меньше данных (buffers).

Отрезки на интервале


Но пересечение отрезков с точкой требуется гораздо реже, чем пересечение отрезков с интервалом.
Типичная бизнес-задача: "кто ходил в отпуск в этом месяце?", "какие задачи в моем календаре на сегодня?"



Собственно, все нужные для этого индексы у нас уже есть, осталось правильно записать условие:

SELECT  *FROM  rangesWHERE  segment = 1 AND  daterange(dtb, dte, '[]') && daterange('2020-01-01', '2020-02-01', '[)'); -- пересечение отрезков, без включения правой границы



Объединение отрезков


Допустим, мы нашли когда и кто ходил в отпуск а теперь хотим узнать, когда же именно команда играла в сокращенном составе. Для этого нам придется найти объединение отрезков по максимальному перекрытию.



Если бы мы знали заранее все интервалы, участвующие в объединении, то могли бы просто написать через оператор "+":

SELECT int4range('[0,2]') + int4range('[1,3]');-- [0,4)

К сожалению, для наборов интервалов штатной функции агрегации вроде range_agg пока не реализовано. Ее, конечно, можно создать самостоятельно, но как-то это и выглядит страшно, и базу засоряет лишними сущностями поэтому сделаем это обычным SQL-запросом.



Вариант, работающий даже в версии 8.4, приведен в PostgreSQL Wiki:

WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT  min(s) s, max(e) eFROM  (    SELECT      s    , e    , max(new_start) OVER(ORDER BY s, e) left_edge    FROM      (        SELECT          s        , e        , CASE            WHEN s < max(le) OVER(ORDER BY s, e) THEN              NULL            ELSE              s          END new_start        FROM          (            SELECT              s            , e            , lag(e) OVER(ORDER BY s, e) le            FROM              rng          ) s1      ) s2  ) s3GROUP BY  left_edge;

Но план у него выглядит не слишком приятно сортировка, три WindowAgg, группировка:



И обратите внимание, что в точке 10 он не сливает касающиеся интервалы, хотя обычно это требуется как в нашей задаче про отпуска.

Давайте сконструируем запрос, который нас устроит:

  1. Отсортируем все отрезки по паре (начало, конец).
  2. Для каждого начала вычислим, стоит ли оно правее максимального из предшествующих ему концов. Если левее вообще ничего нет или самый правый конец из предыдущих все равно левее это точка начала искомого интервала.
  3. Отнесем все отрезки в группы по количеству предшествующих им найденных начал.
  4. В каждой группе берем наименьшее из начал и наибольший из концов это и есть искомый непрерывный интервал.



WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT -- min/max по группе  min(s) s, max(e) eFROM  (    SELECT      *    , sum(ns::integer) OVER(ORDER BY s, e) grp -- определение групп    FROM      (        SELECT          *        , coalesce(s > max(e) OVER(ORDER BY s, e ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), TRUE) ns -- начало правее самого правого из предыдущих концов == разрыв        FROM          rng      ) t  ) tGROUP BY  grp;

Ну, и одно выполнение WindowAgg нам удалось отыграть:


Длина объединенных отрезков


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

  1. Для каждого отрезка вычисляем максимум концов всех предыдущих.
  2. Берем разность между концом отрезка и большим из начала отрезка и найденного конца.
  3. Осталось только просуммировать полученные разности.



WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT  sum(delta)FROM  (    SELECT      *    , greatest(        e - greatest(          max(e) OVER(ORDER BY s, e ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)        , s        )      , 0      ) delta    FROM      rng  ) T;


Спасибо imschur за этот вариант оптимизации.

Подсчет отрезков на каждом интервале


Итак, мы смогли узнать, сколько всего времени у нас работали не все сотрудники. Но ведь их отсутствовало разное количество в разные промежутки времени а сколько именно когда?
Типичная бизнес-задача: анализ и распределение нагрузки например, между операторами call-центра: "Сколько звонков мы обслуживаем одновременно? Сколько для этого нужно операторов в ночной смене?"
  1. Присвоим каждому началу отрезка вес +1, а каждому концу -1.
  2. Просуммируем накопительно значения в каждой точке это и есть количество отрезков на интервале, начинающемся с этой точки и вплоть до следующей по порядку.



WITH rng(s, e) AS (  VALUES    ( 1,  3)  , ( 2,  4)  , ( 5,  6)  , ( 5,  8)  , ( 6,  9)  , ( 7, 10)  , ( 8, 10)  , (10, 11)  , (10, 15)  , (11, 12)  , (12, 13))SELECT DISTINCT ON(p) -- уникализация до единственного значения в точке  p, sum(v) OVER(ORDER BY p) qty -- накопительная суммаFROM  (    SELECT      s p    , +1 v    FROM      rng  UNION ALL    SELECT      e p    , -1 v    FROM      rng  ) TORDER BY  1;

На этом сегодня все. Если знаете еще какие-то полезные нетривиальные алгоритмы работы с отрезками для решения бизнес-задач напишите в комментариях.
Подробнее..
Категории: Алгоритмы , Postgresql , Sql , Блог компании тензор , Sql tips and tricks , Erp-системы , Explain , Explain.tensor.ru , Отрезки , Интервалы , Gist , Btree_gist , Range_agg

PostgreSQL Antipatterns анализируем блокировки SELF JOIN vs WINDOW

08.07.2020 10:20:45 | Автор: admin
Ранее мы уже научились перехватывать блокировки из лога сервера 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'и исключим из анализа это просто ошибки, и попробуем выяснить, сколько всего времени мы потеряли из-за блокировок за конкретный день на определенном хосте.

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

CREATE TABLE lock(  dt       -- ключ хронологического секционирования    date, host     -- сервер, на котором возникла блокировка    uuid, pid      -- PID процесса из строки лога    integer, ts       -- момент события    timestamp, event    -- { lock-wait | lock-acquire | deadlock-detect }    lockevent, type     -- { relation | extend | ... }    locktype, mode     -- { AccessShare | RowShare | ... }    lockmode, lock     -- объект блокировки    uuid, exectime -- продолжительность    numeric(32,2));

Более подробно про организацию секционирования в нашей системе мониторинга можно прочитать в статье Пишем в PostgreSQL на субсветовой: 1 host, 1 day, 1TB, а про различные типы и режимы блокировок в DBA: кто скрывается за блокировкой.

Как слышится, так и пишется


Попробуем ответить на вопрос, вынесенный в начало статьи, простейшим способом.



Что такое время ожидания блокировки? Ну, очевидно же, это время ее получения для каждого случая ее ожидания:

  • берем каждый случай ожидания (lock-wait)
  • для него находим ближайшую снизу по времени запись получения (lock-acquire) этой же (lock, pid, mode) блокировки то есть на тот же объект, в том же процессе, с тем же режимом

Тип блокировки (type) в нашем случае можно опустить, поскольку он однозначно определяется самим объектом (lock).

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

SELECT  ts, pid, event, type, mode, lock, exectime, T.*FROM  lock lc, LATERAL (    SELECT      exectime waittime    FROM      lock    WHERE      (        dt      , host      , lock      , pid      , mode      , event      ) = (        '2020-06-19'::date      , lc.host      , lc.lock      , lc.pid      , lc.mode      , 'lock-acquire'      ) AND      ts >= lc.ts    ORDER BY      ts    LIMIT 1  ) TWHERE  (    lc.dt  , lc.host  , lc.event  ) = (    '2020-06-19'::date  , 'e828a54d-7e8a-43dd-b213-30c3201a6d8e'::uuid  , 'lock-wait'::lockevent  );

Все просто и ясно! А что нам покажет EXPLAIN?..



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

Но является ли этот запрос вообще корректным для нашей задачи? Нет! Посмотрим внимательно в собранные данные:



Ой Оказывается, сервер жалуется в логи на одну и ту же неполученную блокировку иногда много-много раз. А это означает, что мы учли время ее ожидания кратно количеству таких записей в логе, что совсем не соответствует желаемому.

Помни о цели!


Собственно, а зачем мы вообще для каждой записи ожидания ищем связанную? Мы же хотим узнать, сколько заняло ожидание, а оно прямо записано в lock-acquire. Так давайте сразу отбирать только их, тогда будет всего лишь один Index Scan правильно?

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



Так неужели нет способа за одно чтение сразу получить все нужные нам данные?

Window Functions: семерых одним ударом


На помощь нам придут оконные функции.


А конкретнее модель выделения цепочек в готовой выборке из статьи SQL HowTo: собираем цепочки с помощью window functions.

Сначала поймем, что условием окончания цепочки то есть сегмента подряд идущих по ключу (host, lock, pid, mode) записей блокировки для нас является или явное возникновение event = 'lock-acquire' или (что очень редко, но бывает) начало нового сегмента блокировки того же объекта, чья длительность (exectime) начала считаться заново.



Также надо учесть тот факт, что время может совпадать для нескольких записей лога даже с одного PID. В этом случае надо дополнительно сортировать по exectime, чтобы получить правильную последовательность:



-- формируем условие окончания блокировкиWITH lc AS (  SELECT    *  , CASE      WHEN event = 'lock-wait' THEN        exectime > coalesce(lead(exectime) OVER(PARTITION BY lock, pid, mode ORDER BY ts, exectime), 0) -- "перелом" времени ожидания      ELSE TRUE -- 'lock-acquire' - блокировка получена    END cond -- условие окончания "цепочки"  FROM    lock lc  WHERE    event <> 'deadlock-detect' AND -- исключаем все deadlock    (      lc.dt    , lc.host    ) = (      '2020-06-19'::date    , 'e828a54d-7e8a-43dd-b213-30c3201a6d8e'::uuid    ))-- оставляем только "последние" записи - их exectime и есть время ожидания "всей" блокировкиSELECT  ts, pid, event, type, mode, lock, exectimeFROM  lcWHERE  cond;



Теперь мы прочитали всего 8MB данных (в 100 раз меньше!), чуть-чуть уменьшив итоговое время выполнения.

Его можно уменьшить еще, если создать индекс, идеально подходящий под OVER (то есть включающий lock, pid, mode, ts, exectime), избавившись от Sort-узла. Но обычно поле в индексе за timestamp делать не стоит.
Подробнее..
Категории: Postgresql , Sql , Администрирование баз данных , Блог компании тензор , Dba , Sql tips and tricks , Базы данных , Explain , Window functions

Чего энтерпрайзу в PostgreSQL не хватает

26.04.2021 10:24:01 | Автор: admin

В конце прошлого года Иван Панченко предложил мне рассказать на внутреннем семинаре Postgres Pro, чего, по нашему опыту использования PostgreSQL в "кровавом энтерпрайзе" "Тензора", не хватает в этой СУБД.

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

Обслуживание сервера

Легковесный менеджер соединений

он же Built-in connection pooler

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

Процесс старта ресурсоемкий и небыстрый сам по себе, а постоянно существующий процесс еще и резервирует на себя некоторую долю RAM, которая со временем имеет свойство расти за счет "накачки" метаинформацией.

Просто представьте, что у вас в базе развернуто миллион таблиц, к которым вы достаточно случайно обращаетесь. Только на системных таблицах pg_class, pg_depend, pg_statistics это даст объем порядка 1GB, который рано или поздно окажется в памяти процесса.

Поэтому в реальных условиях соединения непосредственно с БД стараются беречь, без нужды новые не создавать, а неактивные закрывать поскорее. Для этого "в разрыв" БЛ-БД ставят отдельный сервис connection pooler, который принимает от БЛ все "хотелки" по открытию/закрытию соединений и выполнению запросов, а в БД транслирует их в ограниченное количество длительно живущих коннектов.

Пример работы connection poolПример работы connection pool

Классические представители:

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

Поэтому еще в начале 2018 года Константин Книжник начал эксперименты с реализацией менеджера соединений "из коробки" на основе интеграции потоков (thread model) в ядро PostgreSQL. В настоящий момент его патч включен на ревью в июльский Commitfest, так что ждем и надеемся на появление хотя бы в v15.

64-bit XID

Если вы пишете в базу много данных, как мы, то достаточно скоро у вас запустится он - autovacuum (to prevent wraparound), чья единственная задача - пробежать по данным и "поправить" их так, чтобы уберечь счетчик транзакций от переполнения.

Одно из наглядных объяснений 32-bit transaction ID wraparoundОдно из наглядных объяснений 32-bit transaction ID wraparound

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

А чтобы никаких подобных процессов не запускалось, достаточно расширить идентификатор транзакции xid с 32 до 64 бит - соответствующий патч Александр Коротков запустил еще в 2017 году. В ядро он тогда так и не был включен, зато попал в Postgres ProEnterprise, откуда рано или поздно доберется и до "ванильного" ядра.

Система хранения данных

Микротаблицы

Каждая таблица и индекс в PostgreSQL с точки зрения хранения представляет из себя не меньше 3 файлов:

То есть если вам необходимо иметь небольшую статичную "словарную" табличку на пару десятков записей, то вы автоматически получаете 3 файла по 8KB, хотя можно было бы обойтись и единственной страницей heap.

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

zheap

Если предыдущий патч устраняет необходимость в достаточно редкой операции, то использование хранилища zheap призвано помочь сэкономить ресурсы сервера на таблицах, в которых почти не бывает ROLLBACK - обычно это различные "логоподобные" вещи или агрегатные "срезы", где важно только текущее состояние, но UPDATE случаются постоянно.

Информация о транзакциях хранится отдельно от контейнера записиИнформация о транзакциях хранится отдельно от контейнера записи

Почитать по теме:

Append-only Storage

В бизнесе СУБД часто используются для хранения многократно повторяющихся или монотонно увеличивающихся значений - например, логи, дата и время создания какого-то документа, его числовой PK, ...

Знание этого факта позволяет существенно сэкономить записываемый объем. Например, переведя базу нашего сервиса мониторинга с v12 на v13, мы сразу получили примерно 10% выигрыша в объеме индексов за счет дедупликации в btree-индексах на реальных данных.

В эту же категорию можно отнести уже принятый патч "BRIN multi-minmax and bloom indexes".

В идеале, хотелось бы иметь возможность назначать часть полей индексов или целых таблиц как "no-MVCC" (то есть не поддерживается никакое версионирование, а потому - и изменение данных, плюс мгновенная видимость со стороны других транзакций), чтобы иметь возможность сэкономить на полях поддержки MVCC.

Timescale базируется на ядре PostgreSQL, но "заточена" на timeseries-dataTimescale базируется на ядре PostgreSQL, но "заточена" на timeseries-data

Отложенная индексация

Одной из причин Uber-скандала, всколыхнувшего PostgreSQL-сообщество несколько лет назад была Index Write Amplification, когда записываемый в таблицу кортеж сразу же одновременно записывается и во все индексы, подходящие по условию для него. Получается, чем больше индексов есть на таблице, тем дольше будет производиться вставка в нее.

Но ведь большинство этих индексов заведомо не будут использованы для поиска этой записи сразу же после завершения запроса! Точнее, разработчик точно может отметить эти индексы и их желаемую "актуальность", примерно как это настраивается для реплики.

CREATE INDEX ... WITH (max_lag = '1min');

В этом случае сервер сам сможет "размазать" необходимые операции во времени для балансировки нагрузки. Наиболее актуальна эта тема при конкуренции с поисковыми системами вроде Sphinx/ElasticSearch, где основная задача "найти вообще", а не "найти прямо сразу сейчас".

Почитать по теме:

Columnar Storage

В идеале - в ядре или в contrib иметь возможность подключения колоночного хранилища для различных аналитических нужд.

Хранение данных "по столбцам"Хранение данных "по столбцам"

Да, есть решения от Citus, но "в энтерпрайзе" не всегда есть возможность установки дополнительных внешних модулей.

In-memory Storage

Появление очень быстрого нетранзакционного хранилища без сброса на диск сильно помогло бы использовать разноуровневые кэши прямо в PostgreSQL, а не выносить их куда-то в Redis, например - получился бы некий аналог Oracle TimesTen In-Memory Database и Tarantool.

Масштабирование

TEMPORARY TABLE и реплики

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

И если с первой проблемой еще как-то можно смириться и исправлять регулярным VACUUM pg_class, то второй недостаток достаточно сильно мешает.

Multimaster

Давно хочется иметь "из коробки".

SQL

SQL-defined Index

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

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

Почитать по теме:

Мониторинг

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

Производительность запросов

Сам анализ, куда ушло время в запросе по тексту плана является нетривиальным, но если его прогнать через визуализацию на explain.tensor.ru, становится попроще:

Визуальный анализ плана запросаВизуальный анализ плана запросаДиаграмма выполнения планаДиаграмма выполнения плана

Почитать по теме:

Снапшоты статистики таблиц

Чтобы определить, в каком именно таблице/индексе "сильно болит", у Oracle есть AWR а наиболее близким аналогом для PostgreSQL является pg_profile.

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

Тепловая карта операций по таблицамТепловая карта операций по таблицам

Почитать по теме:

Это - что напрягает нас в этой, безусловно, отличной СУБД. В комментариях оставляйте рассказы о своих "болях" и "хотелках" при использовании PostgreSQL.

Подробнее..
Категории: Postgresql , Sql , Администрирование баз данных , Высокая производительность , Блог компании тензор , Explain , Explain.tensor.ru , Wraparound , Connection pool , Zheap

Читаем EXPLAIN на максималках

02.03.2021 22:15:14 | Автор: admin

Многим, кто работает с MySQL, известно, что команда EXPLAIN используется для оптимизации запросов, получения информации об использованных и возможных индексах. Большинство разработчиков и администраторов СУБД этой информацией и ограничивается. Я же предлагаю изучить команду EXPLAIN максимально подробно.

Логическая архитектура MySQL

Чтобы понять, как работает EXPLAIN, стоит вспомнить логическую архитектуру MySQL.

Её можно разделить на несколько уровней:

  1. Уровень приложения или клиентский уровень. Он не является уникальным для MySQL. Обычно здесь находятся утилиты, библиотеки или целые приложения, которые подключаются к серверу MySQL.

  2. Уровень сервера MySQL. Его можно разделить на подуровни:

    A. Пул соединений. Сюда относятся аутентификация, безопасность и обработка соединений/потоков. Всякий раз, когда клиент подключается к серверу MySQL, тот выполняет аутентификацию по имени пользователя, хосту клиента и паролю. После того, как клиент успешно подключился, сервер проверяет, имеет ли этот клиент привилегии для выполнения определенных запросов, и, если да, то он получает для своего соединения отдельный поток. Потоки кешируются сервером, поэтому их не нужно создавать и уничтожать для каждого нового соединения.

    B. Сервер MySQL. Этот подуровень во многих источниках называют мозгами MySQL. К нему относятся такие компоненты, как кеши и буферы, парсер SQL, оптимизатор, а также все встроенные функции (например, функции даты/времени и шифрования).

  3. Уровень подсистем хранения. Подсистемы хранения отвечают за хранение и извлечение данных в MySQL.

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

Команда EXPLAIN

Выражение EXPLAIN предоставляет информацию о том, как MySQL выполняет запрос. Оно работает с выражениями SELECT, UPDATE, INSERT, DELETE и REPLACE.

Если у вас версия ниже 5.6

До версии 5.6 команда EXPLAIN работала только с выражениями типа SELECT, и, если вам нужен анализ других выражений, то придется переписать запрос в эквивалентный запрос SELECT.

Для того, чтобы воспользоваться командой, достаточно поставить ключевое слово EXPLAIN перед запросом, и MySQL пометит запрос специальным флагом. Он заставит сервер возвращать информацию о каждом шаге, вместо выполнения. Однако, если в запросе встречается подзапросы в разделе FROM, то сервер будет вынужден выполнить все подзапросы и поместить их результаты во временную таблицу.

Стандартный вывод команды EXPLAIN покажет колонки:

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
Если у вас версия ниже 5.6

В этом случае вы не увидите столбцов filtered и partitions. Для их вывода необходимо, после EXPLAIN, добавить ключевые слова EXTENDED или PARTITIONS, но не оба сразу.

Если у вас версия 5.6

В версии 5.6 и выше столбец partitions будет включено по-умолчанию, однако для вывода столбца filtered вам всё еще придется воспользоваться ключевым словом EXTENDED.

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

Для начала выполним простой запрос:

EXPLAIN SELECT 1

id: 1select_type: SIMPLEtable: NULLpartitions: NULLtype: NULLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: NULLfiltered: NULLExtra: No tables used

Столбец ID

Этот столбец можно назвать идентификатором или порядковым номером каждого SELECT- запроса. Все выражения SELECT нумеруются последовательно, их можно отнести к простым (без подзапросов и объединений) и составным. Составные запросы SELECT можно отнести к:

A. Простым подзапросам

EXPLAIN SELECT (SELECT 1 from Orders) from Drivers

id

select_type

table

1

PRIMARY

Drivers

2

SUBQUERY

Orders

B. Подзапросам с производными таблицами, то есть с подзапросом в разделе FROM

EXPLAIN SELECT * FROM (SELECT 1, 2) AS tmp (a, b)

id

select_type

table

1

PRIMARY

<derived2>

2

SUBQUERY

null

Как я уже писал выше, этот запрос создаст временную таблицу и MySQL будет ссылаться на неё по псевдониму tmp. В более сложных запросах этот псевдоним будет указан в столбце ref. В первой строке, в столбце table можно увидеть название таблицы , которое формируется по правилу , где N ID запроса.

C. Подзапросам с объединением UNION

EXPLAIN SELECT id FROM Cars UNION SELECT id FROM Drivers

id

select_type

table

1

PRIMARY

Cars

2

UNION

Drivers

null

UNION RESULT

<union1,2>

Здесь есть несколько отличий от примера c FROM-подзапросом. Во-первых, MySQL помещает результат объединения во временную таблицу, из которой, затем, считывает данные. К тому же эта временная таблица отсутствует в исходной SQL-команде, поэтому в столбце id для неё будет null. Во-вторых, временная таблица, появившаяся в результате объединения, показана последней, а не первой.

Точно по такому же правилу формируется название таблица в столбце table <unionN,M>, где N ID первого запроса, а M второго.

Столбец select_type

Показывает тип запроса SELECT для каждой строки результата EXPLAIN. Если запрос простой, то есть не содержит подзапросов и объединений, то в столбце будет значение SIMPLE. В противном случае, самый внешний запрос помечается как PRIMARY, а остальные следующим образом:

  • SUBQUERY. Запрос SELECT, который содержится в подзапросе, находящимся в разделе SELECT (т.е. не в разделе FROM).

  • DERIVED. Обозначает производную таблицу, то есть этот запрос SELECT является подзапросом в разделе FROM. Выполняется рекурсивно и помещается во временную таблицу, на которую сервер ссылается по имени derived table.

    Обратите внимание: все подзапросы в разделе FROM являются производной таблицей, однако, не все производные таблицы являются подзапросами в разделе FROM.

  • UNION. Если присутствует объединение UNION, то первый входящий в него запрос считается частью внешнего запроса и помечается как PRIMARY (см. пример выше). Если бы объединение UNION было частью подзапроса в разделе FROM, то его первый запрос SELECT был бы помечен как DERIVED. Второй и последующий запросы помечаются как UNION.

  • UNION RESULT. Показывает результата запроса SELECT, который сервер MySQL применяет для чтения из временной таблицы, которая была создана в результате объединения UNION.

Кроме того, типы SUBQUERY, UNION и DERIVED могут быть помечены как DEPENDENT, то есть результат SELECT зависит от данных, которые встречаются во внешнем запросе SELECT.

Если у вас версия 5.7 и ниже

Поле DEPENDENT DERIVED появилось только в 8 версии MySQL.

Также типы SUBQUERY и UNION могут быть помечены как UNCACHABLE. Это говорит о том, что результат SELECT не может быть закеширован и должен быть пересчитан для каждой строки внешнего запроса. Например, из-за функции RAND().

Начиная с версии MySQL 5.7 в столбце select_type появилось еще одно значение MATERIALIZED, которое говорит о том, что результат подзапроса был сохранен как виртуальная временная таблица и не будет выполняться для каждой следующей строки внешнего запроса.

Столбец table

Показывает, к какой таблице относится эта строка. В самом простом случае это таблица (или её псевдоним) из вашей SQL- команды.

При объединении таблиц стоит читать столбец table сверху вниз.

EXPLAIN SELECT Clients.id        FROM Clients        JOIN Orders ON Orders.client_id = Clients.id        JOIN Drivers ON Orders.driver_id = Drivers.id

id

seelect_type

table

1

SIMPLE

Clients

1

SIMPLE

Orders

1

SIMPLE

Drivers

Здесь мы видим порядок объединения таблиц, который выбрал оптимизатор. Однако, порядок не всегда будет таким, в каком объединяются таблицы в вашем запросе.

Если запрос содержит подзапрос FROM или объединение UNION, то столбец table читать будет не так просто, потому что MySQL будет создавать временные таблицы, на которые станет ссылаться.

О столбце table для подзапроса FROM я уже писал выше. Ссылка derived.N является- опережающей, то есть N ID запроса ниже. А ссылка UNION RESULT (union N,M) является обратной, поскольку встречается после всех строк, которые относятся к объединению UNION.

Попробуем, для примера, прочитать столбец table для следующего странного запроса:

EXPLAIN SELECT id, (SELECT 1 FROM Orders WHERE client_id = t1.id LIMIT 1)       FROM (SELECT id FROM Drivers LIMIT 5) AS t1       UNION       SELECT driver_id, (SELECT @var1 FROM Cars LIMIT 1)       FROM (           SELECT driver_id, (SELECT 1 FROM Clients)           FROM Orders LIMIT 5       ) AS t2

id

select_type

table

1

PRIMARY

<derived3>

3

DERIVED

Drivers

2

DEPENDENT SUBQUERY

Orders

4

UNION

<derived6>

6

DERIVED

Orders

7

SUBQUERY

Clients

5

UNCACHEABLE SUBQUERY

Cars

null

UNION RESULT

<union1,4>

Не так просто разобраться в этом, но, тем не менее, мы попробуем.

  1. Первая строка является опережающей ссылкой на производную таблицу t1, помеченную как <derived3>.

  2. Значение идентификатора строки равно 3, потому что строка относится к третьему по порядку SELECT. Поле select_type имеет значение DERIVED, потому что подзапрос находится в разделе FROM.

  3. Третья строка с ID = 2 идет после строки с бОльшим ID, потому что соответствующий ей подзапрос выполнился позже, что логично, ведь нельзя получить значение t1.id, не выполнив подзапрос с ID = 3. Признак DEPENDENT SUBQUERY означает, что результат зависит от результатов внешнего запроса.

  4. Четвертая строка соответствует второму или последующему запросу объединения, поэтому она помечена признаком UNION. Значение <derived6> означает, что данные будут выбраны из подзапроса FROM и добавятся во временную таблицу для результатов UNION.

  5. Пятая строка - это наш подзапрос FROM, помеченный как t2.

  6. Шестая строка указывает на обычный подзапрос в SELECT. Идентификатор этой строки равен 7, что важно, потому что следующая строка уже имеет ID = 5.

  7. Почему же важно, что седьмая строка имеет меньший ID, чем шестая? Потому что каждая строка, помеченная как DERIVED , открывает вложенную область видимости. Эта область видимости закрывается, когда встречается строка с ID меньшим, чем у DERIVED (в данном случае 5 < 6). Отсюда можно понять, что седьмая строка является частью SELECT, в котором выбираются данные из <derived6>. Признак UNCACHEABLE в колонке select_type добавляется из-за переменной @var1.

  8. Последняя строка UNION RESULT представляет собой этап считывания строк из временной таблицы после объединения UNION.

При чтении с помощью EXPLAIN больших запросов часто приходится читать результат сверху вниз и наоборот. Например, в этом примере имеет смысл начать чтение с последней строки, чтобы понять, что первая строка является частью UNION.

Столбец partitions

Показывает, какой партиции соответствуют данные из запроса. Если вы не используете партиционирование, то значение этой колонки будет null.

Столбец type

Показывает информацию о том, каким образом MySQL выбирает данные из таблицы. Хотя в документации MySQL это поле описывается как The join type, многих такое описание смущает или кажется не до конца понятным. Столбец type принимает одно из следующих значений, отсортированных в порядке скорости извлечения данных:

  • ALL. Обычно речь идет о полном сканировании таблицы, то естьт.е. MySQL будет просматривать строчку за строчкой, если только в запросе нет LIMIT или в колонке extra не указано Distinct/not exists, к чему мы вернемся позже.

  • index. В этом случае MySQL тоже просматривает таблицу целиком, но в порядке, заданном индексом. В этом случае не требуется сортировка, но - строки выбираются в хаотичном порядке. Лучше, если в колонке extra будет указано using index, что означает, что вместо полного сканирования таблицы, MySQL проходит по дереву индексов. Такое происходит, когда удалось использовать покрывающий индекс

  • range. Индекс просматривается в заданном диапазоне. Поиск начинается в определенной точке индекса и возвращает значения, пока истинно условие поиска. range может быть использован, когда проиндексированный столбец сравнивается с константой с использованием операторов =, <>, >, >=, <, <=, IS_NULL, <=>, BETWEEN, LIKE или IN.

  • index_subquery. Вы увидите это значение, если в операторе IN есть подзапрос, для которого оптимизатор MySQL смог использовать поиск по индексу.

  • unique_subquery. Похож на index_subquery, но, для подзапроса используется уникальный индекс, такой как Primary key или Unique index.

  • index_merge. Если оптимизатор использовал range-сканирование для нескольких таблиц, он может объединить их результаты. В зависимости от метода слияния, поле extra примет одно из следующих значений: Using intersect пересечение, Using union объединение, Using sort_union объединение сортировки слияния (подробнее читайте здесь)

  • ref_or_null. Этот случай похож на ref, за исключением того, что MySQL будет выполнять второй просмотр для поиска записей, содержащих NULL- значения.

  • fulltext. Использование FULLTEXT-индекса.

  • ref. Поиск по индексу, в результате которого возвращаются все строки, соответствующие единственному заданному значению. Применяется в случаях, если ключ не является уникальным, то есть не Primary key или Unique index , либо используется только крайний левый префикс ключа. ref может быть использован только для операторов = или <=>.

  • eq_ref. Считывается всего одна строка по первичному или уникальному ключу. Работает только с оператором =. Справа от знака = может быть константа или выражение.

  • const. Таблица содержит не более одной совпадающей строки. Если при оптимизации MySQL удалось привести запрос к константе, то столбец type будет равен const. Например, если вы ищете что-то по первичному ключу, то оптимизатор может преобразовать значение в константу и исключить таблицу из соединения JOIN.

  • system. В таблице только одна строка. Частный случай const.

  • NULL. Означает, что оптимизатор смог сам найти нужные данные для запроса и обращаться к таблице не требуется. Например, для выборки минимального значения проиндексированного, в порядке возрастания, столбца достаточно выбрать первое значение из индекса.

Столбец possible_keys

Показывает, какие индексы можно использовать для запроса. Этот столбец не зависит от порядка таблиц, отображаемых EXPLAIN, поскольку список создается на ранних этапах оптимизации. Если в столбце значение NULL, то соответствующих индексов не нашлось.

Столбец keys

Указывает на ключ (индекс), который оптимизатор MySQL решил фактически использовать. Может вообще отсутствовать в столбце possible_keys. Так бывает в случаях, когда ни один из ключей в possible_keys не подходит для поиска строк, но все столбцы, выбранные запросом, являются частью какого-то другого индекса. Тогда оптимизатор может задействовать эти столбцы для поиска по покрывающему индексу, так как сканирование индекса эффективнее, чем сканирование таблицы целиком.

Столбец key_len

Показывает длину выбранного ключа (индекса) в байтах. Например, если у вас есть primary key id типа int, то, при его использовании, key_len будет равен 4, потому что длина int всегда равна 4 байта. В случае составных ключей key_len будет равен сумме байтов их типов. Если столбец key равен NULL, то значение key_len так же будет NULL.

EXPLAIN SELECT * FROM OrdersWHERE client_id = 1

id

table

possible_keys

key

key_len

1

Orders

Orders_Clients_id_fk

Orders_Clients_id_fk

4

 
EXPLAIN SELECT * FROM OrdersWHERE client_id = 1 AND driver_id = 2

id

table

possible_keys

key

key_len

1

Orders

Orders_Drivers_id_fk,

Orders_client_id_driver_id

Orders_client_id_driver_id

8

Столбец ref

Показывает, какие столбцы или константы сравниваются с указанным в key индексом. Принимает значения NULL, const или название столбца другой таблицы. Возможно значение func, когда сравнение идет с результатом функции. Чтобы узнать, что это за функция, можно после EXPLAIN выполнить команду SHOW WARNINGS.

EXPLAIN SELECT * FROM Drivers

id

table

ref

1

Drivers

null

 
EXPLAIN SELECT * FROM DriversWHERE id = 1

id

table

ref

1

Drivers

const

 
EXPLAIN SELECT * FROM DriversJOIN Orders ON Drivers.id = Orders.driver_id

id

table

ref

1

Orders

null

1

Drivers

Orders.driver_id

Столбец rows

Показывает количество строк, которое, по мнению MySQL, будет прочитано. Это число является приблизительным и может оказаться очень неточным. Оно вычисляется при каждой итерации плана выполнения с вложенными циклами. Часто это значение путают с количеством строк в результирующем наборе, что неверно, потому что столбец rows показывает количество строк, которые нужно будет просмотреть. При вычислении значения не учитываются буферы соединения и кеши (в том числе кеши ОС и оборудования), поэтому реальное число может быть намного меньше предполагаемого.

Столбец filtered

Показывает, какую долю от общего количества числа просмотренных строк вернет движок MySQL. Максимальное значение 100, то есть будет возвращено все 100 % просмотренных строк. Если умножить эту долю на значение в столбце rows, то получится приблизительная оценка количества строк, которые MySQL будет соединять с последующими таблицами. Например, если в строке rows 100 записей, а значение filtered 50,00 (50 %), то это число будет вычислено как 100 x 50 % = 50.

Столбец Extra

В этом столбце собрана различная дополнительная информация. Ниже я разберу все возможные значения. Наиболее важные из них будут помечены (!!!). Такие значения не всегда означают, что это плохо или хорошо, но точно стоит обратить на них внимание.

  • const row not found. Для запроса, вида SELECT FROM table, таблица table оказалась пустая.

  • Deleting all rows. Некоторые движки MySQL, такие как MyISAM, поддерживают методы быстрого удаления всех строк из таблицы. Если механизм удаления поддерживает эту оптимизацию, то значение Deleting all rows будет значением в столбце Extra.

  • Distinct. Если в запросе присутствует DISTINCT, то MySQL прекращает поиск, после нахождения первой подходящей строки.

  • FirstMatch (table_name). Если в системной переменной optimizer_switch есть значение firstmatch=on, то MySQL может использовать для подзапросов стратегию FirstMatch, которая позволяет избежать поиска дублей, как только будет найдено первое совпадение. Представим, что один и тот же водитель возил клиента с id = 10 больше, чем один раз, тогда для этого запроса:

    EXPLAIN
    SELECT id FROM Drivers
    WHERE Drivers.id IN (SELECT driver_id FROM Orders WHERE client_id = 10)

    MySQL может применить стратегию FirstMatch, поскольку нет смысла дальше искать записи для этого водителя.

id

table

extra

1

Orders

Using index;

2

Drivers

Using index; FirstMatch(Orders)

  • Full scan on NULL key. Обычно такая запись идет после Using where как запасная стратегия, если оптимизатор не смог использовать метод доступа по индексу.

  • Impossible HAVING. Условие HAVING всегда ложно.

  • Impossible WHERE. Условие WHERE всегда ложно.

  • Impossible WHERE noticed after reading const tables. MySQL просмотрел все const (и system) таблицы и заметил, что условие WHERE всегда ложно.

  • LooseScan(m..n). Стратегия сканирования индекса при группировке GROUP BY. Подробнее читайте здесь.

  • No matching min/max row. Ни одна строка не удовлетворяет условию запроса, в котором используются агрегатные функции MIN/MAX.

  • No matching rows after partition pruning. По смыслу похож на Impossible WHERE для выражения SELECT, но для запросов DELETE или UPDATE.

  • No tables used. В запросе нет FROM или есть FROM DUAL.

  • Not exists. Сервер MySQL применил алгоритм раннего завершения. То есть применена оптимизация, чтобы избежать чтения более, чем одной строки из индекса. Это эквивалентно подзапросу NOT EXISTS(), прекращение обработки текущей строки, как только найдено соответствие.

  • Plan isnt ready yet. Такое значение может появиться при использовании команды EXPLAIN FOR CONNECTION, если оптимизатор еще не завершил построение плана.

  • Range check for each record (!!!). Оптимизатор не нашел подходящего индекса, но обнаружил, что некоторые индексы могут быть использованы после того, как будут известны значения столбцов из предыдущих таблиц. В этом случае оптимизатор будет пытаться применить стратегию поиска по индексу range или index_merge.

  • Recursive.Такое значение появляется для рекурсивных (WITH) частей запроса в столбце extra.

  • Scanned N databases. Сколько таблиц INFORMATION_SCHEMA было прочитано. Значение N может быть 0, 1 или all.

  • Select tables optimized away (!!!). Встречается в запросах, содержащих агрегатные функции (но без GROUP BY). Оптимизатор смог молниеносно получить нужные данные, не обращаясь к таблице, например, из внутренних счетчиков или индекса. Это лучшее значение поля extra, которое вы можете встретить при использовании агрегатных функций.

  • Skip_open_table, Open_frm_only, Open_full_table. Для каждой таблицы, которую вы создаете, MySQL создает на диске файл .frm, описывающий структуру таблицы. Для подсистемы хранения MyISAM так же создаются файлы .MYD с данными и .MYI с индексами. В запросах к INFORMATION_SCHEMA Skip_open_table означает, что ни один из этих файлов открывать не нужно, вся информация уже доступна в словаре (data dictionary). Для Open_frm_only потребуется открыть файлы .frm. Open_full_table указывает на необходимость открытия файлов .frm, .MYD и .MYI.

  • Start temporary, End temporary. Еще одна стратегия предотвращения поиска дубликатов, которая называется DuplicateWeedout. При этом создаётся временная таблица, что будет отображено как Start temporary. Когда значения из таблицы будут прочитаны, это будет отмечено в колонке extra как End temporary. Неплохое описание читайте здесь.

  • unique row not found (!!!). Для запросов SELECT FROM table ни одна строка не удовлетворяет поиску по PRIMARY или UNIQUE KEY.

  • Using filesort (!!!). Сервер MySQL вынужден прибегнуть к внешней сортировке, вместо той, что задаётся индексом. Сортировка может быть произведена как в памяти, так и на диске, о чем EXPLAIN никак не сообщает.

  • Using index (!!!). MySQL использует покрывающий индекс, чтобы избежать доступа к таблице.

  • Using index condition (!!!). Информация считывается из индекса, чтобы затем можно было определить, следует ли читать строку целиком. Иногда стоит поменять местами условия в WHERE или прокинуть дополнительные данные в запрос с вашего бэкенда, чтобы Using index condition превратилось в Using index.

  • Using index for group-by (!!!). Похож на Using index, но для группировки GROUP BY или DISTINCT. Обращения к таблице не требуется, все данные есть в индексе.

  • Using join buffer (Block nested loop | Batched Key Access | hash join). Таблицы, получившиеся в результате объединения (JOIN), записываются в буфер соединения (Join Buffer). Затем новые таблицы соединяются уже со строками из этого буфера. Алгоритм соединения (Block nested loop | Batched Key Access | hash join) будет указан в колонке extra.

  • Using sort_union, Using union, Using intersect. Показывает алгоритм слияния, о котором я писал выше для index_merge столбца type.

  • Using temporary (!!!). Будет создана временная таблица для сортировки или группировки результатов запроса.

  • Using where (!!!). Сервер будет вынужден дополнительно фильтровать те строки, которые уже были отфильтрованы подсистемой хранения. Если вы встретили Using where в столбце extra, то стоит переписать запрос, используя другие возможные индексы.

  • Zero limit. В запросе присутствует LIMIT 0.

Команда SHOW WARNINGS

Выражение EXPLAIN предоставляет расширенную информацию, если сразу после его завершения выполнить команду SHOW WARNINGS. Тогда вы получите реконструированный запрос.

Если у вас MySQL 5.6 и ниже

SHOW WARNINGS работает только после EXPLAIN EXTENDED.

EXPLAIN SELECT              Drivers.id,              Drivers.id IN (SELECT Orders.driver_id FROM Orders)FROM Drivers;SHOW WARNINGS;

/* select#1 */ select `explain`.`Drivers`.`id` AS `id`,<in_optimizer>(`explain`.`Drivers`.`id`,<exists>(<index_lookup>(<cache>(`explain`.`Drivers`.`id`) in Orders on Orders_Drivers_id_fk))) AS `Drivers.id IN (SELECT Orders.driver_id FROM Orders)` from `explain`.`Drivers`

Отображаемое сообщение получено напрямую из плана запроса, а не из исходной SQL- команды. Сервер генерирует информацию, исходя из плана выполнения. Поэтому отображённый запрос будет иметь ту же семантику, что и исходный, но не тот же текст (в большинстве случаев).

SHOW WARNINGS содержит специальные маркеры, которые не являются допустимым SQL -выражением. Вот их список:

  • <auto_key>. Автоматически сгенерированный ключ для временной таблицы.

  • <cache> (expr). Выражение expr выполняется один раз, значение сохраняется в памяти. Если таких значений несколько, то вместо <cache> будет создана временная таблица с маркером <temporary table>.

  • <exists> (query fragment). Предикат подзапроса был преобразован в EXISTS -предикат, а сам подзапрос был преобразован таким образом, чтобы его можно было использовать совместно с EXISTS.

  • <in_optimizer> (query fragment). Внутренний объект оптимизатора, не обращаем внимания.

  • <index_lookup> (query fragment). Этот фрагмент запроса обрабатывается с помощью поиска по индексу.

  • <if> (condition, expr1, expr2). Если условие истинно, то выполняем expr1, иначе expr2.

  • <is_not_null_test> (expr). Тест для оценки того, что выражение expr не преобразуется в null.

  • <materialize> (query fragment). Подзапрос был материализован.

  • materialized-subquery.col_name. Ссылка на столбец col_name была материализована.

  • <primary_index_lookup> (query fragment). Фрагмент запроса обрабатывается с помощью индекса по первичному ключу.

  • <ref_null_helper> (expr). Внутренний объект оптимизатора, не обращаем внимания.

  • /* select # N */. SELECT относится к строке с номером id = N из результата EXPLAIN.

  • <temporary table>. Представляет собой временную таблицу, которая используется для кеширования результатов.

Читаем EXPLAIN

Учитывая всё вышесказанное, пора дать ответ на вопрос - так как же стоит правильно читать EXPLAIN?

Начинаем читать каждую строчку сверху вниз. Смотрим на колонку type. Если индекс не используется плохо (за исключением случаев, когда таблица очень маленькая или присутствует ключевое слово LIMIT). В этом случае оптимизатор намеренно предпочтет просканировать таблицу. Чем ближе значение столбца type к NULL (см. пункт о столбце type), тем лучше.

Далее стоит посмотреть на колонки rows и filtered. Чем меньше значение rows и чем больше значение filtered,- тем лучше. Однако, если значение rows слишком велико и filtered стремится к 100 % - это очень плохо.

Смотрим, какой индекс был выбран из колонки key , и сравниваем со всеми ключами из possible_keys. Если индекс не оптимальный (большая селективность), то стоит подумать, как изменить запрос или пробросить дополнительные данные в условие выборки, чтобы использовать наилучший индекс из possible_keys.

Наконец, читаем колонку Extra. Если там значение, отмеченное выше как (!!!), то, как минимум, обращаем на это вниманием. Как максимум, пытаемся разобраться, почему так. В этом нам может хорошо помочь SHOW WARNINGS.

Переходим к следующей строке и повторяем всё заново.

Если не лень, то в конце перемножаем все значения в столбце rows всех строк, чтобы грубо оценить количество просматриваемых строк.

При чтении всегда помним о том, что:

  • EXPLAIN ничего не расскажет о триггерах и функциях (в том числе определенных пользователем), участвующих в запросе.

  • EXPLAIN не работает с хранимыми процедурами.

  • EXPLAIN не расскажет об оптимизациях, которые MySQL производит уже на этапе выполнения запроса.

  • Большинство статистической информации всего лишь оценка, иногда очень неточная.

  • EXPLAIN не делает различий между некоторыми операциями, называя их одинаково. Например, filesort может означать сортировку в памяти и на диске, а временная таблица, которая создается на диске или в памяти, будет помечена как Using temporary.

  • В разных версиях MySQL EXPLAIN может выдавать совершенно разные результаты, потому что оптимизатор постоянно улучшается разработчиками, поэтому не забываем обновляться.

EXPLAIN TREE FORMAT и EXPLAIN ANALYZE

Если вы счастливый обладатель восьмой версии MySQL, то в вашем арсенале появляются очень полезные команды, которые позволяют читать план выполнения и информацию о стоимости запроса без использования SHOW WARNINGS.

С версии 8.0.16 можно вывести план выполнения в виде дерева, используя выражение FORMAT=TREE:

EXPLAIN FORMAT = TREE select * from Drivers   join Orders on Drivers.id = Orders.driver_id   join Clients on Orders.client_id = Clients.id

-> Nested loop inner join  (cost=1.05 rows=1)   -> Nested loop inner join  (cost=0.70 rows=1)       -> Index scan on Drivers using Drivers_car_id_index  (cost=0.35 rows=1)       -> Index lookup on Orders using Orders_Drivers_id_fk (driver_id=Drivers.id)  (cost=0.35 rows=1)   -> Single-row index lookup on Clients using PRIMARY (id=Orders.client_id)  (cost=0.35 rows=1)

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

Еще более подробную информацию можно получить, заменив FORMAT = TREE на выражение ANALYZE, которое предоставляет MySQL с версии 8.0.18.

EXPLAIN ANALYZE select * from Drivers   join Orders on Drivers.id = Orders.driver_id   join Clients on Orders.client_id = Clients.id

-> Nested loop inner join  (cost=1.05 rows=1) (actual time=0.152..0.152 rows=0 loops=1)   -> Nested loop inner join  (cost=0.70 rows=1) (actual time=0.123..0.123 rows=0 loops=1)       -> Index scan on Drivers using Drivers_car_id_index  (cost=0.35 rows=1) (actual time=0.094..0.094 rows=0 loops=1)       -> Index lookup on Orders using Orders_Drivers_id_fk (driver_id=Drivers.id)  (cost=0.35 rows=1) (never executed)   -> Single-row index lookup on Clients using PRIMARY (id=Orders.client_id)  (cost=0.35 rows=1) (never executed)

В дополнение к стоимости и количеству строк можно увидеть фактическое время получения первой строки и фактическое время получения всех строк, которые выводятся в формате actual time={время получения первой строки}..{время получения всех строк}. Также теперь появилось еще одно значение rows, которое указывает на фактическое количество прочитанных строк. Значение loops это количество циклов, которые будут выполнены для соединения с внешней таблицей (выше по дереву). Если не потребовалось ни одной итерации цикла, то вместо расширенной информации вы увидите значение (never executed).

Как видите, обновлять MySQL полезно не только с точки зрения производительности и улучшения оптимизатора, но и для получения новых инструментов для профилирования запросов.

Заключение

Команда EXPLAIN станет отличным оружием в вашем арсенале при работе с БД. Зная несложные правила, вы можете быстро оптимизировать ваши запросы, узнавать различную статистическую информацию, пусть и приближенную. Расширенный вывод подскажет, что было закешировано и где создаются временные таблицы.

Слишком большие запросы могут генерировать пугающие результаты EXPLAIN, но тут, как и в любом деле, важна практика. Переходите от простых запросов к сложным.

Пытайтесь, даже просто так, читать различные виды запросов, содержащие FROM, UNION и JOIN , и сами не заметите, как станете мастером оптимизации.

Литература и источники

  1. High Performance MySQL (by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko)

  2. https://dev.mysql.com/

  3. https://stackoverflow.com/

  4. http://highload.guide/

  5. https://taogenjia.com/2020/06/08/mysql-explain/

  6. https://www.eversql.com/mysql-explain-example-explaining-mysql-explain-using-stackoverflow-data/

  7. https://dba.stackexchange.com/

  8. https://mariadb.com/

  9. https://andreyex.ru/bazy-dannyx/baza-dannyx-mysql/explain-analyze-v-mysql/

  10. https://programming.vip/docs/explain-analyze-in-mysql-8.0.html

  11. А также много страниц из google.com

Подробнее..
Категории: Администрирование баз данных , Оптимизация , Backend , Базы данных , Explain , Mysql , Серверная оптимизация , Блог компании ситимобил , Серверная разработка

Категории

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

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru