Переходим к заключительной третьей части регламентного обслуживания баз данных. И сегодня акцент сделаем на обслуживании статистик в СУБД PostgreSQL. Актуальные статистики в PG важны ничуть не менее, чем в MS SQL, но разница в настройках и алгоритмах есть, соответственно, подходы будут чуть различаться.

Как обслуживают статистики в MS SQL Server

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

Изначально в SQL Server статистика обновляется автоматически, потому что по умолчанию включена опция AUTO_UPDATE_STATISTICS. И вроде бы – отлично: ничего не трогай, всё работает.

Но есть нюанс. MSSQL, начиная с SQL Server 2016 (уровня совместимости 130), решает, пора ли обновлять статистику, по формуле (в красной рамке):

Информация взята с портала MS. А в более старых версиях MS SQL алгоритм был совсем незатейливым:

• Если в таблице ≤ 500 строк — обновляет после 500 изменений.

• Если строк больше — после 500 + 20% от текущего размера.

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

Если у вас SQL Server версии до 2016 или же уровень совместимости менее 130, то автообновление статистики идет по простому алгоритму (вторая колонка таблицы выше). Например, если у вас таблица со 100 млн. строк, то автоапдейт сработает только при изменении 20 млн. строк. А, например, в каком-нибудь регистре накопления 1С за день может поменяться всего 20-50 тысяч строк – остатки за сегодняшний день. Соответственно, статистика не обновится. То есть автообновление, запускаемое по общему числу изменений, может постоянно "промахиваться" по этим критическим сегментам и оптимизатор, не видя свежего распределения, выбирает план на основе устаревшей гистограммы, например, примет решение, что данные всё ещё такие, как неделю назад. Как следствие, может выбрать не тот вид соединения таблиц, не тот порядок таблиц, не тот индекс и т.п. И вместо быстрого отчёта – подвисшая сессия, блокировки и жалобы от пользователей.

В SQL Server 2016+ алгоритм более продвинутый, но на больших таблицах тоже легко ошибается.

И, кроме того, есть другая сторона AUTO_UPDATE STATISTICS – очень ненулевая вероятность получения блокировок на самом обновлении статистик. За примером всё же отправлю к первой части цикла статей об обслуживании СУБД.

Поэтому для больших высоконагруженных баз данных мы являемся скорее адептами ручного обновления статистик, нежели автоматического. То есть можно отключить AUTO_UPDATE STATISTICS для всей БД и далее вручную использовать команду UPDATE STATISTICS.

MSSQL не имеет глобального параметра, аналогичного default_statistics_target в PG, поэтому если запускать UPDATE STATISTICS без опций, то размер выборки выбирается эвристически на основе размера таблицы – и почти всегда он меньше, чем нужно для больших и «капризных» таблиц. Если изменения незначительные в общем размере таблицы, например, последний период (а в 1С это остатки регистров), то автообновление не отработает правильно. Поэтому, по нашему мнению, лучше использовать опцию WITH FULLSCAN. Тогда статистики будут максимально точными и будет проанализирована каждая строка таблицы. Это ресурсоемко и долго для больших таблиц, но абсолютно необходимо после, например, массовых загрузок данных или закрытия месяца в 1С.

Есть еще опции SAMPLE X PERCENT и RESAMPLE, но это скорее компромисс между скоростью и точностью (если сравнивать с FULL SCAN), который по факту не даст большого выигрыша во времени, а рисков добавит.

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

Как обслуживают статистики в PostgreSQL

В PostgreSQL сбор статистик – это часть подсистемы autovacuum, что даёт важное преимущество: операция ANALYZE использует снапшот данных (MVCC) и не блокирует конкурентные операции INSERT/UPDATE/DELETE. Для 1С с её высокой конкурентностью это большой плюс.

Когда вы запускаете команду ANALYZE (вручную или автоматически с помощью автовакуума), PostgreSQL проходит по таблице и собирает статистику о распределении данных в каждом столбце. Объем собираемой статистики как раз и контролируется default_statistics_target.

Он влияет на два ключевых объекта статистики:

• Список наиболее частых значений (MCV - Most Common Values). Для столбцов с дискретными значениями (допустим, у вас есть столбец status со значениями 'Новый', 'В работе', 'Завершен') PostgreSQL хранит в статистике «N» самых часто встречающихся значений из этого столбца и частоту их появления. Где N задается как раз параметром default_statistics_target = N.

• Гистограмма (Histogram). Для столбцов с непрерывными данными (например, Период) создается гистограмма. Целевое значение определяет количество "корзин" (buckets) в этой гистограмме. Чем больше корзин, тем точнее гистограмма описывает распределение данных.

Важно!

Статистика собирается не по всей таблице, а по выборке строк. Чем выше default_statistics_target, тем больше строк нужно проанализировать для точности, и тем дольше работает ANALYZE. По умолчанию default_statistics_target = 100, а максимальное значение 10 000.

