Базы данных

Эффективное использование MySQL Performance Schema для повышения производительности

Для лучшего Производительность MySQL Я использую Performance Schema для анализа данных о времени выполнения, ожиданиях, блокировках, памяти и вводе-выводе непосредственно с помощью SQL. Таким образом, я быстрее выявляю причины медленной работы операторов и принимаю целенаправленные меры для Тюнинг и мониторинг, согласно [2][3][15].

Центральные пункты

Следующие основные принципы помогают мне эффективно использовать схему производительности.

Активация и компактная конфигурация с соответствующими приборами и потребителями
Дайджесты заявлений использовать для выявления дорогостоящих шаблонов и «горячих точек»
События ожидания, совместно анализировать блоки Locks и операции ввода-вывода, чтобы выявить реальные узкие места
Схема Sys как сокращение от «быстрых и практических выводов»
Итеративный Рабочий процесс: измерение, изоляция, изменение, повторное измерение

Включение и правильная настройка схемы производительности

Сначала я проверяю, есть ли performance_schema активна, поскольку в текущих версиях MySQL она, как правило, включена по умолчанию [1][12]. Если она отсутствует, я устанавливаю в [миклд]-блок my.cnf переменная performance_schema=ON и перезапускаю сервер. После этого я настраиваю приборы и потребители в соответствии с конкретными потребностями, а не оставляю все на максимальной мощности постоянно. Я сосредотачиваюсь на заявление/%, wait/% и соответствующие пути ввода-вывода, чтобы собирать значимые данные без лишних накладных расходов [6]. Перед началом новой серии измерений я очищаю соответствующие таблицы истории и начинаю с «чистого» База.

Быстрые результаты с помощью схемы Sys

Чтобы быстро сориентироваться, я часто обращаюсь к sys-Schema, поскольку она эффективно агрегирует исходные данные Performance Schema [13]. Таким образом, я за считанные минуты нахожу запросы, на долю которых приходится наибольшая часть времени выполнения. Я начинаю с самых загруженных запросов, проверяю представления файлового ввода-вывода и просматриваю сводки ожиданий для потоков. Как только я обнаруживаю «горячую точку», я возвращаюсь к исходным таблицам и уточняю анализ. Тот, кто проанализирует планы запросов, сможет с помощью подходящих Советы по работе с оптимизатором часто ощутимые уже через короткое время Выигрыши достичь.

Выбор подходящих инструментов и потребителей

Я начинаю с широкого обзора, но при этом держу наблюдение под контролем: сначала я активирую самые важные Инструменты для запросов, ожиданий и операций ввода-вывода, после чего я отключаю всё, что не даёт нужной информации [6]. Такие компоненты, как история событий и сводные таблицы, должны подкреплять вопросы, на которые я хочу получить ответы. Если речь идёт, например, о пиках задержки, я смотрю в сводка_ожиданий_событий_по_названию_события и сравните это с сводка_заявлений_о_мероприятиях_по_дайджесту. Если возникают задержки при вводе-выводе, я проверяю сводка_по_названию_события и table_io_waits_summary_by_table. Такой целенаправленный отбор позволяет свести накладные расходы к минимуму и при этом получить надежные Данные.

Даст-дайджесты: распознавание шаблонов, снижение нагрузки

С помощью дайджестов выписок я вижу, какие шаблоны постоянно требуют больших затрат времени, даже если отдельные запросы содержат различные литералы [17]. Я сортирую данные по общему времени, количеству выполнений и средней задержке, чтобы определить приоритеты. При этом я дополнительно использую Анализ журнала медленных запросов назад, чтобы не упустить редкие аномалии. Если в сводках обнаруживаются пики, я проверяю индексы, стратегии JOIN и порядок фильтров с помощью ПОЯСНИТЬ. Затем я проверяю эффективность, проводя повторные измерения в Performance Schema, чтобы результаты оптимизации оставались измеримыми.

Анализ событий ожидания, блокировок и операций ввода-вывода

Если запросы зависают, я проверяю таблицы Wait и Lock, чтобы определить фактическую Причина можно найти [3]. Если на одних и тех же таблицах работает много потоков, это указывает на table_lock-Ожидаю появления конкуренции. Если события ввода-вывода файлов демонстрируют высокую задержку, я проверяю систему хранения данных, кэширование, а также схемы запросов с помощью обширных сканирований. Если я обнаруживаю блокировки строк InnoDB, я анализирую «горячие» записи, продолжительность транзакций и степень покрытия индексами. Только когда все эти части пазла сложатся воедино, я приступаю к настройке параметров сервера, схемы или кода.

Мониторинг памяти: память и пул буферов

