- PVSM.RU - https://www.pvsm.ru -

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков

Друзья, мы продолжаем публиковать транскрипции наиболее интересных технических докладов прошлых конференций PG Day Russia. Сегодня вашему вниманию предлагается доклад [1] Алексея Ермакова, специалиста компании Data Egret, посвященный устройству и функционированию планировщика.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 1

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

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

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

Доклад состоит из двух частей:

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 2

Первая часть очень теоретическая. Посмотрим, как работает планировщик, как собирается статистика и зачем, немножко вспомним теорию вероятности.

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 3

Как выполняется запрос в PostgreSQL? Сначала приложение устанавливает соединение с PostgreSQL, передает тело запроса напрямую, используя connection pool (pgbouncer и т.п.). Дальше этот запрос парсится на токены и разбивается на части. Строится дерево, передается в rewrite system, и дальше самая интересная часть — planner / optimizer. В этом месте у нас генерируется план запроса.

Планировщик один и тот же запрос может выполнить кучей разных способов, причем, чем больше у нас таблиц, тем больше способов у нас будет получаться, эта зависимость растет как факториал. Пять таблиц — это, допустим, какая-то константа 120, 10! — это уже будет очень большое число. То есть у нас получается очень много планов, если у нас очень много таблиц, поэтому не стоит делать запросы с 20-тью join-ами, которые человек-то не может нормально прочитать, а планировщик тем более будет тупить на этом моменте, и не сможет выбрать самый оптимальный план.

Итак, получили план, далее отправляем это на executor, который план пошагово выполняет и потом генерирует результат клиенту.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 4

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 5

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 6

Посмотрим что получилось: команда d показывает информацию об объекте в psql. Видим много интересного: какие индексы, какие поля, какие атрибуты, в общем, все что ожидалось.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 7

Теперь заполним эту таблицу тестовыми данными. Поле категории id будет числами от 0 до 99, которые встречаются с одинаковой вероятностью. Поле content будет словом «hello world», и в конце мы добавим к нему числовой индикатор id. А рейтинг у нас будет нормальным распределением с математическим ожиданием 50 и стандартным отклонением в 10. Сгенерируем 10 000 таких записей.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 8

Вот что получилось. Рейтинг — это число около 50-ти, category_id — целые числа и поле content.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 9

Посмотрим план выполнения запроса COUNT. Очевидно, что тут sequence scan, некие чтения. То, что нас интересует — это число строк (10 000). Откуда планировщик узнал, что у нас 10 000 строк? Только что создали таблицу, заполнили ее, а планировщик уже знает.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 10

Есть системная таблица pg_class, в которой хранится информация о каждой таблице. В ней есть два таких выгодных поля: reltuples (число строк в этой таблице) и relpages (число страниц, которые занимает данная таблица). Эти числа не актуальные, они записываются в трех случаях:

  • по таблице прошелся процесс auto-vacuum, прочитал таблицу и обновил поля;
  • по таблице прошелся процесс auto-analyze, прочитал всю или часть таблицы и обновил эти же поля;
  • мы создали индекс или провели еще какую-то DDL операцию по таблице, в этом случае тоже могут обновиться все эти поля.

Вроде бы хорошо, что у нас есть некие значения, но они могут быть не актуальные. Что в этом случае делать? Нам хотелось бы актуальные значения иметь. Планировщик в посгресе не может быстро посчитать число строк в таблице (потому что ему нужно их все считать), но он может быстро посчитать число страниц, которые занимает данная таблица. И, предполагая, что число строк на странице почти не изменилось после auto-vacuum или analyze, с помощью такой пропорции можно оценить число строк довольно-таки точно. Почему 10 000? После того как я записал данные, прошел процесс auto-analyze (это можно посмотреть в таблице pg_stat_user_tables, в ней хранятся всякие счетчики мониторинга).

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 11

