Ошибка 53200 нехватка разделяемой памяти

Обновлено: 06.07.2024

SQL Server In-Memory OLTP использует больше памяти, чем SQL Server, и делает это по-другому. Возможно, что объем памяти, установленный и выделенный для Выполняющаяся в памяти OLTP , станет недостаточным для растущих потребностей. В таком случае может возникнуть нехватка памяти. В этом разделе описывается восстановление из ситуации с нехваткой памяти. В статье Наблюдение и устранение неисправностей при использовании памяти вы найдете рекомендации, которые помогут вам избежать многих ситуаций нехватки памяти.

Темы данного раздела

устранить ошибки восстановления базы данных, возникающие из-за нехватки памяти

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

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

Временно закройте выполняющиеся приложения.
Закрыв одно или несколько выполняющихся приложений или остановив ненужные на данный момент службы, можно освободить используемую ими память для операции восстановления. Эти приложения можно будет перезапустить после успешного завершения восстановления.

Увеличьте значение MAX_MEMORY_PERCENT.
Если база данных, как и рекомендуется, привязана к пулу ресурсов, то память, доступная для операции восстановления, регулируется параметром MAX_MEMORY_PERCENT. Если значение слишком мало, восстановление завершится со сбоем. В этом фрагменте кода значение параметра MAX_MEMORY_PERCENT для пула ресурсов PoolHk увеличивается до 70 % от установленной памяти.

Если сервер выполняется на ВМ и не выделен, установите такое же значение MIN_MEMORY_PERCENT, как и MAX_MEMORY_PERCENT.
Дополнительные сведения см. в статье Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин.

Дополнительные сведения о максимальных значениях параметра MAX_MEMORY_PERCENT см в разделе Процент памяти, доступной для оптимизированных для памяти таблиц и индексов.

Увеличьте значение max server memory.
Дополнительные сведения о настройке параметра Макс. памяти сервера см. в разделе Параметры конфигурации сервера "Память сервера".

устранить влияния нехватки свободной памяти на рабочую нагрузку

Очевидно, проще всего вообще избегать ситуаций, связанных с нехваткой памяти. Помочь в этом может хорошее планирование и отслеживание. Однако даже самое хорошее планирование не гарантирует стабильной работы, и возникновение нехватки памяти всегда возможно. Для устранения этой ситуации необходимо выполнить два действия.

Откройте выделенное административное соединение

SQL Server предоставляет выделенное административное соединение (DAC). С помощью выделенного административного соединения администратор может обращаться к запущенному экземпляру ядра СУБД SQL Server для устранения неполадок на сервере, даже если сервер не отвечает на другие клиентские соединения. DAC доступны в программе sqlcmd и в среде SQL Server Management Studio.

Рекомендации по использованию DAC в SSMS или sqlcmd см. в разделе Диагностическое подключение для администраторов баз данных.

Примените действие по исправлению

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

Освобождение имеющейся памяти

Удаление неважных строк оптимизированных для памяти таблиц и ожидание выполнения сборки мусора

Можно удалить неважные строки из оптимизированной для памяти таблицы. Сборщик мусора делает объем памяти, используемый этими строками, доступным. Компонент In-memory OLTP выполняет сбор ненужных строк агрессивно. Однако долго выполняющаяся транзакция может помешать сбору мусора. Например, если имеется транзакция, которая выполняется в течение 5 минут, все версии строк, созданные из-за операций обновления или удаления во время выполнения транзакции, не подпадают под сборку мусора.

Переместить одну или несколько строк в таблице на диске

В следующих статьях TechNet представлены рекомендации по перемещению строк из таблиц, оптимизированных для памяти, в таблицы на диске.

Увеличение объема доступной памяти

Увеличение значения MAX_MEMORY_PERCENT для пула ресурсов