Я устраняю проблемы с памятью, анализируя данные о загрузке таблиц памяти и буферов InnoDB. Если потребность в памяти отдельных компонентов возрастает, я корректирую ограничения и проверяю, не удерживают ли кэши неверные данные. Если кэша InnoDB недостаточно, я увеличиваю его долю или улучшаю локальность запросов. Те, кто хочет углубиться в эту тему, могут воспользоваться Оптимизация буферного пула достичь значительного сокращения задержки. Я подтверждаю этот эффект с помощью Резюме-таблицы и отслеживай, движутся ли показатели LRU-Hits и время ожидания ввода-вывода в нужном направлении.

Итеративный диагностический процесс для повседневной работы

Я всегда работаю по четким циклам, чтобы не терять время и чтобы изменения оставались измеримыми [3]. Сначала я воспроизвожу проблему при контролируемой нагрузке. Затем я собираю результаты измерений в нескольких целенаправленных таблицах и выделяю наиболее заметные варианты. Затем я вношу изменения в то, что обещает наибольшую выгоду: индекс, запрос, параметр или код. В заключение я повторно провожу измерения и кратко документирую результаты. До/после-таблицы, чтобы команда сразу могла увидеть результат.

Примеры запросов: от исходных данных к решениям

Для типичных задач я записал краткие фрагменты кода SQL, которые использую прямо в повседневной работе. В таблице приведены примеры, которыми я часто пользуюсь, и их назначение. Я настраиваю фильтры, такие как LIMIT или ORDER BY в зависимости от конкретной ситуации. Главное — сначала сформулировать гипотезу, затем провести целенаправленный анализ и принять четкое решение. Таким образом я сохраняю целенаправленность анализа и избегаю лишних Загрузить.

Таблица(и) схемы производительности	Цель	Важные столбцы	Пример запроса
`сводка_заявлений_о_мероприятиях_по_дайджесту`	Найти дорогие образцы	`digest_text`, `count_star`, `sum_timer_wait`	`SELECT digest_text, count_star, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest ORDER BY sec_total DESC LIMIT 10;`
`сводка_ожиданий_событий_по_названию_события`	Точки с высокой загрузкой	`event_name`, `sum_timer_wait`	`SELECT event_name, sum_timer_wait/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY sec_total DESC LIMIT 10;`
`table_io_waits_summary_by_table`	Проверить ввод-вывод таблиц	`object_schema`, `object_name`, `read_timer_wait`	`SELECT object_schema, object_name, (read_timer_wait+write_timer_wait)/1e12 AS sec_total FROM performance_schema.table_io_waits_summary_by_table ORDER BY sec_total DESC LIMIT 10;`
`сводка_памяти_глобальная_по_названию_события`	Найти программы, потребляющие много памяти	`event_name`, `current_alloc`	`SELECT event_name, current_alloc/1024/1024 AS mb FROM performance_schema.memory_summary_global_by_event_name ORDER BY mb DESC LIMIT 10;`

Производственное предприятие: минимизация накладных расходов, максимизация эффективности

Во время живого выступления я не включаю инструменты наобум, а выбираю только то, что дает ответ на мой вопрос [6]. С событиями с высокой частотой я обращаюсь осторожно и стараюсь, чтобы окно истории оставалось кратким. Для более длительных наблюдений я предпочитаю сжатые сводки и сохраняю моментальные снимки во внешнем хранилище. Я обращаю внимание на запись в настройка потребителей performance_schema, чтобы я мог управлять коллекциями, а не просто позволять им работать в автоматическом режиме. Такой подход позволяет проводить анализ эффективный и обеспечивает защиту сервера.

Точная настройка: инструменты и потребительские решения для настройки на практике

Чтобы быстро получить достоверные результаты, я целенаправленно настраиваю приборы и потребители. Особое значение имеют заявление/%, wait/%, wait/io/% и — при необходимости — выбранные memory/%-пути. Сначала я активирую только самое необходимое, а затем расширяю набор, если у меня остаются конкретные вопросы, на которые я пока не нашел ответов. Таймеры в схеме производительности измеряют время в пикосекундах; для получения значений в секундах я делю значения в столбцах задержки на 1e12.

Типичная точка начала выполнения:

-- Включить ключевые инструменты
UPDATE performance_schema.setup_instruments
  SET ENABLED='YES', TIMED='YES'
  WHERE NAME LIKE 'statement/%'
 OR NAME LIKE 'wait/io/%'
 OR NAME LIKE 'wait/lock/%';

-- Выбор важных потребителей
UPDATE performance_schema.setup_consumers
  SET ENABLED='YES'
  WHERE NAME IN ('global_instrumentation',
 'thread_instrumentation',
 'statements_digest',
 'events_statements_current',
                 'events_statements_history',
 'events_waits_current',
 'events_waits_history');