Почему вообще запустился процесс auto-analyze, и чем он занимается? Запускается он, если сумма вставленных, обновленных и удаленных строк превышает некое пороговое значение. Пороговое значение рассчитывается на основе двух параметров:

  • фиктивная константа, которая задается в config или для конкретной таблицы через ALTER TABLE (autovacuum_analyze_threshold, фиксированная часть, по умолчании она равна 50-ти);
  • параметр autovacuum_analyze_scale_factor: доля строк в таблице, при изменении которых запустится analyze (по умолчанию, это 0.1 или 10%).

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

Еще параметр, который есть у auto-analyze — это default_statistics_target. Он указывает, сколько страниц и строк мы возьмем из таблицы, и еще влияет на ряд других параметров, про которые речь пойдет позже. По умолчанию этот параметр равен 100. Что делает auto-analyze? Он читает 300 * stats_target страниц, и из этих страниц он выбирает 300 * stats_target строк. Всего по-умолчанию он прочитает 30 000 строк из таблицы и на основе этих данных заполнит таблицу pg_statistic.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 12

Эту таблицу читать не очень удобно, поэтому придумали view pg_stats, которое содержит то же самое, но в более читабельном формате. Что у нас тут есть? Одна строка этого вью представляет информацию об одном поле в таблице. Есть поле tablename, attname (имя таблицы, имя поля), и дальше начинается самое интересное:

  • null_frac говорит нам как процент строк для этой таблицы является null;
  • avg_width — средняя длина поля в таблице (для полей фиксированной длины это поле нам ничего не скажет);
  • n_distinct показывает, сколько в этом поле различных значений;
  • массивы most_common_vals и most_common_freqs содержат самые часто встречающиеся значения поля и соответствующие им частоты;
  • histogram_bounds — массив интервалов, причем вероятность попасть в каждый интервал примерно одинаковая.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 13

Мы создали таблицы, посмотрим, что записалось после analyze для поля ID. Мы видим, что null_frac равен нулю, т.е. все значения в таблице – NOT NULL, потому что это primary key, а он всегда NOT NULL. Средняя длина – 4 (integer), n_distinct равен -1. Почему -1, а не десять тысяч?

n_distinct может быть как больше нуля, так и меньше нуля. Если больше нуля, то он показывает число различных значений, а если меньше нуля, то показывает долю от числа строк, которые будут принимать различные значения. То есть, допустим, если было бы -0.5, то это означало бы, что половина строк у нас содержит уникальное значение, а вторая половина содержит их дубликаты. В данном случае он равен -1, то есть все значения разные.

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

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 14

Как подсчитывается селективность разных условий? Запрос SELECT COUNT(*), где ID < 250. Выбирается план Index Only Scan, и видим, что извлекается 250 строк. Почему 250? Так как у нас в массиве most_common_vals и most_common_freqs нет каких-то значений, которые встречаются чаще, чем другие, планировщик берет информацию из histogram_bounds. Еще раз повторюсь, что вероятность попасть в каждый интервал примерно одинаковая. По такому критерию он и создавался. Планировщик смотрит, сколько интервалов попадает под данное условие. Под него попадает первый интервал (от 1 до 100), второй интервал (от 100 до 200) и половина третьего. То есть два с половиной интервала. А всего таких интервалов 100, и, таким образом, поделив 2.5 на 100, мы получаем selectivity 2.5%. Это процент строк, которые удовлетворяют данному условию. Число 100 получается как selectivity, умноженное на cardinality. Cardinality – это общее число строк, я уже говорил, как оно считается. Получаем 2.5% от 10 тыс., то есть 250 строк.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 15

Теперь такой пример. Поле CategoryID: для него n_distinct равен 100, то есть в это поле мы записывали числа от нуля до 99, которые распределены равномерно. Тут мы видим, что histogram_bounds пустой, но зато полностью заполнены массивы most_common_vals и most_common_freqs. В них числа отсортированы по частоте, то есть самое популярное значение в этой таблице – это 98, которое встречается в 1.21% случаев. Корреляция здесь на уровне 0, то есть все данные здесь равномерно перемешаны на диске, нет такого, что 1 хранится в начале таблицы, а 99 – в конце таблицы. Всего значений у нас 100, и default_statistics_target у нас тоже равен 100, поэтому все значения у нас сюда поместились. Если бы не поместилось, что будет в следующем примере, то там будет по-другому.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 16

