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

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

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

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

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

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

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

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

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

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

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

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

Кроме того, если журналы медленных запросов интегрированы с журналами 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

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

# 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 секунд, проверял более 443k строк и возвращал 126 строк результатов.

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

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

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

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

EXPLAIN <QUERY>

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

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

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

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

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:

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.

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

Чтобы перечислить первые 10 наиболее выполненных запросов к экземпляру гибкого сервера База данных 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:

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 учитывайте рекомендации, перечисленные в следующей таблице:

Устранение повышенных значений 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 для выявления проблемных или медленно выполняющихся запросов, а затем оптимизируйте их.

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

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