Возникла ситуация, когда есть сложно-функциональная система на postgres с хорошей нагрузкой, а сервер временно "относительно слабый", поэтому задача максимально долго на нем прожить.

Основная проблема пока с памятью (доступно около 18гб для СУБД и 8гб swap на достаточно медленном диске). Как я понимаю основными ее потребителями являются shared_buffers (8 гб, но тут никуда не денешься, диск очень медленный, а random чтений очень много) и work_mem + temp_buffers.

work_mem - как я понимаю, с одной стороны делать совсем маленьким нельзя, потому как при больших (с точки зрения данных) запросах можно перегрузить диск. С другой стороны, важно чтобы при пиковой нагрузке влезло в память + swap (кол-во одновременных запросов * work_mem + остальная память), потому как иначе будет OOM (в том числе Killer). Ну также желательно не сильно выходить за оперативную память, так как лучше external disk sort, чем тоже самое в swap'е (вот тут на самом деле вопрос). По идее конечно нужно "таргетировать" log_temp_files, то есть держать количество записей в нем не сильно большим, но тут больше вопрос о максимуме work_mem.

temp_buffers - вот с ними все веселее. По идее это память, после которой временные таблицы пойдут на диск. Самое плохое это то, что:
Temp buffers, once used within a particular backend process, are kept for the life of that process.
http://www.postgresql.org/message-id/8070.1374759702@sss.pgh.pa.us]http://www.postgresql.org/message-id/8070.1374759702@sss.pgh.pa.us
(правда подтверждение этой информации я нигде не нашел)

А вот это не очень хорошо, потому как по сути является "memory leak'ом". То есть если connection в моменте сформировал большую таблицу, а потом сразу ее удалил, все temp_buffers останутся за этим connection'ом на все время его существования. То есть если у вас 60 connection'ов и temp_buffers 128Mb, то через какое-то время все могут зажрать по 128Mb, то есть 7Gb, хотя при этом на практике в каждый момент используется не более 500Мб временных таблиц на все connection'ы. Если же поставить temp_buffers слишком маленьким, то таблицы начнут уходить на диск, а если там не SSD, то скорость может начать резко падать (можно конечно надеяться на кэш ОС, как и в shared_buffers, но это не сильно надежно, как мне кажется).

Вообще эту проблему предлагают решать connection pool'ингом, но тут неясно что делать с временными таблицами (если в них хранятся данные пользователя), потому как если кто-то забрал connection и начал там долгий процесс, "забрать" временные таблицы другому процессу и перенести их в другой connection a) во-первых дает слишком большой overhead , б) не всегда возможно из-за не thread-safety connection'ов.

Соответственно вопрос, можно ли как-то очистить temp_buffers кроме как перестартом connection'а? Discard temps помогает ли в этом вопросе (чуть меньшее зло чем перестарт connection'а)? Ну и вообще у кого какой опыт выставления work_mem \ temp_buffers или pool'инга на функционально сложных системах с нормальным использованием временных таблиц?

tadminNitro_Junkie,
если у вас swap _уже_используется, никакие припарки не помогут.

Вы имеете в виду, что если swap начал использоваться для текущих таблиц ("живых" данных), то туда начнут попадать и "живые" данные ОС и других критически важных процессов и все встанет в любом случае?

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

Я это собственно и написал. :) Просто у нас временные таблицы используются скажем для хранения текущих изменений пользователя, и непонятно как по другому делать. То есть можно конечно хранить на сервере приложений и для каждого объекта по отдельности гонять запрос, или загружать все во временную таблицу непосредственно перед каждым запросом, но и тот и тот способ просто убьет производительность. А ведь по большому счету проблема только в периодическом возврате temp_buffers обратно ОС. (собсно можно самому просто периодически пересоздавать connection, но не хочется такой ерундой заниматься, может есть другой способ)

Возможно, вам удастся заменить временные сессионные таблицы на обычные, если добавить в них ID клиента и учесть это в алгоритмах. Тогда pgBouncer в режиме pool_mode = transaction радикально уменьшит расход памяти.

Да, но дополнительные index scan'ы, блокировки, логи и много чего еще, чего не будет у временных таблиц сожрет весь выигрыш. Ведь каждый connection сам по себе не сильно жрет памяти (даже если это 5мб, то на 1к одновременно работающих пользователей всего 5гб, а такое количество пользователей для сложной системы уже соответствует вполне конкретной корпорации). Или я ошибаюсь?

Что у вас за сервер такой, что нельзя памяти добавить или SSD диск воткнуть?

Там чисто локальная ситуация. Ну и спортивный интерес для меня даже важнее. :)

Maxim BogukConnection pooling c разумным лимитом коннектов + разумный temp_buffers (8-16MB).

Для одновременно работающих пользователей и использованием временных таблиц за пределами транзакций? А где тогда локальные данные пользователей хранить, если к ним надо обращаться в процессе работы пользователя?

