Комментарии 14
Видно, что наибольшее количество времени процессор проводит внутри функции GetSnapshotData. Она вызывает GetTransactionSnapshot, а эта, в свою очередь, exec_eval_simple_expr.
Ну всё же наоборот. exec_eval_simple_expr вызывает GetTransactionSnapshot, а GetTransactionSnapshot вызывает GetSnapshotData. На картинке обратный стек вызовов, а не прямой.
Поэтому первое правило — как можно реже вызывать DDL на нагруженных системах. При необходимости лучше создать временную таблицу в начале сессии и переиспользовать её. Постоянно создавать и удалять таблицы для того или иного действия не рекомендуется.
Вот тут не очень понял. Часто создавать и удалять обычную (не временную) таблицу не имеет никакого смысла. Но все вещи, описанные до этого, для временных таблиц также выглядят странно. Зачем уведомлять другие подключения об изменениях временных таблиц, которые нужны фактически только текущему соединению ? То есть не очень понятно, частый DDL для временных таблиц - зло или нет ?
Частый DDL для временных таблиц в любом случаи зло. Это и посылка сообщений об инвалидациях (даже fast_truncate посылает инвалидацию) и замусоривание таблиц системного каталога (от этого увы никуда не деться). Стоит отметить что внутри ядра PostgreSQL инвалидации нужны не только для того, чтобы информировать другие подключения, но ещё к примеру откатить изменения системного каталога в текущем процессе при ROLLBACK-е.
замусоривание таблиц системного каталога (от этого увы никуда не деться)
Да, есть такая проблема. У нас стабильно pg_class, pg_statistic и pg_attribute разрастаются до 2ГБ. И проблема еще в том, что на сильной загрузке сделать их VACUUM FULL почти нереально из-за блокировок. В итоге приходится с этим просто жить. Непонятно правда насколько такой разросшийся pg_class и pg_statistic влияют на производительность.
Причем мы то DDL как раз особо часто не делаем. А делаем только TRUNCATE (да, не fast_trunc, так как по ряду причин нужны именно транзакционные временные таблицы) и ANALYZE. Причем первый мало того, что меняет pg_class за счет relfilenode, так он еще же и создает новые файлы, что при определенных проблемах может приводить к проблемам на уровне файловой системы. У нас была такая проблема с CentOS 8, когда количество файлов временных таблиц в каталоге начинало превышать 500.000 то местами начинались чудеса (куча процессов висела в TRUNCATE и ANALYZE временных таблиц). К сожалению, не было времени разбираться пришлось откатиться на CentOS 7, и проблема ушла.
Однако, к сожалению, отказаться от временных таблиц тяжело. И именно из-за проблем с планированием. Известно, что PostgreSQL всегда строго придерживается плана, даже если все пошло не так. И легко получить nested loop со сложностью много миллионов. И пока единственный способ с этим бороться - это INSERT фактически подплана в промежуточную таблицу (причем приходится делать TRUNCATE, чтобы не делать DDL по созданию новой), ее ANALYZE, и далее запрос с этой временной таблицей. Тогда PostgreSQL будет уже знать точное количество записей и планировать исходя из нее.
Более того, сейчас практически невозможно автоматически определить, где именно ошибся PostgreSQL. В итоге мы просто ставим таймаут запросу, и если запрос отменяется по нему, то мы считаем, что наверное PostgreSQL ошибся, и сами выбираем как ему разбить запрос на несколько с временными таблицами.
Это я все к чему. К сожалению, как обходится без TRUNCATE и ANALYZE не очень понятно. И в PostgreSQL с этим есть проблемы. В частности то, что инфраструктура со временными таблицами не вынесена как-то отдельно от постоянных таблиц. Но я понимаю, что так было сделать гораздо проще и надежнее.
Все эти проблемы с планированием/оптимизацией легко решались бы, если бы идеологи pg до сих пор не отказывались бы упорото от хинтов. Но они придумали себе, будто бы сервер всегда лучше знает, как оптимизировать запрос, и всех этих проблем старательно не видят (не их же проблема, да?)
К счастью, есть расширения, позволяющие отчасти решить проблему. pg_hint_plan, например, вполне живой пример того. Попробуйте, в вашем случае хотя бы кошмар с кучей промежуточных времянок сократится, а если повезет, то и городить его не придется.
Использование хинтов может решить некоторые проблемы, но это сложная задачка, требующая от разработчика знаний о внутренних механиках СУБД и специфике работы приложения, и оно имеет и обратную сторону - сервер, действительно, может построить более оптимальный план, при ошибке хинт просто не сработает и сервер, опять же, будет использовать свои планы, а при изменении, к примеру, индексов придется переписывать хинты. Учитывая что хинт обычно пишется в каких-то очень неочевидных случаях - разбираться тому кто придет поддерживать код с хинтами часто бывает очень непросто.
Я не против хинтов - я много работал с ними, и хорошо знаю что это совсем не панацея.
Учитывая, что хинты обычно пишутся именно тогда, когда ясно, что сервер косячит с планами, все перечисленное становится малосущественным — за исключением, конечно, меняющихся индексов и прочей физической структуры. Когда это не учитывается (или когда хинтов просто не завезли, как в тот же pg), начинается дурной вариант заката солнца вручную — попытки переписать запрос так, чтобы сервер все-таки построил план, близкий к тому, который у хорошего разработчика всегда в голове уже при написании запроса, и надеяться, что при любом изменении, например, статистики, все не сломается заново.
И да, сервер практически никогда не нарисует план лучше того, который в голове у хорошего разработчика, а вот сделать из плана даже среднего запроса полную и удивительную чертовщину — запросто. "Практически" — потому что помню случай, где сервер применил логически неочевидную оптимизацию, и выдал суперэффективный, но удивительный на первый (и по десятый включительно) взгляд план.
И, разумеется, этим действительно должны заниматься люди со знаниями и опытом явно выше среднего.
после выхода 13-й каждое выражение перестало требовать создания снимка
Кстати, зато в 13й поломали работу plpgsql. Если послать pg_cancel_backend в тот момент, когда запрос выполняется в определенном месте на определенном этапе внутри plpgsql функции, то валится вообще весь сервер. Причем это тяжело воспроизвести (так как отменяется часто, а валится раз в 2-3 месяца). Выглядит это вот так :
И это бывает совершенно на разных plpgsql функциях. В итоге приходится все переписывать на чистый SQL.
Кстати по описанным в статье причинам, параметр force_parralel_mode в 16-й версии переименовали в debug_parallel_query.
Отличная статья, спасибо!
Отличная статья, спасибо!
Чуть более года назад тоже разбирались с проблемой связанной с подтранзакциями в нагруженной системе. Я тестировал патч v17 - https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/22
В результате была написана статья, рекомендую почитать https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
Смотрю уже доступен патч v24, надо будет выделить время и проверить что изменилось. А пока, пришли к выводу что надо выпиливать использование подтранзакций в нагруженных системах.
Аномалии под нагрузкой в PostgreSQL: о чём стоит помнить и с чем надо бороться