Как в таком случае рассчитывается selectivity для условия CategoryID = 98? Просто дается значение из most_common_vals. Если у нас такое значение есть, то просто берем соответствующую частоту и считаем ее равной selectivity. В данном случае у нас 121 строка выбирается.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 17

Попробуем теперь поменять default_statistics_target для этого поля. Поставим его 10, хотя по умолчанию у нас было 100. Сделаем analyze. Команда d+ показывает, кроме всего прочего, еще и значение stats_target, если мы его меняли. Вот, что у нас получилось.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 18

Теперь у нас most_common_vals и most_common_freqs пустые, но зато теперь у нас есть поле histogram_bounds. Как в этом случае рассчитывается selectivity?

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 19

По такой формуле. То есть значение у нас может быть либо null, либо most_common_vals, либо histogram_bounds. В числителе есть вероятность того, что значение будет histogram_bounds, а в знаменателе у нас число различных значений, кроме тех, что уже есть в most_common_vals. sumcommon – это сумма всех значений из массива most_common_freqs. Неочевидная формула, но если разобраться, понятно, почему это так работает. Различные значения распределены равномерно по массиву histogram_bounds. Хотя у нас получается selectivity 1%, и это уже другая оценка, в сто строк вместо 121. Но 100 или 121 – в данном случае это не проблема, даже если у нас значение отличается на полпорядка, то это бывает хорошая оценка.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 20

Посмотрим теперь, когда у нас есть два условия: CategoryID = 98 и id < 250, то есть комбинируем два условия. Selectivity здесь считается как произведение selectivity, то есть по формуле из теории вероятности: вероятность того, что произойдет два события одновременно, равна вероятности того, что произойдет первое событие, помноженная на вероятность того, что произойдет второе событие. Это определение статистической зависимости, то есть полагается, что эти колонки никак друг от друга не зависят. В данном случае так и есть, что они не зависят друг от друга. Получается справедливая оценка, 2.5 строки. Дробную часть мы отбрасываем и получаем 2 строки. А реально, 2.5 строки — тоже хорошая оценка.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 21

В качестве демонстрации посмотрим, как выглядит неравномерное распределение в поле rating. Тут заполнены и most_common_vals, и most_common_freqs, и даже гистограмма есть. Этого должно быть достаточно, чтобы понять, что здесь данные неравномерны. Почему так? Если бы они были равномерно распределены, то вероятность для каждого значения была бы одна 1/72, то есть около 1%. Мы видим, что есть значения с вероятностью около 4%. Заметно неравномерное распределение.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 22

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

Допустим, вы создали индекс, который хранит значение rating в квадрате. Конечно, пользы никакой от этого нет, только в качестве демонстрации, чтобы понять. Если мы сравним это с предыдущим, то увидим, что значение просто возводится в квадрат. То есть если оно было 50, то стало 2500. И histogram_bounds, соответственно, тоже будет возводиться в квадрат.

Еще есть такая недокументированная штука, как поменять statistics_target для функционального индекса: ALTER INDEX ALTER COLUMN … set statistics — этого почему-то тоже нет в документации ALTER INDEX.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 23

До этого были примеры, где мы сравнивали число с какой-то константой, CategoryID у нас равно 98. А если у нас не константа, а подзапрос или результат какой либо функции, что тогда с этим делать? Планировщик предполагает, что есть какие-то default estimators, то есть просто берет значение selectivity в виде константы. Вероятность того, что поле равно чему-то, скорее всего, где-то 0.005%. А вероятность того, что что-то меньше чего-то будет где-то 33%. Когда мы сравниваем одну колонку с другой, такое тоже может быть.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 24

В качестве примера: ID < Select 100. Информации никакой нет, поэтому число строк оценивается как одна треть, то есть selectivity = 0.33(3).

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 25

Все, о чем я говорю, можно посмотреть и руками, запросами, не ходя в pg_stats. Число строк мы смотрим как SELECT COUNT(*), число различных строк — как SELECT COUNT(DISTINCT *).

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 26

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 27

