Сказ о том, как online_analyze INSERT "удлинял"

10.02.20

База данных - Статистика базы данных

Немного о тонкостях работы модуля online_analyze для PostgreSQL. Опус для тех, у кого, как и у меня, не всегда хватает времени на то, чтобы разобраться, как это работает, и поэтому бывает так, что следуешь рекомендациям из сети и пользуешься методом "копипаста", пока не прижмет.

В тридевятой СУБД, в тридесятой PostgreSQL сущестовала одна таблица: ни большая, ни маленькая, - на каких-то несколько миллионов строк.

Приходили к таблице этой хлопцы разные, с именем заморским INSERT, подавали челобитные свои с прошением поселить в таблице строки новые.

И жили себе все счастливо до той поры, пока не явился админушко окоянный, который в разных бывал СУБД, да много каких "сводов правил" для PostgreSQL видовал. Так вот решил он и в этой СУБД установить нововведения "полезные", - пристройку сгородил online_analyze зовущуюся. Беда одна, - невнимательный он был, да то что к пристройке этой свой "свод правил" полагается, не учел ...

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

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

Чтож, отбросим лирические отступления, перейдем к фактическому материалу!

Как воиспроизвести? Для экспериментов необходимо найти таблицу в которой команда analyze выполняется весомое количество секунд!

ВНИМАНИЕ: Предполагается, что все опыты вы ставите на тестовой среде! Вся ответственность за опыты на продуктивной среде целиком и полностью лежит на вас!

Ниже приводятся команды, выполненные в psql

Если у вы еще не пользуетесь online_analyze, т.е этот модуль не загружен через конфигурационный файл postgresql.conf, то вам в текущей сессии необходимо выполнить команду

db_test=# load 'online_analyze';
LOAD

Установим ряд параметров для модуля

db_test=# set "online_analyze.enable" = on;
SET
db_test=# set "online_analyze.verbose" = on;
SET
db_test=# set "online_analyze.table_type" = 'all';
SET

Откроем явную транзакцию

db_test=# begin transaction;
BEGIN

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

db_test=# explain (analyze,buffers) INSERT INTO _AccRg29166 (...) VALUES(...);
ИНФОРМАЦИЯ:  анализируется "public._accrg29166"
ИНФОРМАЦИЯ:  "_accrg29166": просканировано страниц: 150000 из 164949, они содержат "живых" строк: 2592878, "мёртвых" строк: 20; строк в выборке: 150000, примерное общее число строк: 2851284
ИНФОРМАЦИЯ:  analyze "_accrg29166" took 12.00 seconds
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Insert on _accrg29166  (cost=0.00..0.01 rows=1 width=1069) (actual time=0.194..0.194 rows=0 loops=1)
   Buffers: shared hit=32 dirtied=6
   ->  Result  (cost=0.00..0.01 rows=1 width=1069) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.072 ms
 Execution time: 12014.868 ms
(5 строк)

Что мы здесь видим? Что у нас 12 секунд от времени выполнения запроса insert "съедено" выполнением analyze, в то время как сама вставка выполнялась 0.194 мс!

Делаем откат транзакции

db_test=# rollback transaction;
ROLLBACK

Отключаем online_analyze и вопроизводим предыдущие действия

db_test=# set "online_analyze.enable" = off;
db_test=# begin transaction; explain (buffers,analyze) INSERT INTO _AccRg29166 (...) VALUES(...); rollback transaction;
BEGIN
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Insert on _accrg29166  (cost=0.00..0.01 rows=1 width=1069) (actual time=0.196..0.196 rows=0 loops=1)
   Buffers: shared hit=32 dirtied=6
   ->  Result  (cost=0.00..0.01 rows=1 width=1069) (actual time=0.002..0.002 rows=1 loops=1)
 Planning time: 0.072 ms
 Execution time: 0.325 ms
(5 строк)

ROLLBACK

И имеем вполне вменяемое время выполнения!

Какие вопросы родились из всего этого и какие ответы получены от автора online_analyze (Федора Сигаева):