Если именованный пул ресурсов для таблиц в памяти еще не создан, то его необходимо создать и привязать к нему базы данных Выполняющаяся в памяти OLTP . Инструкции по созданию пула ресурсов и привязки к нему баз данных см. в разделе Привязка базы данных с таблицами, оптимизированными для памяти, к пулу ресурсов Выполняющаяся в памяти OLTP .

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

Увеличьте значение MAX_MEMORY_PERCENT.
В этом фрагменте кода значение параметра MAX_MEMORY_PERCENT для пула ресурсов PoolHk увеличивается до 70 % от установленной памяти.

Установка дополнительной памяти

В конечном счете наилучшим решением является установка дополнительной памяти. Если выбран этот вариант, то необходимо учитывать, что, скорее всего, также можно будет увеличить значение MAX_MEMORY_PERCENT (см. подраздел Изменение параметров MIN_MEMORY_PERCENT и MAX_MEMORY_PERCENT для существующего пула), так как SQL Server вряд ли будет нужно больше памяти, а это позволит выделить большую часть или даже всю установленную новую память пулу ресурсов.

Устранение ошибок выделения страниц, возникших из-за нехватки памяти при наличии достаточных ресурсов памяти

Для устранения этой ошибки необходимо включить регулятор ресурсов.

См. в разделе Включение регулятора ресурсов дополнительные сведения об ограничениях, а также рекомендации по включению регулятора ресурсов через обозреватель объектов, свойства регулятора ресурсов или Transact-SQL.

Рекомендации по использованию выполняющейся в памяти OLTP в среде виртуальных машин

Виртуализация серверов позволяет не только снизить расходы на приобретение и эксплуатацию, но и добиться большей эффективности ИТ-процессов благодаря оптимизации подготовки, обслуживания, доступности и операций резервного копирования или восстановления приложений. В результате недавних успехов в развитии технологий стало проще консолидировать сложные рабочие нагрузки базы данных с применением виртуализации. В этой статье приведены рекомендации по использованию выполняющейся в памяти OLTP для SQL Server в виртуализированной среде.

Предварительное выделение памяти

В виртуальной среде важными факторами для памяти являются более высокая производительность и расширенная поддержка. Необходимо иметь возможность как быстро выделять память виртуальным машинам в зависимости от их требований (пиковые и низкие нагрузки), так и исключить бесполезные траты памяти. Компонент Hyper-V Dynamic Memory делает выделение памяти между виртуальными машинами, выполняемыми на узле, и управление ею более гибким.

Некоторые рекомендации по виртуализации и управлению SQL Server необходимо скорректировать при виртуализации базы данных с таблицами, оптимизированными для памяти. При отсутствии оптимизированных для памяти таблиц есть две рекомендации.

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

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

Решение

Чтобы смягчить этот эффект, заранее выделите достаточную память, чтобы восстановить или перезапустить базу данных, а не минимальное значение, в расчете на то, что динамическая память выделит дополнительную память при необходимости.

ОШИБКА: нехватка разделяемой памяти
HINT: Возможно, следует увеличить параметр max_locks_per_transaction.