SELECT COUNT(*) по таблице в несколько гигабайт — это уже очень медленно, а если еще делать группировки — то совсем медленно. Если нам требуется не одно поле, а несколько, стоит посмотреть в pg_stats. Более удобный формат и больше информации.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 28

Много полезной информации есть в pg_stats, stats_target можно менять для отдельных колонок в таблицах, настройки autovacuum и autoanalyze можно менять для отдельных таблиц, и тут проявляется статистическая независимость условий. Если у нас несколько условий, то они считаются независимыми, потому что никакой другой информации нет.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 29

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 30

Есть полезный прием: таблица foo, 73GB, очень большая, число строк можно посмотреть в reltuples. Оказывается, что в ней 73 млн. строк.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 31

У нас есть запрос: SELECT * FROM foo WHERE bar_id = 183, нужно отобрать из них последние 20 записей. Если бы у нас была таблица posts, требовалось бы отобрать 20 новостей определенной категории, например. Очень типичный запрос. У поля n_distinct = 50, то есть 50 различных значений, а всего строк у нас 70 млн. И мы видим, что число 20 встречается в 55% случаев. То есть распределение крайне неравномерное. Можно поставить индекс на (bar_id, id), он будет тут работать, но таблица у нас на 73 ГБ. Получается, что этот индекс вполне может занимать 5-7 ГБ. Не очень хочется такой индекс создавать.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 32

Если мы посмотрим, то на 4 значения приходится 88% записей. Такая мысль возникает: «А может, мне их выкинуть из индекса? Зачем они там нужны? У нас будет индекс в 10 раз меньше».

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 33

Возьмем и выкинем частично из индекса эти четыре значения bar_id. Размер индекса стал 758 МБ. Для всех значений, которые не входят в эти 4, он у нас будет работать, и все будет очень быстро выполняться.

Вопрос из зала: что если, параметры для исключения bar_id изменятся?

Если мы знаем, что у нас есть такие-то очень частые значения, то мы можем “захардкодить”. А если такого не бывает, что сегодня у нас распределение такое, а завтра оно обратное, то это не подходит очевидно. Это подходит именно для этого случая, когда мы знаем, что у нас таблица медленно растет, и если что-то не будет работать, то мы это уже заметим. То есть мы увидим, что индекс будет расти.

Так вот, индекс работает для всех значений, кроме четырех. Оказывается, что он нам не нужен. Если у нас значение 20 встречается в каждой второй строке, то мы просто берем индекс по primary key, считываем последние 40 строк и, скорее всего, 20 из них будут теми, которые нам нужны. А в худшем случае мы считаем не 40 строк, а 200 или даже 2000. Но это ерунда, 2000 строк считать по индексу – не проблема. Таким образом, мы этот случай разобрали.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 34

Более такой абстрактный случай. Допустим, у нас есть условия: a равно некому значению и b равно некому значению, и мы думаем, как нужно создать индекс? Можно создать индекс по полю a, по полю b. Можно создать два индекса или индекс по полям a и b одновременно, или в обратном порядке. Можно создать частичный индекс по a, где b равно чему-то. А, может, нам индекс вообще не нужен. Как можно понять, какой индекс нужен на этот запрос?

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 35

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

Если какое-то условие с небольшим selectivity (в данном случае, это процент строк, который удовлетворяет данному условию), то мы его берем и ставим индекс. Если есть другое условие с небольшим selectivity, мы можем поставить оба индекса, если нам не хватает одного поля. А вот если selectivity большое (поле типа boolean, которое в 90% случаев равно false), то индекс нам, скорей всего, не нужен по нему. А если и нужен, то мы будем делать некий частичный индекс, скорей всего, чтобы эти 90% в индексе не тащить, пользы от них никакой не будет.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 36

Какие есть вообще грабли с планировщиком…

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 37

