Анализ производительности и оптимизация Managed Service for MySQL®
К снижению производительности кластера Managed Service for MySQL® обычно приводят следующие проблемы:
- высокая утилизация CPU, дискового I/O и сети;
- неэффективное выполнение запросов в MySQL®;
- блокировки.
Инструменты мониторинга MySQL® и диагностики производительности кластера Managed Service for MySQL®, а также специальные запросы MySQL® помогут эти проблемы обнаружить.
Перед началом работы
- Выберите базы данных для диагностики.
- Активируйте сбор статистики.
- Создайте пользователя MySQL® с привилегией PROCESS для этих баз. Диагностические запросы необходимо выполнять от имени этого пользователя.
Диагностика дефицита ресурсов
Дефицит ресурсов — одна из вероятных причин падения производительности кластера. Дефицит ресурсов виден по графикам мониторинга кластера (CPU, дисковые операции I/O, сетевые соединения). Если постоянно росший график нагрузки на ресурс остановился на одном уровне, использование ресурса достигло лимита или выходит за границы гарантированного уровня обслуживания.
Причины повышенной загрузки ресурсов можно установить специальными запросами:
-
Чтобы оценить потребление Disk IO разными потоками MySQL®, выполните запрос:
SELECT t.name AS thread_name, t.processlist_user AS user, t.processlist_info AS query, t.processlist_time AS time, io.bytes AS bytes FROM performance_schema.threads t JOIN ( SELECT thread_id, sum(number_of_bytes) AS bytes FROM performance_schema.events_waits_history_long WHERE object_type='FILE' GROUP BY thread_id) io ON t.thread_id = io.thread_id ORDER BY io.bytes DESC
Запрос возвращает список файловых потоков MySQL®, упорядоченных по убыванию занимаемого объема памяти. Как правило, в начале списка находятся потоки, обслуживающие репликацию и буфер InnoDB
для кеширования таблиц и индексов. -
Чтобы оценить потребление ресурсов сети разными потоками MySQL®, выполните запрос:
SELECT t.name AS thread_name, t.processlist_user AS user, t.processlist_info AS query, t.processlist_time AS time, net.bytes/t.processlist_time AS avg_bytes, net.bytes AS total_bytes FROM performance_schema.threads t JOIN ( SELECT thread_id, Sum(variable_value) bytes FROM performance_schema.status_by_thread WHERE variable_name IN ('Bytes_sent', 'Bytes_received') GROUP BY thread_id ) net ON t.thread_id = net.thread_id WHERE t.processlist_time IS NOT NULL ORDER BY net.bytes DESC
Этот запрос возвращает статистику с момента запуска потоков, поэтому долгоживущие соединения (например, репликация) будут возвращаться в первых строках.
Следует обратить внимание на операции чтения и записи, работающие с большим числом строк. Они также могут вызывать повышенную нагрузку на сеть. В случае операций записи изменения в WAL будут переноситься на реплики и это дополнительно увеличит нагрузку на сеть.
-
Отследить потребление CPU отдельными запросами в MySQL® невозможно, но можно выявить неэффективно выполнявшиеся запросы (см. далее).
Диагностика неэффективного выполнения запросов
Чтобы выявить проблемные запросы в MySQL®, выполните запрос:
SELECT *
FROM sys.statement_analysis
LIMIT 10
Запрос возвращает 10 наиболее долго выполнявшихся запросов за всю историю сервера.
Следует обратить внимание на запросы с высокими значениями ROWS_EXAMINED
, ROWS_SORTED
или флагом FULL_SCAN
.
Подробнее об информации в выдаче см. в документации MySQL®
Диагностика наличия блокировок
Причиной низкой производительности кластера могут быть блокировки (locks), вызванные попытками одновременного доступа к одному и тому же ресурсу БД (таблице, строке).
Для диагностики проверьте очереди ожидания блокировок в запросах:
-
Очередь ожидания блокировок уровня таблиц:
SELECT * FROM sys.schema_table_lock_waits
-
Очередь ожидания блокировок уровня отдельных строк:
SELECT * FROM sys.innodb_lock_waits
Решение проблем
Найденные в результате диагностики проблемные запросы можно попробовать оптимизировать. Существует несколько способов оптимизации:
-
проанализировать план запроса (query plan) с помощью команды
EXPLAIN
и воспользоваться приемами по оптимизации запросов из документации MySQL® ; -
оптимизировать таблицы InnoDB
, чтобы снизить нагрузку на диск.
Если не удается ни оптимизировать найденные проблемные запросы, ни отказаться от них, можно поднять класс хостов.