Comments 45
Ох, давно жду in memory table в PostgreSQL
Ну в целом, если у вас очень большие shared_buffers, и поставить очень долгий checkpoint, а wal вынести куда-нибудь отдельно, то чтения и записи диска будет минимально.
Другое дело, что конечно для хранения исключительно в памяти подойдет лучше другая архитектура. Но тогда непонятно, что делать с ACID, и как сделать, чтобы можно было использовать в OLTP системах.
Исключительно для temporary table/тестов. Где совсем не нужна транзакционность и MVCC, WAL и все оверхеды в PostgreSQL связанные с ними.
Ну в temporary table я бы не сказал, что прям совсем не нужна транзакционность. Потому как если в них хранятся какие-то (пусть и временные) данные, вы проводите транзакцию и, в том числе, изменяете эти временные таблицы (что бывает часто), а потом ловите скажем update conflict, то вам нужно заново начать транзакцию. И если вы не откатите временные таблицы на начало транзакции у вас будет нецелостное состояние и повторить транзакцию заново (что подразумевается при update conflict) уже не получится.
Там, где ACID не обязателен, можно разгрузить wal при помощи unlogged table, используя их повторно с идентификатором процесса и очищая их delete.
Да, такой подход возможен, но мы не можем это использовать, как минимум, по трем причинам. Во-первых, определенный ACID нужен (в том числе и для временных таблиц, чтобы при откате транзакции не потерялись в них данные). Во-вторых, временные таблицы создаются автоматически в разных случаях, и если держать unlogged table под все случае для всех разновидностей ключей/колонок - их будут тысячи. А в третьих, если все записывать в одну таблицу, то на 2000 пользователях, где у каждого будет по 100 записей, то в таблице будет 200.000 записей, и работа с ней будет значительно медленнее, чем с 2000 таблиц по 100 (скорее всего, даже не окупятся затраты на дополнительный DDL).
работа с ней будет значительно медленнее, чем с 2000 таблиц по 100
Откуда это утверждение? По моему опыту - с точностью наоборот. Речь не идет о секционировании, но тут и объемы еще далеки для это.
Быстрее, как минимум, по двум причинам :
Во всех seq scan / index scan по этой таблице, который появятся в плане запроса, будет пробег и фильтрация не по 200.000, а по 100 записям.
Будет гораздо правильнее статистика, так как PostgreSQL не придется гадать сколько записей отсечется по фильтру идентификатора процесса, а он точно будет знать количество записей в таблице и будет строить план исходя из этого.
Я просил ссылку на источник такого утверждения, а не теоретические выкладки без описания повторяемого эксперимента.
К тому же выкладки ложные.
По 100 записям в таблице всегда будет full scan, а по 200 тыс. - только если нет такого индекса.
Если выборка идет только по индексированному номеру сессии, то он никак не затрагивает другие сессии. То есть "пробег и фильтрация" будет в любом случае по 100 записям.
Так что все же сначала докажите свое утверждение. Я хотел бы увидеть сравнение издержек на работу с 4 тыс. файлами для 2 тыс. таблиц, по сравнению с издержками на BTree индекс по 2 тыс. сессиям, которые помещаются в одну 8К страницу БД.
Издержки бывают разные. Есть разные ресурсы (CPU/память/диск) и при каждом подходе где-то могут одни ресурсы больше использоваться, а где-то другие.
Важно понимать, что есть накладные расходы просто на использование индексов. Например, не забывайте, что во-первых, просто сложность пробега по индексу - это логарифм. Во-вторых, это фактически отдельный relation с точки зрения PostgreSQL, и PostgreSQL при прогоне по индексу будет использовать два relation вместо одного, что уже допрасходы (для решения этой проблемы даже придуман Index Only Scan, но он в редких случаях только подходит). Ну а в третьих, как я и писал выше - статистика. При индексе всегда идет эвристика для расчета количества записей. Если Вы анализировал тысячи разных планов, то должны понимать насколько в PostgreSQL важна правильная статистика.
Сравнение же конкретных тестов - это сферический конь в вакууме. В определенных случаев, у Вас может работать на 30% быстрее, а в других случаях в 10 раз медленнее. Все зависит от конкретных запросов и планов.
Все же вернемся к нашим баранам. На основании какого эксперимента Вы утверждаете, что издержки на работу с 4 тыс. файлами для 2 тыс. таблиц меньше, чем издержки на BTree индекс по 2 тыс. сессиям в одной таблице с двумя файлами? И как этот эксперимент повторить?
А вот уже после этого у нас будет не сферический конь в вакууме, а хоть какая-то основа для конструктивного разговора.
У нас данные "временных" таблиц используются только в пределах транзакции, поэтому выбрали такое же решение с unlogged table. Поскольку данные используются только в пределах транзакции, то каждая транзакция видит только данные, которые она сама и создала.
Да, это конечно решает логическую проблему, но интересно было бы увидеть план в работе с такими таблицами. Ведь, если не делать ANALYZE (или если не сработает autoanalyze), то откуда PostgreSQL будет знать правильную статистику по ней ? А если он не будет знать (и считать, что там, например 0 записей), то может быть много проблем с неправильным планом.
А почему autoanalyze должен не сработать?
Это для временных таблиц autoanalyze не работает, а для unlogged — вполне себе работает.
Мне всегда казалось, что autoanalyze - фоновый процесс. А основной кейс использования временных таблиц - INSERT, а потом сразу же SELECT JOIN эту временную таблицу. В случае, со временными мы делаем TRUNCATE - INSERT - ANALYZE (в явную) - SELECT. А как с unlogged autoanalyze успеет обновить статистику между INSERT и SELECT ?
Но в целом, мне не доводилось использовать unlogged tables, поэтому я по ним знаю не так много. Судя по документации, запись в них просто не отражается в wal. Но в остальном структура хранения, насколько я понимаю такая же ? Если так, то возникает вопрос. Описанный выше кейс использования временных таблиц, как будет с unlogged ? Сначала DELETE, а потом INSERT ? А кто тогда старые записи очистит ? Autovacuum постоянно будет их насиловать ? Но даже, если не autovacuum, а в DELETE есть отдельная ветка для unlogged, то все равно же может быть сильная фрагментация, как с обычными таблицами.
А как с unlogged autoanalyze успеет обновить статистику между INSERT и SELECT ?
А это не всегда нужно. Если в нежурналируемую таблицу пишут все пользователи, то, не редко, статистика у этих пользователей не сильно различается. В исключительных случаях действительно оправдано обновление статистик явным вызовом ANALYZE
А если отличается ? Если кто-то записал 2 записи и выгоднее сделать Nested Loop, а кто-то 20.000 и там Hash join нужен ? Да, PostgreSQL выберет в таком случае какой-то усредненный вариант, но он может быть не самым эффективным.
А явный вызов ANALYZE пойдет по всей таблице, а не только по тем записям, которые изменились.
Во-первых, 2 или 20 тыс. - разница ничтожна. Это не 2 и 20 миллионов.
Во-вторых, раз в эту таблицу может какой-то процесс записать 20 тыс. записей, то, с большой вероятностью, это уже было. А значит будет учтено в статистиках и вероятность явно плохого плана будет низка.
В-третьих, при явном вызове ANALYZE по всей таблице не подойдет. default_statistics_target по умолчанию вообще 100 и сильно увеличивать его не стоит.
ANALYZEtakes a random sample of the table contents, rather than examining every row
https://www.postgresql.org/docs/current/sql-analyze.html
Так что плохой план Вы можете получить и после ANALYZE. Просто потому, что с рандомом не повезло.
Если не требуется индексов, то обхожусь массивами записей. Иногда даже когда нужна индексация, scan where в unnest даёт меньший оверхед, чем temp table с индексом. Собственно говоря, если добавить в массивы индексацию, то и получим in memory temp table.
У нас временные таблицы создаются автоматически. И, к сожалению, заранее неизвестно сколько там будет записей (чаще всего, чтобы посчитать количество записей, которые вернет запрос, по сложности сопоставимо с выполнением самого запроса). И если там окажется 100.000 записей, то таким темпами можно забить всю память. А с временными таблицами есть защита, что в случае превышения temp_buffers они пойдут на диск.
Я имел в виду исключительно те случаи, где применимы in memory tables. Там где они не применимы из-за большого количества записей, там не применимы и массивы.
Ну и 100 тыс. записей вполне приемлемо. Если они по килобайту, то это всего 100 МБ, что для сервера с десятками (если не сотнями) гигабайт памяти не так уж много.
Разница в том, что у вас скорее всего конкретные запросы для конкретных целей, в которых вы в принципе знаете, сколько там должно быть записей. В нашем случае мы автоматически генерируем запросы на основе логики, описанной разработчиком на высокоуровневом языке. По этой причине мы не можем знать заранее сколько там будет записей. Да, у нас есть определенная статистика по таблицам, но как только включается фильтрация, то возможны варианты.
Например, если у вас в строках документов в среднем 20 записей, но есть документ на 30.000 (а такое бывает, например, ввод начальных остатков). И тогда все предсказание в общем случае рушится.
Что касается 100МБ, то проблема как раз в том, что если там случайно окажется 100ГБ, то у вас может просто oom killer сработать и убить весь процесс postgresql. Для временных таблиц есть ограничитель в виде temp_buffers.
Игорь написал:
Ох, давно жду in memory table в PostgreSQL
Я ему ответил:
Если не требуется индексов, то обхожусь массивами записей.
Потом еще раз уточнил:
Я имел в виду исключительно те случаи, где применимы in memory tables.
А теперь объясните пожалуйста, что Вы пытаетесь донести своим ответом?
В частности, они гарантировано не используются несколькими подключениями одновременно
А чем обеспечивается эта гарантия? Сама СУБД запрещает использовать одну временную таблицу двумя подключениями?
Мне казалось, я что-то такое делал
Временные таблицы хранятся пока конкретный сеанс с БД существует. Соответственно, если произойдет разрыв коннекта, то временные таблицы исчезнут. Как вы решаете эту проблему? Ведь пользователь может долго что-то делать, прежде чем решит нажать на кнопку сохранить. К этому моменту, в связи с особенностями инфраструктуры, коннект к БД может быть оборван. Вы как-то умеете восстанавливать все ранее записанные данные во временные таблицы ? К примеру ведете свой транзакционный лог...или как-то иначе... И ещё ведь такой подход требует иметь возможность подключения к БД одновременно всех заявленных пользователей, а это может быть очень большое число....Это приведет к большому расходу ресурсов сервера и к замедлению его работы.
Да, если произойдет разрыв соединения данные теряются. Но на практике разрывы соединения у нас бывают только, если сеть ляжет (что маловероятно, сервер приложений и сервер БД обычно соединены очень коротким путем), либо если весь PostgreSQL ляжет, что бывает крайне редко.
По сути, нужно такое же количество соединений, сколько и количество одновременно работающих пользователей. И да, в PostgreSQL одно соединение - один процесс в ОС. Приводит ли это к большому расходу сервера и замедлению работы ? Все относительно.
Вот сейчас снял на одном из работающих серверов :ps aux | grep postgres | wc -l
Результат :2284Собственно, там сейчас залогинено приблизительно такое же количество пользователей. Shared buffers - 256GB, temp_buffers и work_mem по 96MB. Да, каждый процесс использует свое количество памяти, но соединения автоматически время от времени закрываются и открываются, что убивает процессы и очищает память (новый процесс тратит немного памяти). Всего этими процессами вне shared_buffers используется 150ГБ памяти. При этом есть еще большой резерв по памяти.
Если честно, не наблюдал какого-то overhead'а idle процессами postgres. Если он и есть, то не очень большой.
Я выкручиваюсь через unlogged tables, добавляя в уникальный ключ идентификатор процесса. Но вот truncate к ним тогда не применим. Только delete. Зато delete на них выполняется быстро. И статистики не нужно обновлять на каждый чих.
Вы используете пул соединений? Баунсер или что-то другое?
Есть внутренний пул соединений, куда складываются неиспользуемые потоками соединения без временных таблиц / транзакций, и потом выдаются при необходимости новым потокам. Но так как таких соединений не сильно много по понятным причинам, особой роли этот пул не играет.
Правда есть важный механизм асинхронного "перестарта соединений", когда отдельный поток периодически собирает (весьма хитрым скорингом) соединения, которые давно работают, использовали много временных таблиц, и т.п. и асинхронно перестартовывает такие соединения (создает новое соединение, копирует все временные таблицы в новое соединение, закрывает старое соединение, и это все не прерывая поток использующий старое соединение). Это очень важно, так как у PostgreSQL есть не совсем объяснимая утечка памяти на долгоживущих соединениях, использующих временные таблицы. Кстати этот же механизм перестарта соединений может использоваться в том числе для кластеризации. Баунсеры к сожалению такого делать не умеют (они просто ограничивают использование временных таблиц ЕМНИП).
ЗЫ: Есть еще пул временных таблиц в соединении, но это из другой оперы.
"Если временные таблицы изменялись внутри транзакции, а она потом откатывается, то и временная таблица вернется в состояние до начала транзакции" - каламбур как то... Кто "она"? Временные таблицы откатились, то и временная таблица откатилась и треснула от смеха.
"Она" относится к последнему существительном (в данном случае "транзакции"). Но исправил в статье, чтобы не было двойного толкования. Суть в том, что, например, у вас во временной таблице t0 было 10 записей, потом началась транзакцию, и внутри ее таблица очистилась. Нужно, чтобы после отката транзакции в ней опять было бы 10 записей.
Я думаю что можно вычисления сделать на ресурсах пользователя, затем при сохранении отправить на сервер и если нужно пересчитать. И не нужно будет на сервере хранить тысячи временных таблиц
Вопрос в том, что в сложных логиках (не обычных CRUD) большинство данных для вычислений есть только на сервере (например цены товаров, условия поставки и т.п.), соответственно придется эти данные туда сюда на клиента гонять как минимум. Не говоря уже о том, что для вычислений придется императивщину вместо SQL использовать. Т
о есть непонятно в чем смысл. Использовать вычислительные ресурсы клиента, за счет большего трафика, неудобства разработки (то есть к SQL и условной Java / Python серверной логике еще клиентский JavaScript добавить) и т.п.? И это не говоря еще о безопасности.
Кроме того, после добавления записей во временную таблицу приходится делать ее ANALYZE, чтобы PostgreSQL знал правильную статистику данных в ней. ANALYZE, в свою очередь, также изменяет системные таблицы и обращается к диску (в функции visibilitymap_count).
Analyze это очень проблематичная вещь. Вот например 1С при каждой записи набора ее вызывает (не важно там 10 записей или 1000) и в результате имеем 30% расхода производительности на этот оператор Postgres как предчувствие. Вычисляем процент импортозамещения в режиме Highload от 1С / Хабр (habr.com)
Т.е. ее получается нужно вызвать в зависимости от количества записей? Чтобы на маленьких Full scan а на больших уже по статистике
Fasttruncate тоже не особенно быстрый по моим замерам (30% т.е. это не выглядит как классический truncate c high watermark) но как я понимаю альтернатив нет?
Однако существует один подход в Linux, который позволяет значительно уменьшить использования диска временными таблицами. Он заключается в выделении для временных таблиц отдельного RAM-диска. Причем для этого не требуется никаких изменений в коде программы, а указанную процедуру можно делать на работающей базе без остановки СУБД или приложения.
Почему Вы уверены, что менеджер RAM диска (процесс или что там управляет Ram диском) переварит конкурентную запись с многих потоков и не повиснет просто на одном ядре под 100%, даже если остальные ядра свободны?
Просто Postgres легко может бомбить запросами его с 50 бэкэндов , а RAM для их обработки нужно иметь процессы которые принимают это с нескольких ядер (это уже непростая архитектура)
Я просто смотрел RAM диски для win и не на одном не нашел упоминания о многопоточной обработке и вот у некоторых это вылезает на тесте Тестирование скорости работы 1C в режиме файловой версии, MS SQL и POSTGRES на HDD, SSD и RAMDisk / Хабр (habr.com)
Хочу обратить внимание, что в обоих статьях, на которые Вы ссылаетесь, PostgreSQL запускался под Windows. Одного этого достаточно, чтобы получить просадку производительности на 20-30% в ряде сценариев. Уж слишком болезненно обходятся ненужные копирования памяти при fork() без CoW, неэффективная эмуляция shared memory и не умение NTFS эффективно работать с тысячами файлов в одной директории.
Хочу обратить внимание, что в обоих статьях, на которые Вы ссылаетесь, PostgreSQL запускался под Windows.
Синтетический тест в Postgres как предчувствие. Вычисляем процент импортозамещения в режиме Highload от 1С / Хабр (habr.com) я запускал и на Unix на кластере с похожей конфигурацией (все на двухпроцессорных серверах и серверных SSD) .
Если смотреть на что тратится время то и там и там отчет показывает примерно одинаково
Вот например эта Как эффективно настроить autovacuum в Postgres для 1С / Хабр (habr.com)
гонялась c Postgres на Unix
Поэтому проблемы Analyze и Fasttruncate для меня одинаковы для этих ОС
Пересмотрел первую статью и опять не нашел данных, полученных под Linux. Тем более с правильными настройками (хотя бы huge pages).
Во второй статье речь вообще о VACUUM, что совсем иная тема. С одной стороны, при правильной архитектуре БД (версионность таблиц, когда старые записи не модифицируются и не удаляются, за исключением переноса их в архив) и приложения (когда данные агрегируются OLAP методами - columnstore или pipeline) VACUUM только в плюс, так как снижает нагрузку на WAL, по сравнению с MS SQL или Oracle. С другой стороны, если приложение уже написано так, что выполняет DELETE и UPDATE, особенно ключевых полей, на каждый чих - это действительно деградация производительности, решение которой есть пока только в не принятых в мейнстрим патчах.
Да, это боль для 1С, которая, чуть ли не единственная из известных мне ERP, позволяет модифицировать и удалять уже учтенные документы (операции). И где ввод операций в закрытый период возможен не только через труп финдиректора )
Во второй статье речь вообще о VACUUM, что совсем иная тема.
Как эффективно настроить autovacuum в Postgres для 1С / Хабр (habr.com) там просто гонял 1С на Linux . Настройки типа huge page не смотрел, Oracle linux с какой то default configuration. Если там есть тюнинг для Postgres - поставлю посмотрю. Есть проверенная статья по этому поводу?
В Oracle Linux Server как раз для PostgreSQL версий старше 9.3 ничего, кроме HugePages, настраивать и не требуется, в отличии от той же Ubuntu или просто Oracle Linux (desktop). По крайней мере в случае, когда кроме PostgreSQL на сервере больше ничего нет.
О настройке HugePages можно почитать тут: https://oracle-base.com/articles/linux/configuring-huge-pages-for-oracle-on-linux-64
И тут уже касательно именно PostgreSQL: https://postgrespro.ru/docs/postgresql/16/kernel-resources
Analyze это очень проблематичная вещь. Вот например 1С при каждой записи набора ее вызывает (не важно там 10 записей или 1000) и в результате имеем 30% расхода производительности на этот оператор
В lsFusion тоже при каждом записи набора вызывается ANALYZE. И скрины с perf там есть. И там никак не 30%. Возможно разница в способе замеров (perf vs postgresql), или 1С реально использует ну СЛИШКОМ много временных таблиц (что вполне возможно).
Т.е. ее получается нужно вызвать в зависимости от количества записей? Чтобы на маленьких Full scan а на больших уже по статистике
Теоретически можно смотреть сколько записей было до truncate, и сколько стало после и не вызывать ANALYZE. Но в ANALYZE не только количество записей анализируется, но и сами данные (например, сколько разновидностей в колонках). Просто не вызывать ANALYZE на маленьких нельзя, так как если до этого в этой таблице было много записей, то PostgreSQL будет думать, что там много записей. А если таблицы не было, то вообще может считать, что 0 записей - что еще хуже. Так что просто так избавится не получится.
Почему Вы уверены, что менеджер RAM диска (процесс или что там управляет Ram диском) переварит конкурентную запись с многих потоков и не повиснет просто на одном ядре под 100%, даже если остальные ядра свободны?
Опытным путем. Я включил RAM-диски на нескольких клиентах с 1-2К одновременных пользователей (и столько же процессов postgresql на сервере). Никаких проблем с производительностью или блокировками не было. И опять же, непонятно откуда они должны возникнуть, ведь фактически просто вместо обращения к ext4 идет обращение к tmpfs (а напомню, что в linux'е куча чего построено на tmpfs, которое гораздо более критично к параллелизму).
Но опять же, у меня нет вообще никакого опыта работы PostgreSQL на Windows (у нас все сервера на CentOS и Debian). Возможно в Windows все по-другому. Но, если честно, я совершенно не вижу никакого смысла в PostgreSQL на Windows (PostgreSQL во многом проектировался под Linux).
И там никак не 30%. Возможно разница в способе замеров (perf vs postgresql), или 1С реально использует ну СЛИШКОМ много временных таблиц (что вполне возможно).
У 1С все просто, для каждого набора записываемых записей (N штук) она сначала создает нужные временные таблицы, потом заполняет и делает Analyze , а после использования Drop
Код этого естественно генерируется платформой автоматом, а не разработчиком 1С
бывает выгоднее всего сначала записать ключи объектов во временную таблицу, а затем уже использовать ее для расчета конкретных значений при помощи JOIN
Описываемый сценарий подразумевает полный скан временной таблицы и присоединение к ней постоянных таблиц уже по их индексам. Но тоже самое можно реализовать через CTE
WITH PreCalc AS MATERIALIZED (
SELECT ... ),
FirstStage AS (
INSERT INTO ...
FROM PreCalc P
JOIN ...
RETURNING ...),
SecondStage AS (
UPDATE ...
FROM PreCalc P
WHERE ...
RETURNING ...)
INSERT INTO ...
FROM PreCalc P
JOIN ... ;может привести к повторению одних и тех же вычислений
Заодно решается и эта проблема, так результаты уже произведенных вычислений могут использоваться в последующих запросах.
Что помешало воспользоваться этим методом?
PostgreSQL и временные таблицы