Информация

Дата основания
Местоположение
Россия
Сайт
sbis.ru
Численность
1 001–5 000 человек
Дата регистрации

Блог на Хабре

Обновить
126,65
Рейтинг
Тензор
Разработчик системы СБИС

PostgreSQL Antipatterns: статистика всему голова

Блог компании ТензорPostgreSQLSQLАдминистрирование баз данных
Для выбора наиболее эффективного плана выполнения запроса PostgreSQL пользуется накопленной статистикой о распределении значений данных в целевых таблицах.

Она обновляется с помощью явного запуска команд ANALYZE и VACUUM ANALYZE или в фоновом режиме процессом autovacuum/autoanalyze. Но если статистика не успеет актуализироваться — может произойти беда.

Как такую проблему обнаружить и исправить?

Основной вариант, когда такая ситуация вообще может произойти, — если в таблице резко изменился набор данных. То есть по ней прогнали большое количество INSERT/UPDATE/DELETE или просто «влили» данные в пустую таблицу — например, при восстановлении из резервной копии.

В справке по штатной утилите восстановления pg_restore даже явно сказано:
После восстановления имеет смысл запустить ANALYZE для каждой восстановленной таблицы, чтобы оптимизатор получил актуальную статистику.
Поэтому если вы делаете с базой что-то похожее — не поленитесь, сразу прогоните ANALYZE по наиболее «жирным» таблицам или по всей базе целиком.

Определяем наличие проблемы


Как выглядит ситуация «все плохо» именно из-за этого? Обычно примерно вот так:


В столбце ratio как раз показывается отношение «в разах» между планировавшимся на основании статистики и фактически прочитанным количеством записей:

Bitmap Heap Scan on ... (... rows=14831 ...) (actual ... rows=9 ...)

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

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

Вот так выглядит загрузка CPU на сервере базы до и после этой операции для примера выше:



Часто обновляемая таблица


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

В этом случае нам помогут следующие конфигурационные параметры:
autovacuum_naptime (integer)
Задаёт минимальную задержку между двумя запусками автоочистки для отдельной базы данных. Демон автоочистки проверяет базу данных через заданный интервал времени и выдаёт команды VACUUM и ANALYZE, когда это требуется для таблиц этой базы. Если это значение задаётся без единиц измерения, оно считается заданным в секундах. По умолчанию задержка равна одной минуте (1min). Этот параметр можно задать только в postgresql.conf или в командной строке при запуске сервера.

autovacuum_analyze_threshold (integer)
Задаёт минимальное число добавленных, изменённых или удалённых кортежей, при котором будет выполняться ANALYZE для отдельно взятой таблицы. Значение по умолчанию — 50 кортежей. Этот параметр можно задать только в postgresql.conf или в командной строке при запуске сервера. Однако данное значение можно переопределить для избранных таблиц, изменив их параметры хранения.

autovacuum_analyze_scale_factor (floating point)
Задаёт процент от размера таблицы, который будет добавляться к autovacuum_analyze_threshold при выборе порога срабатывания команды ANALYZE. Значение по умолчанию — 0.1 (10% от размера таблицы). Задать этот параметр можно только в postgresql.conf или в командной строке при запуске сервера. Однако данное значение можно переопределить для избранных таблиц, изменив их параметры хранения.

ССЗБ


Иногда при настройке сервера autovacuum_naptime «удавливают» до «раз в сутки» (1d), чтобы autoVACUUM'ы ходили по базе пореже и ресурсов ели поменьше.

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

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

Как раз в этом случае вас и будут ждать проблемы с часто изменяемой таблицей.

Тут — или все-таки выставлять более адекватный интервал запуска, или гонять ANALYZE по такой таблице в «ручном» режиме по каким-то прикладным соображениям (например, по внешнему таймеру или после окончания очередного этапа обработки очереди).

Товарищ, бди актуальность статистики!
Теги:визуализация данныхбазы данныхdbaexplainexplain.tensor.rusql antipatternsanalyze
Хабы: Блог компании Тензор PostgreSQL SQL Администрирование баз данных
Рейтинг +10
Количество просмотров 5,2k Добавить в закладки 53
Комментарии
Комментарии 1

Похожие публикации

Product Owner
от 2 700 до 4 700 $ТензорМожно удаленно

Лучшие публикации за сутки