Maxim BogukPS: "Temp buffers, once used within a particular backend process, are kept for the life of that process." касается и work_mem тоже кстати.

Из той же ссылки:
Memory consumed for work_mem will be
released back to libc at the end of the query. The net effect of that
is platform-dependent --- my experience is that glibc on Linux is able
to give memory back to the OS, but on other platforms the process memory
size doesn't shrink.

А вообще как по вашему, если обнаруживаем, что в connection'е была много \ большие временные таблицы, а сейчас нет (есть немного маленьких таблиц), техника - забрать те что есть таблицы на сервер приложений, пересоздать connection (ну точнее взять из пула "пустых" connection'ов), залить туда считанные таблицы, а старый connection закрыть, решит проблему поедания памяти connection'ами? Или есть еще какие-то подводные камни, которые я упускаю?

Maxim_Boguk,

И вообще по вашему опыту, "слабоактивный" connection насколько много жрет ресурсов?

Maxim_Boguk,

Ну и заодно, а можно средствами postgres (не обращаясь к ОС) узнать сколько процесс сожрал памяти (в данном случае речь о work_mem идет, temp_buffers можно если что и самому прикинуть)?

tadminТо у вас памяти совсем нет, то вам и 5Гб на коннекты погоды не делают.

Я имел ввиду, что если бы postgres возвращал temp_buffers и work_mem оперативно, мне бы и 2-3гб грубо говоря хватило (1к пользователей и 5гб памяти я для примера привел, сейчас речь идет о 70 где-то пользователях). Соответственно вопрос как его заставить это делать. Pool'инг не вариант, соответственно вопрос насколько пересоздание connection'а поможет?

Maxim BogukА не проще ли закрывать коннект как только в нем нет необходимости (т.е. клиент закончил текущую работу)? Память вот сразу и почистится.

Так в бизнес-приложениях клиент весь день непрерывно работает.

Но вообще идея про 5000коннектов она больная (производительнось базы начинает падать после 100 коннектов а уж после 1000 так совсем ниже плинтуса), т.е. держать по коннекту на клиента - оно работать не будет нормально за пределами 10-20-50 клиентов.
Ну на 70 активных коннектах, и еще 30 пассивных коннектах (с активностью раз в час), пока ничего особенного не наблюдаю. (то есть были несколько раз ООМ, но после отрезания памяти у других приложений, и уменьшения work_mem + temp_buffers все пока ок) Но возможно пока, потому как коннектов будет все больше и больше.

Но мне собсно и интересно где bottleneck на большом количестве коннектов, почему по сравнению с pool'г идет экспоненциальное падение производительности.

Maxim BogukА клиентские временные данные надо в клиенте держать а не в базе (им в базе просто нечего делать), заодно и обрывы соединения с базой будут нормально обрабатываться.

Ну смотрите, самый простой пример, есть "окно" товаров, который сейчас видит пользователь (допустим 150) и ему нужно видеть остаток на складе, который выбирается сверху. Пользователь меняет склад. Нужно отобразить ему остаток. Если товары лежит на клиенте, надо эти 150 товаров залить обратно на сервер, выполнить запрос, получить остатки. И время заливки может быть больше получения остатков, не говоря уже о доптраффике, пинге и т.п.

Или например нужно пользователю выполнить импорт, посмотреть данные, которые он импортировал, и потом сохранить. Когда данные хранятся в базе : а) серверу приложений можно отдавать только подмножества, которые ему нужны (то есть несколько раз по 100 записей чисто для предварительного просмотра), б) при нажатии сохранить не надо заливать данные назад на сервер. Ну и так далее, в таком духе.

Обрыв соединение с базой без причины мягко говоря очень редкое событие, чтобы его имело смысл брать в расчет.

AlexiusNitro_Junkie,

а что такого нужно во временных таблицах хранить каждому пользователю что в 8МБ памяти не влезает? учитывая то, что это какие-то "изменения".

Не надо. Просто если пользователь вдруг сделает один раз (!) сложный расчет на пару секунд, которому потребуется таблица на 32мб, то эти 32мб уже не вернутся в ОС. Хотя конечно, если сделать temp_buffers 8Мб, оставшиеся 24Мб может и не пойдут на диск из-за кэша ОС. Но гарантировать это тяжело, а в понимании пользователя провис два раз в день на 10 секунд, в его понимании означает что "программа тормозит". (тот же эффект как Stop-The-World в Java при не concurrent сборщик мусора, когда в итоге общее мнение пользователя, программа жутко тормозит, хотя на самом деле она раз в час подвисает на 3 секунды).

vyegorovБольшой кэш приводит к агрессивным чекпойнтам и добавленной нагрузке на ЦПУ. И как он может спасти от произвольных чтений? Если обращение идет к холодным блокам, то размер кэша не влияет, система полезет на диск. А вот насколько эти данные нужны после разового чтения еще вопрос.

