Share via

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

  • Статья

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

Важно!

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

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

  • Все запросы, выполняемые на База данных Azure для MySQL гибком экземпляре сервера?
  • определенного набора запросов?
  • определенного запроса?

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

Включение функций ведения журнала

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

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

Прежде чем начать трассировку длительных запросов, необходимо включить параметр slow_query_log с помощью портала Azure или Azure CLI. После включения этого параметра вы также должны настроить значение параметра long_query_time, указав количество секунд, в течение которых могут выполняться запросы, прежде чем они будут определены как "медленные". Значение параметра по умолчанию — 10 секунд, но его можно настроить в соответствии с Соглашением об уровне обслуживания для вашего приложения.

Azure Database for MySQL flexible server slow query log interface.

Хотя журнал медленных запросов является отличным инструментом для трассировки длительных запросов, существуют определенные сценарии, в которых он может оказаться неэффективным. Например, журнал медленных запросов имеет следующие недостатки.

  • Отрицательно влияет на производительность, если количество запросов очень велико или инструкция запроса очень велика. Измените значение параметра long_query_time соответствующим образом.
  • Может оказаться бесполезным, если вы также включили параметр log_queries_not_using_index, указывающий, что запросы журнала должны извлекать все строки. Запросы, выполняющие полную проверку индекса, используют преимущества индекса, но регистрируются, поскольку индекс не ограничивает количество возвращаемых строк.

Получение сведений из журналов

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

Azure Database for MySQL flexible server retrieving data from the logs.

Кроме того, если журналы медленных запросов интегрированы с журналами Azure Monitor с помощью журналов диагностики, вы можете выполнять запросы в редакторе для их дальнейшего анализа:

AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10

Примечание.

Дополнительные примеры, которые помогут вам приступить к диагностике журналов медленных запросов с помощью журналов диагностики, см. в разделе Анализ журналов в журналах Azure Monitor.

На следующем снимке показан пример медленного запроса.

# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @  [172.30.209.6]  Id: 735026
# Query_time: 25.314811  Lock_time: 0.000000 Rows_sent: 126  Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;

Обратите внимание, что запрос выполнился за 26 секунд, проанализировал более 443 тыс. строк и вернул 126 строк результатов.

Как правило, следует сосредоточиться на запросах с высокими значениями Query_time и Rows_examined. Однако если вы заметили запросы с высокими значениями Query_time, но всего с несколькими Row_examined, это часто указывает на наличие узкого места в ресурсах. В таких случаях следует проверить, нет ли регулирования операций ввода-вывода или загрузки ЦП.

Профилирование запроса

После определения конкретного медленно выполняющегося запроса можно использовать команду EXPLAIN и профилирование для получения дополнительных сведений.

Чтобы проверить план запроса, выполните следующую команду:

EXPLAIN <QUERY>

Примечание.

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

Помимо создания плана EXPLAIN для запроса можно использовать команду SHOW PROFILE, которая позволяет диагностировать выполнение инструкций, запущенных в текущем сеансе.

Чтобы включить профилирование и профилировать конкретный запрос в сеансе, выполните следующий набор команд:

SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;

Примечание.

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

Рассмотрим подробнее использование этих команд для профилирования запроса. Сначала включите профилирование для текущего сеанса, выполнив команду SET PROFILING = 1:

mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (27.60 sec)

Затем отобразите список всех доступных профилей запросов, выполнив команду SHOW PROFILES:

mysql> SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration    | Query                                              |
+----------+-------------+----------------------------------------------------+
|        1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
+----------+-------------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Наконец, чтобы отобразить профиль для запроса 1, выполните команду SHOW PROFILE FOR QUERY 1.

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000102 |
| checking permissions |  0.000028 |
| Opening tables       |  0.000033 |
| init                 |  0.000035 |
| System lock          |  0.000018 |
| optimizing           |  0.000017 |
| statistics           |  0.000025 |
| preparing            |  0.000019 |
| executing            |  0.000011 |
| Sending data         | 27.594038 |
| end                  |  0.000041 |
| query end            |  0.000014 |
| closing tables       |  0.000013 |
| freeing items        |  0.000088 |
| cleaning up          |  0.000020 |
+----------------------+-----------+
15 rows in set, 1 warning (0.00 sec)

Перечисление наиболее часто используемых запросов на сервере базы данных

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

Чтобы вывести список наиболее выполненных запросов к База данных Azure для MySQL гибкому экземпляру сервера, выполните следующий запрос:

SELECT digest_text AS normalized_query,
 count_star AS all_occurrences,
 Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
 Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
 Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
 Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
 Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS sum_rows_selected,
 sum_rows_examined AS sum_rows_scanned,
 sum_created_tmp_tables,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

Примечание.

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

Перечисление 10 самых дорогих запросов по общему времени выполнения

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

