RussianLDP Рейтинг@Mail.ru
WebMoney: 
WMZ Z294115950220 
WMR R409981405661 
WME E134003968233 
Visa 
4274 3200 2453 6495 

Глава 7. Исполнительные инструменты

7.1. Исполнительная инструментальная панель

Статистика работы сервера представления в графической инструментальной панели. Чтобы показать инструментальную панель, откройте вкладку запроса и затем нажмите Dashboard из области Performance боковой панели Navigator с выбранной вкладки Management.

Эта особенность требует MySQL Server 5.6 или выше.

Рис. 7.1. Performance: инструментальная панель

Content is described in the surrounding text.

Сетевой статус

Это подчеркивает статистику для сетевого трафика, посланного и полученного сервером MySQL по связям клиента. Точки данных включают Incoming Network Traffic, Outgoing Network Traffic и Client Connections.

Статус MySQL

Это подчеркивает основную статистику деятельности и работы сервера MySQL. Точки данных включают Table Open Cache, SQL Statements Executed, количество (в секунду) SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER и DROP.

Статус InnoDB

Это предоставляет обзор пула буферов InnoDB и активности диска, которая произведена механизмом хранения InnoDB. Точки данных разделены на три группы:

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

  • Usage

    • Read Requests: количество запросов логического чтения (в секунду) InnoDB к пулу буферов.

    • Write Requests: количество логических запросов записи (в секунду) InnoDB к пулу буферов.
    • Disk Reads: количество логических чтений, которые InnoDB не мог удовлетворить от пула буферов. В результате они должны были быть прочитаны с диска.
    • InnoDB Buffer Pool Usage: процент пула буферов InnoDB, который используется. Наведите курсор мыши на диаграмму, чтобы видеть дополнительную информацию, такую как Usage Rate и Pages Free.

  • Writes

    • Data Written: количество записей в файл журнала отката InnoDB.

    • Writes: количество физических записей в файл журнала отката InnoDB.
    • InnoDB Disk Writes: Наведите курсор мыши на динамический граф, чтобы видеть количество записей на диск за определенную секунду. Доступный диапазон включает прошлые 120 секунд.
    • Writing: Общая сумма данных (в байтах), записанных в файлы с использованием механизма хранения InnoDB.

  • Reads

    • Doublewrite Buffer Writes: количество операций doublewrite, которые были выполнены.

    • InnoDB Disk Reads: Наведите курсор мыши на динамический граф, чтобы видеть видеть количество чтения с диска за определенную секунду. Доступный диапазон включает прошлые 120 секунд.
    • Reading: Общая сумма данных (в байтах) прочитанная в операциях с файлами механизмом хранения InnoDB.

7.2. Отчеты о схеме Performance

Основанные на исполнительной схеме отчеты обеспечивают понимание операций по серверу MySQL через полезные отчеты высокого уровня. MySQL Workbench использует представления SYS на Performance Schema, чтобы произвести более 20 отчетов и помочь проанализировать исполнение ваших баз данных MySQL. Отчеты помогают проанализировать горячие точки IO, обнаружить дорогостоящие SQL-операторы и изучить статистика и метрики InnoDB. Для получения дополнительной информации о схеме SYS см. MySQL sys Schema.

Эта особенность требует MySQL Server 5.6 или выше.

Установка и конфигурация

GUI для формирования и точной настройки инструментовки Performance Schema. Первоначально это загружает вкладку Easy Setup, которая является достаточной для большинства пользователей. Чтобы позволить все доступные инструменты Performance Schema, наведите мышь на Fully Enabled и щелкните по кругу на панели.

Схема SYS встроена в MySQL Server 5.7 и выше, MySQL Workbench использует эту версию. Однако для MySQL Server 5.6 Workbench устанавливает свою собственную комплектную версию схемы SYS.

Эта особенность требует MySQL Server 5.6 или выше.

Размер сохраненного запроса определяется сервером MySQL.

Рис. 7.2. Настройка Performance Schema

Content is described in the surrounding text.

Нажим Show Advanced предоставляет методы, чтобы точно настроить инструментовку Performance Schema.

Рис. 7.3. Настройка Performance Schema: обзор

Content is described in the surrounding text.

Средства управления отчетом об исполнении

