Share via

Устранение проблем с низкой памятью в База данных Azure для MySQL — гибкий сервер

  • Статья

ОБЛАСТЬ ПРИМЕНЕНИЯ: Отдельный сервер Базы данных Azure MySQL Гибкий сервер Базы данных Azure MySQL

Важно!

База данных Azure для MySQL один сервер находится на пути выхода на пенсию. Настоятельно рекомендуется выполнить обновление до База данных Azure для MySQL гибкого сервера. Дополнительные сведения о миграции на гибкий сервер База данных Azure для MySQL см. в статье "Что происходит с одним сервером База данных Azure для MySQL?"

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

Обратите внимание, что База данных Azure для MySQL гибкий сервер использует память для достижения максимально возможного количества попаданий в кэш. В результате использование памяти часто может колебаться в пределах от 80 до 90 % от доступной физической памяти экземпляра. Если проблем с ходом выполнения рабочей нагрузки запроса нет, это не вызывает беспокойства. Однако вы можете столкнуться с проблемами нехватки памяти по следующим причинам:

  • настроены слишком большие буферы;
  • выполняются неоптимальные запросы;
  • запросы выполняют объединение и сортировку больших наборов данных;
  • задано слишком большое максимальное количество подключений к серверу базы данных.

Большая часть памяти сервера используется глобальными буферами и кэшами InnoDB, которые включают такие компоненты, как innodb_buffer_pool_size, innodb_log_buffer_size, key_buffer_size и query_cache_size.

Значение параметра innodb_buffer_pool_size задает область памяти, в которой InnoDB кэширует таблицы базы данных и данные, связанные с индексом. MySQL пытается разместить в буферном пуле как можно больше данных, связанных с таблицами и индексами. Чем больше буферный пул, тем меньше операций ввода-вывода перенаправляется на диск.

Наблюдение за использованием памяти

База данных Azure для MySQL гибкий сервер предоставляет диапазон метрик для оценки производительности экземпляра базы данных. Чтобы получить более полное представление об использование памяти сервером базы данных, просмотрите метрики Процент памяти узла или Процент памяти.

Viewing memory utilization metrics.

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

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

Причины высокого уровня использования памяти

Рассмотрим еще несколько причин высокого уровня использования памяти в MySQL. Эти причины зависят от характеристик рабочей нагрузки.

Увеличение количества временных таблиц

MySQL использует "временные таблицы", которые представляют собой таблицы особого типа, предназначенные для хранения временного результирующего набора. Временные таблицы можно использовать несколько раз во время сеанса. Поскольку любые созданные временные таблицы являются локальными для сеанса, в разных сеансах могут быть разные временные таблицы. В рабочих системах с большим количеством сеансов, выполняющих компиляцию больших временных результирующих наборов, следует регулярно проверять глобальный счетчик состояния created_tmp_tables, который отслеживает количество временных таблиц, создаваемых в часы наибольшей нагрузки. Большое количество временных таблиц в памяти может быстро привести к низкой доступности памяти в экземпляре База данных Azure для MySQL гибком сервере.

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

Параметр Description
tmp_table_size Задает максимальный размер внутренних временных таблиц в памяти.
max_heap_table_size Задает максимальный размер, до которого могут увеличиваться созданные пользователем таблицы MEMORY.

Примечание.

При определении максимального размера внутренней временной таблицы в памяти MySQL учитывает меньшее из значений, установленных для параметров tmp_table_size и max_heap_table_size.

Рекомендации

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

  • Прежде чем увеличивать значение tmp_table_size, убедитесь, что ваша база данных правильно проиндексирована, особенно столбцы, участвующие в соединениях и сгруппированные по операциям. Использование соответствующих индексов для базовых таблиц ограничивает количество создаваемых временных таблиц. Увеличение значения этого параметра и параметра max_heap_table_size без проверки индексов может привести к тому, что неэффективные запросы будут выполняться без индексов и создавать больше временных таблиц, чем необходимо.
  • Настройте значения параметров max_heap_table_size и tmp_table_size в соответствии с потребностями рабочей нагрузки.
  • Если значения, заданные для параметров max_heap_table_size и tmp_table_size, слишком малы, временные таблицы могут регулярно переноситься в хранилище, увеличивая задержку в запросах. Вы можете отслеживать перенос временных таблиц на диск с помощью глобального счетчика состояния created_tmp_disk_tables. Сравнивая значения переменных created_tmp_disk_tables и created_tmp_tables, можно просмотреть количество созданных внутренних временных таблиц на диске и общее количество созданных внутренних временных таблиц.

Кэш таблиц

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