При 18Гб общей памяти я бы понизил кэш до 4Гб.

При такой постановке непонятно зачем вообще shared_buffers нужен. Я так понимаю для гарантии, что его чтения в постгрес будут агрессивней кэшироваться (по сравнению с другими приложениями), ну и само чтение все же из shared_buffers быстрее, чем обращение к файловой системе, и чтение из кэша ОС.

tadminа где же тут сложные клиентские данные, которые приходится во временных таблицах держать?
Достаточно хранить в интерфейсе ID склада и передавать его на сервер. В ответ получать набор данных с товарами и остатками.


Еще раз. У вас есть список товаров отображаемый пользователю (150 штук), он определяется фильтром, порядком и некоторым "окном", до которого пролистал пользователь. Если вы опять будете его перечитывать, СУБД придется опять join'ть с группами товаров, упорядочивать и фильтровать для получения видимого "окна". При этом вам все равно это, по хорошему, надо материализовать во временную таблицу, чтобы ее можно было протолкнуть в подзапросы, если показатели которые надо рассчитать (остатки, цены и т.п.) не материализованны, так как Postgres сам этого делать не умеет, а будет полностью рассчитывать подзапрос для всей базы и только потом Join'ить (даже если вам сильно повезет, и он будет подзапрос выполнять для каждой записи, то скорость будет все равно очень низкой, по сравнению с проталкивание предиката). Если же у вас таблица уже на сервере, первый шаг можно полностью пропустить.

Но вообще могу дать вам проще пример. Пользователь ввел накладную, и изменил соглашение, надо пересчитать цены по всем строкам для нового соглашения, ваши действия?

идея в том, чтобы в приложении хранить вычисленный список id товаров, например. 150 id много места не займут. при изменении каких-то условий в приложении, запрашивать данные повторно по id.

Запрашивать предварительно загружая 150 id назад во временную таблицу на сервер? Или запрашивать для каждой записи? Я надеюсь вы понимаете, что во втором случае у вас скорость будет в раз 70 меньше?

Leonid KudryavtsevПо временный таблицам - не уверен, как это сделано в PostgreSQL, но например в Oracle 8i (про более новые не уверен), они _никак_ в плане кэширования от обычных таблиц на отличаются и это _нисколько_ не мешает. Ну будут грязные блоки висеть в shared pool (в Oracle), ну и пусть. Пока shared pool достаточен (есть свободные блоки) и нет чекпоинта - ну и пофиг.Потребуется место, ну будут _в_фоне_ записываться на диск - когда нибудь рассосутся. Если загрузка дисков не 100%, когда нибудь _в_фоне_ запишутся, опять таки, пока загрузка не критичная (систему не довели, что все "стоит колом"), процесс должен идти в _фоне_ и особо никому не мешать.

Описания в доке PostgreSQL как и чем отделяется temp_buffers от shared_buffers и как отличается поведение Background Writer для одного и другого - не нашел.

В PostgreSQL не так. Тут temp_buffers, не shared_buffers, только для временных таблиц. Это строго локальный не возвращаемый кэш.

В общем пока видится следующее решение проблемы. Мы такой же подход использовали для LRU кэшей в Java, и он показал достаточно высоку. эффективность. Там мы таргетировали память (old-gen), стараясь удержать ее в некоторых пределах. Основной операцией была очистка кэшей в соответствии с двумя приоритетами важность кэша \ время последнего обращения. Сам алгоритм на самом деле не важен, важно что есть некоторый параметр, увеличение которого линейно ускоряет сборку, а уменьшение замедляет. Соответственно, если использование памяти превышает верхний предел, параметр увеличивается, в противном случае уменьшается. Плюс ко всему есть критический порог, после превышения которого единовременно удаляется соответствующий процент LRU кэшей. После использования этого механизма у нас java heap space и stop-the-world практически полностью исчезли.

Теоретически, здесь можно применить похожий механизм, только операцией "очистки" будет перестарт соединения, а приоритетом, скажем комбинация из максимального единовременного объема использованных временных таблиц (таргетирование temp_buffers), объемов использованных данных в запросах (work_mem) и времени последнего рестарта (для перестраховки первых двух). Единственное, что этот механизм был бы существенно эффективнее (в смысле адаптивнее), если можно было бы как-то узнавать сколько всего postgres зажрал памяти (по всем процессам, может кто-то знает как это сделать ? ). Но с другой стороны в отличие от Java тут есть естественный стоппер для каждого connection'а в виде собственно work_mem и temp_buffers, что не позволяет резко уходить в критическую область использования памяти. Поэтому достаточно будет просто сделать период, раз во сколько времени выбирать самый не приоритетный connection и его перестартовывать.