SELECT REPLACE(event_name, 'statement/sql/', '') AS statement, 
 count_star AS all_occurrences , 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency, 
 Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency, 
 Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time  , 
 sum_rows_affected AS sum_rows_changed, 
 sum_rows_sent AS  sum_rows_selected, 
 sum_rows_examined AS  sum_rows_scanned, 
 sum_created_tmp_tables,  sum_created_tmp_disk_tables, 
 IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables / 
 sum_created_tmp_tables * 100, 0))) AS 
 tmp_disk_tables_percent, 
 sum_select_scan, 
 sum_no_index_used, 
 sum_no_good_index_used 
FROM performance_schema.events_statements_summary_global_by_event_name 
WHERE event_name LIKE 'statement/sql/%' 
 AND count_star > 0 
ORDER BY sum_timer_wait DESC 
LIMIT 10;

Мониторинг сборки мусора InnoDB

Если сборка мусора InnoDB заблокирована или отложена, в базе данных может возникнуть значительная задержка очистки, которая может негативно повлиять на использование хранилища и производительность запросов.

Длина списка журналов сегментов отката InnoDB (HLL) измеряет количество записей об изменениях, хранящихся в журнале отката. Растущее значение HLL указывает на то, что потоки сборки мусора InnoDB (потоки очистки) не справляются с рабочей нагрузкой записи или что очистка заблокирована длительным запросом или транзакцией.

Чрезмерные задержки в сборке мусора могут иметь серьезные негативные последствия.

  • Системное табличное пространство InnoDB будет расширяться, тем самым ускоряя рост базового тома хранилища. Иногда системное табличное пространство может увеличиваться на несколько терабайт в результате заблокированной очистки.
  • Помеченные для удаления записи не будут удалены своевременно. Это может привести к увеличению табличных пространств InnoDB и предотвратить повторное использование подсистемой хранилища, занимаемого этими записями.
  • Производительность всех запросов может снизиться, а загрузка ЦП может увеличиться из-за роста структур хранилища InnoDB.

Таким образом, важно отслеживать значения, закономерности и тенденции HLL.

Поиск значений HLL

Вы можете узнать значение HLL, выполнив команду show engine innodb status. Значение будет указано в выходных данных под заголовком TRANSACTIONS:

mysql> show engine innodb status\G 
*************************** 1. row *************************** 
 
(...) 
 
------------ 
TRANSACTIONS 
------------ 
Trx id counter 52685768 
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle 
History list length 2964300 
 
(...)

Вы также можете определить значение HLL, запросив таблицу information_schema.innodb_metrics:

mysql> select count from information_schema.innodb_metrics  
    -> where name = 'trx_rseg_history_len'; 
+---------+ 
|  count  | 
+---------+ 
| 2964300 | 
+---------+ 
1 row in set (0.00 sec)

Интерпретация значений HLL

При интерпретации значений HLL учитывайте рекомендации, перечисленные в следующей таблице:

Value Примечания
Менее 10 000 Нормальные значения, указывающие на то, что темп сборки мусора не снижен.
От 10 000 до 1 000 000 Эти значения указывают на небольшую задержку в сборке мусора. Такие значения могут быть приемлемыми, если они остаются устойчивыми и не увеличиваются.
Более 1 000 000 Эти значения должны быть расследованы и могут потребоваться корректирующие действия

Устранение повышенных значений HLL

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

Например, чтобы получить информацию о транзакциях из таблицы information_schema.innodb_trx, выполните следующие команды:

select * from information_schema.innodb_trx  
order by trx_started asc\G

Подробные сведения в столбце trx_started помогут вычислить возраст транзакции.

mysql> select * from information_schema.innodb_trx  
    -> order by trx_started asc\G 
*************************** 1. row *************************** 
                    trx_id: 8150550 
                 trx_state: RUNNING 
               trx_started: 2021-11-13 20:50:11 
     trx_requested_lock_id: NULL 
          trx_wait_started: NULL 
                trx_weight: 0 
       trx_mysql_thread_id: 19 
                 trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(…)

Сведения о текущих сеансах базы данных, включая время, проведенное в текущем состоянии сеанса, см. в таблице information_schema.processlist. Например, в следующих выходных данных показан сеанс, который активно выполнял запрос в течение последних 1462 секунд:

mysql> select user, host, db, command, time, info  
    -> from information_schema.processlist  
    -> order by time desc\G 
*************************** 1. row *************************** 
   user: test 
   host: 172.31.19.159:38004 
     db: employees 
command: Query 
   time: 1462 
   info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
 
(...)

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

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

  • Избегайте больших или длительных транзакций, разбивайте их на небольшие транзакции.

  • Настройте innodb_purge_threads в соответствии с рабочей нагрузкой, чтобы повысить эффективность фоновых операций очистки.

    Примечание.

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

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

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

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

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

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