1. То, что запрос ожидает завершения процесса обновления статистки - это "проектное" поведение, т.е. так и планировалось или может быть это неизбежное явление?

Да. Оно так было задумано.

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

Когда оно делалось, тогда background worker не было. Сейчас можно, но пока в плане нет.

3. То что при откате транзакции статистика остается неизменной и это может приводить к сильно "кривой" статистике - это является ошибкой или допустимым явлением?

Скорее допустимым. По трем причинам:
- "оптимистичесоке" поведение. Откатываются транзакции значительно реже, чем соммитятся
- все-таки расчет на то, что одиночный инсент/апдейт/делете меняет не так много записей
- откат транзакции в постгресе не значит немедленного удаления записей, они будут удалены вакуумом. А значит как минимум индекс сканам проверяться будут.
Поэтому мертные записи тоже должны учитываться статистикой.

А какие у вас возникают вопросы/мысли исходя из приведенной выше информации?

Что до меня, так после разбора возникшей ситуации модуль online_analyze и его настройки "заиграли" новыми красками! ;)

Надеюсь, данная информация окажется полезной ...

postgresql online_analyze insert длительный

См. также

Администрирование СУБД Системный администратор Программист

В крупных компаниях, где много типовых и сильно доработанных баз с режимом работы 24/7, переход с MS SQL на PostgreSQL затягивается. Получается гетерогенная структура – когда прод уже на PostgreSQL, а разработка и тестирование – пока на MS SQL. О том, какие варианты помогут постепенно перевести прод с несколькими базами MS SQL на PostgreSQL, не сломав среду тестирования и разработки, пойдет речь в статье.

21.11.2024    3177    a.doroshkevich    7    

15

HighLoad оптимизация Администрирование СУБД Системный администратор Программист Платформа 1С v8.3 Россия Бесплатно (free)

Мы исследуем проблему долгого выполнения запросов PostgreSQL при использовании конструкции VALUES: когда она возникает, как на нее можно повлиять, а главное, почему ее продуманная отработка важна для более быстрого функционирования решений на базе 1С

12.11.2024    1078    Tantor    20    

15

HighLoad оптимизация Администрирование СУБД Механизмы платформы 1С Программист Платформа 1С v8.3 ИТ-компания Россия Бесплатно (free)

В данной статье мы рассмотрим, как работает механизм временных таблиц на postgres на платформе 8.3.23 и что изменилось в нем при добавлении новых возможностей в платформе 8.3.25. А также на примере покажу, как понимание работы платформы позволяет оптимизировать СУБД для работы с 1С.

29.10.2024    3691    Tantor    38    

35

Администрирование СУБД Системный администратор Программист Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ и решение ошибок СУБД. Во время реиндексации базы Ошибка СУБД: Microsoft SQL Server Native Client 11.0: Не удалось найти объект "ИмяБазы.dbo._RefSInf21806", так как он не существует, или отсутствуют разрешения. Во время проверки целостности Ошибка СУБД: Microsoft SQL Server Native Client 11.0: Недопустимое имя объекта "dbo._RefSInf21806".

19.09.2024    4782    Xershi    10    

17

Статистика базы данных Программист Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

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

1 стартмани

11.09.2024    1139    16    m_kislyak    3    

9

Администрирование СУБД Программист Платформа 1С v8.3 Конфигурации 1cv8 Россия Бесплатно (free)

В статье описала свой опыт аудита 1С базы, порядок действий + статьи, которые сильно помогли в работе.

14.07.2024    8391    limonen    16    

19
Комментарии
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
1. KAV2 157 31.08.20 08:24 Сейчас в теме
Я так понимаю фирма 1С рекомендует использовать этот модуль только для временных таблиц, то есть online_analyze.table_type = 'temporary'

В этом случае обновление статистики будет применено только к временным таблицам.
2. Sloth 393 31.08.20 09:43 Сейчас в теме
(1) Все верно!
Однако если включить online_analyze, а тип таблиц при этом забыть указать, то можно получить "неожиданный" результат ...
Собственно об этом и опус
Оставьте свое сообщение