В обсуждении под статьями «СУБД Tantor Postgres 18: обзор улучшений для 1С» и «В погоне за APDEX‑ом, или как создать HighLoad на недорогом серверном железе» чрезвычайно уважаемые люди задались вопросом про *_collapse_limit и его отношение к geqo_threshold. В сообществе обсуждали эти параметры в большом топике, в этой статье коротко описано то, к чему там пришли.
Число 20 взялось из поста Роберта Хааса 2009 года по то, можно ли что‑то сделать с параметрами join_collapse_limit и from_collapse_limit в PostgreSQL версии 8.5, которая в то время выходила. Техника добавления OFFSET 0 взялась из того же обсуждения.
В 2009 году Хаас предложил увеличить geqo_threshold до 16 и *_collapse_limit до 12 (или сделать булевыми или вообще убрать), и спросил мнение практиков. Для справки: GEQO появился в PostgreSQL 19 февраля 1997 года, то есть очень давно и он очень нужен, не стоит им пренебрегать. Его отключение не может привести, а приводит к выделению десятков и сотен гигабайт памяти на этапе создания плана.
Практик, Кевин Гриттнер, написал: у нас отключен geqo, и мы установили *_collapse_limit на 20. Вероятно, нам следует просто установить их на несколько сотен и не ждать, пока какой‑нибудь запрос с более чем 20 таблицами начнет работать плохо, но я не уверен, что у нас пока есть такие запросы. По его опыту, в тех случаях, когда увеличение *_collapse_limit хоть как‑то влияло на результат, это всегда приводило к созданию плана, который экономил больше времени, чем дополнительное время на планирование.
Роберт Хаас выразил суть параметров: параметры join_collapse_limit и from_collapse_limit ограничивают порядок соединения. Если оценки планировщика верны, то для них оптимальны наибольшие значения (бесконечность). Бывают случаи, когда небольшие значения этих параметров улучшают планы, но это означает, что расчет стоимости планировщиком неверен, и ограничение порядка соединения позволяет планировщику исключить планы, которые имеют небольшую стоимость и в реальности хуже, но такое встречается нечасто.
То есть: что 20, что 100 разницы нет, если оценки верны. Сделать булевыми хотели, чтобы оставить возможность сделать порядок соединения точно таким, как в запросе.
Том Лейн написал, что *_collapse_limit были добавлены исключительно на основе предположения, что кому‑то они могут пригодиться. Том не против удаления join_collapse_limit или сведения его к логическому значению.
Альваро Эррера никогда не сталкивался со случаями, когда *_collapse_limits были бы ему полезны.
Ноа Миш доложил сообществу то, что при добавлении статистически неразличимых столбцов на PostgreSQL 8.3, он наблюдает увеличение времени планирования и памяти в 4 раза (В PostgreSQL 18 в 3.x раз или, как абсолютно правильно пишет Андрей Лепихов, «сверхэкспоненциально» — экспонента 2.7) и привёл пример соединения 16 таблиц. Для запросов с 19–21 соединениями, Ноа Мишу пришлось «отключить детерминизм» (то есть включить GEQO), так как планирование длилось более, чем 600 секунд и использовало, на этапе планирвания, более 10 Гб памяти. Ноа Миш никогда не сталкивался с ситуацией, когда значение, отличное от 1 или «бесконечность», для *_collapse_limit, оказалось бы оптимальным.
Том Лейн написал, что DBA любят предсказуемость, поэтому, значения по‑умолчанию, для join_collapse_limit и from_collapse_limit установлены так, чтобы предотвратить использование GEQO. Если убирать из PostgreSQL параметры *_collapse_limit, то посыпятся жалобы, что запрос выполняется то быстро, то медленно, если только не отключить GEQO и сослался на недавнюю жалобу пользователя PostgreSQL с соединением 17 таблиц. Пользователь жаловался, что запрос выполняется то быстро, то медленно.
Мнение Тома подтвердил Кевин Гриттнер (он играл в обсуждении роль DBA), что главная причина, по которой он старается избегать GEQO, в том, что GEQO может редко, но метко, создать кривой план, что приведет к загадочным жалобам пользователей, и эти жалобы могут отнять у него много времени на поиск причины, а она в случайности.
Андреас Фройнд потратил несколько дней и создал сложный пример из реальной системы, который и опубликовал в топике.
Скрытый текст
Любой может скачать пример Андреаса и использовать в своих тестах. Вместо «PREPARE foo AS SELECT * FROM proband» в query_2.sql можно поставить «CREATE OR REPLACE VIEW test AS SELECT sample_id FROM proband» и измерять время планирования запроса простой командой: explain (memory) select * from test.
Результаты теста Андреаса на 18 версии:
\timing on \\ SET SEARCH_PATH = test_data, test_view; SET geqo = off; SET join_collapse_limit = 11; --- 2617.020 ms Memory: used=597053kB allocated=599284kB SET join_collapse_limit = 12; --- 3205.557 ms used=759366kB allocated=763124kB SET join_collapse_limit = 13; --- 3266.530 ms used=769482kB allocated=771316kB SET join_collapse_limit = 14; --- 7079.614 ms used=1090170kB allocated=1091380kB SET join_collapse_limit = 15; --- 15926.589 ms used=2179803kB allocated=2181044kB SET join_collapse_limit = 16; --- 13124.880 ms used=2210545kB allocated=2213812kB SET join_collapse_limit = 17; --- 19529.137 ms used=2793452kB allocated=2796724kB SET join_collapse_limit = 18; --- 63925.147 ms used=8861636kB allocated=8869044kB SET join_collapse_limit = 19; --- 56204.530 ms used=8861646kB allocated=8869044kB SET join_collapse_limit = 20; --- 85993.891 ms used=11677634kB allocated=11679733kB SET join_collapse_limit = 21; --- следующий скачок использования памяти и времени.
Оказалось, что на тесте Андреаса Фройнда, память и время увеличивались скачкообразно. Следующий скачок происходил после join_collapse_limit = 20.
Особенность диагностики: нехватка памяти при планировании, выглядит как подвисание системы (память выделяется очень часто небольшими кусочками) и можно подумать, что процессор сильно грузится, а дело в памяти. OOM killer приходит с сильно большой задержкой. Евгений Бредня об этом написал в комментарии.
Скрытый текст
Чтобы OMM killer не срабатывал, linux не подвисал, а экземпляр не падал при экспериментах, стоит установить:
postgres@tantor:~$ sudo sysctl -w vm.overcommit_ratio=100 sudo sysctl -w vm.overcommit_memory=2 vm.overcommit_ratio = 100 vm.overcommit_memory = 2
После этого работа становится безопасной:
postgres@tantor:~$ psql -q postgres=# SET SEARCH_PATH = test_data, test_view; SET geqo = off; SET join_collapse_limit = 21; \timing on \\ explain (memory) select * from test; ERROR: out of memory DETAIL: Failed on request of size 64 in memory context "explain analyze planner context". Time: 34715.513 ms (00:34.716) postgres=#
Собственно, из теста Андреаса (Кевин Гиттнер тоже пришел к этому числу, но эмпирически — без теста) и взялось число 20. Устанавливать его стали, вероятно, из предположения, что при отключенном GEQO система хотя бы не подвиснет по нехватке памяти. То есть *_collapse_limit = 20 разумная рекомендация, чтобы защититься от geqo = off и этой рекомендации можно следовать. Если не отключать GEQO и не увеличивать пороговое значение GEQO, то вместо 20 можно ставить и большие значения.
Таким образом, 1C ИТС вставила рекомендацию для значения *_collapse_limit = 20 не с потолка, а зная, какие запросы генерирует 1С и аккуратно прочтя приведённое обсуждение в сообществе, так как число 20 выделяют и другие, например: «Historical context from PostgreSQL mailing lists (2009): Core developers discussed raising these values but wanted evidence. Production users reported successfully running with collapse_limit = 20 and GEQO disabled entirely without issues. The defaults haven't changed because they're still safe for all workloads, but modern systems can handle much higher values.»
Пример Андреаса и Кевина для типичных бизнес‑запросов. Интересен граничный случай, подсказанный Ноа Миш. Граничный (худший) случай нужен, чтобы не обольщаться, что можно увеличить geqo_threshold до больших значений.
Создадим запрос для join_colapse_limit со статистически неразличимыми столбцами:
\timing on \\ SET geqo = on; SET geqo_threshold = 16; SET join_collapse_limit = 16; EXPLAIN (analyze, memory, settings) with t as (select 1 a) select 1 from t t0 natural join t t1 natural join t t2 natural join t t3 natural join t t4 natural join t t5 natural join t t6 natural join t t7 natural join t t8 natural join t t9 natural join t t10 natural join t t11 natural join t t12 natural join t t13 natural join t t14; Settings: geqo_threshold = '15', join_collapse_limit = '15' Planning: Memory: used=235kB allocated=256kB Planning Time: 33.668 ms Execution Time: 0.328 ms Settings: geqo_threshold = '15', join_collapse_limit = '14' Planning: Memory: used=5155822kB allocated=5162241kB Planning Time: 18779.568 ms Execution Time: 0.410 ms Settings: geqo_threshold = '15', join_collapse_limit = '13' Planning: Memory: used=1680916kB allocated=1688193kB Planning Time: 5986.123 ms Execution Time: 0.409 ms Settings: geqo_threshold = '15', join_collapse_limit = '12' Planning: Memory: used=546392kB allocated=549153kB Planning Time: 1937.615 ms Execution Time: 0.395 ms
Да‑да, этот небольшой запрос способен выделить гигабайты памяти при планировании. Каждое соединение в запросе увеличивает выделенную память в ~3 раза.
В приведённом результате, худший случай, когда *_collapse_limit меньше geqo_threshold на единицу, так как тогда попадает на "разгонную полосу" на втором графике из статьи Павла Толмачёва:
А при geqo_threshold = 16, join_collapse_limit =15 выделяется вообще 15Гб памяти:
Planning Time: 41241.285 ms Memory: used=15761345kB
Обратите внимание, в начале статьи было написано, что Роберт Хаас предлагал поднять geqo_threshold до 16, а join_collapse_limit до 12, то есть на 4 меньше (а не на -3, не -2 и не -1). Кто скажет, что он их взял с потолка, пусть первый бросит в себя камень.
Рекомендация *_collapse_limit = 20 разумна. Если не отключать GEQO и не менять geqo_threshold, то его даже можно увеличить и установить 30, 40,100.
Скрытый текст
На это указывает приведённый график из статьи Павла. В статье отлично протестированы комбинации параметров. Единственно, обтекаемо написано «расход этих ресурсов возрастает экспоненциально при увеличении числа таблиц в запросе». Ресурсы — это оперативная память, которая в огромных объемах выделяется при планировании. Ещё хорошие картинки про планирование есть статье, где упоминается: "Heuristic: эвристики, порядок вокруг «звезды» (факт + измерения)", то есть star transformation из Oracle Database.
Остался правильный и прямой вопрос Антона: а кто же это всё-таки убил Налестро имеет ли смысл ставить *_collapse_limit больше, чем geqo_threshold, который оставляют 12? Имеет. В 1С была проблема, когда в конец запроса с большим числом соединений добавалялся «AND t1.col1 IN ( VALUES(..». Проблема описана в статье Александра Симонова. Андрей Лепихов и Алёна Рыбакина создали патч (ревьюверы Иван Кушнаренко и Александр Коротков), который чрезвычайно быстро был закоммичен Томом Лейном. Проблема, когда простой VALUES плодит соединения, ушла благодяря патчу, но другие проблемы могли остатья и параметры *_collapse_limit, установленные в значение, большее geqo_threshold, позволяют планировщику соединить условия из начала и конца списка соединений или предикатов. Именно поэтому и стоит устанавливать их в число, больше, чем соединений в запросе. Без этого хороший план (если он есть) никогда не создастся, а с установкой в большое значение — есть вероятность и, на практике большая (алгоритм GEQO дает хорошие результаты, только в небольшой части даёт сбой), что будет создан. Генерирует ли 1С сейчас запросы с большим числом соединений, где последний предикат (соединение) оптимальнее соединить с предикатом в начале, я не знаю. Это можно спросить в чате специалистов по 1С https://t.me/PostgreSQL_1C_Linux . Дает ли минусы установка *_collapse_limit в меньшее значение, чем 20 (но не меньше geqo_threshold)? Не даёт и планы не улучшит (если только нет ошибок в оценках планировщика).