Комментарии 29
В postgres при работе с временными таблицами нужно избегать долгих скриптов вида:
select a, b
into temp_table
from...Они блокируют схему БД на все время выполнения, не давая создавать-удалять временные таблицы параллельным потокам
Можете уточнить конкретным примером и указать версию PostgreSQL, на которой это проявляется?
Проверил как на древней 12.4, так и на свеженькой 16.1. В одной сессии запускаю длительный запрос вида:
DROP TABLE IF EXISTS tmp_tmp;
CREATE TEMP TABLE tmp_tmp AS
SELECT id
FROM generate_series(1,100000000) G(id);В другой - такой же запрос, но уже не со 100 миллионами, а со 100 тысячами. Никаких блокировок не наблюдаю.
"Круто", как же 1с-ные базы с этим живут? Там без временных таблиц вообще жизни нет в принципе.
Так и живут с распухшими системными таблицами и деградацией производительности при операциях с системным каталогом. Просто соотношение длительности работы с системным каталогом к длительности обработки остальных данных очень мало. И то, что из-за временных таблиц оно с условных 0.001 на MS SQL увеличилось до условных 0.01 на PostgreSQL - не видно на общем фоне.
Возможно 1с чтото оптимизировало в этом плане, есть же сборки Постгре "адаптированные" для 1С
Вряд ли, потому что если бы придумали, как этот момент исправить со стороны СУБД, то такие патчи бы предлагались далеко не только для 1С. А я ничего подобного на CommitFest очень давно не видел.
У postgrespro в сборке есть fasttrun, он сильно уменьшает распухание.
Вообще-то он предотвращает только один сценарий распухания, предоставляя альтернативу TRUNCATE. На практике же, очистка временных таблиц требуется редко. Множественные TRUNCATE на временные таблицы - это специфика 1С.
Это не специфика 1С. У нас в lsFusion - точно также. Просто этом часто и есть смысл временных таблиц - записать туда данные, очистить, записать снова и т.д. Схема таблицы то, как правило, одинаковая. Зачем постоянно их создавать и удалять, если можно просто truncat'ить ?
Тут палка о двух концах. Одна не используемая сейчас временная таблица - это не только ворох записей в системном каталоге, но и несколько файлов в файловой системе. Одно дело, если на одном соединении десяток временных таблиц из которых в один момент используется пять. И совсем другое дело, когда их тысяча, а в один момент используется все равно пять.
Я вот, ради интереса, залез в git репозиторий БД MS SQL одного из проектов, которому только предстоит миграция на PostgreSQL, и крошечным скриптом на Perl нашел создание 1051 различных временных таблиц. Точно нужен миллион временных таблиц и 3-5 миллионов файлов в одной директории?
Хорошо бы все-таки прежде чем принимать какие-то решения по работе со временными таблицами, замерять результаты при помощи perf. Да, с ними в PostgreSQL не все хорошо (мой опыт работы с ними - в этой статье). У нас в пике бывают сотни тысяч временных таблиц (а используем мы их также активно как и тот же 1С). Так вот работа с ними (я имею ввиду непосредственно выполнение DDL команд и TRUNCATE) - это все равно не более 15% общего CPU. А если вынести их всех в память, то диск они не трогают. Так что не все так плохо с ними, хотя конечно могло быть лучше.
Ну и чуть что, в Postgres Pro вроде как как что-то улучшили, но я особо не тестировал.
Так вот работа с ними (я имею ввиду непосредственно выполнение DDL команд и TRUNCATE) - это все равно не более 15% общего CPU.
15% - это очень много, по сравнению с MS SQL. Только массивами композитных типов и pg_variables это значение можно существенно уменьшить.
К тому же - это лишь вершина айсберга. Ведь при активном выполнении DDL еще порождается множество dead tuples в системных таблицах, отчего они распухают и теряют в производительности уже при любом обращении к ним. В том числе при компиляции каждого SQL предложения. Поэтому perf, конечно, хорошо и правильно, но всей картины в нем так просто не увидеть.
15% - это очень много, по сравнению с MS SQL. Только массивами композитных типов и pg_variables это значение можно существенно уменьшить.
Ну 15% все-таки это не так много. И я посмотрел точные цифры- там все-таки на практике у нас меньше 10%. Композитные типы и pg_variables тоже имеют свои проблемы, в частности планы с ними могут быть хуже за счет отсутствия по ним статистики (на времянки мы всегда делаем ANALYZE после их заполнения) и индексов.
Опять же не знаю, как в MSSQL сделана работа с временными, но думаю, что там тоже есть свои нюансы (вот, например, тут товарищи извращались). Так что не факт, что эти 10% по сравнению с MS SQL - это чистые потери. Может деградация будет процентов на 5.
Ведь при активном выполнении DDL еще порождается множество dead tuples в системных таблицах, отчего они распухают и теряют в производительности уже при любом обращении к ним
Есть такое, но во-первых обращение идет к ним в основном при планировании, что значительно меньше, чем время выполнения. Во-вторых, системные каталоги все время в памяти, и достаточно часто вакуумятся (я бы сказал почти всегда). То есть да, таблицы разросшиеся (у нас до нескольких гигабайт). Да, там много мертвых туплов. Но в целом, затраты на все это на общем фоне незначительны.
Во-первых, я специально смотрел производительность до и после VACUUM FULL системных таблиц, и разницы особо не увидел. А во-вторых, вот я смотрю на рабочий perf, там планирование - это 12% времени CPU где-то. Из них считывание из системных каталогов - лишь незначительная часть. То есть на общую загрузку CPU это тоже не так сильно влияет.
Композитные типы и pg_variables тоже имеют свои проблемы, в частности планы с ними могут быть хуже за счет отсутствия по ним статистики (на времянки мы всегда делаем ANALYZE после их заполнения) и индексов.
А никто не спорит, что "золотого молотка" не существует. Вопрос в том, насколько чаще план запроса возникает не оптимальный и насколько это вообще критично в данном случае. Применяйте перечисленные мной в статье методы к каждому запросу и по отдельности смотрите на результат. Ну и есть немало методов направить планировщик на путь истинный. Я временные таблицы в PostgreSQL использую редко, в 95% случаев один из перечисленных выше методов работает. Лидируют CTE (несколько INSERT/UPDATE/DELETE ... RETURNING в одном запросе) и UNLOGGED TABLE (часто в сочетании с ручным параллелизмом через dblink), так как объемы данных, у меня, обычно, весьма солидные.
не знаю, как в MSSQL сделана работа с временными
Так как их метаданные тоже в tempdb, то и методы работы с системными таблицами ничем не отличаются, от методов работы с такими объектами, как глобальные временные таблицы. Нет необходимости делать sync на диск ни для данных, ни для журнала транзакций.
тоже есть свои нюансы
Баги есть везде. Не вижу смысла заострять на них внимание.
обращение идет к ним в основном при планировании, что значительно меньше, чем время выполнения
Но значительно чаще, чем создание временных таблиц.
я специально смотрел производительность до и после VACUUM FULL системных таблиц, и разницы особо не увидел
Если загрузка была типовая - то и не увидите. Лишние обращения с страницам данным, только для того, чтобы убедиться, что индекс ссылается на мертвый кортеж начинают происходить сразу же. AUTOVACUUM это подчищает на периодической основе. Но и сам при этом создает нагрузку на БД.
Вопрос в том, насколько чаще план запроса возникает не оптимальный и насколько это вообще критично в данном случае.
Это, кстати, очень критично. Так как в случае кривого плана вы легко можете войти в Nested Loop, который будет иметь сложность много миллиардов, и который будет вам насиловать CPU, память и диск, и никогда при этом не выполнится.
Если загрузка была типовая - то и не увидите. Лишние обращения с страницам данным, только для того, чтобы убедиться, что индекс ссылается на мертвый кортеж начинают происходить сразу же. AUTOVACUUM это подчищает на периодической основе. Но и сам при этом создает нагрузку на БД.
Так поэтому и бессмысленно говорить без конкретных цифр. В любом случае, это часть планирования запросов, и в perf конкретно часть обращения к системным таблицам незначительна. Все, что создает значительную нагрузку на ЦП, как правило, видно в perf.
Так как в случае кривого плана вы легко можете войти в Nested Loop
Я потому и написал "в данном случае". Например, если только одна табличная функция или материализованный CTE в запросе не фигурирует в WHERE, а все остальные связываются с ней по индексам, то ничего планировщик тут изобретать не будет.
часть обращения к системным таблицам незначительна
Сюда надо прибавить труды VACUUM, которые в противном случае не потребовались бы. У меня autovacuum_naptime стоит по умолчанию в одну минуту и никаких проблем от этого не испытываю.
Использование временных таблиц нужно избегать, если есть такая возможность. В mssql я их не применяю. Использую либо таблицы в памяти либо СТЕ. Иногда вместо СТЕ лучшую производительность показывают вложенные запросы. Есть практически идентичные реализации сложных запросов для mssql и sqlite ( сетевой и локальный варианты). Всё переносится без проблем. С постгресом наверно тоже проблем бы не было. Но мои приложения перенести в постгесс было бы сложно из-за использования синонимов mssql, которые отсутствуют в постгесс.
В mssql я их не применяю. Использую либо таблицы в памяти либо СТЕ.
А как решаете проблему невозможности использования статистик в этом случае? Ведь явно указывают, что "you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). Temp tables may be a better solution in this case"
из-за использования синонимов mssql, которые отсутствуют в постгесс
Не понял, что имеется ввиду. Синонимы для функций или процедур легко заменяются созданием прокси функции или процедуры. Синонимы для таблиц - представлениями.
Насчёт таблиц в памяти-использую набор записей не более 32. В реальности больше не надо, это количество датчиков в тестовом пуле. Использую встроенную процедуру. Идея простая - в таблицу в памяти добавляю данные расчёта из реальной таблицы данных по ключевому полю. Прохожу агрегатом min to min по каждой записи реальной таблицы , расчитываю кучу параметров для каждой записи и вычисляю референсные значения, рассчитанные по формулам через динамический sql, а затем добавляю в таблицу в памяти. Когда пул пройден, в памяти хранится весь набор рассчитанных параметров записей пула. Остаётся его вернуть финальным select из процедуры.
Насчёт синонимов постгресс. Идея синонимов это не идея представлений. Это для того, чтобы допустим перенести таблицы и процедуры в другую БД, допустим это БД общих ресурсов и рабочая и тестовая БД. Если весь функционал у меня в процедурах, то код в обоих базах одинаковый, нет необходимости использовать указатели на базу и схему в процедурах или добавлять представления и процедуры как в постгресс. Достаточно только создать синонимы. Это понятно. В постгресс пришлось бы в представления использовать указатели на БД и схему. К тому же думаю использование представлений повлияло бы на производительность. Ну а синонимов функций в по стрессе тоже нет и пришлось бы опять как-то выкручиваться. Зачем усложнять себе жизнь?.
Таблицы
использую набор записей не более 32
У Вас совершенно не типовой и редкий сценарий использования БД. В подавляющем большинстве сценариев речь идет от тысячах и миллионах записей. Для примера, только вагонов у меня в справочнике свыше миллиона. А под управлением из них в конкретный момент времени - 100 тыс.
чтобы допустим перенести таблицы и процедуры в другую БД
Вообще-то это задача CI/CD, который в зависимости от зоны подменяет значения ряда переменных при сборке из GIT и разворачивании.
У нас совершенно другие задачи, связанные в основном с управлением измерительных и калибровочным оборудованием и сложной эматематической подготовкой и обработкой данных при производстве и тестировании интеллектуальных датчиков давления и расходомеров различных типов. Один расходомер может содержать сотни регистров модбас, а датчик давления должен выполнять сотни команд.
В общем все в глубину а не в ширину.как у вас.
А с чего Вы взяли, что у вагона меньше сотни характеристик, причем изменяющихся во времени? И что с ним может производится меньше сотни различных операций? А математика прогнозирования времени приезда вагона до станции назначения или даже ремонтов - не уступает по сложности Вашей.
А Вы в курсе, сколько ПУ обслуживает АСКУЭ, даже такой небольшой области, как Тульская?
Так что Ваш опыт 32-х регистров/стеков одного ПУ для большинства задачи не применим.
Вряд ли вы используете степенные полиномы и операции с матрицами. У нас это производство и сложнейшие расчёты и тестирование прециционных измерительных устройств с точностью до 0.04%.
Для регрессионного анализа и прогнозирования я не только это, но еще дробное интегрирование и тензорное исчисление использую. Из-за этого в PostgreSQL довольно активно используются хранимые процедуры на R. Так что мимо.
Если у Вас действительно сложные и тяжёлые вычисления, то на MS SQL прямой смысл либо вынести их на клиента, либо в CLR на C#. Математика в T-SQL жутко медленная, особенно если требуются операции с матрицами
В общем так и сделано. Клиент у меня на labview и ему скармливаются уже транспонированные матрицы и вычисляются коэффициенты полиномов. Не все методы вычисления дают требуемую точность, остановились на методе Гивенса. Затем проверяем подстановкой коэффициентов в поли
ном и проверяем точность. В общем ничего сложного нет, когда знаешь что и зачем вычислять. Значительно сложнее было придумать метод мониторинга выполняемого теста в режиме онлайн, так чтобы не грузился сильно сервер, или вынос выполняемого скрипта теста во внешнюю таблицу и показ операций скрипта в онлайне вместе с текущими параметрами ( в бд и циклы и прямая и косвенная адресации показа параметров и тп.) =Пришлось придумать свой скиптовый язык фактически. Это было действительно непросто если сравнивать с реализацией линейной регрессии.
Ну тогда Вам точно прямая дорога в PostgreSQL и pl/R. А если безопасность позволяет, то в pl/Python.
А почему безопасность хуже во втором случае?
Если для R есть доверенный (trusted) вариант, то для Python есть только не доверенный (not trusted).
Подробней в документации. Если коротко, то когда у всех пользователей на PostgreSQL и так права суперпользователя (superuser), то можете использовать не доверенные языки. Иначе имейте ввиду, что средствами не доверенного языка любой пользователь может дать себе права суперпользователя.
Проблемы при переходе с MS SQL на PostgreSQL.Активное использование временных таблиц