-- Очистить сводки для новой серии измерений
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
TRUNCATE TABLE performance_schema.events_waits_summary_global_by_event_name;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_table;

Если мне нужен анализ памяти, я выборочно включаю memory/%-инструменты. Это требует дополнительных затрат, но оправдывает себя в случае утечек или сильной нагрузки на аллокатор.

Понятие «измерений»: понимание понятий «пользователь», «хост» и «схема»

Пиковые нагрузки зачастую носят не глобальный характер, а ограничиваются определенными Пользователь, Хозяева или Схема ограничено. Для этого схема производительности предоставляет сводки по каждой учетной записи и каждому хосту. Кроме того, в сводке я включаю столбец имя_схемы, чтобы ограничить количество «горячих точек» для каждой базы данных.

Примеры, которые я часто использую:

Лучшие схемы по общей продолжительности выполнения: SELECT schema_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_digest GROUP BY schema_name ORDER BY sec_total DESC LIMIT 10;
Пользователи/хосты, вызывающие наибольшую задержку (по учетным записям): SELECT user, host, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_statements_summary_by_account_by_event_name GROUP BY user, host ORDER BY sec_total DESC LIMIT 10;
Потоки с максимальным временем ожидания: SELECT thread_id, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_by_thread_by_event_name GROUP BY thread_id ORDER BY sec_total DESC LIMIT 10;

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

Отображение длительных транзакций и блокировок метаданных

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

Активные транзакции: SELECT thread_id, timer_wait/1e12 AS sec_running, state FROM performance_schema.events_transactions_current ORDER BY sec_running DESC LIMIT 10;
Обнаружение блокировок метаданных (конкуренция DDL/DML): SELECT event_name, SUM(sum_timer_wait)/1e12 AS sec_total FROM performance_schema.events_waits_summary_global_by_event_name WHERE event_name LIKE 'wait/lock/metadata/sql/mdl%' GROUP BY event_name ORDER BY sec_total DESC;

Если доминирует MDL, я перепроектирую окна DDL, сокращаю время удержания блокировок в коде (укорачиваю транзакции) и проверяю, нет ли лишних AUTOCOMMIT=0- Не следует оставлять сессии блоков открытыми дольше, чем это необходимо.

Репликация, резервное копирование и побочные эффекты: что нужно учитывать

Процессы репликации и резервного копирования отображаются в представлениях «Ожидания» и «Ввод-вывод». Задержки можно выявить с помощью статуса рабочих процессов и ожиданий файлов. Я анализирую рабочие процессы Applier, поток SQL и события файлового ввода-вывода:

Applier-Worker с высокой задержкой: SELECT worker_id, THREAD_ID, APPLYING_TRANSACTION, APPLYING_STATE FROM performance_schema.replication_applier_status_by_worker;
«Горячие точки» ввода-вывода файлов во время резервного копирования: SELECT event_name, (sum_timer_read + sum_timer_write) / 1e12 AS sec_total FROM performance_schema.file_summary_by_event_name ORDER BY sec_total DESC LIMIT 10;

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

Временные интервалы, моментальные снимки и стратегии сброса

Для измерений необходимы четкие временные интервалы. Чтобы сравнить „до“ и «после», я использую целенаправленные сбросы и моментальные снимки:

Сбросить сводки, чтобы получить новые интервалы: TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;
Сохранение моментального снимка на внешнем носителе: CREATE TABLE IF NOT EXISTS perf_snapshot_digest AS SELECT NOW() AS captured_at, * FROM performance_schema.events_statements_summary_by_digest;
Сохранять краткие исторические данные (потребитель), а данные о долгосрочных тенденциях собирать из внешних источников.

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

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

Распространенное предубеждение заключается в том, что схема производительности „слишком дорогая“. На практике я свожу накладные расходы к минимуму с помощью трёх мер: активирую только необходимые инструменты, сокращаю длительность часто используемых потребителей истории и правильно выбираю параметры памяти. При высокой дисперсии дайджестов я целенаправленно увеличиваю размер_сводных_данных_performance_schema а также — при необходимости — performance_schema_max_sql_text_length, чтобы идентичности оставались стабильными. Если требуются инструменты управления памятью, я ограничиваю их применение проблемными подсистемами.

Типичные регулировочные винты в my.cnf:

[mysqld]
performance_schema=ON
performance-schema-instrument='statement/%=ON'
performance-schema-instrument='wait/io/%=ON'
performance-schema-instrument='wait/lock/%=ON'
performance-schema-consumer-events-statements-history=ON
performance-schema-consumer-events-waits-history=ON
# Необязательно, если много шаблонов:
performance_schema_digests_size=10000
performance_schema_max_sql_text_length=4096

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

