Устранение проблем с высокой загрузкой ЦП в Базе данных Azure для MySQL с гибким сервером
ОБЛАСТЬ ПРИМЕНЕНИЯ: 
База данных Azure для MySQL — отдельный сервер База данных Azure для MySQL — гибкий сервер
Внимание
База данных Azure для MySQL один сервер находится на пути выхода на пенсию. Настоятельно рекомендуется выполнить обновление до База данных Azure для MySQL гибкого сервера. Дополнительные сведения о миграции на гибкий сервер База данных Azure для MySQL см. в статье "Что происходит с одним сервером База данных Azure для MySQL?"
База данных Azure для MySQL гибкий сервер предоставляет ряд метрик, которые можно использовать для выявления узких мест ресурсов и проблем с производительностью на сервере. Чтобы определить, существует ли на сервере проблема высокой загрузки ЦП, отслеживайте такие метрики, как "Процент загрузки ЦП узла", "Всего подключений", "Процент памяти узла" и "Процент операций ввода-вывода". Иногда просмотр сочетания этих метрик даст аналитические сведения о том, что может привести к увеличению использования ЦП на вашем База данных Azure для MySQL гибком экземпляре сервера.
Например, это может быть резкое увеличение числа подключений, которые инициируют большое число запросов к базе данных, что в свою очередь приводит к увеличению загрузки ЦП.
Помимо просмотра метрик важно также отследить рабочую нагрузку, чтобы понять связана ли пиковая загрузка ЦП с одним или несколькими запросами.
Причины высокой загрузки ЦП
Пики ЦП могут возникать по различным причинам, в первую очередь из-за пиков подключений и плохо написанных запросов SQL или сочетания обоих:
Пик подключений
Увеличение подключений может привести к увеличению потоков, что, в свою очередь, может привести к увеличению загрузки ЦП, так как он должен управлять этими подключениями вместе с их запросами и ресурсами. Чтобы устранить всплеск подключений, проверьте метрику "Общее количество подключений" и дополнительные сведения об этих подключениях см. в следующем разделе. Можно использовать performance_schema для идентификации узлов и пользователей, подключенных к серверу, с помощью следующих команд:
Текущие подключенные узлы
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
Текущие подключенные пользователи
select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');
Плохо написанные SQL-запросы
Запросы, которые требуют много ресурсов для выполнения и сканирования большого количества строк без индекса, или запросы, которые выполняют временные сортировки вместе с другими неэффективными планами, могут привести к всплеску загрузки ЦП. Хотя некоторые запросы могут выполняться быстро в одном сеансе, они могут вызвать всплеск загрузки ЦП при выполнении в нескольких сеансах. Поэтому крайне важно всегда объяснить ваши запросы, которые вы записываете из списка процессов шоу и гарантирует, что их планы выполнения эффективны. Это можно сделать, убедив, что они сканируют минимальное количество строк с помощью фильтров или предложений, используют индексы и не используют большие временные сортировки вместе с другими плохими планами выполнения. Дополнительные сведения о планах выполнения см. в разделе "Формат выходных данных EXPLAIN".
Сбор сведений о текущей рабочей нагрузке
Команда SHOW (FULL) PROCESSLIST отображает список всех сеансов пользователей, подключенных к База данных Azure для MySQL гибкому экземпляру сервера. Кроме того, она также позволяет получить сведения о текущем состоянии и активности каждого сеанса.
Эта команда создает только моментальный снимок текущего состояния сеанса и не предоставляет сведения о предыдущих действиях во время сеансов.
Рассмотрим пример выходных данных после выполнения этой команды.
mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails;|
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)
Обратите внимание, что есть два сеанса, принадлежащих пользователю "adminuser" клиента, которые были инициированы из одного IP-адреса:
- Сеанс 24835 выполнял инструкцию SELECT в течение последних семи секунд.
- Сеанс 24837 выполняет инструкцию "show full processlist".
При необходимости может потребоваться завершить запрос, например отчет или запрос HTAP, который привел к пику использования ЦП рабочей нагрузки. Однако всегда учитывайте потенциальные последствия прерывания запроса, прежде чем предпринимать действия для уменьшения загрузки ЦП. В других случаях, если существуют длительные запросы, которые приводят к пиковой загрузке ЦП, настройте эти запросы таким образом, чтобы ресурсы были оптимально использованы.
Подробный анализ текущей рабочей нагрузки
Для получения точных сведений о состоянии сеанса, транзакции и запроса необходимо использовать по крайней мере два источника информации:
- Список процессов сервера из таблицы INFORMATION_SCHEMA.PROCESSLIST, к которой также можно получить доступ, выполнив команду SHOW [FULL] PROCESSLIST.
- Метаданные транзакции InnoDB из таблицы INFORMATION_SCHEMA. INNODB_TRX.
С информацией только из одного из этих источников невозможно описать состояние подключения и транзакций. Например, список процессов не сообщает о наличии открытой транзакции, связанной с любым из сеансов. С другой стороны, метаданные транзакции не отображают состояние сеанса и время, в течение которого они находятся в этом состоянии.
Следующий пример запроса, который объединяет сведения о списке процессов с некоторыми важными фрагментами метаданных транзакции InnoDB:
mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G
В следующем примере показаны выходные данные этого запроса:
*************************** 1. row ***************************
session_id: 11
user: adminuser
host: 172.31.19.159:53624
db: NULL
command: Sleep
time: 636
state: cleaned up
info: NULL
trx_started: 2019-08-01 15:25:07
trx_age_seconds: 2908
trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
*************************** 2. row ***************************
session_id: 12
user: adminuser
host: 172.31.19.159:53622
db: NULL
command: Query
time: 15
state: executing
info: select * from classicmodels.orders
trx_started: NULL
trx_age_seconds: NULL
trx_rows_modified: NULL
trx_isolation_level: NULL
Анализ этих сведений по сеансу приведен в следующей таблице.
| Область | Анализ |
|---|---|
| Сеанс 11 | Этот сеанс в настоящее время находится в состоянии простоя (спящего режима), никакие запросы не выполняются, в таком состоянии он находится в течение 636 секунд. В сеансе транзакция, открытая в течение 2908 секунд, изменила 17 825 792 строк и использует изоляцию REPEATABLE READ. |
| Сеанс 12 | В настоящее время сеанс выполняет инструкцию SELECT, она выполняется уже в течение 15 секунд. Запросы в сеансе не выполняются, как показывают значениями NULL для параметров trx_started и trx_age_seconds. Сеанс будет продолжать хранить границу сборки мусора в течение всего времени выполнения, если он не использует более свободную изоляцию READ COMMITTED. |
Обратите внимание, что если сеанс отображается в состоянии простоя, он больше не выполняет никаких инструкций. В этот момент сеанс завершил все предыдущие действия и ожидает новых инструкций от клиента. Однако сеансы в состоянии простоя по-прежнему потребляют некоторые ресурсы ЦП и используют память.
Перечисление открытых транзакций
Выходные данные из следующего запроса содержат список всех транзакций, выполняемых на сервере базы данных, в порядке времени начала транзакции, чтобы можно было легко определить, есть ли длительные и блокирующие транзакции, превышающие ожидаемую среду выполнения.
SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;
Понимание состояний потоков
Транзакции, которые приводят к более высокой загрузке ЦП во время выполнения, могут иметь потоки в различных состояниях, как описано в следующих разделах. Эта информация поможет лучше понять жизненный цикл запросов и различные состояния потоков.
Проверка разрешений или открытие таблиц
Это состояние обычно означает, что операция открытия таблицы занимает много времени. Как правило, эту проблему можно устранить путем увеличения размера кэша таблицы. Однако медленно открывающиеся таблицы также могут свидетельствовать о других проблемах, например о наличии слишком большого числа таблиц в одной базе данных.
Отправка данных
Несмотря на то, что это состояние может означать, что поток отправляет данные через сеть, оно также может указать, что запрос считывает данные с диска или из памяти. Это состояние может быть вызвано последовательным сканированием таблицы. Необходимо проверить значения innodb_buffer_pool_reads и innodb_buffer_pool_read_requests, чтобы определить, выполняется ли считывание с диска большого количества страниц в память. Дополнительные сведения см. в разделе "Устранение проблем с низкой памятью" на гибком сервере База данных Azure для MySQL.
Обновление
Обычно это состояние означает, что поток выполняет операцию записи. Проверьте метрику, связанную с вводом-выводом, в Мониторе производительности, чтобы лучше понять, какие операции выполняются в текущих сеансах.
Ожидание блокировки <lock_type>
Это состояние указывает, что поток ожидает вторую блокировку. В большинстве случаев это может быть блокировка метаданных. Необходимо просмотреть все остальные потоки и узнать, который из них вызывает блокировку.
Понимание и анализ событий ожидания
Важно понимать базовые события ожидания в системе MySQL, так как длительные ожидания или большое количество ожиданий в базе данных могут приводить к увеличению загрузки ЦП. В следующем примере показаны соответствующие команды и пример выходных данных.
SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)
Ограничение времени выполнения инструкций SELECT
Если вы не знаете о затратах на выполнение и время выполнения операций с базами данных, включающими запросы SELECT, любые инструкции SELECT, выполняемые в течение длительного времени могут привести к непредсказуемости или неустойчивости на сервере базы данных. Размер инструкций и транзакций, а также количество используемых ресурсов продолжает расти в зависимости от увеличение базового набора данных. Из-за этого неограниченного роста инструкции и транзакции конечных пользователей занимают больше времени, потребляя все больше ресурсов, до тех пор, пока это не приведет к перегрузке сервера базы данных. При использовании несвязанных запросов SELECT рекомендуется настроить параметр max_execution_time таким образом, чтобы все запросы, которые выполняются дольше указанного времени, прервались.
Рекомендации
- Убедитесь, что в базе данных выделено достаточно ресурсов для выполнения запросов. Иногда может потребоваться увеличить размер экземпляра, чтобы получить больше ядер ЦП для размещения рабочей нагрузки.
- Избегайте больших или длительных транзакций, разбивайте их на небольшие транзакции.
- По возможности запустите инструкции SELECT на серверах реплик чтения.
- Используйте оповещения в разделе "Процент ЦП узла", чтобы получать уведомления, если в системе будут превышены любые из указанных пороговых значений.
- Используйте функцию анализа производительности запросов или книги Azure для выявления проблемных или медленно выполняющихся запросов, а затем оптимизируйте их.
- Для серверов рабочей базы данных собирайте диагностические данные через регулярные интервалы, чтобы обеспечить их бесперебойную работу. В противном случае выполните диагностику и устраните обнаруженные проблемы.
Следующие шаги
Чтобы найти ответы на самые важные вопросы либо опубликовать новый вопрос или ответ, посетите Stack Overflow.