Поделиться через

Устранение проблем с высокой загрузкой ЦП в Базе данных 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. Кроме того, она также позволяет получить сведения о текущем состоянии и активности каждого сеанса.

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

Давайте рассмотрим пример выходных данных из выполнения этой команды.

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, любые длительные SELECTs могут привести к непредсказуемости или волатильности на сервере базы данных. Размер инструкций и транзакций, а также количество используемых ресурсов продолжает расти в зависимости от увеличение базового набора данных. Из-за этого неограниченного роста инструкции и транзакции конечных пользователей занимают больше времени, потребляя все больше ресурсов, до тех пор, пока это не приведет к перегрузке сервера базы данных. При использовании несвязанных запросов SELECT рекомендуется настроить параметр max_execution_time таким образом, чтобы все запросы, превышающие эту длительность, прерваны.

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

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

Связанный контент

Stack Overflow