Планировщик не всегда работает идеально. Например, у нас нет cross column статистики. Информацию о зависимости одного поля от другого мы никак не храним. В качестве примера, рассмотрим content < ‘hello world 250’. Условие не очень приятное. У нас выбирается 1600 строк, sequential scan получается. А если мы сделаем два условия: одно из них то же самое, а второе – id < 250, то оценка отличается на полпорядка: 142 строки против 1697 строк. Это еще не очень плохо. Если бы у нас было не два условия, а три, которые зависят друг от друга, или selectivity было поменьше, то оценка может отличаться на много порядков.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 38

Вот такая проблема, которую сложно обойти хорошим способом. Можно как-то “захачить”, использовать информацию о том, что есть default estimator, который нам возвратит оценку в 1/3 от всех строк и вот такое вот вкрутить. Не рекомендуется так делать постоянно, но иногда так можно сделать, если больше ничего сделать нельзя.

Тут берется поле id и добавляется к нему по abs(id) < 0. Что это означает? Это условие заведомо ложное, abs – это модуль числа, а модуль числа не может быть меньше нуля. Это условие всегда false, оно не влияет на результат, но оно влияет на план. Оценка тут увеличится примерно на одну треть для этого условия, и план станет чуть получше. Можно хаки такие комбинировать, использовать знание о default estimator’ах. Наверное, можно придумать и получше, но в данном случае более-менее нормально получилось.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 39

Следующая проблема: нет статистики по JSON полям. В таблице pg_stats никакой информации по этому полю нет. Если с этим полем у нас какое-то условие, где оно NULL, или по нему делается JOIN, планировщик ничего об этом не знает, план может быть не совсем оптимальный. Можно использовать поле JSONB, там все есть.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 40

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 41

Но, если мы создадим extension intarray и выполним тот же самый запрос, то окажется, что используется default estimator и он выбирает у нас 0.1%. Иногда на это можно наступить, если используются такие операторы и есть intarray, то оценка может быть не очень хорошей.

Федор Сигаев, из зала: я прокомментирую, можно? На этой неделе был “закоммичен” патч, который лечит эту проблему, но, к сожалению, это будет только в 9.6.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 42

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 43

Следующая проблема – это когда у нас не хватает statistics_target. Допустим, бывают большие таблицы, в которых хранится небольшое число различных элементов. В таком случае, оценка какого-нибудь редкого элемента (которого может даже и не быть) может отличаться на несколько порядков. Я уже об этом говорил про эту формулу, она считает, что все оставшиеся значения распределены равномерно, но обычно это не так. В данном случае, мы можем выкрутить статистику до 10000 или до 1000. Есть артефакты, конечно. Analyze по этой таблице будет медленнее работать и план будет строиться чуть медленнее, но иногда такое приходится делать. Например, когда join идет по нескольким таблицам и не хватает статистики.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 44

Последняя часть – про мониторинг. Я говорил про статистику по распределениям (собирается процессом auto analyze). Кроме того, процессом auto collector собирается статистика по всяким счетчикам. Тут довольно много интересного, рекомендую заглянуть во все “вьюшки” pg_stat_*. Про часть из них расскажу.

pg_stat_user_tables – я про него уже говорил, одна строка представляет собой информацию по одной таблице. Что в ней есть интересного? Сколько по этой таблице было sequence scan’ов, сколько эти сканы выбрали строк, сколько было index scan’ов, сколько index scan’ы выбрали строк, когда был последний autovacuum и autoanalyze, сколько их было всего. Также есть число вставленных записей, число удаленных записей, число живых записей. Кажется, зачем это нужно? Допустим, если у какой-то таблицы большое число строк, которые извлечены, используя sequence scan, и сама таблица тоже большая, то, скорей всего, ей не хватает какого-то индекса. У нас есть в репозитории такой запрос, который показывает TOP таблиц, по которым у нас больше всего сканов, и можно понять, где там добавить индекс.

pg_stat_user_indexes – здесь хранится информация по отдельным индексам. Можно понять, допустим, сколько раз данный индекс использовался. Полезно, если мы хотим понять, используется созданный нами индекс, или мы хотим какие-то лишние индексы взять и удалить.

