Анализ производительности и оптимизация Managed Service for MySQL

К снижению производительности кластера Managed Service for MySQL обычно приводят следующие проблемы:

• высокая утилизация CPU, дискового I/O и сети;
• неэффективное выполнение запросов в MySQL;
• блокировки.

Инструменты мониторинга MySQL и диагностики производительности кластера Managed Service for MySQL, а также специальные запросы MySQL помогут эти проблемы обнаружить.

Перед началом работы

1. Выберите базы данных для диагностики.
2. Активируйте сбор статистики.
3. Создайте пользователя 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, чтобы снизить нагрузку на диск.

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