Устранение проблем с низкой памятью в База данных 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 гибкий сервер предоставляет диапазон метрик для оценки производительности экземпляра базы данных. Чтобы получить более полное представление об использование памяти сервером базы данных, просмотрите метрики Процент памяти узла или Процент памяти.
Если вы заметили, что использование памяти внезапно возросло, а объем доступной памяти быстро уменьшился, отслеживайте другие показатели, такие как Процент ЦП узла, Всего подключений и Процент операций ввода-вывода, чтобы определить, является ли причиной проблемы внезапный пик рабочей нагрузки.
Важно отметить, что для каждого подключения, установленного с сервером базы данных, требуется выделение некоторого объема памяти. В результате резкое увеличение количества подключений к базе данных может привести к нехватке памяти.
Причины высокого уровня использования памяти
Давайте рассмотрим некоторые другие причины использования большого объема памяти в 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
Рассмотрим следующие примеры.
show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name | Value |
| +--------------------------+-------+ |
| Innodb_buffer_pool_reads | 197 |
| +--------------------------+-------+ |
| 1 row in set (0.00 sec) |
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 для выявления проблемных или медленно выполняющихся запросов, а затем оптимизируйте их.
- Для серверов рабочей базы данных собирайте диагностические данные через регулярные интервалы, чтобы обеспечить их бесперебойную работу. В противном случае выполните диагностику и устраните обнаруженные проблемы.