Умножая 10 000 на 300 строк на корзину, получаем потолок в 3 млн. строк для анализа. Для гигантских таблиц в 1С (какой-нибудь регистр бухгалтерии, занимающий половину объема базы) эта выборка может быть недостаточно репрезентативной. Это ключевое ограничение по сравнению с MS SQL, где WITH FULLSCAN позволяет проанализировать всю таблицу целиком.

Параметр default_statistics_target глобальный для всей базы данных (сервера), включая временные таблицы, коих в 1С очень много и часто с огромным количеством строк. Но можно устанавливать параметр точечно для отдельных таблиц и даже отдельных колонок:

ALTER TABLE _AccumRgT256 SET (autovacuum_analyze_scale_factor = 0.01, statistics_target = 2000);

ALTER TABLE _AccumRgT256 ALTER COLUMN Period SET STATISTICS 1000;

-- Сразу пересобрать статистику
ANALYZE _AccumRgT256;

Для временных таблиц статистика собирается автоматически сразу после их заполнения (INSERT) – платформа сама запускает ANALYZE и работает на основании значений всё того же глобального параметра default_statistics_target.

Когда срабатывает AUTOVACUUM ANALYZE?

PostgreSQL запускает ANALYZE, когда количество вставленных, обновлённых или удалённых строк превышает:

autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltuples

• pg_class.reltuples – это оценочное количество "живых" строк в таблице (или индексе), хранящееся в системном каталоге pg_class. PostgreSQL обновляет оценку при выполнении команд ANALYZE или VACUUM.

• autovacuum_analyze_threshold – минимальное количество "мёртвых" строк, при котором обязательно будет запущен VACUUM, даже если таблица очень маленькая. По умолчанию равен 50.

• autovacuum_analyze_scale_factor – доля от общего числа строк в таблице (reltuples), при превышении которой также срабатывает VACUUM. По умолчанию равен 0.2 (т.е. 20%).

• autovacuum_naptime – интервал времени (в секундах), с которым главный процесс автовакуума "просыпается" и проверяет, нужно ли запускать VACUUM или ANALYZE для каких-либо таблиц.

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

Порог = 50 + 0.2 * 100000 = 20 050 изменённых строк

То есть, если в таблице произошло более 20 050 изменений (INSERT/UPDATE/DELETE) с момента последнего ANALYZE, то будет запущен автоматический ANALYZE.

В целом, в PG пересчет статистик более гибкий, но имеет ограничение на максимальное количество строк к обработке ~3 млн. Настройка требует понимания специфики нагрузки. Неверные настройки могут привести к слишком частому или слишком редкому анализу, а также к слишком долгому анализу на больших таблицах, включая временные.

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

Пользователь на протяжении двух дней (24-го и 25 ноября) пытался получить отчёт, который выполнялся на сервере 6 (шесть!) часов и, о чудо, при очередной попытке на второй день он его же получил за 3,5 минуты.

Отчет связан с выполнением фактически одного запроса, и все попытки пользователя хорошо зафиксировались в трассе READS мониторинга Perfexpert:

Обратите внимание, что 25-го числа в 13:09 запрос вдруг выполнился быстро: вместо шести часов - за три с половиной минуты.

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

Из данных по таблице было выяснено что последние пересчеты статистик были зафиксированы с помощью avtovacuum analyze 19 и 25 ноября:

То есть в промежутке между 19.11.25 02:50 и 25.11.25 11:39 статистики не обновлялись.

Теперь посмотрим почему же отчет пользователя (=sql-запрос), который запускался даже же 25.11, когда пересчет статистик уже был, всё равно выполнялся долго.

Посмотрим на текст запроса, который один раз выполнился за 6 часов, а второй за 3,5 минуты. Повторюсь, разбирать планы выполнения в этой статье не будем. Просто посмотрим на участок запроса с проблемной таблицей _accrged2640, где происходит ее фильтрация по дате – 19 ноября.

Пользователь получает отчет за 19 ноября и вроде бы последний ANALYZE тоже был на тот момент 19 ноября, но только маленький нюанс – он был в 02:50 утра, т.е. рассчитанные статистики не учитывали всех изменений за этот день. Оптимизатор из-за отсутствия актуальной информации о фактическом количестве строк за указанную дату в таблице _accrged2640 неверно оценивает масштабы данных и выбирает неудачный план выполнения запроса: неправильная последовательность фильтрации и соединения таблиц (Nested Loops вместо Hash Join). Отсюда огромное количество логических чтений и не менее огромная длительность выполнения запроса.

Запрос тот же, параметры те же, но статистика к тому моменту пересчитана! И вот план запроса правильный, и время выполнения и количество логических чтений снижены на порядки.

А случилось такое потому, что автообновление 25 ноября сработало только тогда, когда количество изменений в таблице достигло 14+ млн., несмотря даже на то, что параметр autovacuum_analyze_scale_factor выставлен в достаточно маленькое значение 0,5%:

Пример еще раз подтверждает распространенные рекомендации, что после любой достаточно крупной вставки строк в таблицу (INSERT) нужно сразу же запускать ANALYZE по ней.

Итого

Ну что же, в очередной раз убеждаемся, что пересчет статистик – это must have. Сведем в табличку некоторые аспекты по пересчету статистик для двух СУБД.