Распространенные проблемы и быстрые способы их устранения

Крупная сумма в сводка_заявлений_о_мероприятиях_по_дайджесту, много сканов: Проверьте индексы, порядок фильтров и возможность саргирования; подтвердите с помощью ПОЯСНИТЬ и повторить измерение (время переваривания должно заметно сократиться).
Доминанта table_io_waits в нескольких таблицах: Улучшить локализацию ввода-вывода (обращения к кластерным индексам, покрывающие индексы), сократить объем данных на один запрос, при необходимости использовать пакетную обработку вместо обработки всей таблицы.
Время ожидания wait/lock/innodb/%: Выявлять «горячие записи», устранять конфликты записи с помощью мелких транзакций, подходящих индексов или организации очередей.
Многие wait/lock/metadata/sql/mdl: Планирование окон DDL, ОНЛАЙН- отдавать предпочтение операциям, поддерживающим эту функцию, развязывать читатели и записывающие устройства с помощью более коротких транзакций.
Рост запасов в сводка_памяти_глобальная_по_названию_события: Ужесточить ограничения, целенаправленно ограничить кэши запросов, выявить проблемные компоненты с помощью memory/% подробно разбить по статьям.
„Спики“ в латентности при в остальном нормальных средних значениях: Используйте системные отчеты с процентилями и, при необходимости, отдельно измеряйте пиковые нагрузки (более узкое окно, короткая история, целевые инструменты).

Корреляция: от потока к оператору и ожиданию

Чтобы быстро установить взаимосвязь между причинами, я сопоставляю performance_schema.threads с помощью таблиц «Current» и «History» для операций и ожиданий. Так я могу увидеть, что последнее делал соответствующий поток и чего он ждет. Краткое описание процесса:

Пострадавшие PROCESSLIST_ID соответственно THREAD_ID с сайта performance_schema.threads забрать.
Последнее заявление через история_событий_заявлений определить (по THREAD_ID и сортировать по времени).
Параллельные ожидания из история_ожиданий_событий проверить, чтобы увидеть причины блокировки или ожидания ввода-вывода.

Этот шаблон „Drilldown & Join“ я использую в качестве стандарта, когда отдельные сессии или веб-запросы выходят из синхронизации.

Контрольные точки качества и непрерывное обеспечение эффективности

Чтобы оптимизации не пошли насмарку, я внедряю «бережливые» контрольные точки качества: перед и после каждого релиза запускаются заранее определенные запросы из Performance Schema. Я сохраняю моментальные снимки, сравниваю показатели (Top-Digests, Top-Waits, I/O на таблицу) и документирую отклонения. В CI/CD я добавляю репрезентативные профили нагрузки и пороговые значения для 95-го процентиля. Если какой-либо показатель выходит за пределы допустимого диапазона, запускается четкий алгоритм действий: проверка гипотезы, фокусировка инструментов, развертывание исправления, повторное измерение.

Избегать источников ошибок

Слишком много инструментов в долгосрочной перспективе: Диагностический режим является временным; в обычном режиме работы следует оставить активным только минимальный набор параметров.
Смешанные периоды измерения: Перед проведением новых тестов следует очистить сводки, иначе старые данные снизят их достоверность.
Неверная единица измерения времени: Время в таймере указывается в пикосекундах; это правило соблюдается на протяжении всего текста 1e12 Поделиться.
Поток дайджестов: Изменяющиеся литералы могут нарушить шаблон; нормализовать SQL и performance_schema_max_sql_text_length проверьте.
История слишком длинная: Частые события + длинная история создают нагрузку; окно истории должно быть коротким, снимки — внешние.

Практический чек-лист

Сформулировать вопрос, сформулировать гипотезу.
Использовать подходящие инструменты/привлечь потребителей, минимизировать накладные расходы.
Очистить сводки, выбрать короткий интервал измерения.
Проверить Top-Digests, Waits, I/O; подтвердить «горячие точки».
Целенаправленная настройка индекса/запроса/кода/параметров.
Провести повторные измерения, создать резервные копии, зафиксировать принятое решение.
Свести конфигурацию к минимальному рабочему уровню.

Вкратце: мой подход на практике

Я активирую схему производительности целенаправленно: сначала охватываю широкий спектр, а затем сужаю его до наиболее полезных элементов Инструменты [1][2][12]. Для быстрого обзора я использую схему Sys и, при необходимости, перехожу к исходным данным [13]. Сначала я выявляю «горячие точки» в дайджестах и событиях ожидания, прежде чем менять параметры [3][15][17]. Затем я подтверждаю каждое изменение новыми измерениями, чтобы прогресс оставался видимым и воспроизводимым. Таким образом я обеспечиваю стабильную надежность Время реагирования и избавлюсь от лишней работы.