Настройка производительности и обслуживание баз данных в службе "База данных Azure MySQL" с помощью sys_schema

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

Схема performance_schema MySQL, которая стала впервые доступна в MySQL 5.5, предоставляет способ инструментирования многих важных ресурсов серверов, таких как выделение памяти, хранимые программы, блокировка метаданных и т. д. Однако performance_schema содержит более 80 таблиц. Для получения необходимой информации часто требуется объединение таблиц в performance_schema, а также таблицы из information_schema. Созданная на основе performance_schema и information_schema схема sys_schema предоставляет большую коллекцию понятных представлений в базе данных только для чтения и полностью поддерживается в службе "База данных Azure для MySQL" версии 5.7.

views of sys_schema

В sys_schema есть 52 представления, каждое из которых имеет один из следующих префиксов:

  • Host_summary или операции ввода-вывода: задержки, связанные с операциями ввода-вывода.
  • InnoDB: состояние и блокировки буфера InnoDB.
  • Память: использование памяти узлом и пользователями.
  • Схема. информация, связанная со схемой, такая как автоматическое увеличение, индексы и т. д.
  • Statement: информация об инструкциях SQL. Это может быть инструкция, выполнение которой привело к полному сканированию таблицы или длительному времени запроса.
  • User: Потребляемые ресурсы, сгруппированные по пользователям. Примерами являются операции ввода-вывода файлов, подключения и память.
  • Wait: события ожидания, сгруппированные по узлу или пользователю.

Теперь рассмотрим некоторые общие шаблоны использования sys_schema. Для начала сгруппируем шаблоны использования в две категории: Настройка производительности и Обслуживание баз данных.

Настройка производительности

sys.user_summary_by_file_io

Операции ввода-вывода являются наиболее ресурсоемкими операциями в базе данных. Мы можем узнать среднюю задержку операций ввода-вывода, запросив представление sys.user_summary_by_file_io. При использовании подготовленного хранилища по умолчанию размером 125 ГБ задержка ввода-вывода составляет около 15 секунд.

io latency: 125 GB

Так как База данных Azure для MySQL масштабирует операции ввода-вывода в соответствии с хранилищем, после увеличения объема подготовленного хранилища до 1 ТБ задержка операций ввода-вывода уменьшается до 571 мс.

io latency: 1TB

sys.schema_tables_with_full_table_scans

Несмотря на тщательное планирование, многие запросы могут по-прежнему привести к сканированию всей таблицы. Дополнительные сведения о типах индексов и способах их оптимизации см. в следующей статье: Решение проблем с производительностью запросов. Полное сканирование таблиц является ресурсоемким и снижает производительность вашей базы данных. Самый быстрый способ поиска таблиц с полным сканированием — запросить представление sys.schema_tables_with_full_table_scans.

full table scans

sys.user_summary_by_statement_type

Чтобы устранить проблемы с производительностью базы данных, может быть полезно просто выявить события, происходящие внутри базы данных, а представление sys.user_summary_by_statement_type выведет необходимые сведения по типу инструкций.

summary by statement

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

Обслуживание базы данных

sys.innodb_buffer_stats_by_table

[!ВАЖНО]

Запрос этого представления может повлиять на производительность. Устранение неполадок следует запланировать на нерабочее время.

Буферный пул InnoDB находится в памяти и является основным механизмом кэширования между СУБД и хранилищем. Размер буферного пула привязан к уровню производительности. Его можно изменить, только выбрав другой номер SKU продукта. Как и с памятью в операционной системе, старые страницы выгружаются, чтобы освободить место для новых данных. Чтобы узнать, какие таблицы используют больше всего памяти буферного пула InnoDB, можно запросить представление sys.innodb_buffer_stats_by_table.

InnoDB buffer status

На рисунке выше видно, что, за исключением системных таблиц и представлений, каждая таблица базы данных mysqldatabase033, которая размещает один из сайтов WordPress, занимает 16 КБ или 1 страницу данных в памяти.

sys.schema_unused_indexes&sys.schema_redundant_indexes

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

unused indexes

redundant indexes

Заключение

Таким образом, sys_schema является мощным инструментом, который подходит и для настройки производительности, и для обслуживания базы данных. Обязательно воспользуйтесь этой функцией в Базе данных Azure для MySQL.

Дальнейшие действия

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