Данные отчета об исполнении могут быть просмотрены и экспортированы с использованием следующих средств управления:

  • Export: Экспортируйте все записи и связанные данные (и заголовки столбцов) из текущего отчета об исполнении, который включает все и запросы и значения. Открывает диалог файла для экспорта.

  • Copy Selected: Копирует одну запись и связанные данные (и заголовки столбцов) из текущего отчета об исполнении в буфер обмена системы.
  • Copy Query: Копирует SQL-запрос, который произвел отчет об исполнении в буфер обмена системы.
  • Refresh: Обновляет (перезагружает) отчет об исполнении.

Описание отчета об исполнении

Рис. 7.4. Отчет об исполнении: максимум I/0 в байтах

Content is described in the surrounding text.

Доступные отчеты об исполнении включают (это не исчерпывающий список):

  • Top File I/O Activity Report : Покажите файлы, выполняющие большую часть IO (в байтах).

  • Top I/O by File by Time: Покажите самое высокое использование IO файлом и время ожидания.
  • Top I/O by Event Category: Покажите самое высокое использование IO данных категориями событий.
  • Top I/O in Time by Event Categories : Покажите самых больших потребителей времени IO по категориям событий.
  • Top I/O Time by User/Thread: Покажите главных потребителей времени IO по пользователям и потокам.
  • Statement Analysis: Список запросов с присоединенной статистикой.
  • Statements in Highest 5 Percent by Runtime : Перечисляет 5% запросов с самым высоким временем выполнения (в микросекундах).
  • Using Temp Tables: Перечисляют все запросы, использующие временные таблицы, сортировка по самому большому количеству дисковых временных таблиц.
  • With Sorting: Перечислите все нормализованные запросы, которые сделали сортировки, и получали доступ к ним в следующем порядке: sort_merge_passes, sort_scans, sort_rows.
  • Full Table Scans: Список запросов, которые выполнили полное сканирование таблицы. Производительность запросов зависит от части where и если никакой индекс не используется, рекомендуется добавить индексы для больших таблиц.
  • Errors or Warnings: Запросы, которые подняли ошибки или предупреждения.
  • Schema Object Overview (High Overhead) : Показать счетчики типов объектов для каждой схемы. Это может занять много времени, чтобы выполнить на системах с большим количеством объектов.
  • Schema Index Statistics
  • Schema Table Statistics
  • Schema Table Statistics (with InnoDB buffer)
  • Tables with Full Table Scans: Находит таблицы, к которым получает доступ полное сканирование таблицы, сортируя их по количеству просмотренных строк (DESC).
  • Unused Indexes: Список индексов, которые никогда не использовались, начиная с запуска сервера или с начала сбора данных.
  • Waits by Time: Перечисляет ожидания события по их полному времени, игнорируя неработающие (это часто содержит большие значения).
  • Waits by User by Time: Перечисляет ожидания события по их полному времени, игнорируя неработающие (это часто содержит большие значения).
  • Wait Classes by Time: Перечисляет ожидания события по их полному времени, игнорируя неработающие (это часто содержит большие значения).
  • Waits Classes by Average Time: Перечисляет ожидания события по их полному времени, игнорируя неработающие (это часто содержит большие значения).
  • InnoDB Buffer Stats by Schema: Суммирует вывод INFORMATION_SCHEMA.INNODB_BUFFER_PAGE по схемам.
  • InnoDB Buffer Stats by Table: Суммирует вывод INFORMATION_SCHEMA.INNODB_BUFFER_PAGE по схемам и именам таблиц.

7.3. Статистика запроса

Вкладка результатов Query Stats редактора SQL использует данные Performance Schema, чтобы собрать ключевые статистические данные, собранные для выполненного запроса, такие как выбор времени, временные таблицы, индексы, соединения и т.д.

Требования

  • MySQL server 5.6 или выше.

  • Включенные Query, Collect Performance Schema Stats.
  • Включенная performance_schema с инструментовкой запросов.

Рис. 7.5. SQL Editor: статистика запроса

Content is described in the surrounding text.

Рис. 7.6. SQL Editor: статистика запроса с исполнительными графами схемы

Content is described in the surrounding text.

7.4. План Visual Explain

Visual Explain производит и показывает визуальное представление MySQL EXPLAIN при помощи расширенной информации, доступной в расширенном формате JSON.

Расширенный формат EXPLAIN доступен, начиная с сервера MySQL 5.6.5.

MySQL Workbench обеспечивает все форматы EXPLAIN для выполненных запросов, включая расширенный JSON, традиционный формат и визуальный план запросов.

Соглашения Visual Explain

Диаграмма Visual Explain ниже показывает визуальное представление следующего запроса:

SELECT CONCAT(customer.last_name, ', ', customer.first_name)
       AS customer, address.phone, film.title FROM rental
       INNER JOIN customer ON rental.customer_id = customer.customer_id
       INNER JOIN address ON customer.address_id = address.address_id
       INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
       INNER JOIN film ON inventory.film_id = film.film_id
       WHERE rental.return_date IS NULL AND
             rental_date+INTERVAL film.rental_duration
             DAY < CURRENT_DATE() LIMIT 5;

Рис. 7.7. Пример Visual Explain

Content is described in the surrounding text.

Порядок выполнения: снизу вверх и слева направо.

Графические соглашения

  • Стандартные рамки: таблицы.

  • Округленные рамки: операции вроде GROUP и SORT.
  • Обрамленные рамки: подзапросы.
  • Алмазы: соединения.

Текстовые соглашения

  • Стандартный текст ниже рамки: имя (или псевдоним) таблицы.

  • Полужирный текст ниже рамки: ключ/индекс, который использовался.
  • Число справа вверху: количество строк из таблицы после фильтрации.
  • Число слева вверху: относительная стоимость доступа к таблице (требует MySQL 5.7 или выше).
  • Число справа от алмазов вложенного цикла: количество строк, произведенных JOIN.
  • Число выше алмазов: относительная стоимость JOIN (требует MySQL 5.7 или выше).

Следующая таблица показывает связанные цвета и описания, используемые в диаграмме Visual Explain. Для получения дополнительной информации о сметах см. The Optimizer Cost Model.

Таблица 7.1. Информация о диаграмме Visual Explain

Имя системыЦвет Текст на визуальной диаграмме Соответствующая информация подсказки
SYSTEMГолубой Single row: system constant Очень низкая стоимость
CONSTГолубойSingle row: constant Очень низкая стоимость
EQ_REFЗеленый Unique Key Lookup Низкая стоимость: оптимизатор в состоянии найти индекс, который это может использовать, чтобы получить необходимые записи. Это быстро, потому что поиск по индексу непосредственно приводит к странице со всеми данными о строке
REFЗеленыйNon-Unique Key Lookup Низкая стоимость, если количество соответствий строкам маленькое, растет как увеличение количества строк
FULLTEXTЖелтый Fulltext Index SearchСпециализированный полнотекстовый поиск. Низкая стоимость для этого специализированного поиска
REF_OR_NULLЗеленый Key Lookup + Fetch NULL Values Низкая стоимость, если количество соответствий строк маленькое, растет как увеличение количества строк
INDEX_MERGEЗеленыйIndex Merge Средняя стоимость: ищет лучший выбор индекса в запросе, чтобы улучшить работу
UNIQUE_SUBQUERYОранжевый Unique Key Lookup into table of subquery Низкая стоимость: используется для эффективной обработки подзапроса
INDEX_SUBQUERYОранжевый Non-Unique Key Lookup into table of subquery Низкая стоимость: используется для эффективной обработки подзапроса
RANGEОранжевыйIndex Range Scan Средняя стоимость: частичный просмотр индекса
INDEXКрасныйFull Index Scan Высокая стоимость: специально для больших индексов
ALLКрасныйFull Table Scan Очень высокая стоимость: очень дорогостоящий для больших таблиц, но уменьшается для маленьких. Никакие применимые индексы не были найдены для таблицы, которая вынуждает оптимизатор искать каждую строку. Это может также означать, что диапазон поиска так широк, что индекс был бы бесполезен.
UNKNOWNЧерныйunknown Это значение по умолчанию в случае, если соответствие не может быть определено

Использование Visual Explain

Чтобы просмотреть план visual explain, выполните ваш запрос из редактора SQL и затем выберите подвкладку Execution Plan на вкладке результатов запроса. По умолчанию план выполнения "Visual Explain", но также имеет представление "Tabular Explain", которое подобно тому, что вы видели бы, выполняя EXPLAIN в клиенте MySQL.

7.5. Обучающая программа: использование Explain, чтобы улучшить производительность запросов

Эта обучающая программа описывает, как использовать отчеты Explain, чтобы определить местонахождение и зафиксировать проблематичные (медленные) запросы. Это использует DBT-3 database и начинается со следующего примера простого запроса.

SELECT * FROM orders
       WHERE YEAR(o_orderdate) = 1992 AND
       MONTH(o_orderdate) = 4 AND o_clerk LIKE '%0223';