pg_stat_user_functions – информация по вызовам функций. Для каждой функции есть поля, которые показывают, сколько раз она вызывалась, сколько времени это заняло. Полезно знать, если у нас много используется функций.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 45

Это статистика stat collector’а с момента старта, и она не обнуляется сама по себе, даже при рестарте. Ее можно сбросить командой pg_stat_reset(). Она просто сбросит все счетчики. Проблем от этого, скорей всего, никаких не будет, но можно посмотреть, как поменялась картина после того, как вы что-то поменяли.

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

track_functions можно включить для того, чтобы собиралась статистика по вызовам функций pg_stat_user_functions. Еще такой случай, если у нас очень много таблиц или очень много баз, то статистики может быть очень много, и stats collector пишет ее прямо в файл, это постоянная дисковая нагрузка на запись не очень полезная. Мы можем вынести stats_temp_directory на RAM-диск, и тогда будет писаться в память. При выключении посгреса просто запишется в эту директорию. Если аварийное какое-то выключение, мы потеряем статистику, но ничего страшного из-за этого не будет.

Поле track_activity_query_size говорит, какой длины запросы мы собираем в pg_stat_activity и pg_stat_statements. По умолчанию собирается 1024 байта от запроса, иногда стоит его немножко поднять, если у нас длинные запросы, чтобы их можно было целиком прочитать.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 46

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 47

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

Всего у нас запросы выполнялись 50 часов, из них времени на I/O ушло 0.6%. Самый первый запрос занял больше всего времени, на него ушло 28% времени процесса от времени всего выполнения в посгресе. Много всякой полезной информации, очень удобный. У нас лежит в репозитории, он называется query_stat_total.sql, ссылка дальше будет.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 48

Вот такими параметрами это регулируется. Немного параметров, можно почитать про них документацию, ничего сложного нет. Иногда стоит track_utility выключать, некоторые системные команды (всякие ORM или в Java, например) любят дописывать в запрос deallocate чего-то там, и deallocate будет каждый раз иметь какой-то новый идентификатор, и вот он будет затирать значения, которые у нас есть в pg_stat_statements. Он отслеживает только ТОП запросов, по умолчанию это одна тысяча запросов (можно побольше поднять значение), но если эти запросы у нас на 90% будут deallocate какой-то идентификатор, то мы какую-то информацию потеряем. В таком случае, стоит выключить track_utility.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 49

Заключение: есть статистика по распределениям, статистика по использованию ресурсов, записывается различными процессами, используется тоже по-разному. Статистика по распределениям используется планировщиком, а статистика по ресурсам – вряд ли чем-то используется, больше для мониторинга информация.

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

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 50

Некоторые полезные ссылки на мануал, про оценку числа строк планировщиком, statistics collector, цикл статей depesz про то, как работает планировщик (пять статей, и в каждой рассматривается, как что работает). Ссылка на наш репозиторий и ссылка на наш блог.

Использование статистики в PostgreSQL для оптимизации производительности — Алексей Ермаков - 51

Понравился доклад Алексея? Приезжайте к нам на PG Day'17 Russia [2]! Алексей будет читать познавательный материал про особенности устройства планировщика PostgreSQL [3]. Приглашаем всех, кто хочет узнать подробно, как работает планирование запросов в Посгресе. Какими способами можно воздействовать на процесс построения плана выполнения запроса? В каких случаях надо пытаться заставить планировщик работать корректно вручную? Приходите [2], Алексей все расскажет :-)

Автор: rdruzyagin

Источник [4]


Сайт-источник PVSM.RU: https://www.pvsm.ru

Путь до страницы источника: https://www.pvsm.ru/postgresql/256486

Ссылки в тексте:

[1] доклад: https://pgday.ru/ru/2015/papers/17

[2] PG Day'17 Russia: http://pgday.ru/ru/2017/request/registration?utm_source=habr&utm_medium=post&utm_campaign=ermakov

[3] особенности устройства планировщика PostgreSQL: http://pgday.ru/ru/2017/papers/170?utm_source=habr&utm_medium=post&utm_campaign=ermakov

[4] Источник: https://habrahabr.ru/post/329542/