MySQL дает сбой при увеличении значения max_heap_table_size

Question 1

При наличии 120 ГБ памяти вполне возможно, что на вашем компьютере также имеется несколько физических процессоров и архитектура с неоднородным доступом к памяти (NUMA). Если это так, то у вас может быть много доступной памяти, но все равно, как ни странно, ее будет недостаточно.

Если я правильно понял, вы можете столкнуться с этой проблемой, когда MySQL пытается увеличить объем памяти, выделенной для таблицы MEMORY, и обнаруживает ошибку, связанную с тем, как Linux обрабатывает выделение памяти при работе на этой архитектуре — рассматривая только доступную память, которая находится на планках памяти, напрямую подключенных к одному конкретному ЦП, который был произвольно выбран в качестве «того», с которого будут обрабатываться запросы памяти, — несмотря на то, что другая физическая память свободна в других местах на материнской плате.

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

Исправление будет заключаться в изменении mysqld_safeскрипта путем добавления следующей строки:

cmd="/usr/bin/numactl --interleave all $cmd"

...немедленнопослеэта строка...

cmd="$NOHUP_NICENESS"

Причины объясняются здесь, в превосходной статье, изначально написанной для того, чтобы разобраться, почему серверы MySQL с этой архитектурой интенсивно перезагружались, несмотря на свободную память; однако это был симптом более серьезной проблемы, которую авторвпоследствииуказано, что «это не только проблема подкачки», которая, даже при отключенной подкачке, может включать «сбои в распределении памяти».

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Я, конечно, размышляю о том, применимо ли это к вашей системе, но это кажется стоящей возможностью. Я использовал именно это исправление, когда столкнулся с проблемой на новой машине на 128 ГБ, когда я пытался предоставить пул буферов InnoDB на 64 ГБ, и MySQL не смог найти 64 ГБ свободных на машине сничегоelse running. Когда я понял, что наибольшее значение, которое я мог бы успешно использовать, было меньше 1/4 от общей памяти в 16-ядерной машине с 4физическийпроцессоры... что-то вроде того, с чем вы, похоже, столкнулись... было, когда я сложил все воедино относительно сути проблемы.

Answer

При наличии 120 ГБ памяти вполне возможно, что на вашем компьютере также имеется несколько физических процессоров и архитектура с неоднородным доступом к памяти (NUMA). Если это так, то у вас может быть много доступной памяти, но все равно, как ни странно, ее будет недостаточно.

Если я правильно понял, вы можете столкнуться с этой проблемой, когда MySQL пытается увеличить объем памяти, выделенной для таблицы MEMORY, и обнаруживает ошибку, связанную с тем, как Linux обрабатывает выделение памяти при работе на этой архитектуре — рассматривая только доступную память, которая находится на планках памяти, напрямую подключенных к одному конкретному ЦП, который был произвольно выбран в качестве «того», с которого будут обрабатываться запросы памяти, — несмотря на то, что другая физическая память свободна в других местах на материнской плате.

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

Исправление будет заключаться в изменении mysqld_safeскрипта путем добавления следующей строки:

cmd="/usr/bin/numactl --interleave all $cmd"

...немедленнопослеэта строка...

cmd="$NOHUP_NICENESS"

Причины объясняются здесь, в превосходной статье, изначально написанной для того, чтобы разобраться, почему серверы MySQL с этой архитектурой интенсивно перезагружались, несмотря на свободную память; однако это был симптом более серьезной проблемы, которую авторвпоследствииуказано, что «это не только проблема подкачки», которая, даже при отключенной подкачке, может включать «сбои в распределении памяти».

http://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architecture/

Я, конечно, размышляю о том, применимо ли это к вашей системе, но это кажется стоящей возможностью. Я использовал именно это исправление, когда столкнулся с проблемой на новой машине на 128 ГБ, когда я пытался предоставить пул буферов InnoDB на 64 ГБ, и MySQL не смог найти 64 ГБ свободных на машине сничегоelse running. Когда я понял, что наибольшее значение, которое я мог бы успешно использовать, было меньше 1/4 от общей памяти в 16-ядерной машине с 4физическийпроцессоры... что-то вроде того, с чем вы, похоже, столкнулись... было, когда я сложил все воедино относительно сути проблемы.

Question 2

Документация дляразмер_таблицы_tmpговорит, что эта настройка — максимальный размер временной таблицы.в памятиПревышение этого размера не приводит к возникновению ошибок переполнения таблицы; оно приводит к изменению использования таблицы в памяти на использование таблицы MyISAM на диске.

Система зависает, потому что вы позволяете временным таблицам использовать гораздо больше памяти, чем им положено. То же самое с таблицами памяти.

Вам следует удалить tmp_table_sizeспособdown. Помните, что эта настройка не является общим максимумом. Это максимум для каждой временной таблицы. Если у вас есть 5 запросов, создающих огромную временную таблицу (скажем, чуть меньше 20 ГБ каждый), то теперь у вас есть временные таблицы, использующие до 100 ГБ вашей оперативной памяти. Добавьте 6-ю, и вы используете больше оперативной памяти, чем есть на сервере в целом.

Если ваши скрипты фактически используют механизм хранения MEMORY, то после записи данных в файл MyISAM вам следует рассмотреть возможность изменения этого. Если действительно нужна такая высокая производительность для временной таблицы такого размера, вам следует рассмотреть возможность получения более быстрого хранилища (например.Fusion-io,Вириденти др.) Если это излишне или слишком дорого, я бы по крайней мере рассмотрел SSD-накопители потребительского класса.

MyISAM работает лучше всего, когда его данные кэшируются в системной памяти (неиспользуемая оперативная память). Если ваши скрипты опустошают кэш каждый раз, когда они делают огромный запрос (используя движок MEMORY), производительность вашего MyISAM пострадает.

Подозреваю, что скрипт использует механизм хранения MEMORY для «улучшения» производительности, когда на самом деле убивает его. Если это временная таблица, она должна использовать временные таблицы, а ваш tmp_table_size должен быть намного меньше, заставляя его сохраняться на диске после того, как он нарушает tmp_table_size.

Значения по умолчанию для max_heap_table_size и tmp_table_size составляют 16 МБ. Если возможно, я бы предложил изменить настройки обратно на значения по умолчанию. Увеличивайте их небольшими шагами, отслеживая использование ресурсов (дисковый ввод-вывод, общее использование памяти, использование процессора и т. д.), пока не найдете настройки, которые подходят для вашего набора данных.

Answer