Что хочется отметить. Универсальных настроек по пересчету статистик, которые подходили бы всем системам нет, т.к. нюансов у всех много. А приведенный выше пример продемонстрировал, что без качественного мониторинга понять причину происходящего очень тяжело. И в большинстве случаев крайними назначили бы разработчиков 1С, которые «опять что-то сломали, и отчёт «Любимый отчет» уже как целую неделю строится безобразно долго».

Тем не менее, попробую сформулировать подходы к регламенту пересчета статистик.

1. Общие, подходящие обеим СУБД

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

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

      

   2. Если вы загружаете данные большими пакетами – добавляете/изменяете много строк, то после этого имеет смысл принудительно пересчитать статистику по данной таблице, не дожидаясь срабатывания autoupdate / avtovacuum analyze или регламентного ночного задания.

2. Для MS SQL

   Основная проблема – риск возникновения блокировок на обновлении в течение рабочего дня. Особенно при автоматическом обновлении. Чем больше таблица, тем больше рисков возникновения блокировок на ней.

   Рекомендация. Настроить регулярное (ночное) обслуживание ключевых таблиц с UPDATE STATISTICS ... WITH FULLSCAN. Для тех таблиц, где статистики сильно устаревают в течение дня (обычно это буквально одна-две таблицы) рассмотреть возможность увеличения частоты обновления статистик.

3. Для PostgreSQL

   С одной стороны, есть "потолок" в ~3 млн. строк (statistics_target = 10000), которые пытается проанализировать PG. Строки псевдослучайны, но при этом равномерно размазаны по всей таблице, т.е. каждая строка в таблице имеет равную вероятность попасть в выборку, независимо от её позиции. На самом деле для большинства ситуаций этого объема выборки достаточно и проблемой это не является.

   Конечно, если у вас 2 млрд. строк с абсолютно уникальными значениями или среди них есть очень редкие значения, которые не попали в выборку, то да, оптимизатор будет недооценивать селективность фильтров или будет неверно оценивать число уникальных значений (n_distinct), но это очень редкий случай.

   Рекомендации:

   a)   Для гигантских таблиц (>100 млн. строк) увеличение statistics_target до 1000-5000 и даже 10000 скорее всего даст положительный эффект, даже если выборка неполная. Высокие значения глобальной настройки default_statistics_target могут повлиять на запросы с временными таблицами, т.к. если временная таблица будет большой, а такие тоже есть, то автоматический ANALYZE удлинит длительность запроса на время этого ANALYZE. Но давайте будем честны, запросы с миллионами строк во временных таблицах сами по себе не быстрые и добавление 3-5 секунд не сделает погоды в общей длительности операции.

   Поэтому для больших таблиц statistics_target лучше выставлять индивидуально, и очень аккуратно трогать глобальную настройку default_statistics_target. Точно также индивидуально стоит настроить для больших таблиц и параметр autovacuum_analyze_scale_factor (установить доли процента), чтобы autovacuum не ждал пока накопятся миллионы изменений.

   b)    Активно используйте мониторинг представления pg_stat_all_tables для отслеживания времени последнего analyze.

Ссылки на остальные части Записок оптимизатора 1С:

1. Записки оптимизатора 1С (ч.1). Странное поведение MS SQL Server 2019: длительные операции TRUNCATE

2. Записки оптимизатора 1С (ч.2). Полнотекстовый индекс или как быстро искать по подстроке

3. Записки оптимизатора 1С (ч.3). Распределенные взаимоблокировки в 1С системах

4. Записки оптимизатора 1С (ч.4). Параллелизм в 1С, настройки, ожидания CXPACKET

5. Записки оптимизатора 1С (ч.5). Ускорение RLS-запросов в 1С системах

6. Записки оптимизатора 1С (ч.6). Логические блокировки MS SQL Server в 1С: Предприятие

7. Записки оптимизатора 1С (ч.7). «Нелогичные» блокировки MS SQL для систем 1С предприятия

8. Записки оптимизатора 1С (ч.8). Нагрузка на диски сервера БД при работе с 1С. Пора ли делать апгрейд?

9. Записки оптимизатора 1С (ч.9). Влияние сетевых интерфейсов на производительность высоконагруженных ИТ-систем

10. Записки оптимизатора 1С (ч.10): Как понять, что процессор — основная боль на вашем сервере MS SQL Server?

11. Записки оптимизатора 1С (ч.11). Не всегда очевидные проблемы производительности на серверах 1С.

12. Записки оптимизатора 1С (ч.12).  СрезПоследних в 1C:Предприятие на PostgreSQL. Почему же так долго?

13. Записки оптимизатора 1С (ч.13). Что не так в журнале регистрации 1С в формате SQLitе?

14. Записки оптимизатора 1С (ч.14.1). Любите свою базу данных и не забывайте обслуживать

15. Записки оптимизатора 1С (ч.14.2). Пересчет индексов на SSD-дисках. Делаем или игнорируем?

16. Записки оптимизатора 1С (ч.14.3). Отличия в обслуживании статистик в MS SQL и в PostgreSQL