Пример вопроса был сначала выполнен в визуальном редакторе SQL. Затем отчет Explain был произведен при нажатии в меню на Query и Explain Current Statement. Первоначальное сообщение показывает Visual Explain с информацией, которая появляется, когда вы перемещаете свое указывающее устройство над таблицей orders в полном сканировании таблицы.

Рис. 7.8. DBT-3 Explain: Visual Explain с Full Table Scan

Content is described in the surrounding text.

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

Рис. 7.9. DBT-3 Explain: Tabular Explain с Full Table Scan

Content is described in the surrounding text.

Вопросы об этом запросе:

  • Почему этот запрос производил полное сканирование таблицы?

  • Почему индексируемый столбец o_orderdate не применен как возможный ключ?

Смотря более внимательно, также заметьте, что индексированный столбец используется в выражении "WHERE YEAR(o_orderdate) = 1992 AND MONTH(o_orderdate) = 4", таким образом, индекс не используется. Чтобы использовать существующий индекс, можно приспособить запрос следующим образом.

SELECT * FROM orders
         WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30' AND
               o_clerk LIKE '%0223';

Обновленные результаты запроса в качестве примера в Visual Explain, в котором Index Range Scan заменяет Full Table Scan, произведенный последним примером запроса.

Рис. 7.10. DBT-3 Explain: Visual Explain с Index Range Scan

Content is described in the surrounding text.

Рис. 7.11. DBT-3 Explain: Tabular Explain с Index Range Scan

Content is described in the surrounding text.

Заметьте различия. Type, измененный с ALL на range, возможные ключи (и используемый ключ) изменены с NULL на i_o_orderdate, количество просмотренных строк изменено с 1.5 миллионов до приблизительно 33 тысяч. Это, конечно, та еще разница. Однако, просмотр 33 тысяч, возвращая всего 18, это слишком. Таким образом, акцент может быть перенесен на столбец o_clerk. Следующий пример запроса добавляет следующий индекс, который должен улучшить работу.

CREATE INDEX i_o_clerk ON orders(o_clerk);

Рис. 7.12. DBT-3 Explain: Tabular Explain с Index Range Scan и After Index

Content is described in the surrounding text.

Новый индекс не рассматривают как возможный ключ, потому что запрос ищет суффикс o_clerk и индексы не работают с суффиксами (хотя они действительно работают с префиксами). Вместо этого этот простой пример мог использовать clerk ID. Наладка запроса следующим образом показывает лучшие результаты.

SELECT * FROM orders WHERE o_orderdate BETWEEN '1992-04-01' AND '1992-04-30'
       AND o_clerk LIKE 'Clerk#000000223';

Рис. 7.13. DBT-3 Explain: Visual Explain с Index Range Scan и Full ID

Content is described in the surrounding text.

Рис. 7.14. DBT-3 Explain: Tabular Explain с Index Range Scan и Full ID

Content is described in the surrounding text.

Новый индекс o_clerk использован, запрос просмотрел 1546 строк вместо 32642, выполнение запроса улучшено с 0.281 до 0.234 секунд. Однако, EXPLAIN показывает, что этот запрос просматривает 1546 строк, чтобы возвратить 18. После рассмотрения запроса снова, предположим, что многостолбцовый индекс может удовлетворить условиям WHERE, который основан на обоих столбцах o_orderdate и o_clerk.

CREATE INDEX io_clerk_date ON orders(o_clerk, o_orderdate)

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

Теперь выполнение приспособленного запроса приводит к еще лучшим результатам. Приблизительно 18 строк просмотрены и возвращены, время выполнения примера запроса составляет 0.234 секунды.

Рис. 7.15. DBT-3 Explain: Visual Explain с Multiple-Column Index Range Scan

Content is described in the surrounding text.

Рис. 7.16. DBT-3 Explain: Tabular Explain с Multiple-Column Index Range Scan

Content is described in the surrounding text.

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

Таблица 7.2. Сравнение запросов в DBT-3 Explain

TypeВозможные ключи КлючПросмотренные строки Продолжительность (секунды) Дополнительная информация Строк возвращено
ВсеNULLNULL1.50M 1.201Используя where18
Диапазонi_o_orderdatei_o_orderdate 326420.281Используя условие индекса и where 18
Диапазонi_o_orderdate, i_o_clerk i_o_clerk15460.234 Используя условие индекса и where18
Диапазон i_o_orderdate, i_o_clerk, i_o_clerk_datei_o_clerk_date 180.234Используя условие индекса18

Поиск

 

Найди своих коллег!

Вы можете направить письмо администратору этой странички, Алексею Паутову. mailto:alexey.v.pautov@mail.ru