Рекомендации

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

  • Параметр table_open_cache указывает количество открытых таблиц для всех потоков. Увеличение этого значения увеличивает количество дескрипторов файлов, необходимых mysqld. Вы можете выяснить, нужно ли вам увеличить кэш таблиц, проверив переменную состояния opened_tables в счетчике отображения глобального состояния. Постепенно увеличивайте значение этого параметра в соответствии с рабочей нагрузкой.
  • Установка table_open_cache слишком низкой может привести к тому, что гибкий сервер База данных Azure для MySQL тратить больше времени на открытие и закрытие таблиц, необходимых для обработки запросов.
  • Слишком большое значение этого параметра может привести к использованию большего объема памяти и исчерпанию файловых дескрипторов в операционной системе, что приведет к отказу в подключении или сбою обработки запросов.

Другие буферы и кэш запросов

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

Сетевой буфер (net_buffer_length)

Размер сетевого буфера соответствует размеру буферов соединений и потоков для каждого клиентского потока и может увеличиваться до значения, указанного для max_allowed_packet. Если инструкция запроса большая, например, все операции вставки и обновления имеют очень большое значение, то увеличение значения параметра net_buffer_length поможет повысить производительность.

Буфер соединения (join_buffer_size)

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

Буфер сортировки (sort_buffer_size)

Буфер сортировки используется для выполнения сортировки для некоторых запросов ORDER BY и GROUP BY. Если в выходных данных SHOW GLOBAL STATUS отображается много проходов Sort_merge_passes в секунду, рассмотрите возможность увеличения значения sort_buffer_size для ускорения операций ORDER BY или GROUP BY, которые не могут быть улучшены с помощью оптимизации запросов или более эффективного индексирования.

Избегайте произвольного увеличения значения sort_buffer_size, если у вас нет сведений, указывающих на обратное. Память для этого буфера назначается для каждого подключения. В документации MySQL в статье "Системные переменные сервера" говорится, что в Linux существует два пороговых значения: 256 КБ и 2 МБ, и что использование больших значений может значительно замедлить выделение памяти. Таким образом, избегайте увеличения значения sort_buffer_size свыше 2 млн, так как снижение производительности перевесит любые преимущества.

Кэш запросов (query_cache_size)

Кэш запросов — это область памяти, используемая для кэширования результирующих наборов запросов. Параметр query_cache_size определяет объем памяти, выделенный для кэширования результатов запроса. По умолчанию кэш запросов отключен. Кроме того, кэш запросов устарел в версии MySQL 5.7.20 и удален в версии MySQL 8.0. Если кэш запросов в настоящее время включен в вашем решении, прежде чем отключить его, убедитесь, что в нем нет запросов.

Вычисление коэффициента попаданий в буферный кэш

Коэффициент попадания в кэш буфера важен в среде гибкого сервера База данных Azure для MySQL, чтобы понять, может ли буферный пул разместить запросы рабочей нагрузки или нет, и как правило большого пальца рекомендуется всегда иметь коэффициент попадания кэша буферного пула более 99 %.

Чтобы вычислить коэффициент попаданий в буферный кэш InnoDB для запросов на чтение, можно запустить команду SHOW GLOBAL STATUS и получить данные счетчиков Innodb_buffer_pool_read_requests и Innodb_buffer_pool_reads, а затем вычислить значение, используя формулу, приведенную ниже.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

Рассмотрим следующий пример.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

Используя приведенные выше значения, вычисление коэффициента попаданий в буферный кэш InnoDB для запросов на чтение приводит к следующему результату:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

В дополнение к коэффициенту попаданий в буферный кэш инструкций select, для любых инструкций DML запись в буферный пул InnoDB происходит в фоновом режиме. Однако, если необходимо прочитать или создать страницу, а чистых страниц нет, также необходимо сначала дождаться очистки страниц.

Счетчик Innodb_buffer_pool_wait_free подсчитывает, сколько раз это произошло. Значение Innodb_buffer_pool_wait_free больше 0 является явным индикатором того, что буферный пул InnoDB слишком мал, и требуется увеличение размера буферного пула или экземпляра для размещения операций записи, поступающих в базу данных.

Рекомендации

  • Убедитесь, что в базе данных выделено достаточно ресурсов для выполнения запросов. Иногда может потребоваться увеличить размер экземпляра для получения большего объема физической памяти, чтобы буферы и кэши соответствовали рабочей нагрузке.
  • Избегайте больших или длительных транзакций, разбивайте их на небольшие транзакции.
  • Используйте оповещения в разделе "Процент памяти узла", чтобы получать уведомления, если в системе будут превышены любые из указанных пороговых значений.
  • Используйте функцию анализа производительности запросов или книги Azure для выявления проблемных или медленно выполняющихся запросов, а затем оптимизируйте их.
  • Для серверов рабочей базы данных собирайте диагностические данные через регулярные интервалы, чтобы обеспечить их бесперебойную работу. В противном случае выполните диагностику и устраните обнаруженные проблемы.

Следующие шаги

Чтобы найти ответы на самые важные вопросы либо опубликовать новый вопрос или ответ, посетите Stack Overflow.