После использования pg_try_advisory_lock(
я делаю

ОШИБКА: нехватка разделяемой памяти
HINT: Возможно, следует увеличить параметр max_locks_per_transaction.

После использования pg_try_advisory_lock(
я делаю

А Что посоветуете для такого запроса

SELECT pg_advisory_unlock_all()
надо вызвать не перед началом обработки
а сразу после окончания обработки этих 25 записей.
у вас где то advisory_locks текут.

проще всего посмотреть в pg_locks сколько и чего у вас там висит может понятнее будет.

А Что посоветуете для такого запроса

поэтому вам надо посмотреть сначала на план запроса - на предмет того, что лимит пытается пользовать index scan + filter, а не накладывается потом на полный filter/ (т.е. строка вида
должна быть последней строкой следующего уровня вложенности плана в LIMIT (см. пример, который вам давали)

или можете руками организовать перебор id, с проверкой каждого на pg_try_advisory_lock(id) и отсечкой перебора сразу, как наберёте именно 25. (WITH RECURSIVE . )

автор
проще всего посмотреть в pg_locks сколько и чего у вас там висит может понятнее будет.

А не подскажете где это посмотреть, интересно было бы глянуть сколько у меня блокировок, сейчас тестирую все в один поток что будет при нескольких потоках непонятно

индексы,
То есть мне нужно сделать индексы на все поля которые идут после WHERE ? у меня там по двум полям идет выборка

trom
Или второй вариант который если я правильно понял выглядит так, сделать цикл с помощью клиентской проги и каждый раз выбирать по 1 записи записывая все в массив, тогда и индексы не нужны и все надежно будет ? Второй вариант мне нравиться больше но непонятно куда вставлять WITH RECURSIVE, я пока представляю это простейший цикл на 25 запросов к базе и запись всего в массив, мне пока нужен самый простой и надежный вариант, даже в ущерб производительности

trom

Или второй вариант который если я правильно понял выглядит так, сделать цикл с помощью клиентской проги и каждый раз выбирать по 1 записи записывая все в массив, тогда и индексы не нужны и все надежно будет ?
Второй вариант мне нравиться больше но непонятно куда вставлять WITH RECURSIVE, я пока представляю это простейший цикл на 25 запросов к базе и запись всего в массив, мне пока нужен самый простой и надежный вариант, даже в ущерб производительности

если по одной, и именно клиентом, то WITH RECURSIVE вам не нужно.
нужны PREPARE параметрические запросы вида

и гоняя их в цикле добираться до незалоченных ещё id

у вас есть опыт планирования и выполнения мелких тестов ?
вы можете на свои "вопросы" посмотреть как на задачу для микро-теста
который вам надо накидать на коленке за 5 (пять) минут
и гоняя его (микротест) руками, обдумать его результаты мозгом ?

не альтернативно здоровы ли вы ?
следует ли общаться с вами как с альтернативно разумным ?

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

Если правильно понял это пример того как узнать какие блокировки стоят и кто их вызвал ?

Тогда вопрос вот в перовм примере для конкретной таблицы, куда писать имя таблицы
простейщий запрос если имя таблицы "Table1" как будет выглядеть ?

-- relation IS NULL => как и ожидалось, никакой реальный релейшн с этими блокировками не связан
(поэтому вы в запросе предложенном tadmin ничего про advisory не увидите)

On 03/16/2018 04:00 AM, francis cherat wrote:
> Hello,
>
> we have got an error 53200 after sql statement
>
> 5 ERROR: 53200: out of memory
> 6 DETAIL: Failed on request of size 1572864.
> 7 LOCATION: AllocSetRealloc, aset.c:973
>
> in jboss logs we have got those errors
>
> org.jboss.logging.jboss-logging - 3.1.2.GA | ERROR: out of memory
> Détail : Failed on request of size 1572864.
> executing failed
> org.hibernate.exception.GenericJDBCException: could not extract ResultSet
>
> cluster configuration
> server_version | 9.3.16
> effective_cache_size | 12800MB
> shared_buffers | 384MB
> work_mem | 384MB
>
> Server configuration
> RHEL 6.5
> RAM : 16Go
> 2 CPUs
>
> Thanks for your feedback

Is there anything in the Postgres and/or system logs from the same time
that might shed on this?

there is no message in /var/log/messages

I don't think so, but i am not in my office. I can't connect to this server.

Is there anything in the Postgres and/or system logs from the same time
that might shed on this?

Доброго дня.
Сегодня получил такую ошибку в логах. До этого сервер крутился без рестарта где-то месяца три, база не то чтобы очень большая, но в некоторых таблицах сотни тысяч строк. Увеличил этот самый "max_locks_per_transaction", поднял "shared_buffers" и "work_mem", перезапустил postgres, проблема исчезла. Но навсегда ли?
Было:

Выполнение запроса к pg_locks вменяемой информации не дало (или я просто не умею читать между строк).

Собственно, вопрос следующий: куда копать, возможно, настроить доп. логирование? Я хочу точно понять, почему это появилось, дабы предотвратить дальнейшие рецидивы. Может, у меня кривые запросы, которые продуцируют дедлоки? Или просто увеличение объема бд требует увеличение объема выделенной памяти? Еще что-то?

This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited. The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g. query of a parent table with many children.

Не имеет отношения к размерам таблиц. Имеет отношение к размеру внутренней таблицы локов и количеству затронутых объектов (например, таблиц) в транзакции. Строго говоря: максимальное число локов объектов одновременно всеми активными транзакциями.
Не имеет никакого отношения к shared_buffers (не вычитается из него, а прибавляется сверху. shared_buffers - строго буфер страниц, даже без учёта структур данных по управлению этим буфером. Сегмент разделяемой памяти всегда больше shared_buffers) или work_mem или чему-то ещё (кроме как max_connections + max_prepared_transactions). Тем более не имеет никакого отношения к дедлокам.

Дефолтного значения обычно более чем достаточно. Может быть злоупотребляете партицированием.

Жил на Винде-7, имел забитый на 70% 500Гб SSD диск, все было хорошо. Но потребовалось установить Ubuntu. Ставил через WUBI, т.к. другие более подробные мануалы по установке ниасилил. Сейчас при старте компа появляется выбор из 2 операционок (как надо) и еще какой-то Граб (по ошибке поверил одному описанию установки, но это вроде не мешает). Параметры системы: Память 3,6 ГиБ, Диск 18,2 ГБ. По команде free -h выдает

гуглил - пишут, что можно настраивать postgres, чтобы он не был таким прожорливым. А я не знаю, может у меня настолько мало свободной памяти, что это уже не поможет? И можно ли теперь как-то добавить еще памяти в систему, или уже поздно, диск размечался при установке? Переставлять все по новой? И весь установленный уже софт и т.п.? Что скажете, доктора?

А я не знаю, может у меня настолько мало свободной памяти, что это уже не поможет?

У тебя свободно примерно 1.8 Гб, постгрес настроен на то, чтобы откушать сразу 2.2Гб.

Настроить Postgres можно, но лучше ответить на вопрос, почему у тебя так мало памяти доступно на сервере. И почему некруглое число.

Ты где сервер видишь? У него десктоп на 4 гига, доступно 3.6

Deleted ( 10.10.17 22:20:59 )
Последнее исправление: Deleted 10.10.17 22:21:22 (всего исправлений: 1)

наверни свапца, места хватает же пока

Спасибо, навернул 5 ГБ (!) свапца, теперь из 2 докер-контейнеров стартует первый, а второй валится (раньше было наоборот :)). Но сейчас я могу их оба запустить руками не через compose а через docker start (хотя может так запускается не все что нужно, буду смотреть дальше). И да, со свапцом оно стало время от времени виснуть наглухо, перезгружаюсь регулярно.

ППЦ! тебе постгрес - для одноэс?

Я поверил установщику, который по умолчанию запросил 18ГБ.

Сейчас то что делать? Имеет смысл параметры Постгреса крутить?

Действительно, куда могло место деться…

Где запускается постгрес, там и сервер. Пусть даже он развёрнут на ноутбуке под подушкой.

Со свапцом жилось тяжко, все время зависала система, и много из того что нужно вообще не стартовало. Только что докупил один модуль памяти на 8 гигов - и навскидку все стало гораздо веселее, и даже работает. Насчет все/не все буду дальше смотреть.

Вообще моя материнка умеет в 32 гига максимум (есть 4 слота, можно во все поставить по 8 гигов), есть возможность для апгрейда еще. Просто по умолчанию стояла 32 бит Винда, которая не умеет физически больше 4 гигов ОЗУ подключать, поэтому и было такое железо.

Читайте также: