О чем доклад

  • Зачем нужен мониторинг
  • Подход к решению проблем
    • Поиск проблем
      • ​Мониторинг с Prometheus, Grafana
      • Мониторинг with Anemometer
    • Определение причин низкой производительности
    • Подготовка и проверка решения
    • Внедрение решения и валидация результатов
  • Полезные советы
  • Ссылки

Зачем нужен мониторинг

"Ты не можешь управлять тем,
что не можешь измерить"

  • Как посмотреть текущее состояние дел?
  • Когда серверу станет совсем плохо?
  • Что будет на black friday?
  • Можно ли не делать ничего?

План по устранению проблем

Поиск проблем
Открываем ящик пандоры

Основные проблемы производительности

  • Процессор

  • Память

  • Диск: место, I/O

  • Сеть: большое количество данных, медленные клиенты

  • Конфигурация

Подход к решению проблем

Сложность: найти причину плохой производительности

Откуда получить данные о системе?

  • Performance Schema
  • Information Schema
  • Slow query log
  • SHOW
  • EXPLAIN
  • Метрики операционной системы:
    • Память
    • Диск
    • Сеть
  • ...

Слишком много
"сырых" данных

Визуализация + интерфейсы

Подход к решению проблем

Основные задачи системы мониторинга

  • Метрики нужно собирать, хранить, отслеживать в динамике
  • Унифицированный формат для метрик от разных источников
  • Query language (PromQL)
  • Инструменты для визуализации
  • Оповещения

/   Мониторинг с Prometheus, Grafana

Подход к решению проблем

Архитектура

/   Мониторинг с Prometheus, Grafana

Prometheus server

Targets
(exporters)

Data visualization and export:

  • Grafana
  • Prometheus web UI
  • API

Pull metrics

PromQL

  • Alertmanager
  • Pushgateway
  • Service discovery

Подход к решению проблем

Targets
(exporters)

Exporters

/   Мониторинг с Prometheus, Grafana

Exporter'ы - программы (GO) для сбора метрик из источников: операционная система, база данных (MySQL, PostreSQL), ...

Работает как процесс, слушает определенный порт и по HTTP запросу "выставляет" метрики.

mysqld_exporter может находиться на одной машине (контейнере), а мониторить другую (hello RDS).

...

mysqld_exporter может собирать разное количество метрик в зависимости от версии сервера MySQL и конфигурации.

Подход к решению проблем

Типы метрик

/   Мониторинг с Prometheus, Grafana

Counter

Gauge

Histogram

Количество 404 ошибок

Количество свободной оперативной памяти в текущий момент времени

Какой процент запросов было выполнено за время < 500 ms?

Подход к решению проблем

Визуализация данных

/   Мониторинг с Prometheus, Grafana

  • Провести анализ показателей
  • Установить точку отсчета
  • Настроить оповещения

Достаточно данных

Подход к решению проблем

Как найти проблемные запросы?

/   Мониторинг с Prometheus, Grafana

Подход к решению проблем

Slow log

/    Проблемные запросы

  • Файл на диске, например /var/lib/mysql/slow.log
  • Логирование может быть отключено
  • В лог попадают запросы, время выполнения которых > slow_launch_time
  • Читать возможно, но неудобно. Понять общую картину невозможно
# Time: 2020-05-25T20:50:53.399620Z
# User@Host: root[root] @  [192.168.240.2]  Id:  5333
# Query_time: 3.526603  Lock_time: 0.000147 Rows_sent: 20000  Rows_examined: 1987320 Thread_id: 5333 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 10469146 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 1987320 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2020-05-25T20:50:49.873017Z End: 2020-05-25T20:50:53.399620Z
SET timestamp=1590439849;
SELECT * FROM test_table_big WHERE number = '50' ORDER BY number LIMIT 20000;

...

Из нечитабельного текстового файла получаем конфету при помощи Anemometer !

Подход к решению проблем

Мониторинг с Anemometer и как он устроен?

/    Проблемные запросы

SELECT STATEMENT_DIGEST_TEXT('SELECT * FROM city WHERE ID = 130') AS digest;
  • Подготовка данных:
    • pt-query-digest (консольная утилита):
      • ​парсит slow log файл, сохраняет запросы в таблицу в отдельной базе данных
      • составляет по запросам дайджест (группировка однотипных запросов):
SELECT * FROM `city` WHERE `ID` = ?
  • Отображение данных: веб-интерфейс на PHP

Получаем проблемный SQL запрос

Подход к решению проблем

EXPLAIN

/     Проблемные запросы

  • А если нет?
  • SELECT ускорится? А INSERT?
  • Как измерить?
  • Почему запрос проблемный?

Достаточно добавить индекс

Подход к решению проблем

Почему возникает проблема?

/     Проблемные запросы

Как сделать быструю базу данных?

  • Место в памяти для быстрого доступа к данным
  • Общий ресурс
  • Размер буфера ограничен
  • Данные хранятся страницами (default 16KB)
  • Если места не хватает, менее востребованные страницы вытесняются

InnoDB buffer pool

Небольшой объем буфера (128 MB)  +  большое количество данных +
много запросов =
плохая производительность

Как правило, диск работает медленнее оперативной памяти

Подход к решению проблем

Создаем highload на ровном месте

2 таблицы:

CREATE TABLE `test_table_*` (
  `id` int NOT NULL AUTO_INCREMENT,
 
`number` tinyint DEFAULT NULL,
 
`string_short` varchar(5) DEFAULT NULL,
 
`string_long` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
ENGINE=InnoDB;

Table Rows Size
test_table_small ~ 100 000 ~ 70 MB
test_table_big ~ 4 000 000 ~ 2.4 GB

Нет индексов
(кроме PK)

/     Проблемные запросы

Можно ли с этим жить?

Buffer pool 128 MB

Подход к решению проблем

Table Query
test_table_small SELECT AVG(number)
FROM test_table_small
​WHERE number = ?
test_table_big SELECT *
FROM test_table_big
WHERE number = ?
ORDER BY number
LIMIT 20000

/     Проблемные запросы

Добавляем индекс

Но сначала замеряем производительность

Подход к решению проблем

Benchmarks

/     Проблемные запросы

Sysbench

  • Консольная утилита
  • Используется LuaJIT
  • мультипоточность из коробки
  • Кастомные тесты
  • Не только для MySQL и баз данных

Подход к решению проблем

Benchmarks, Sysbench

/     Проблемные запросы

> sysbench        [options]         [testname]         [command]

custom_insert_test

run

  • prepare
  • run
  • cleanup

Типичный тест состоит из команд:

event !!! LINK

call

Подход к решению проблем

--mysql-host=playground-db
--threads=1
--time=60

Результаты

/     Benchmarks, Sysbench

Operation Threads Events Time, s
SELECT 1 10 61.6
SELECT 10 46 74.4
INSERT 1 7205 60.0
INSERT 10 59382 59.83
Operation Threads Events Time, s
SELECT 1 48 61.3
SELECT 10 145 61.4
INSERT 1 12601 59.7
INSERT 10 46905 59.8
ALTER TABLE `playground-db`.`test_table_big`
ADD INDEX `idx_number` (`number` ASC) VISIBLE;

 

Посмотрим на buffer pool, CPU

Подход к решению проблем

Что еще можно сделать?

/     Проблемные запросы

Увеличить buffer pool size до 4 GB

Подход к решению проблем

Результаты

/     Проблемные запросы

Operation Threads Events Time, s
SELECT 1 704 60.01
SELECT 10 1260 60.12
INSERT 1 14158 59.66
INSERT 10 56767 59.83
Operation Threads До После ADD INDEX После buffer pool size 4 GB
SELECT 1 10 48 704
SELECT 10 46 145 1260
INSERT 1 7205 12601 14158
INSERT 10 59382 46905 56767

Общая таблица по events

Подход к решению проблем

Выводы

/     Проблемные запросы

  • Нет универсальных рецептов: у вас другой профиль нагрузки и данные
  • Заниматься мониторингом, установить baseline
  • По возможности проводить тесты до и после изменений (составить точный профиль нагрузки тяжело)
  • Не допускать попадания плохих запросов
  • Дважды подумать над CREATE TABLE

Подход к решению проблем

Tips and tricks

  • INVISIBLE индексы (MySQL 8.0)
  • Проверить и выключить неиспользуемые индексы (осторожно):
    SELECT * FROM sys.schema_unused_indexes;
  • Удалить избыточные индексы:
    SELECT * from sys.schema_redundant_indexes;
  • Префиксные индексы
  • Полное сканирование по таблицам:
    SELECT * from sys.schema_tables_with_full_table_scans;
  • Дайджест без anemometer:
    SELECT * FROM performance_schema.events_statements_summary_by_digest;

Tips and tricks

Не всегда получается оптимизировать. Другие варианты

  • Выполнение запросов в не пиковое время
  • Кеширование
  • Выполнение на реплике
  • Другая база данных может подойти лучше
  • ...

Спасибо

Ссылки